Working on my own fairly simple report for Job Costing which shows each job and the number of hours worked by each employee on that job from timesheets entered, with a total no of hours worked for the job.
Each job is attached to one of 3 offices, Manchester, Leeds or Liverpool
and these are set as job analysis codes
I am attempting to create a filter on this report that will only include jobs attached to the Manchester office, by going into Format/Filters and picking the table/field and using the operator = with the office name, which shows in the filter builder as
'tblJob'.'JobAnalysisName1'=Manchester
I'm obviously doing something wrong because without the filter the report runs fine albeit with all the jobs for all offices included, but when I try to run the report with this filter, it throws up an error message
ODBC SQL Execute call failed [Microsoft][ODBC Microsoft Access Driver]Too few parameters. Expected 5.
Can anyone help me please? Sage helpline won't help me because we don't have Sage Cover - was considered an unnecessary expense by my predecessor!
Thanks in advance
Lesley Anderton
Replies (7)
Please login or register to join the discussion.
an Office solution!
if the underlying database is Access, then you could use Access to create appropriate queries, and link directly to them in Excel, or use the Access report writer, or both.
the downside is that you would need to garner an understanding of the data dictionary.
An alternative
Mamut Software, which just won the Small business Software of the Year, has this functionality as standard. If it is a report that you require regularly you may find some benefits both in time and satisfaction by changing software.
Glad you got sorted. I too have had cause to look in the access tables to try and understand how it works
Do you use the work in progress reports?
We struggle to get anything useful from ours.
?
It is a long time since I have looked at the (infamous) sage line 50 report designer, but the error message suggests it is having a problem in passing the parameter to the database engine.
adding quotes around manchester may solve it, but if not, and if you can get into the filter builder, then you might try substituting
tblJob.JobAnalysisName1="Manchester"
i.e. remove the single quotes around the text to the left of the equals sign.
It may be that the problem is in how you are setting up the parameter in the report - it may be a simple syntax problem. I recall that the help files were sometimes helpful, so you could try help when in the report designer.
A quick search on the web indicates that there are lots of options if you want a training course, but little useful online help. However you might find someone who has had the relevant training and is willing to let you have a copy of the notes?
Quote marks?
Working on the basis that there might be a sinmple answer, should't Manchester be in quotation marks (try ' or ")
Let us know how you get on, I also have the misfortune to struggle with sage JC.