Query retrieving wrong data from text box?

[Gerhard]

Hi, all

I run into the same problem on Access 2000 and 2003.

Hopefully someone can replicate it – or not.

1. Create an unbound form – call it Form1.

2. Insert two unbound text boxes – call it Text0 and Text2.

3. Run the form and enter some data in both text boxes.

4. While the form is still loaded, create a query that retrieves the data from these text boxes.
The query’s statements in design view must be:
Expr1: [Forms]![Form1]![Text0]
and
Expr2: [Forms]![Form1]![Text2]

5. Run the query (it should retrieve the data you entered into the two text boxes).

6. Now go back to the form in design view and change the format of Text2 to “Short Date” (or any other format you like).

7. Run the form and enter some new data in both text boxes.

8. While the form is still loaded, run the query.

9. Now the query’s Expr2 returns garble, while Expr1 still works fine. (It also does not help to change Text2’s format back to something else).

The same problem occurs if I use “[Forms]![FormName]![ControlName]” to retrieve data from a bound text box - if it is bound to a field which is formatted as “Short Date” or “Long Date”. But I do not get an error if the text box is bound to a field which is formatted as text.

Any advice?

[NeoPa]

After that jibe I can't resist a response (I was going to).
In fact, it's not necessary to use format on the date. The format works effectively because it causes an implicit conversion to a Date/Time format of the data first. CDate() would actually be a more logical function to use here. TextBoxes on a form are stored as string data no matter what format they use to display the data. Typing of non-string data therefore needs to be done afterwards (either explicitly or, more usually, implicitly).

BTW The Irish did actually play very impressively and I'm afraid the English team is still not yet ready to challenge seriously (Even with Johnny back almost fully fit again).
At least after some recent results we look to have stopped the downward spiral.