Expression trouble

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have been using my database for over 1 year and have been pulling a query
which uses a beginning date and ending date to pull my information.
I have had no trouble until May 4th. I had entered my information and went
on to 5-5-07 and then had to enter more information for May 4th. I've
entered more information all the way through current date. Now the problem I
have is in pulling a query for the whole month to date (5-1-07 through
5-24-07) it gives me an error message of: "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."

It's puzzling to me because I can pull the string of information of 5-1-07
through 5-3-07 and 5-5-07 through 5-24-07, without any problems. I have
reentered the dates in all records for 5-4-07 on the chance that there was a
entry error, but I still am having the problem.

Any idea of what I can do to correct the problem to get the information?
I'm thinking that as a last ditch effort to delete the records and reenter
them, I don't want to do it if I don't have to.

Thanks for the input.

Thanks
 
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Check that the "date" fields in the table are actually date/time datatype
and not just text that looks like a date.
 
Here's the SQL:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], Sum(([# YDS]*[W/S $ Concrete/CY])/[Sale $]) AS
[Concrete Cost to Ttl Sls %], Sum((([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost])/[Sale $]) AS [Dir Costs to Ttl Sls %], Sum(([Sale $]-(([#
YDS]*[W/S $ Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time
Returned Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg
MPG])*[Fuel Cost /Gal])+[Addt'l Prod Cost]))/[Sale $]) AS [Gross Profit %],
[Ticket Info].[Sale $], Sum([# YDS]*[W/S $ Concrete/CY]) AS [Concrete Cost],
Sum((([Driver Hrly Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time
Left Plant])*1440)) AS [Loaded Labor $], Sum((([Driver Hrly Rate]*(1+[Wage
Load])/60))*(([Time Returned Plant]-[Time Left Plant])*1440)/[Sale $]) AS
[Labor %], Sum(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal]) AS
[Fuel Cost/Load], Sum((([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal])/[Sale $]) AS [Fuel %], [Ticket Info].[Addt'l Prod Cost], Sum([Addt'l
Prod Cost]/[Sale $]) AS [Prod %], Sum(([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost]) AS [Total Cost], Sum([Sale $]-(([# YDS]*[W/S $
Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned
Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel
Cost /Gal])+[Addt'l Prod Cost])) AS [Gross Profit $], Sum((((([Driver Hrly
Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time Left
Plant])*1440))+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal]))/[Rndtrip Miles]) AS [Cost Per Mile], Sum(([Time Returned Plant]-[Time
Left Plant])*1440) AS [# Min on Order], Sum(([Time End Discharge]-[Time
Arrived Job])*1440) AS [# Min on Job Site], Sum((([Time End Discharge]-[Time
Arrived Job])*1440)/[# YDS]) AS [Mins/CY on Job], Sum(([Time Arrived
Job]-[Time Left Plant])*1440) AS [1-Way Travel-Time to Job], [Ticket Info].[#
YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket Info].[Retail $ /CY],
[Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket Info].DRIVER, [Ticket
Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load], Sum([Driver Hrly
Rate]*(1+[Wage Load]))/60 AS [Loaded Labor Rate per Min], [Ticket
Info].[Rndtrip Miles], Sum([Rndtrip Miles]/[SC Fleet Avg MPG]) AS [# Gal Fuel
Used]
FROM [Ticket Info]
GROUP BY [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], [Ticket Info].[Sale $], [Ticket Info].[Addt'l
Prod Cost], [Ticket Info].[# YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket
Info].[Retail $ /CY], [Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket
Info].DRIVER, [Ticket Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load],
[Ticket Info].[Rndtrip Miles]
HAVING ((([Ticket Info].DATE) Between [Beginning Date] And [Ending Date]));

Primary Key is on the Ticket Info Table "ID" field
Relationships is from Trucking/Driver Table - "DRIVER HRLY RATE" field
to Ticket Info Table - "DRIVER HRLY RATE" field
& from Customers Table - "CUSTOMER NAME" field to Ticket Info Table -
"CUSTOMER NAME" field

Just to clarify - the query works for everything before 5/4/07 and
everything after.
Thanks
 
[Ticket Info].DATE

Date is a reserved word so that "might" cause a problem. You could change
the name to something like TicDate; however, would mess up all your other
queries, forms, etc. Try putting brackets around it like so:

[Ticket Info].[DATE]

Still I doubt that's the problem. As it's pulling from only one table, it
isn't a join problem.

The query does quite a bit of math on the various fields. I could see a
divide by zero sneaking in or maybe a null value. I suggest running a select
query just on the date in question and seeing if any of the returned record
fields have a Null or 0 that could cause grief.

Another possibility is that the records for 5-4-07 has a time component. As
your are grouping by DATE, it might be causing multiple records for that date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

