Query Tied to Form doesn't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a button on a form that populates a text box on the same form with
text. I have a query where a field's criteria is a reference to the text box
on the form so the text in the text box becomes the criteria for the query.

After I click the button to populate the text box on the form and then run
the query, it comes up blank (should be records). If I click the button on
the form and copy the text from the text box to the query (in stead of a
reference to the text box on the form) the query works fine.

Any ideas why the query won't work with the reference to the text box on form?

Thanks,

Joel
 
I have a button on a form that populates a text box on the same form with
text. I have a query where a field's criteria is a reference to the text box
on the form so the text in the text box becomes the criteria for the query.

After I click the button to populate the text box on the form and then run
the query, it comes up blank (should be records). If I click the button on
the form and copy the text from the text box to the query (in stead of a
reference to the text box on the form) the query works fine.

Any ideas why the query won't work with the reference to the text box on form?

Thanks,

Joel
Click to expand...

It would have been be so much simpler to help you if you had only
posted the exact SQL of the query. Without it, we can only guess what
you ave done, or not done.
Please try again. Post the query SQL (and include an example of the
data the field might contain).
 
I'm so sorry...you are absolutely correct...

Here is the text created by code that fills a text box named test on a form:
(>=1601000 And <=1601215) Or (>=1601800 And <=1602316 And <>1601920)

The query criteria in the query design is Forms![Main Menu]![test] (the
text box on the form). The query doesn't work with the text box reference
but if I copy the above criteria to the query, it works fine.

Thanks so much for your help.

Joel
 
I have a button on a form that populates a text box on the same form with
text. I have a query where a field's criteria is a reference to the text box
on the form so the text in the text box becomes the criteria for the query.

After I click the button to populate the text box on the form and then run
the query, it comes up blank (should be records). If I click the button on
the form and copy the text from the text box to the query (in stead of a
reference to the text box on the form) the query works fine.

Any ideas why the query won't work with the reference to the text box on form?
Click to expand...

No... because you don't show how you're setting it or how you're using it as a
criterion. Care to post your code and the SQL of the query?

John W. Vinson [MVP]
 
Code to generate test is:

Private Sub Command0_Click()
test = "(>=1601000 And <=1601215) Or (>=1601800 And <=1602316 And <>1601920)"
End Sub

The query criteria references the text box with:
Forms![Main Menu]![test]


SELECT [Incident and Injury].[Dept Acct Number]FROM [Incident and Injury]
WHERE ((([Incident and Injury].[Dept Acct Number])=[Forms]![Main
Menu]![test]));

Once again, it works if I copy the text generated by the code from the text
box to the query but the above SQL doesn't work when there is the reference
to the text box on the form...

Thanks,

Joel
 
Code to generate test is:

Private Sub Command0_Click()
test = "(>=1601000 And <=1601215) Or (>=1601800 And <=1602316 And <>1601920)"
End Sub

The query criteria references the text box with:
Forms![Main Menu]![test]


SELECT [Incident and Injury].[Dept Acct Number]FROM [Incident and Injury]
WHERE ((([Incident and Injury].[Dept Acct Number])=[Forms]![Main
Menu]![test]));

Once again, it works if I copy the text generated by the code from the text
box to the query but the above SQL doesn't work when there is the reference
to the text box on the form...
Click to expand...

You cannot pass operators such as >= using a Parameter query.

Rather than building the SQL string WHERE clause into a textbox, you'll need
to use VBA code to build up the entire SQL string, from the SELECT... through
the ORDER BY. You can then assign this string to the Recordsource of a form or
report.

John W. Vinson [MVP]
 
Thanks so much for your help...

can one use Between and And in a reference for a parameter query?

Thanks.

Joel
John W. Vinson said:
Code to generate test is:

Private Sub Command0_Click()
test = "(>=1601000 And <=1601215) Or (>=1601800 And <=1602316 And <>1601920)"
End Sub

The query criteria references the text box with:
Forms![Main Menu]![test]


SELECT [Incident and Injury].[Dept Acct Number]FROM [Incident and Injury]
WHERE ((([Incident and Injury].[Dept Acct Number])=[Forms]![Main
Menu]![test]));

Once again, it works if I copy the text generated by the code from the text
box to the query but the above SQL doesn't work when there is the reference
to the text box on the form...
Click to expand...

You cannot pass operators such as >= using a Parameter query.

Rather than building the SQL string WHERE clause into a textbox, you'll need
to use VBA code to build up the entire SQL string, from the SELECT... through
the ORDER BY. You can then assign this string to the Recordsource of a form or
report.

John W. Vinson [MVP]
Click to expand...
 
No.

All a parameter can be is a reference to a value, not to an operator.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Thanks so much for your help...

can one use Between and And in a reference for a parameter query?

Thanks.

Joel
John W. Vinson said:
Code to generate test is:

Private Sub Command0_Click()
test = "(>=1601000 And <=1601215) Or (>=1601800 And <=1602316 And
<>1601920)"
End Sub

The query criteria references the text box with:
Forms![Main Menu]![test]


SELECT [Incident and Injury].[Dept Acct Number]FROM [Incident and
Injury]
WHERE ((([Incident and Injury].[Dept Acct Number])=[Forms]![Main
Menu]![test]));

Once again, it works if I copy the text generated by the code from the
text
box to the query but the above SQL doesn't work when there is the
reference
to the text box on the form...
Click to expand...

You cannot pass operators such as >= using a Parameter query.

Rather than building the SQL string WHERE clause into a textbox, you'll
need
to use VBA code to build up the entire SQL string, from the SELECT...
through
the ORDER BY. You can then assign this string to the Recordsource of a
form or
report.

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
Back
Top