Criteria Search stopped working

[Guest]

I am a novice when working with Access. That being said ....I have a problem.
The database I have is for sales with several fields. We review our daily
sales through a query I have created and so far there has been no problems.
My problem today...I'm trying to pull up the sales for 7/22/06. I have set
up the criteria as "Between [Beginning Date] And [Ending Date]" and so far it
has been able to pull up sales requested being either single dates or within
a specific date span. I enter the date 7/22/06 or 07/06/2006 or 7/6/6 or
etc. (you get the picture) the response I get back is....."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. (Error
3071)" I can type in dates later in the month or any dates in August and it
displays the info but I can't get 7/22/06 - yes there is sales information on
this date. Any Idea of what's happened. It just started today. Thanks

 

[Guest]

Open the query in design view. Go up to Query, Parameters.

Do you see two entries for [Beginning Date] And [Ending Date] and are they
Date/Time datatype? If not you need to create them. It helps Access in not
getting confused about the type of data that it's working with.

 

[Guest]

Jerry,
Your right in presuming I didn't have it set up that way.
I did follow your suggestion and it still is giving me the same message.
I'll take more suggestions.

--
dsimpkins

Open the query in design view. Go up to Query, Parameters.

Do you see two entries for [Beginning Date] And [Ending Date] and are they
Date/Time datatype? If not you need to create them. It helps Access in not
getting confused about the type of data that it's working with.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am a novice when working with Access. That being said ....I have a problem.
The database I have is for sales with several fields. We review our daily
sales through a query I have created and so far there has been no problems.
My problem today...I'm trying to pull up the sales for 7/22/06. I have set
up the criteria as "Between [Beginning Date] And [Ending Date]" and so far it
has been able to pull up sales requested being either single dates or within
a specific date span. I enter the date 7/22/06 or 07/06/2006 or 7/6/6 or
etc. (you get the picture) the response I get back is....."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. (Error
3071)" I can type in dates later in the month or any dates in August and it
displays the info but I can't get 7/22/06 - yes there is sales information on
this date. Any Idea of what's happened. It just started today. Thanks

 

[Guest]

Strange. 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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Your right in presuming I didn't have it set up that way.
I did follow your suggestion and it still is giving me the same message.
I'll take more suggestions.

--
dsimpkins

Open the query in design view. Go up to Query, Parameters.

Do you see two entries for [Beginning Date] And [Ending Date] and are they
Date/Time datatype? If not you need to create them. It helps Access in not
getting confused about the type of data that it's working with.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am a novice when working with Access. That being said ....I have a problem.
The database I have is for sales with several fields. We review our daily
sales through a query I have created and so far there has been no problems.
My problem today...I'm trying to pull up the sales for 7/22/06. I have set
up the criteria as "Between [Beginning Date] And [Ending Date]" and so far it
has been able to pull up sales requested being either single dates or within
a specific date span. I enter the date 7/22/06 or 07/06/2006 or 7/6/6 or
etc. (you get the picture) the response I get back is....."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. (Error
3071)" I can type in dates later in the month or any dates in August and it
displays the info but I can't get 7/22/06 - yes there is sales information on
this date. Any Idea of what's happened. It just started today. Thanks

 

[Guest]

Jerry,

I'm not sure what part of the SQL you need to see so I've sent the whole
thing, Primary Keys and Relationships follow the SQL. Thanks for the help.

SQL is as follows:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT [Ticket Info].DATE, [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].[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 Keys for My Tables are as Follows:

Ticket Info = Field name "ID" (AutoNumber)
Customers = Field name "ID" (AutoNumber)
JOB CODE = Field name "JOBCODE" (text)
Trucking/Driver = Field name "DRIVER" (text)

Relationships:

Trucking/Driver "DRIVER HRLY RATE" to Ticket Info "DRIVER HRLY RATE"
Customers "CUSTOMER NAME" to Ticket Info "CUSTOMER NAME"


Thanks again

--
dsimpkins

Strange. 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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Your right in presuming I didn't have it set up that way.
I did follow your suggestion and it still is giving me the same message.
I'll take more suggestions.

--
dsimpkins

Open the query in design view. Go up to Query, Parameters.

Do you see two entries for [Beginning Date] And [Ending Date] and are they
Date/Time datatype? If not you need to create them. It helps Access in not
getting confused about the type of data that it's working with.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am a novice when working with Access. That being said ....I have a problem.
The database I have is for sales with several fields. We review our daily
sales through a query I have created and so far there has been no problems.
My problem today...I'm trying to pull up the sales for 7/22/06. I have set
up the criteria as "Between [Beginning Date] And [Ending Date]" and so far it
has been able to pull up sales requested being either single dates or within
a specific date span. I enter the date 7/22/06 or 07/06/2006 or 7/6/6 or
etc. (you get the picture) the response I get back is....."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. (Error
3071)" I can type in dates later in the month or any dates in August and it
displays the info but I can't get 7/22/06 - yes there is sales information on
this date. Any Idea of what's happened. It just started today. Thanks

 

[Guest]

I don't see anything obvious so here's some WAGs:

Try putting brackets around the DATE in [Ticket Info].DATE like so:
[Ticket Info].[DATE]
Date is a reserved word in Access as it is a function. I see a few places
with [Ticket Info].DATE in the SQL.

The amount of text in the SQL statement is getting up there. As there is
only one table you could remove all the [Ticket Info]. prefixes. That would
simplify things a little.

Definitely double check the data for 7/22/2006. There might be a Null or
something messing up things. As you are doing a lot of division, any 0 values
would cause problems. It might not be obvious as you are doing some
subtraction that could dynamically produce a 0 or you might have some
negative numbers in the table.

You could try simplifying things by removing all the summing and just
bringing in the fields that are in the Group By. If it works then, it might
be something in the rather complicated calculations.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

I'm not sure what part of the SQL you need to see so I've sent the whole
thing, Primary Keys and Relationships follow the SQL. Thanks for the help.

SQL is as follows:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT [Ticket Info].DATE, [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].[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 Keys for My Tables are as Follows:

Ticket Info = Field name "ID" (AutoNumber)
Customers = Field name "ID" (AutoNumber)
JOB CODE = Field name "JOBCODE" (text)
Trucking/Driver = Field name "DRIVER" (text)

Relationships:

Trucking/Driver "DRIVER HRLY RATE" to Ticket Info "DRIVER HRLY RATE"
Customers "CUSTOMER NAME" to Ticket Info "CUSTOMER NAME"

Thanks again

--
dsimpkins

Strange. 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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Your right in presuming I didn't have it set up that way.
I did follow your suggestion and it still is giving me the same message.
I'll take more suggestions.

--
dsimpkins


:

Open the query in design view. Go up to Query, Parameters.

Do you see two entries for [Beginning Date] And [Ending Date] and are they
Date/Time datatype? If not you need to create them. It helps Access in not
getting confused about the type of data that it's working with.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am a novice when working with Access. That being said ....I have a problem.
The database I have is for sales with several fields. We review our daily
sales through a query I have created and so far there has been no problems.
My problem today...I'm trying to pull up the sales for 7/22/06. I have set
up the criteria as "Between [Beginning Date] And [Ending Date]" and so far it
has been able to pull up sales requested being either single dates or within
a specific date span. I enter the date 7/22/06 or 07/06/2006 or 7/6/6 or
etc. (you get the picture) the response I get back is....."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. (Error
3071)" I can type in dates later in the month or any dates in August and it
displays the info but I can't get 7/22/06 - yes there is sales information on
this date. Any Idea of what's happened. It just started today. Thanks