Data from Form Text Box comes up blank

[Phil Smith]

I have a form with two unbound text boxes, STARTDATE and ENDDATE. Both
formatted as short date.

Like I have done thousands of times, I use these two controls in
criteria for queries to get records, like invoice records, between those
two dates.

This time it does not work. In order to figure it out, I went to the
query, and added two fields, using "build" to select each of those two
fields, just so I can see what the query is getting from the form.

I put into the form's controls 1/1/09 and 1/31/09, or 1/1/2009 and
1/31/2009.

No matter what, both of those fields come up blank in the query. I have
verified that I am indeed referencing the correct text boxes.

Ideas?

 

[Allen Browne]

Suggestions:

1. Copy the exact expression to clipboard, such as:
[Forms].[Form1].[STARTDATE]
Open the Immediate Window (Ctrl+G), add a question mark, space, and paste:
? [Forms].[Form1].[STARTDATE]
When you press Enter, does it show the correct date?
- No: the form reference is wrong, so address that.
- Yes: the form reference is correct. Continue to step 2.

2. In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Paste in 2 rows, like this:
[Forms].[Form1].[STARTDATE] Date/Time
[Forms].[Form1].[ENDDATE] Date/Time
This may help JET to interpret the dates correctly.

3. Still stuck? Double-check that the field you place these under is a
date/time field. Open the table in design view, and check the Data Type.
(For example, it won't work correctly if it's a Text field.)

4. When you run the expression, make sure you have tabbed out of both boxes
on the form. If the cursor is still in the ENDDATE, for example, its Value
may not yet have been updated, so the records won't match.

 

[Phil Smith]

Option 2 did the trick, but I do not understand why. It did not appear
to be misunderstanding the data type, (coming back wuth text for
instance) but treatng them both as null.

3 and 4 I was already aware of.

Thanx

 

[Allen Browne]

That's great, Phil.

When I use parameters, I always explicitly declare them, to prevent JET
getting them wrong. The issue of data types is very important.

The only exception is parameters against a Text field, as Access gets them
wrong if you do declare them and it calls the Expression Service:
http://allenbrowne.com/bug-13.html

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

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

Option 2 did the trick, but I do not understand why. It did not appear to
be misunderstanding the data type, (coming back wuth text for instance)
but treatng them both as null.

3 and 4 I was already aware of.

Thanx

Suggestions:

1. Copy the exact expression to clipboard, such as:
[Forms].[Form1].[STARTDATE]
Open the Immediate Window (Ctrl+G), add a question mark, space, and
paste:
? [Forms].[Form1].[STARTDATE]
When you press Enter, does it show the correct date?
- No: the form reference is wrong, so address that.
- Yes: the form reference is correct. Continue to step 2.

2. In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Paste in 2 rows, like this:
[Forms].[Form1].[STARTDATE] Date/Time
[Forms].[Form1].[ENDDATE] Date/Time
This may help JET to interpret the dates correctly.

3. Still stuck? Double-check that the field you place these under is a
date/time field. Open the table in design view, and check the Data Type.
(For example, it won't work correctly if it's a Text field.)

4. When you run the expression, make sure you have tabbed out of both
boxes on the form. If the cursor is still in the ENDDATE, for example,
its Value may not yet have been updated, so the records won't match.