Between statement using Like

[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

 

[Jerry Whittle]

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".

 

[Ron2006]

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

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

 

[fredg]

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

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]))

 

[Crazyhorse]

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

 

[Jerry Whittle]

Did you declare the parameter by going up to Queries, Parameter while in
design view?

 

[Crazyhorse]

Thanks. Now both of them work now.

Nice little trick.

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.

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

 

[Jerry Whittle]

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.

Thanks. Now both of them work now.

Nice little trick.

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.

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