Between statement using Like

  • Thread starter Thread starter Crazyhorse
  • Start date Start date
C

Crazyhorse

I have a Timestamp field. It contains the date and time. I need a date range.
Between [enter you first date] and [enter your second date] works but this
field has a time stamp after the date. Like [Enter first date] & "*" works
but not with the between statement.

Thanks in Advance
 
Here's a little cheat that I use:

Between [enter your first date] And [enter your second date] + .99999

The .99999 adds up to one second before midnight.

Make sure to declare your parameters as Date/Time. The SQL statement would
start with this:

PARAMETERS [enter your first date] DateTime, [enter your second date]
DateTime;

I'm assuming that the "you" should be "your".
 
I have a Timestamp field. It contains the date and time. I need a date range.
Between [enter you first date] and [enter your second date] works but this
field has a time stamp after the date. Like [Enter first date] & "*" works
but not with the between statement.

Thanks in Advance
Click to expand...

One way to get around that is:

Between [enter you first date] and [enter your second date] + 1

The reason this works:

[enter you first date] will have a time stamp of 00:00:00 so
everything for startdate will be selected.

[enter your second date] will also have a time stamp of 00:00:00 but
by adding 1 to that you will then have the next day with a timestamp
of 00:00:00 and therefore everything for the prior day will be
selected.

This will work as long as you NEVER have an entry in the tested filed
that does NOT have a time stamp.

If that is not true assumption then post back and an alternative
approach will be posted.

Ron
 
I have a Timestamp field. It contains the date and time. I need a date range.
Between [enter you first date] and [enter your second date] works but this
field has a time stamp after the date. Like [Enter first date] & "*" works
but not with the between statement.

Thanks in Advance
Click to expand...

That's because 5/12/2008 15:30 PM
is greater than 5/12/2008

Add one day to the ending date parameter.
Between [Start date] and DateAdd("d",1,[Enter End Date]))
 
Thank you for your help and some new tricks but out of the three only one
worked. The data type in the field is Date/time. Every record has a Date and
timestamp.

Between [enter your first date] And [enter your second date] + .99999
Between [enter you first date] and [enter your second date] + 1

I get this error using these:
This expression is typed incorrectly or it is too complex to be evaluated.
For Example a numeric expression may contain too may complicated elements.
Try simplifying the expression by assigning parts of the expression to
variable.



This one worked
Between [Start date] And DateAdd("d",1,[Enter End Date])


This is very odd.

Thanks
 
Did you declare the parameter by going up to Queries, Parameter while in
design view?
 
Thanks. Now both of them work now.

Nice little trick.

Jerry Whittle said:
Did you declare the parameter by going up to Queries, Parameter while in
design view?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Crazyhorse said:
Thank you for your help and some new tricks but out of the three only one
worked. The data type in the field is Date/time. Every record has a Date and
timestamp.

Between [enter your first date] And [enter your second date] + .99999
Between [enter you first date] and [enter your second date] + 1

I get this error using these:
This expression is typed incorrectly or it is too complex to be evaluated.
For Example a numeric expression may contain too may complicated elements.
Try simplifying the expression by assigning parts of the expression to
variable.



This one worked
Between [Start date] And DateAdd("d",1,[Enter End Date])


This is very odd.

Thanks
Click to expand...
Click to expand...
 
If you use parameters in the criteria of a query, always declare them. It
stops some strange errors especially if you use the query as the basis for a
crosstab query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Crazyhorse said:
Thanks. Now both of them work now.

Nice little trick.

Jerry Whittle said:
Did you declare the parameter by going up to Queries, Parameter while in
design view?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Crazyhorse said:
Thank you for your help and some new tricks but out of the three only one
worked. The data type in the field is Date/time. Every record has a Date and
timestamp.

Between [enter your first date] And [enter your second date] + .99999
Between [enter you first date] and [enter your second date] + 1

I get this error using these:
This expression is typed incorrectly or it is too complex to be evaluated.
For Example a numeric expression may contain too may complicated elements.
Try simplifying the expression by assigning parts of the expression to
variable.



This one worked
Between [Start date] And DateAdd("d",1,[Enter End Date])


This is very odd.

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