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?

[MMcCarthy]

I can't see how you can retrieve data from an unbound control in the on load event of the form.

[Gerhard]

No, the query need not be in the on load event. You can add a command button to run the query after you entered data into the text boxes.

[MMcCarthy]

No, the query need not be in the on load event. You can add a command button to run the query after you entered data into the text boxes.

Post the SQL of the query you are running.

To run a query based on the values in a textbox on a form the form must be open.

Mary

[Gerhard]

OK, let me try again, this time with the query being run from the open form itself.

1. Create an unbound form – call it Form1.

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

3. Create a query, which will retrieve the data from these text boxes.
The query’s statements in design view must be:
Expr1: [Forms]![Form1]![Text0]
and
Expr2: [Forms]![Form1]![Text2]

The query's sql will be:
SELECT Forms!Form1!Tex t0 AS Expr1, Forms!Form1!Tex t2 AS Expr2;

5. Now Insert a command button on the form that runs the above query in its on click event.

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

7. Click the command button to run the query (it should work perfectly and retrieve the data you entered into the two text boxes).

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

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

10. Run the query.

11. This time the query’s Expr2 returns garble, while Expr1 still works fine.

[MMcCarthy]

Try this ...

 
SELECT [Forms]![Form1]![Text0] AS Expr1, Format([Forms]![Form1]![Text2], "mm/dd/yyyy") AS Expr2;

[Gerhard]

Thank you ya beauty! It works.

But tell me, why is it necessary to specify the format in the query if you use “[Forms]![FormName]![ControlName]"?

[MMcCarthy]

Thank you ya beauty! It works.

But tell me, why is it necessary to specify the format in the query if you use “[Forms]![FormName]![ControlName]"?

Because you are dealing with unbound controls. There is no where to get the format from. Formating the textbox only works for the data while it's in the textbox.

Mary

[Gerhard]

Clever Irish. And they can play rugby too!

[MMcCarthy]

Clever Irish. And they can play rugby too!

We sure can, especially when we're playing the English - LOL