Access Report Broken 1

[dwg23]

Hello,
we have an old access data base that we use to keep track of build jobs and production schedules.
It was written in Access 2003 and now after opening it in 2013 one of the reports is broken.
when you click on the report to run it a box pops up asking you to input a Parameter Value, In this case it is the Start Month/Day of the Report, we would enter 1/1. You click OK and another box pops up asking for the End Month/Day and we would enter 3/31 as we report by the quarter, and again click OK. after clicking OK for the second time an error box pops up with this message.
" This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. try simplifying the expression by assigning parts of the expression to variables."

I can't get any simpler that 1/1!
any ideas on how to fix this?

 

[combo]

If it is a parameter for the underlying query, check the query and parameter type. You may need full date that has to be entered, in this case the year is missing. Also, you need to input date in proper format.

combo

 

[dwg23]

combo,
the query seems to be written to accept the input like it always has......
Here is the query, also what is the proper format to input the date? i have also tried inputting date as 01/01 and 03/31, also #01/01# and #1/1#

Thanks,
DWG23

SELECT wo.Customer, wo.Assembly, Sum(wo.Quantity) AS Qty, sum(wo.Quantity * wo.[BOM Labor]) AS CYBOMLabor, Sum(wa.[build_time]) AS CYBuildTime, (CYBOMLabor/CYBuildTime) AS [%Efficiency], sum(wo.[samples inspected]) AS SampInsp, sum(wo.[samples passed]) AS SampPass, sum(wo.[products tested]) AS ProdTest, sum(wo.[products passed]) AS ProdPass
FROM build_time_sum AS wa INNER JOIN [work order] AS wo ON wa.[Work Order] = wo.[Work Order]
WHERE ([wo].[Date Build Completed] between [Start Month/Day] & '/' & Year(Now()) and [End Month/Day] & '/' & Year(Now()) ) and wo.[work order] <> 'Overhead' and wo.assembly is not null and wo.customer is not null and wo.assembly <> '' and wo.customer <> ''
GROUP BY wo.Customer, wo.Assembly
ORDER BY wo.Customer, wo.Assembly;

 

[SkipVought]

None of the examples you posted is a date!

yyyy/Mm/Dd

Yrea, month, day ALL THREE!

 

[dwg23]

Skip,
if you look at the query you will see that it is asking for.
WHERE ([wo].[Date Build Completed] between [Start Month/Day] & '/' & Year(Now()) and [End Month/Day] & '/' & Year(Now()) )
seems like the date would work as input using the information on the line above.

Thanks,
DWG23

 

[combo]

