using dates from forms as criteria

  • Thread starter Thread starter the_dude
  • Start date Start date
T

the_dude

Hi,

I am fairly new to access so this migth be a simple problem to solve.

I have a form and have two text boxes for dates. Start Date and End Date. I
would like the input to be used as criteria in a make table query. I have
looked around and the usual way would be to use

"Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]"

However, this gives me no records. I know there are records thougth because
when I do not use the form for date entry but let Access prompt me for input
it works fine.

Can anyone help? Has it something to do with the formatting of the text
fields? I have set them to 'Format: Short Date'.

I have noticed that access puts '#' around dates when used as criteria in a
query. Could that be the problem?

I would apprciate any help.
 
If you included the quote marks in your criterion string, remove them. It
should then work OK.

HTH,

Rob
 
Thanks very much for your help, Rob.

Unfortunately I used the quotation marks only for display puposes in this
thread. I do not use them in my criteria.

Any ideas what it could be? I have tried the same on a different PC with the
same outcome.
 
Could be several things:

1. Parameter type
==============
In query design, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter 2 rows, like this:
[FORMS]![MyForm]![StartDate] Date/Time
[FORMS]![MyForm]![EndDate] Date/Time
This helps Access understand the data type of the 2 parameters.

2. Field type
=========
Presumably you have this "Between ..." statement in the Criteria row under a
field. What is the data type of the field? Open your table in design view.
It need to be a Date/Time field, not a Text field.

If the field is a calculated field, such as:
Date() + 30
enclose the expression in CVDate() so that Access treats it like a date,
e.g.:
CVDate(Date() + 30))
More info about that:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

3. Value of text box
==============
When you type a date into the text box, Access updates the value when you
*leave* the field. If the cursor has not left the text box since you entered
the date, it may not have its Value yet. Tab out of the text box, and try
again.

4. Dates with time component
=======================
Date/Time fields can contain a date as well as a time. If there is a time
component, records on the last date in the range won't match.

To avoid this, use this as your criteria:
= [FORMS]![MyForm]![StartDate] And < ([FORMS]![MyForm]![EndDate] + 1)
Click to expand...

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

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

the_dude said:
Hi,

I am fairly new to access so this migth be a simple problem to solve.

I have a form and have two text boxes for dates. Start Date and End Date.
I
would like the input to be used as criteria in a make table query. I have
looked around and the usual way would be to use

"Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]"

However, this gives me no records. I know there are records thougth
because
when I do not use the form for date entry but let Access prompt me for
input
it works fine.

Can anyone help? Has it something to do with the formatting of the text
fields? I have set them to 'Format: Short Date'.

I have noticed that access puts '#' around dates when used as criteria in
a
query. Could that be the problem?

I would apprciate any help.
Click to expand...
 
thank you allen,

i have set the parameters in the qry to date/time. the field is a proper date
field and not calculated. It all works fine if i do not have the date fields
on the form but let acccess prompt me (in criteria: Between [Start_Date] And
[End_Date]) but for some reason it does not work when I have the date fields
populated by the form?

has anyone any ideas?
 
I may not understand your problem exactly, but it reminded me of a
situation I had in which I needed to find the pay record for a selected
employee for a certain date. I could not get a match until I tried
this:
MyQuery = "SELECT * FROM PayRecords WHERE " _
& "EmployeeId = '" & cboEmployee.Value & "'" _
& " AND Format$(PayDate,'mm/dd/yyyy') = '" _
& Format(txtDatePaid, "mm/dd/yyyy") & "'"
The key to the solution was that both dates had to be formatted the
same way.
I hope this helps
Cordially,
Robert
 
Hello,

what I use is a form with unbound combo boxes for start and end dates.

In the query I use the following:
=[forms]![report options]![txtdatefrm] And <=[forms]![report options]![txtdateto]
Click to expand...

Enter the appropriate dates into the form and run the query.

It will (or should) work a treat!



--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t 2 !!


the_dude said:
Hi,

I am fairly new to access so this migth be a simple problem to solve.

I have a form and have two text boxes for dates. Start Date and End Date. I
would like the input to be used as criteria in a make table query. I have
looked around and the usual way would be to use

"Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]"

However, this gives me no records. I know there are records thougth because
when I do not use the form for date entry but let Access prompt me for input
it works fine.

Can anyone help? Has it something to do with the formatting of the text
fields? I have set them to 'Format: Short Date'.

I have noticed that access puts '#' around dates when used as criteria in a
query. Could that be the problem?

I would apprciate any help.
Click to expand...
 
Time to ask Access what's going on.

1. Open the form open and type the dates into the 2 text boxes.

2. Open the query in design view.
Copy the exact words:
[FORMS]![MyForm]![StartDate]
from the query statement to clipboard (Ctrl+C.)

2. Open the Immediate Window (Ctrl+G)

3. Type a question mark, space, and Paste (Ctrl+V).
You should now have:
? [FORMS]![MyForm]![StartDate]
Press Enter.
Does it show the correct date?

4. Repeat for the EndDate, to show the result in the Immediate window.

5. Once both dates are verified, switch the query to Datasheet view.

If the dates are correct, but the query shows no values, there's something
wrong with the data. Are you showin 4-digit years here, so you can verify
the dates are in the right century?

It would also be a good idea to compact/repair the database (under Database
Utilities on the Tools menu), just in case there is a bad index on the date
field.

If it still fails, post the full SQL statement from the query.
 
Set the format of your textboxes to ShortDate on the form. Access may not
be interpreting your entry as a date.
 
Ah, it works now. The check in the immediate window worked a treat and showed
my error... even thougth I was sure that my spelling was correct before I
started experimenting to try and track down the error.

Thank you everyone who helped. I appreciate it very much and I hope I can be
off help sometime as well.

Kind regards
 
Back
Top