Query date ranges working opposite

[Bigtuna]

I have this query that suddenly stopped working, or rather working opposite. I have an expression to (Format[Time], “General Date”) and a criteria of Between [Forms]![frmDateRanges]![startdate] And [Forms]![frmDateRanges]![enddate]. It is run once a month and no longer works. It returns the dates not between the two.

 

[Minty]

Are you in the UK or more accurately somewhere that doesn't use US date formats by any chance?

I suspect your dates are being input in dd/mm/yyyy format and being interpreted as mm/dd/yyyy

How is the query being run , what code is invoked ?

 

[Bigtuna]

I have a format([Time], “General Date”) so that is matches the date format of General Date in the form I use for the date criteria. The criteria is in my original message.

 

[Minty]

That only formats the [Time] field to your local date format. Access in certain circumstances will interpret Non-US dates correctly, but in other circumstances will not, hence me asking the question.

Humour me and put 1/1/2018 and 1/2/2018 into your form, and tell me what actual dates are retrieved.

A lot more detailed information here http://allenbrowne.com/ser-36.html

 

[Bigtuna]

Thank you for your responses. I searched between 9/2/2018 and 9/3/2018, data is purged on a monthly so to size of data. It returned everything on 9/2/2018, 9/20/2018-9/29/2018, and the 9/3/2018 00:00

 

[Minty]

Remove the Format() assuming your [Time] field is really a date time field and not text. Run the same test query and let us know the results?

 

[Bigtuna]

It does not return any data. When I enter 9/1/2018 00:00 - 10/1/2018 00:00 I return 8/31 data, but when I enter 9/1 - 9/2 I return all of 9/1 and 9/10-9/19

 

[Minty]

Can you upload a sample of your database. I'm convinced this is a datetime datatype error.

Just need enough data to replicate your issue, and the form used for the filtering in the query. Remove any sensitive data. Compact and repair then zip before uploading to reduce file size.

Logically the time element is immaterial if you use the between expression. You still haven't mentioned what your local date format is ? Have you read the Allen Browne page ?

 

[Bigtuna]

I am thinking the same thing, some sort of format mismatch. I am working off of US date format, mm/dd/yyyy. I will have to have it cleared by IT, security measures, to the database anywhere. Until I get approval, I am researching options on date formatting. I appreciate your input.

 

[Minty]

Okay - As a final check, can you confirm what data type access thinks [Time] is ?

If it's a linked table you can still go into table design and see what datatype the table thinks it is.

 

[Bigtuna]

Short text in the table, but I have a format expression in the query

 

[Minty]

Okay, that might help explain things a little.

Try using DateValue([Time]) instead of the format().

 

[Bigtuna]

Yes that worked, I have some queries that require time field in addition to the the date fields. On those I used DateValue() + TimeValue() and all seems to be working great. You have been an absolute blessing and I can’t thank you enough.