Query date ranges working opposite (1 Viewer)

Bigtuna

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
Short text in the table, but I have a format expression in the query
 

Minty

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
Okay, that might help explain things a little.

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

Bigtuna

New member
Local time
Today, 06:34
Joined
Oct 4, 2018
Messages
7
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.
 

Users who are viewing this thread

Top Bottom