473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query retrieving wrong data from text box?

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
10 3729
MMcCarthy
14,534 Expert Mod 8TB
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
Gerhard
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
14,534 Expert Mod 8TB
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
Gerhard
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
14,534 Expert Mod 8TB
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
Gerhard
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
14,534 Expert Mod 8TB
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
Gerhard
23
Clever Irish. And they can play rugby too!
Feb 28 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
Clever Irish. And they can play rugby too!
We sure can, especially when we're playing the English - LOL
Feb 28 '07 #10
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Quentin Huo | last post by:
Hi: If I have a query string for retrieving data from SQL Server database, is there a way to transfer it to a query string for retrieving data from Oracle or any other database like mySQL...? I...
1
by: antonyliu2002 | last post by:
This message was originally a follow-up in a thread, but it went ignored and I do want some help so I am initiating it as a new topic. After Patrick and Karl showed me some examples, I was trying...
5
by: aniket_sp | last post by:
i am using a data adapter and a dataset for filling and retrieving data into .mdb database. following is the code..... for the form load event Dim dc(0) As DataColumn Try If...
7
by: John | last post by:
After reading the tutorial from http://www.startvbdotnet.com/ado/msaccess.aspx I wrote this code in an attempt to get information from my MDB file called FillMe. The only table in that Access file is...
5
by: AdrianG | last post by:
I am trying to write a single SQL query that would retrieve the data that I need. For example, I have a table called Athletes that has 2 fields: name and sport containing the name of an athlete and...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
10
by: darkenroyce | last post by:
Hi Guys I am creating a sports database and one of the features involves create drop down dialogue boxes that retrieve data from MySQL tables and provides them as <option> within...
34
by: vpriya6 | last post by:
Hi guys, I am new to Ajax, xml and javascript. I want to know how can I retrieve data from xml and display in the html page? please help me out. suppose my xml file is customer.xml the code...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.