By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Query retrieving wrong data from text box?

P: 23
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?
Feb 27 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I can't see how you can retrieve data from an unbound control in the on load event of the form.
Feb 27 '07 #2

P: 23
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.
Feb 27 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 28 '07 #4

P: 23
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!Text0 AS Expr1, Forms!Form1!Text2 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.
Feb 28 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [Forms]![Form1]![Text0] AS Expr1, Format([Forms]![Form1]![Text2], "mm/dd/yyyy") AS Expr2;
Feb 28 '07 #6

P: 23
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]"?
Feb 28 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 28 '07 #8

P: 23
Clever Irish. And they can play rugby too!
Feb 28 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Clever Irish. And they can play rugby too!
We sure can, especially when we're playing the English - LOL
Feb 28 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 2 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.