Use Date as Query Criteria

[Guest]

Hi,
I hope someone can help me.
I have a query that I want to execute using a date range in the criteria. It
works fine if I prompt the user to enter the dates into the query - by using
"Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section.

However, I want to use a form for the user to enter the date range into and
whilst using "Between [Forms]![Frmxx]![StartDate] And
[Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are
fields on the form, the query doesn't seem to recognise the values in the
form fields and returns no records.

The fields on the form are formatted as Medium Date as are the respective
fields on the query.

Any suggestions/solutions would be very much appreciated.

 

[Allen Browne]

That approach should work, Dave.

Suggestions to help you debug this:

1. Make sure the focus is not still in the StartDate or EndDate boxes on the
form. If it is the Value of the control may not be up to date yet.

2. Declare the 2 parameters in the query, to ensure Access understands their
data type correctly.
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
[Forms]![Frmxx]![StartDate] Date/Time
[Forms]![Frmxx]![EndDate] Date/Time

3. Try Short Date instead of Medium Date in the text boxes on the form.
(Medium Date should work, but Short Date is a system setting.)

4. Presumably the field you are applying this to is a Date/Time type field
if you open your table in design view? (not a Text field?)

5. If the field might have a time as well as a date component, change the
criteria in your query to:

= [Forms]![Frmxx]![StartDate] And < ([Forms]![Frmxx]![EndDate]+1)

6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form
is open and has the dates in its text boxes, and ask Access how it
understands the values, e.g.:
? [Forms]![Frmxx]![StartDate]
? TypeName([Forms]![Frmxx]![StartDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that I want to execute using a date range in the criteria.
It
works fine if I prompt the user to enter the dates into the query - by
using
"Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section.

However, I want to use a form for the user to enter the date range into
and
whilst using "Between [Forms]![Frmxx]![StartDate] And
[Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate
are
fields on the form, the query doesn't seem to recognise the values in the
form fields and returns no records.

The fields on the form are formatted as Medium Date as are the respective
fields on the query.

 

[Guest]

Thanks for your rapid response. I will work through your debug suggestions
and see if can get it working.
Dave

That approach should work, Dave.

Suggestions to help you debug this:

1. Make sure the focus is not still in the StartDate or EndDate boxes on the
form. If it is the Value of the control may not be up to date yet.

2. Declare the 2 parameters in the query, to ensure Access understands their
data type correctly.
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
[Forms]![Frmxx]![StartDate] Date/Time
[Forms]![Frmxx]![EndDate] Date/Time

3. Try Short Date instead of Medium Date in the text boxes on the form.
(Medium Date should work, but Short Date is a system setting.)

4. Presumably the field you are applying this to is a Date/Time type field
if you open your table in design view? (not a Text field?)

5. If the field might have a time as well as a date component, change the
criteria in your query to:

= [Forms]![Frmxx]![StartDate] And < ([Forms]![Frmxx]![EndDate]+1)

6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form
is open and has the dates in its text boxes, and ask Access how it
understands the values, e.g.:
? [Forms]![Frmxx]![StartDate]
? TypeName([Forms]![Frmxx]![StartDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that I want to execute using a date range in the criteria.
It
works fine if I prompt the user to enter the dates into the query - by
using
"Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section.

However, I want to use a form for the user to enter the date range into
and
whilst using "Between [Forms]![Frmxx]![StartDate] And
[Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate
are
fields on the form, the query doesn't seem to recognise the values in the
form fields and returns no records.

The fields on the form are formatted as Medium Date as are the respective
fields on the query.

 

[Guest]

Hi again Allen,
Problem sorted!
I feel such an idiot but I will own up to the cause - the form field names
specified in the query criteria did not match those on the actual form.
"StartDate"/"EndDate" vs. "dteStartDate"/"dteEndDate".

Next time I promise I will check the basics a little more closely.

Thanks for your time; I will file your reply incase I run into something
similar in the future.

Regards

(a little humiliated) Dave

That approach should work, Dave.

Suggestions to help you debug this:

1. Make sure the focus is not still in the StartDate or EndDate boxes on the
form. If it is the Value of the control may not be up to date yet.

2. Declare the 2 parameters in the query, to ensure Access understands their
data type correctly.
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
[Forms]![Frmxx]![StartDate] Date/Time
[Forms]![Frmxx]![EndDate] Date/Time

3. Try Short Date instead of Medium Date in the text boxes on the form.
(Medium Date should work, but Short Date is a system setting.)

4. Presumably the field you are applying this to is a Date/Time type field
if you open your table in design view? (not a Text field?)

5. If the field might have a time as well as a date component, change the
criteria in your query to:

= [Forms]![Frmxx]![StartDate] And < ([Forms]![Frmxx]![EndDate]+1)

6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form
is open and has the dates in its text boxes, and ask Access how it
understands the values, e.g.:
? [Forms]![Frmxx]![StartDate]
? TypeName([Forms]![Frmxx]![StartDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that I want to execute using a date range in the criteria.
It
works fine if I prompt the user to enter the dates into the query - by
using
"Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section.

However, I want to use a form for the user to enter the date range into
and
whilst using "Between [Forms]![Frmxx]![StartDate] And
[Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate
are
fields on the form, the query doesn't seem to recognise the values in the
form fields and returns no records.

The fields on the form are formatted as Medium Date as are the respective
fields on the query.

 

[Allen Browne]

Glad you got it sorted out, Dave.

And thanks for posting the solution.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi again Allen,
Problem sorted!
I feel such an idiot but I will own up to the cause - the form field names
specified in the query criteria did not match those on the actual form.
"StartDate"/"EndDate" vs. "dteStartDate"/"dteEndDate".

Next time I promise I will check the basics a little more closely.

Thanks for your time; I will file your reply incase I run into something
similar in the future.

Regards

(a little humiliated) Dave

That approach should work, Dave.

Suggestions to help you debug this:

1. Make sure the focus is not still in the StartDate or EndDate boxes on
the
form. If it is the Value of the control may not be up to date yet.

2. Declare the 2 parameters in the query, to ensure Access understands
their
data type correctly.
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
[Forms]![Frmxx]![StartDate] Date/Time
[Forms]![Frmxx]![EndDate] Date/Time

3. Try Short Date instead of Medium Date in the text boxes on the form.
(Medium Date should work, but Short Date is a system setting.)

4. Presumably the field you are applying this to is a Date/Time type
field
if you open your table in design view? (not a Text field?)

5. If the field might have a time as well as a date component, change the
criteria in your query to:

= [Forms]![Frmxx]![StartDate] And < ([Forms]![Frmxx]![EndDate]+1)

6. If you're still stuck, open the Immediate Window (Ctrl+G) while the
form
is open and has the dates in its text boxes, and ask Access how it
understands the values, e.g.:
? [Forms]![Frmxx]![StartDate]
? TypeName([Forms]![Frmxx]![StartDate])

I have a query that I want to execute using a date range in the
criteria.
It
works fine if I prompt the user to enter the dates into the query - by
using
"Between [Enter 1st date] And [Enter 2nd Date]" in the criteria
section.

However, I want to use a form for the user to enter the date range into
and
whilst using "Between [Forms]![Frmxx]![StartDate] And
[Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate
are
fields on the form, the query doesn't seem to recognise the values in
the
form fields and returns no records.

The fields on the form are formatted as Medium Date as are the
respective
fields on the query.