Having Trouble with Between dates in criteria in a query (1 Viewer)

atrium

Registered User.
Local time
Today, 22:27
Joined
May 13, 2014
Messages
348
I have used the following in the criteria of the field I'm checking

Between [Forms]![AddDdScheduleFrm]![FirstDateFld] And [Forms]![AddDdScheduleFrm]![EndDateFld]

I'm testing this away from the form so it prompts me for the date values

I don't get any results. Am I using the right syntax

I know that there are records on the file that meet the criteria

Thanks for reading this
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Most likely problem is that in sql you need the date format to be in US format i.e. MM/DD/YYYY, where you are using DD/MM/YYYY

Second likely problem is your date in your database includes a time component, i.e. 07/31/2020 08:33:45 and you are looking from 07/31/2020 to 07/31/2020 this will not find the times on that day, instead try searching from 07/31/2020 to 08/01/2020

These are my best guesses for now.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:27
Joined
Jul 9, 2003
Messages
16,244
includes a time component,

Further to namliam's excellent observations, if it is the time component, then you may find my YouTube video helpful:-

 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,038
I *thought* if one used the form controls, the format just worked as Access knew it was a date.?
The same happens in Query window?. I would enter #31/07/2020#, but Access creates the sql as #07/31/2020# ?
 

Dreamweaver

Well-known member
Local time
Today, 11:27
Joined
Nov 28, 2005
Messages
2,466
I would use format(yourdate,"short date") for your dates
 

Isaac

Lifelong Learner
Local time
Today, 04:27
Joined
Mar 14, 2017
Messages
8,738
I'm testing this away from the form so it prompts me for the date values
The form has to be open while you're testing.
One of the reasons I don't use direct references to forms/controls inside queries.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
I *thought* if one used the form controls, the format just worked as Access knew it was a date.?
The same happens in Query window?. I would enter #31/07/2020#, but Access creates the sql as #07/31/2020# ?
Try using a date that is ambigues (spelling) like 01-08-2020 ... Jan 8th or Aug 1st?
This is where troubles arrize, disambiguating is very very important...

Note the lack of # on the OP for starters
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,038
Try using a date that is ambigues (spelling) like 01-08-2020 ... Jan 8th or Aug 1st?
This is where troubles arrize, disambiguating is very very important...

Note the lack of # on the OP for starters
Well I've just tried a simple form with one control for a date and a button to requery.
Query is the source of the form and criteria is a date equal to that on the control on that form.
I used 18/03/2016 and obtained the records I expected.
I then used 08/01/2016 and also obtained the 5 records expected. For 01/08/2016, I only have one record.?

That was what I meant.?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Actually the # is not needed when using this method.
actually yes its needed... sql gets the values but has no clue what they are and has to guess as to their purpose.
This is implicit conversions and are the source of many a trouble in any database.... it works while it works untill it breaks for whatever reason and starts giving false results without anyone noticing because they now trust the application.

Explicit where's and explicit calculations are KEY
 

Isaac

Lifelong Learner
Local time
Today, 04:27
Joined
Mar 14, 2017
Messages
8,738
actually yes its needed... sql gets the values but has no clue what they are and has to guess as to their purpose.
This is implicit conversions and are the source of many a trouble in any database.... it works while it works untill it breaks for whatever reason and starts giving false results without anyone noticing because they now trust the application.

Explicit where's and explicit calculations are KEY
Seems to work fine on the attached with no #
Relying on implicit conversions is common - and about like relying on default values of VBA objects. (The latter, something I actually don't do, but most people do).

But I agree, it's probably a good idea to minimize them.
I don't use queries that reference form controls in the first place, for - among other things - this type of reason (I'd prefer to troubleshoot literal SQL in all cases), but just pointing these things out.
The most relevant thing here is the format of the date being passed in, which must be mm/dd/yyyy or yyyy-mm-dd format.
And of course, the fact that OP is getting a "parameter prompts", which most likely says the form wasn't opened when he tried to run the query, as I mentioned in an early post.
 

Attachments

  • Testing 20200731_2.accdb
    512 KB · Views: 106
Last edited:

atrium

Registered User.
Local time
Today, 22:27
Joined
May 13, 2014
Messages
348
Thanks for all of your input guys.

This is the solution, works for all transaction that fall into the variable values.

>=[Forms]![AddDdScheduleFrm]![FirstDateFld] And <=[Forms]![AddDdScheduleFrm]![LastDateFld]

The SQL is
Code:
WHERE (((DdSchedDDTrans.TransCode)<>40) AND ((DdSchedDDTrans.DdSchedId)=[Forms]![AddDdScheduleFrm]![DdSchedIdFld]) AND ((DdSchedDDTrans.DueDate)>=[Forms]![AddDdScheduleFrm]![FirstDateFld] And (DdSchedDDTrans.DueDate)<=[Forms]![AddDdScheduleFrm]![LastDateFld]) AND ((DdSchedDDTrans.Status)<>"Not Paid"));

Thanks again for all of your help
 

Isaac

Lifelong Learner
Local time
Today, 04:27
Joined
Mar 14, 2017
Messages
8,738
Thanks for all of your input guys.

This is the solution, works for all transaction that fall into the variable values.

>=[Forms]![AddDdScheduleFrm]![FirstDateFld] And <=[Forms]![AddDdScheduleFrm]![LastDateFld]

The SQL is
Code:
WHERE (((DdSchedDDTrans.TransCode)<>40) AND ((DdSchedDDTrans.DdSchedId)=[Forms]![AddDdScheduleFrm]![DdSchedIdFld]) AND ((DdSchedDDTrans.DueDate)>=[Forms]![AddDdScheduleFrm]![FirstDateFld] And (DdSchedDDTrans.DueDate)<=[Forms]![AddDdScheduleFrm]![LastDateFld]) AND ((DdSchedDDTrans.Status)<>"Not Paid"));

Thanks again for all of your help
Glad to hear you got it working!
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Code:
WHERE (((DdSchedDDTrans.TransCode)<>40) 
AND ((DdSchedDDTrans.DdSchedId)=[Forms]![AddDdScheduleFrm]![DdSchedIdFld]) 
AND ((DdSchedDDTrans.DueDate)>=[Forms]![AddDdScheduleFrm]![FirstDateFld] 
And (DdSchedDDTrans.DueDate)<=[Forms]![AddDdScheduleFrm]![LastDateFld]) 
AND ((DdSchedDDTrans.Status)<>"Not Paid"));

Thanks again for all of your help
Yay! Now its readable :)

duedate >= x and duedate <=y
This is the same as your original duedate between x and y ! So what else has changed to make it work ?
 

Falcone203

Member
Local time
Today, 06:27
Joined
Dec 5, 2019
Messages
40
I use a form to select my dates for my reports. It's an unbound form that uses first of or last of dates. Mine is formatted to year or quarter for the past year
1596290463666.png
1596290619989.png
 

Users who are viewing this thread

Top Bottom