Using a text box in the Select Statement of SQL

K

Kou Vang

How so I write a SQL statement, referencing a text box from a form to a
query? Is it not something like:
[Forms!][FormName]![TextBox]?
 
D

Douglas J Steele

Yes, that's the syntax. The form must be open for it to work, though: Access
will not open the form for you.

How exactly are you trying to use the textbox?
 
K

Kou Vang

I am using the textbox as a user imput, to imput an ID number, ex: 8143.
Then in a query, I'm trying to reference the textbox to populate one of the
fields in a new table. But access continues to give me an error saying that
it doesn't recognize the syntax [Forms]![Tirtltest][LstSite] off my form
named Tirtltest.
 
D

Douglas J Steele

That doesn't really explain how you're actually using the textbox.

Can you post the SQL of your query?
 
J

John Vinson

I am using the textbox as a user imput, to imput an ID number, ex: 8143.
Then in a query, I'm trying to reference the textbox to populate one of the
fields in a new table. But access continues to give me an error saying that
it doesn't recognize the syntax [Forms]![Tirtltest][LstSite] off my form
named Tirtltest.
Click to expand...

You are missing a !: try [Forms]![Tirtltest]![LstSite]


John W. Vinson[MVP]
 
D

Douglas J Steele

John Vinson said:
I am using the textbox as a user imput, to imput an ID number, ex: 8143.
Then in a query, I'm trying to reference the textbox to populate one of the
fields in a new table. But access continues to give me an error saying that
it doesn't recognize the syntax [Forms]![Tirtltest][LstSite] off my form
named Tirtltest.
Click to expand...

You are missing a !: try [Forms]![Tirtltest]![LstSite]
Click to expand...

Good eyes, John!
 
K

Kou Vang

I wish it was just a missing !, but it's not. I don't understand why Access
won't recognize the textbox on the form. If I go to Design view of the query
calling the textbox, can I just do:

NewField: Forms![FormName]![TextBox]
 
J

John Vinson

I wish it was just a missing !, but it's not. I don't understand why Access
won't recognize the textbox on the form. If I go to Design view of the query
calling the textbox, can I just do:

NewField: Forms![FormName]![TextBox]
Click to expand...

Please post the actual SQL of the query. Is the Form open at the time
the query is being run?

John W. Vinson[MVP]
 
K

Kou Vang

I realized something...While testing the Run Query off the form into a brand
new query, when I didn't attach any table or query and simply created a field
referencing the text box, ie- Field: Site:[forms]![formname]![textbox]
worked. It came back with a datasheet giving me exactly what was in my form.
However, with the table/query attached, it got hung up with the Microsoft
Jet Database Engine not recognizing the statement. So why does access
recognize the statement with no table/query being present, but dosen't when
one is? Strange...
Here is what I got so far for my SQL:

SELECT [Forms]![tirtltest]![LstSite] AS SITE, TIRTLhourly.DATE,
TIRTLhourly.TIME, TIRTLhourly.LANE, TIRTLhourly.TOTAL, TIRTLhourly.[1],
TIRTLhourly.[2], TIRTLhourly.[3], TIRTLhourly.[4], TIRTLhourly.[5],
TIRTLhourly.[6], TIRTLhourly.[7], TIRTLhourly.[8], TIRTLhourly.[9],
TIRTLhourly.[10], TIRTLhourly.[11], TIRTLhourly.[12], TIRTLhourly.[13],
TIRTLhourly.[14], TIRTLhourly.[15], Int("3") AS TYPE
FROM TIRTLhourly
WHERE (((TIRTLhourly.DATE)=[forms]![tirtltest]![lstbegdate]) AND
((TIRTLhourly.TIME)>[forms]![tirtltest]![lstbegtime])) OR
(((TIRTLhourly.DATE)=[forms]![tirtltest]![lstenddate]));
 
J

John Vinson

I realized something...While testing the Run Query off the form into a brand
new query, when I didn't attach any table or query and simply created a field
referencing the text box, ie- Field: Site:[forms]![formname]![textbox]
worked. It came back with a datasheet giving me exactly what was in my form.
However, with the table/query attached, it got hung up with the Microsoft
Jet Database Engine not recognizing the statement. So why does access
recognize the statement with no table/query being present, but dosen't when
one is? Strange...
Here is what I got so far for my SQL:

SELECT [Forms]![tirtltest]![LstSite] AS SITE, TIRTLhourly.DATE,
TIRTLhourly.TIME, TIRTLhourly.LANE, TIRTLhourly.TOTAL, TIRTLhourly.[1],
TIRTLhourly.[2], TIRTLhourly.[3], TIRTLhourly.[4], TIRTLhourly.[5],
TIRTLhourly.[6], TIRTLhourly.[7], TIRTLhourly.[8], TIRTLhourly.[9],
TIRTLhourly.[10], TIRTLhourly.[11], TIRTLhourly.[12], TIRTLhourly.[13],
TIRTLhourly.[14], TIRTLhourly.[15], Int("3") AS TYPE
FROM TIRTLhourly
WHERE (((TIRTLhourly.DATE)=[forms]![tirtltest]![lstbegdate]) AND
((TIRTLhourly.TIME)>[forms]![tirtltest]![lstbegtime])) OR
(((TIRTLhourly.DATE)=[forms]![tirtltest]![lstenddate]));
Click to expand...

Well, first off, if you're using the textboxes lstbegdate, lstbegtime
and lstenddate as criteria for a query, they MUST be Unbound controls
- i.e. no control source. Otherwise you'll either get an error or
overwrite data in whatever record is currently displayed on the form
when you do a search.

Secondly, you criteria may not do what you want.As written it will
return records where DATE (a badly named field, it's a reserved word
and Access WILL get confused) is equal to lstbegdate provided that
TIME (another reserved word - change them both!) is later than
lstbegtime; OR any time at all on lstenddate. If the DATE is between
lstbegdate and lstenddate you won't get the record displayed.

Finally, any table with fields named 1 to 15 is almost certainly NOT
properly normalized.

COuld you explain what this query is intended to accomplish?

John W. Vinson[MVP]
 
K

Kou Vang

I took the form out of the equation and put the data I wanted into a table.
I tried running the query, referencing instead the table now instead of the
form. Same exact error?

Field: Site:
![field]

Can I use this statement?
 
J

John Vinson

I took the form out of the equation and put the data I wanted into a table.
I tried running the query, referencing instead the table now instead of the
form. Same exact error?

Field: Site:
![field]

Can I use this statement?
Click to expand...


No. There is no Table! object.

You can use DLookUp("[field]", "
", "<optional criteria>") to
retrieve a field from a table.

John W. Vinson[MVP]
 
K

Kou Vang

After quite a workaround, I got it John! Thanks for all the help and telling
me what wasn't working! That last piece did it with the dlookup!
 
Top