All depends on your regional settings. Generally access should accept input in 1/1 format, without other characters.
You could make a copy of the query and test with full date ([Start Month/Day/Year]) input, fixed date (#1/1/2015#), other date sequence ([Start Day/Month/Year]). You could check text descriptor in access sql format, I have ", but we are in different regions.

combo

 

[SkipVought]

Dates are really numbers, like 42586, that is 42586 days since 12/31/1899.

In your query you must convert your mm/did/yyyy string to a number by de limiting with #mm/did/yyyy#

 

[dwg23]

combo,
Sorry in advance here as I am not an Access person. I changed query to
WHERE ([wo].[Date Build Completed] between #1/1/2015# and #3/31/2015# and wo.[work order] <> 'Overhead' and wo.assembly is not null and wo.customer is not null and wo.assembly <> '' and wo.customer <> ''
GROUP BY wo.Customer, wo.Assembly
ORDER BY wo.Customer, wo.Assembly;

and get Syntax error (Missing Operator) in query expression.
What did I miss?

 

[dwg23]

combo,
now changed the query to the below and get an Data Type Mismatch in criteria expression.
I also put brackets around the dates and get the same error as at the start of all this. The date popup comes up with the Parameter Box again.

SELECT wo.Customer, wo.Assembly, Sum(wo.Quantity) AS Qty, sum(wo.Quantity * wo.[BOM Labor]) AS CYBOMLabor, Sum(wa.[build_time]) AS CYBuildTime, (CYBOMLabor/CYBuildTime) AS [%Efficiency], sum(wo.[samples inspected]) AS SampInsp, sum(wo.[samples passed]) AS SampPass, sum(wo.[products tested]) AS ProdTest, sum(wo.[products passed]) AS ProdPass
FROM build_time_sum AS wa INNER JOIN [work order] AS wo ON wa.[Work Order] = wo.[Work Order]
WHERE ([wo].[Date Build Completed] between #1/1/2015# and #3/31/2015# ) and wo.[work order] <> 'Overhead' and wo.assembly is not null and wo.customer is not null and wo.assembly <> '' and wo.customer <> ''
GROUP BY wo.Customer, wo.Assembly
ORDER BY wo.Customer, wo.Assembly;

 

[combo]

Do you mean that you are still asked for a paramerer? If so, you should either have declared parameters either for this query or you have a query with parameter as one of input tables/queries.

Date and string format: try #31/3/2015# date and try to replace single quotes by double ones:
...and wo.[work order] <> "Overhead" and wo.assembly is not null and wo.customer is not null and wo.assembly <> "" and wo.customer <> ""

If this works, try to go back to earlier structure with:
WHERE ([wo].[Date Build Completed] between [Start Month/Day] & "/" & Year(Now()) and [End Month/Day] & "/" & Year(Now())


combo

 

[dhookom]

Can you confirm [Date Build Completed] is a date/time field, Work Order and Assembly are text fields?

What happens when you remove one criteria at a time?

I would always use controls on a form for providing filtering/criteria values. You can actually convert your user input to dates in the form to use in the query. IMO, parameter prompts are not acceptable.

Duane
Hook'D on Access
MS Access MVP

 

[dwg23]

dhookom,
All fields are correct, and when I remove the conditions one at a time it still fails.

combo,
When I run the below query the parameter box comes up to enter the info into and it fails with the "Expression Typed Incorrectly" error(note brackets around dates).
If I remove the brackets from around the dates I get a data type mismatch in criteria expression.

I did find an old copy of the database and the report ran fine on it, it just did not have any current data, so i appended the current data to the existing tables one at a time testing after each data import.
the query ran fine (minus current data) until I imported the data from the WO Assembly Time table. it then errored as it has been with the Expression error. I also noted that the build_time_sum in the from clause is a query if that makes a difference.


SELECT wo.Customer, wo.Assembly, Sum(wo.Quantity) AS Qty, sum(wo.Quantity * wo.[BOM Labor]) AS CYBOMLabor, Sum(wa.[build_time]) AS CYBuildTime, (CYBOMLabor/CYBuildTime) AS [%Efficiency], sum(wo.[samples inspected]) AS SampInsp, sum(wo.[samples passed]) AS SampPass, sum(wo.[products tested]) AS ProdTest, sum(wo.[products passed]) AS ProdPass
FROM build_time_sum AS wa INNER JOIN [work order] AS wo ON wa.[Work Order] = wo.[Work Order]
WHERE ([wo].[Date Build Completed] between [#01/01/2015#] and [#03/31/2015#] ) and wo.[work order] <> "Overhead" and wo.assembly is not null and wo.customer is not null and wo.assembly <> "" and wo.customer <> ""
GROUP BY wo.Customer, wo.Assembly
ORDER BY wo.Customer, wo.Assembly;

 

[combo]

Can you run build_time_sum query without problems?
Remove temporarily [tt]([wo].[Date Build Completed] between [#01/01/2015#] and [#03/31/2015#] ) and[/tt], do you still have error?

combo

 

[dhookom]

What is the SQL view of [build_time_sum].

You don't want [] around a static value.

and wo.assembly is not null and wo.customer is not null and wo.assembly <> "" and wo.customer <> ""

can be written

and wo.assembly & "" <> "" and wo.customer & "" <> ""

Duane
Hook'D on Access
MS Access MVP

 

[dwg23]

combo,
when running both the Build_time_sum query and the first query with out the ([wo].[Date Build Completed] between #01/01/2015# and #03/31/2015# ) and. I get the Data Type Mismatch Error.

dhookom,
Here is the build_time_sum query.

SELECT [work order], sum( [actual build time] ) AS build_time
FROM [WO Assembly Time]
GROUP BY [work order]
ORDER BY [work order];

Thanks to both of you for all the help!

 

[SkipVought]

"when running both the Build_time_sum query and the first query with out the ([wo].[Date Build Completed] between #01/01/2015# and #03/31/2015# ) and. I get the Data Type Mismatch Error."

Sure sounds like [Date Build Completed] is not a Date type field.

 

[dhookom]

When you view [work order].[Date Build Completed] in datasheet view, it should be right-aligned.

Duane
Hook'D on Access
MS Access MVP

 

[dwg23]

SkipVought,
Double checked and [Date Build Completed] is a date/time field.
dhookom,
[work order].[Date Build Completed] is right aligned.

 

[SkipVought]

Change your date strings to #yyyy/mm/dd# which is unambiguous.

 

[dwg23]

Still get the same error.
Thanks!