Count of in Range of Dates

[sonofroy]

I have a query that counts dates in a field

SELECT tblvehicles.[Comm Number], DateValue([CheckOut]) AS Expr1, Count(*)
AS Expr2
FROM tblvehicles LEFT JOIN tbldispatch ON
tblvehicles.VechID=tbldispatch.VechID
WHERE (((tbldispatch.[CheckOut]) Between [Enter Starting date: ] And [Enter
Final date: ]))
GROUP BY tblvehicles.[Comm Number], DateValue([CheckOut]);


The problem is I just found out I am missing some information I
need for it. Some vehicles are checked out for multiple days at a time and I
am using a [CheckOut] date field plus I have an estimated [EstRtn] Date
Field for the return of the vehicle. And I have to show not only the vehicle
the day its checked otu but I need to show a count for each day that vehicle
is out. So if it goes out on a
monday until wednesday I need to show a count of 1 for each of the 3 days
and add it to this query.

Is it possible to add this to query?

 

[Jerry Whittle]

I hate to ask, but can a vehicle be checked in and then checked out on the
same day? In fact could a vehicle be checked in and out multiple times on the
same day? That could really complicate matters.

 

[John Spencer]

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[sonofroy]

I have 12 vehicles total in my pool. Some can be checked out multiple times
a day. But some vehicles have been checked out for weeks at a time. My end
result with this is a chart showing how many times a day vehicles are checked
out to audit if we really need 12 vehicles in our pool. This is where I am
struggling to come up with a query that will work.

I hate to ask, but can a vehicle be checked in and then checked out on the
same day? In fact could a vehicle be checked in and out multiple times on the
same day? That could really complicate matters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that counts dates in a field

SELECT tblvehicles.[Comm Number], DateValue([CheckOut]) AS Expr1, Count(*)
AS Expr2
FROM tblvehicles LEFT JOIN tbldispatch ON
tblvehicles.VechID=tbldispatch.VechID
WHERE (((tbldispatch.[CheckOut]) Between [Enter Starting date: ] And [Enter
Final date: ]))
GROUP BY tblvehicles.[Comm Number], DateValue([CheckOut]);


The problem is I just found out I am missing some information I
need for it. Some vehicles are checked out for multiple days at a time and I
am using a [CheckOut] date field plus I have an estimated [EstRtn] Date
Field for the return of the vehicle. And I have to show not only the vehicle
the day its checked otu but I need to show a count for each day that vehicle
is out. So if it goes out on a
monday until wednesday I need to show a count of 1 for each of the 3 days
and add it to this query.

Is it possible to add this to query?

 

[sonofroy]

So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a query that counts dates in a field

SELECT tblvehicles.[Comm Number], DateValue([CheckOut]) AS Expr1, Count(*)
AS Expr2
FROM tblvehicles LEFT JOIN tbldispatch ON
tblvehicles.VechID=tbldispatch.VechID
WHERE (((tbldispatch.[CheckOut]) Between [Enter Starting date: ] And [Enter
Final date: ]))
GROUP BY tblvehicles.[Comm Number], DateValue([CheckOut]);


The problem is I just found out I am missing some information I
need for it. Some vehicles are checked out for multiple days at a time and I
am using a [CheckOut] date field plus I have an estimated [EstRtn] Date
Field for the return of the vehicle. And I have to show not only the vehicle
the day its checked otu but I need to show a count for each day that vehicle
is out. So if it goes out on a
monday until wednesday I need to show a count of 1 for each of the 3 days
and add it to this query.

Is it possible to add this to query?

 

[John Spencer]

Did you try what I posted?

If so, did it not work?

If not, did you have trouble understanding what to do? Can I change the
instructions to help you?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Yes, you need to add a table with just one field called TheDate.

Add records to that table - one record for each date in your date range. And
NO duplicated dates. As I said the easy way to build that table would be to
open EXCEL and enter the first two dates in the first column
1/1/2008
1/2/2008
Select a range of records From the first row to the 730 (for roughly a two
year range) and then Select Edit : Fill : Series from the menu.

Now you can save the Excel file and import it into your data base (or you can
link to it if you wish).

Once you have done that you can build your queries. Post back if you need
more help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[sonofroy]

What do "C." And "D." mean in your queries?

Yes, you need to add a table with just one field called TheDate.

Add records to that table - one record for each date in your date range. And
NO duplicated dates. As I said the easy way to build that table would be to
open EXCEL and enter the first two dates in the first column
1/1/2008
1/2/2008
Select a range of records From the first row to the 730 (for roughly a two
year range) and then Select Edit : Fill : Series from the menu.

Now you can save the Excel file and import it into your data base (or you can
link to it if you wish).

Once you have done that you can build your queries. Post back if you need
more help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

They are aliases for the tables. D is the alias for tblDispatch and C is the
alias for the calendar table. It allows you to substitute the actual table
names in place of the ones in the SQL and then use the alias everywhere else
in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What do "C." And "D." mean in your queries?

Yes, you need to add a table with just one field called TheDate.

Add records to that table - one record for each date in your date range. And
NO duplicated dates. As I said the easy way to build that table would be to
open EXCEL and enter the first two dates in the first column
1/1/2008
1/2/2008
Select a range of records From the first row to the 730 (for roughly a two
year range) and then Select Edit : Fill : Series from the menu.

Now you can save the Excel file and import it into your data base (or you can
link to it if you wish).

Once you have done that you can build your queries. Post back if you need
more help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

:

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[sonofroy]

Learned something new today! Thank you very much and it works great!