dsimpkins said:
Here's the SQL:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], Sum(([# YDS]*[W/S $ Concrete/CY])/[Sale $]) AS
[Concrete Cost to Ttl Sls %], Sum((([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost])/[Sale $]) AS [Dir Costs to Ttl Sls %], Sum(([Sale $]-(([#
YDS]*[W/S $ Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time
Returned Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg
MPG])*[Fuel Cost /Gal])+[Addt'l Prod Cost]))/[Sale $]) AS [Gross Profit %],
[Ticket Info].[Sale $], Sum([# YDS]*[W/S $ Concrete/CY]) AS [Concrete Cost],
Sum((([Driver Hrly Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time
Left Plant])*1440)) AS [Loaded Labor $], Sum((([Driver Hrly Rate]*(1+[Wage
Load])/60))*(([Time Returned Plant]-[Time Left Plant])*1440)/[Sale $]) AS
[Labor %], Sum(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal]) AS
[Fuel Cost/Load], Sum((([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal])/[Sale $]) AS [Fuel %], [Ticket Info].[Addt'l Prod Cost], Sum([Addt'l
Prod Cost]/[Sale $]) AS [Prod %], Sum(([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost]) AS [Total Cost], Sum([Sale $]-(([# YDS]*[W/S $
Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned
Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel
Cost /Gal])+[Addt'l Prod Cost])) AS [Gross Profit $], Sum((((([Driver Hrly
Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time Left
Plant])*1440))+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal]))/[Rndtrip Miles]) AS [Cost Per Mile], Sum(([Time Returned Plant]-[Time
Left Plant])*1440) AS [# Min on Order], Sum(([Time End Discharge]-[Time
Arrived Job])*1440) AS [# Min on Job Site], Sum((([Time End Discharge]-[Time
Arrived Job])*1440)/[# YDS]) AS [Mins/CY on Job], Sum(([Time Arrived
Job]-[Time Left Plant])*1440) AS [1-Way Travel-Time to Job], [Ticket Info].[#
YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket Info].[Retail $ /CY],
[Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket Info].DRIVER, [Ticket
Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load], Sum([Driver Hrly
Rate]*(1+[Wage Load]))/60 AS [Loaded Labor Rate per Min], [Ticket
Info].[Rndtrip Miles], Sum([Rndtrip Miles]/[SC Fleet Avg MPG]) AS [# Gal Fuel
Used]
FROM [Ticket Info]
GROUP BY [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], [Ticket Info].[Sale $], [Ticket Info].[Addt'l
Prod Cost], [Ticket Info].[# YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket
Info].[Retail $ /CY], [Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket
Info].DRIVER, [Ticket Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load],
[Ticket Info].[Rndtrip Miles]
HAVING ((([Ticket Info].DATE) Between [Beginning Date] And [Ending Date]));

Primary Key is on the Ticket Info Table "ID" field
Relationships is from Trucking/Driver Table - "DRIVER HRLY RATE" field
to Ticket Info Table - "DRIVER HRLY RATE" field
& from Customers Table - "CUSTOMER NAME" field to Ticket Info Table -
"CUSTOMER NAME" field

Just to clarify - the query works for everything before 5/4/07 and
everything after.
Thanks
--
dsimpkins


Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Check that the "date" fields in the table are actually date/time datatype
and not just text that looks like a date.
Click to expand...
Click to expand...
 
Jerry,

It's funny you should mention the time calculations. My supervisor has
decided to eliminate those calculations, so they shouldn't be a factor
anymore. We don't have any situations where the time calculations were
impacting the dates.
I tried to pull an inquiry for just the one day and I can't get it to
respond. I keep getting that error message.
I thought of the null value before my first message and did catch one cell
that was lacking information and corrected the cell but it didn't correct the
problem.
I just tried a new query for the 4th and it pulled the information this
time. It must have been something in those time cells.
Thanks for the help
Have a good rest of the week.

--
dsimpkins


Jerry Whittle said:
[Ticket Info].DATE

Date is a reserved word so that "might" cause a problem. You could change
the name to something like TicDate; however, would mess up all your other
queries, forms, etc. Try putting brackets around it like so:

[Ticket Info].[DATE]

Still I doubt that's the problem. As it's pulling from only one table, it
isn't a join problem.

The query does quite a bit of math on the various fields. I could see a
divide by zero sneaking in or maybe a null value. I suggest running a select
query just on the date in question and seeing if any of the returned record
fields have a Null or 0 that could cause grief.

Another possibility is that the records for 5-4-07 has a time component. As
your are grouping by DATE, it might be causing multiple records for that date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

