Need Between Dates criteria

[Uschi via AccessMonster.com]

I have been trying a write a simple between dates expression for a query.
Nothing is working. Can anyone help? Between dates 01/01/2006 and 06/30/2006.

Many thanks

 

[fredg]

I have been trying a write a simple between dates expression for a query.
Nothing is working. Can anyone help? Between dates 01/01/2006 and 06/30/2006.

Many thanks

Is the Date Field is a Date/Time datatype field?
If you wish to hard code the dates, then:

Where YourTable.[DateField] Between #1/1/2006# and #6/30/2006#

If you wish the user to be able to enter the date range as needed,
then:
Where YourTable.[DateField] Between [Enter Start Date] and [Enter End
Date]

Change YourTable and DateField to whatever the actual table and date
field names are.

 

[Uschi via AccessMonster.com]

I have been trying a write a simple between dates expression for a query.
Nothing is working. Can anyone help? Between dates 01/01/2006 and 06/30/2006.

Many thanks

Is the Date Field is a Date/Time datatype field?
If you wish to hard code the dates, then:

Where YourTable.[DateField] Between #1/1/2006# and #6/30/2006#

I just tried that and now a prompt comes up when I run the query.

If you wish the user to be able to enter the date range as needed,
then:
Where YourTable.[DateField] Between [Enter Start Date] and [Enter End
Date]

Change YourTable and DateField to whatever the actual table and date
field names are.

 

[Uschi via AccessMonster.com]

[quoted text clipped - 5 lines]

Where YourTable.[DateField] Between #1/1/2006# and #6/30/2006#

I just tried that and now a prompt comes up when I run the query. Previouly, I was using Between "01/01/2006" AND "06/30/2006". It will give me all of the records with the months and the days specified but it will NOT give me just 2006.

If you wish the user to be able to enter the date range as needed,
then:

[quoted text clipped - 3 lines]

Change YourTable and DateField to whatever the actual table and date
field names are.

 

[Uschi via AccessMonster.com]

[quoted text clipped - 5 lines]

Where YourTable.[DateField] Between #1/1/2006# and #6/30/2006#

I just tried that and now a prompt comes up when I run the query.

If you wish the user to be able to enter the date range as needed,
then:

[quoted text clipped - 3 lines]

Change YourTable and DateField to whatever the actual table and date
field names are.

When I run the query with the criteria Between "01/01/2006" AND "06/30/2006"
I will get the months and days specified but it will not give me all the
years and not just 2006.
Thank you,

 

[Rick Brandt]

Uschi wrote:
When I run the query with the criteria Between "01/01/2006" AND
"06/30/2006" I will get the months and days specified but it will not
give me all the years and not just 2006.
Thank you,

If you use quotes (") then you get a string comparison rather than a Date
comparision. Use # instead.

 

[Uschi via AccessMonster.com]

If you use quotes (") then you get a string comparison rather than a Date
comparision. Use # instead.

Rick, thank you for your reply. I am revising a database created years ago by
someone else and after going around in circles all weekend on this I
discovered the problem is the Date Field in the tables. Some of Date Fields
are Text and others are Date/Time. Some have an InPut Mask, others don't.

For the tables that have date fields using (Date/Time)the query works using #,
however many of the dates look like 1_/1_/2006. The Find and Replace will not
work. Do you suggestions on how to clean this up? Any suggestions will be
greatly appreciated.