Unable to use Between query without pulling dates out of range

[DawnDesM]

Unable to use Between query without pulling dates out of range

If I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.
I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date\time field that I have set up an expression on - CreateDate: DateValue(

![date])
the date in the table is stored with time and I just need to show the date.

Due to company policy I wouldn't be able to post database or pics even if I had the post count needed

Any help is greatly appreciated.

DawnDesM

 

[CJ_London]

the main difference between

Between [Enter the start date:] And [Enter the end date:]

and

Between #1/2/2014# And #1/15/2014#

is the #

# is required when using a text equivant of the date e.g. 1/15/2014 (as opposed to its numeric stored value such as 14896) so needs to be used in this situation. The date also needs to be in american format (mm/dd/yyyy) which looks like is ok for you but means us Brits have to use the format function to convert our dd/mm/yyyy structure

Alternatively you can use the cdate function which converts a text date to a date value e.g.

Between cDate([Enter the start date:]) And cDate([Enter the end date:])

but I don't know if this has any performance issues

 

[Galaxiom]

CreateDate: DateValue(

![date])
the date in the table is stored with time and I just need to show the date.

Processing every record in the table with that function will slow down your query a lot.

It is far better to use Between on the field directly. Since you have a time component you could simply add one day to the End Date parameter. This will return records up to midnight but stop before one second past.

Make sure there is an index on the field.

Also instead deriving a field to display the date only, use the Format property of the control where the Date/Time is displayed to show just the date.

 

[DawnDesM]

Thank you both! using the Format property on the table worked like a charm. I was creating a new table with each run, but changed it to append instead. Thanks again.