They are aliases for the tables. D is the alias for tblDispatch and C is the
alias for the calendar table. It allows you to substitute the actual table
names in place of the ones in the SQL and then use the alias everywhere else
in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What do "C." And "D." mean in your queries?

Yes, you need to add a table with just one field called TheDate.

Add records to that table - one record for each date in your date range. And
NO duplicated dates. As I said the easy way to build that table would be to
open EXCEL and enter the first two dates in the first column
1/1/2008
1/2/2008
Select a range of records From the first row to the 730 (for roughly a two
year range) and then Select Edit : Fill : Series from the menu.

Now you can save the Excel file and import it into your data base (or you can
link to it if you wish).

Once you have done that you can build your queries. Post back if you need
more help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sonofroy wrote:
So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

:

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[sonofroy]

All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

Learned something new today! Thank you very much and it works great!

They are aliases for the tables. D is the alias for tblDispatch and C is the
alias for the calendar table. It allows you to substitute the actual table
names in place of the ones in the SQL and then use the alias everywhere else
in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What do "C." And "D." mean in your queries?

:

Yes, you need to add a table with just one field called TheDate.

Add records to that table - one record for each date in your date range. And
NO duplicated dates. As I said the easy way to build that table would be to
open EXCEL and enter the first two dates in the first column
1/1/2008
1/2/2008
Select a range of records From the first row to the 730 (for roughly a two
year range) and then Select Edit : Fill : Series from the menu.

Now you can save the Excel file and import it into your data base (or you can
link to it if you wish).

Once you have done that you can build your queries. Post back if you need
more help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sonofroy wrote:
So you are saying I need another database table for just dates? I am not
understanding where you are suggesting I go with this sorry!

:

Assuming vehicles can be checked out multiple times on one day. The
easiest solution would be to use a small calendar table with dates for
the oldest to the newest date.

SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

You can use that query to get the vehicle count for each day

SELECT tblvehicles.[Comm Number]
, QDays.TheDate as DateOut
, Count(*) as VehicleCount
FROM tblvehicles LEFT JOIN qDays ON
tblvehicles.VechID=qDays.VechID
GROUP BY tblvehicles.[Comm Number], QDays.TheDate

It is pretty easy to create the calendar table. Build the table in
EXCEL and fill down. Then copy it into your Access database. Even a
100 years is only about 36525 records.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[sonofroy]

I got the same error message

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

 

[sonofroy]

I closed it and reopened and it worked thank you very much

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

 

[sonofroy]

its returning all vehicles every day

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

 

[sonofroy]

Ok lets back up I am getting ahead of myself. I put the parameters in and I
am still getting the same error message

its returning all vehicles every day

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

 

[John Spencer]

At this point I am stumped.

From your earlier post, I thought this was working and now fails. Is that
correct?

If so, you may be having a references problem. Since the VBA function
DateValue is being used in the query. A quick check would be to remove
DateValue from the query. If it now runs then you need to fix your references.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target machine).
Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you just
unselected (you can tell by doing a Compile All Modules), go back in and
reselect them.

If none have "MISSING:", select an additional reference at random, back out of
the dialog, then go back in and unselect the reference you just added. If that
doesn't solve the problem, try to unselect as many of the selected references
as you can (Access may not let you unselect them all), back out of the dialog,
then go back in and reselect the references you just unselected. (NOTE: write
down what the references are before you delete them, because they'll be in a
different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains the
specific function that's failing doesn't have a problem.

**** End Quote ****


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ok lets back up I am getting ahead of myself. I put the parameters in and I
am still getting the same error message

its returning all vehicles every day

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sonofroy wrote:
All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.

 

[sonofroy]

I was giving still giving me errors so I went in and double checked all field
and table names and one of them was not right because it is now working.
Thank you very much for all of your help!

At this point I am stumped.

From your earlier post, I thought this was working and now fails. Is that
correct?

If so, you may be having a references problem. Since the VBA function
DateValue is being used in the query. A quick check would be to remove
DateValue from the query. If it now runs then you need to fix your references.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target machine).
Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you just
unselected (you can tell by doing a Compile All Modules), go back in and
reselect them.

If none have "MISSING:", select an additional reference at random, back out of
the dialog, then go back in and unselect the reference you just added. If that
doesn't solve the problem, try to unselect as many of the selected references
as you can (Access may not let you unselect them all), back out of the dialog,
then go back in and reselect the references you just unselected. (NOTE: write
down what the references are before you delete them, because they'll be in a
different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains the
specific function that's failing doesn't have a problem.

**** End Quote ****


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ok lets back up I am getting ahead of myself. I put the parameters in and I
am still getting the same error message

its returning all vehicles every day

:

Good chance that the parameter prompt is not being understood as to the
correct data type.

Try declaring the parameters at the beginning of the query.
Parameters [Enter Starting date: ] DateTime,
Enter Final date: ] DateTime;
SELECT DISTINCT VechId, C.TheDate
FROM tblDispatch as D INNER JOIN tblCalendar as C
ON C.TheDate >= DateValue(D.Checkout)
and C.TheDate <=D.EstRtn
WHERE C.TheDate Between [Enter Starting date: ]
and [Enter Final date: ]

In Query Design view
== SELECT Query: Parameters from the menu
== Enter the Parameter prompt EXACTLY as it is in criteria
== Select the datatype
== Repeat for the other parameter.
== Click the OK button to save the parameters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sonofroy wrote:
All of a sudden I keep getting an error that states "the expression is typed
incorrectly or is too complex to be evaluated. Do you know what this means
and how to fix it. It comes up on the first query you suggested.