dsimpkins said:
Here's the SQL:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], Sum(([# YDS]*[W/S $ Concrete/CY])/[Sale $]) AS
[Concrete Cost to Ttl Sls %], Sum((([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost])/[Sale $]) AS [Dir Costs to Ttl Sls %], Sum(([Sale $]-(([#
YDS]*[W/S $ Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time
Returned Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg
MPG])*[Fuel Cost /Gal])+[Addt'l Prod Cost]))/[Sale $]) AS [Gross Profit %],
[Ticket Info].[Sale $], Sum([# YDS]*[W/S $ Concrete/CY]) AS [Concrete Cost],
Sum((([Driver Hrly Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time
Left Plant])*1440)) AS [Loaded Labor $], Sum((([Driver Hrly Rate]*(1+[Wage
Load])/60))*(([Time Returned Plant]-[Time Left Plant])*1440)/[Sale $]) AS
[Labor %], Sum(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal]) AS
[Fuel Cost/Load], Sum((([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal])/[Sale $]) AS [Fuel %], [Ticket Info].[Addt'l Prod Cost], Sum([Addt'l
Prod Cost]/[Sale $]) AS [Prod %], Sum(([# YDS]*[W/S $ Concrete/CY])+(([Driver
Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned Plant]-[Time Left
Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost /Gal])+[Addt'l
Prod Cost]) AS [Total Cost], Sum([Sale $]-(([# YDS]*[W/S $
Concrete/CY])+(([Driver Hrly Rate]*(1+[Wage Load]))/60)*(([Time Returned
Plant]-[Time Left Plant])*1440)+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel
Cost /Gal])+[Addt'l Prod Cost])) AS [Gross Profit $], Sum((((([Driver Hrly
Rate]*(1+[Wage Load])/60))*(([Time Returned Plant]-[Time Left
Plant])*1440))+(([Rndtrip Miles]/[SC Fleet Avg MPG])*[Fuel Cost
/Gal]))/[Rndtrip Miles]) AS [Cost Per Mile], Sum(([Time Returned Plant]-[Time
Left Plant])*1440) AS [# Min on Order], Sum(([Time End Discharge]-[Time
Arrived Job])*1440) AS [# Min on Job Site], Sum((([Time End Discharge]-[Time
Arrived Job])*1440)/[# YDS]) AS [Mins/CY on Job], Sum(([Time Arrived
Job]-[Time Left Plant])*1440) AS [1-Way Travel-Time to Job], [Ticket Info].[#
YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket Info].[Retail $ /CY],
[Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket Info].DRIVER, [Ticket
Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load], Sum([Driver Hrly
Rate]*(1+[Wage Load]))/60 AS [Loaded Labor Rate per Min], [Ticket
Info].[Rndtrip Miles], Sum([Rndtrip Miles]/[SC Fleet Avg MPG]) AS [# Gal Fuel
Used]
FROM [Ticket Info]
GROUP BY [Ticket Info].DATE, [Ticket Info].[SPECIAL CODING], [Ticket
Info].[JOB CODE], [Ticket Info].[CUSTOMER NAME], [Ticket Info].[TICKET #],
[Ticket Info].[JOB NAME/#], [Ticket Info].[Sale $], [Ticket Info].[Addt'l
Prod Cost], [Ticket Info].[# YDS], [Ticket Info].[W/S $ Concrete/CY], [Ticket
Info].[Retail $ /CY], [Ticket Info].PLT, [Ticket Info].[Truck #], [Ticket
Info].DRIVER, [Ticket Info].[DRIVER HRLY RATE], [Ticket Info].[Wage Load],
[Ticket Info].[Rndtrip Miles]
HAVING ((([Ticket Info].DATE) Between [Beginning Date] And [Ending Date]));

Primary Key is on the Ticket Info Table "ID" field
Relationships is from Trucking/Driver Table - "DRIVER HRLY RATE" field
to Ticket Info Table - "DRIVER HRLY RATE" field
& from Customers Table - "CUSTOMER NAME" field to Ticket Info Table -
"CUSTOMER NAME" field

Just to clarify - the query works for everything before 5/4/07 and
everything after.
Thanks
--
dsimpkins


Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Check that the "date" fields in the table are actually date/time datatype
and not just text that looks like a date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

I have been using my database for over 1 year and have been pulling a query
which uses a beginning date and ending date to pull my information.
I have had no trouble until May 4th. I had entered my information and went
on to 5-5-07 and then had to enter more information for May 4th. I've
entered more information all the way through current date. Now the problem I
have is in pulling a query for the whole month to date (5-1-07 through
5-24-07) it gives me an error message of: "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."

It's puzzling to me because I can pull the string of information of 5-1-07
through 5-3-07 and 5-5-07 through 5-24-07, without any problems. I have
reentered the dates in all records for 5-4-07 on the chance that there was a
entry error, but I still am having the problem.

Any idea of what I can do to correct the problem to get the information?
I'm thinking that as a last ditch effort to delete the records and reenter
them, I don't want to do it if I don't have to.

Thanks for the input.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top