Queries based on text box values

gmc5135

Registered User.
Local time
Today, 18:27
Joined
Jul 10, 2012
Messages
25
I have attached my database so you guys can see what I'm looking at. I would like the query to search my database based on what is entered in the text boxes in the form. Right now, it has pop-ups that prompt you to enter information, and even that isn't working right. Any help is appreciated. Thanks!
 

Attachments

Bob, attached is the 2003 version.
 

Attachments

Without an Access 2003 Version to look at, I can only provide an example for you to compare your code to.

[forms]![<YourFormNameHere>]![<YourControlNameHere>]

Example:

[forms]![frmEquipmentListing]![EquipModel]

You can use this format as a Reference to the Control in the Form.
 
Bob, attached is the 2003 version.
The form is called Form1 but in the query criteria you are calling it "searchform". Change the form name in the query to "Form1" or change the name of the form to "searchform" as I have done in the attached db.
 

Attachments

Thank you for the help. That was a stupid mistake on my part. Now that we got that straightened out, it still doesn't seem to be working. If you type a number into one of the text boxes and hit run query, it does not show any records. Maybe what I'm expecting it to do is different from the way it's set up, but if you type 2 into the text box next to enter lot, I expect all records that are lot 2 to show. Am I wrong?
 
I have just been looking at it. It works if you delete the "Like" from each of the criteria.
 
It works for me when you enter data all the text boxes. Any idea on how to make it work if you only type in one of the text boxes?
 
It works for me when you enter data all the text boxes. Any idea on how to make it work if you only type in one of the text boxes?
Yes. Put each line of criteria in a row of its own, instead of in the same row. Or change to SQL view and replace the "AND"s in the WHERE clause with "OR"s.
 
Sorry to be a pain, but when I do that and try to run it, I get a prompt saying, "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." Doesn't seem to me that this is that complex of a query.
 
Sorry to be a pain, but when I do that and try to run it, I get a prompt saying, "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." Doesn't seem to me that this is that complex of a query.
You are not being a pain. I should have tested it before posting. I will look at it again.
 
For some reason it started working now. Thanks so much for all the help!
 
And now it's not working again. Funny how these things work.
 
Ok so this is what I've gathered. If you clear parameters, then try the single text box, it will not work. However, if you type a value into all the text boxes, it does work. Then after you do that, you can click on the text boxes and hit backspace, then the single text box thing works. So I'm guessing either my clear parameters button, or the fact that I put "" for the default value is causing the problem.
 
OK, Try this. Remove the "" default values from the text boxes. Use the SQL below for the query:
Code:
SELECT Alloys.Alloy, Alloys.OxSample, Alloys.IngotNo, Alloys.LotNo
FROM Alloys
WHERE (((Alloys.Alloy)=[forms]![searchform].[qalloy] Or [forms]![searchform].[qalloy] Is Null) AND ((Alloys.OxSample)=[forms]![searchform].[qsample] Or [forms]![searchform].[qsample] Is Null) AND ((Alloys.IngotNo)=[forms]![searchform].[qingot] Or [forms]![searchform].[qingot] Is Null) AND ((Alloys.LotNo)=[forms]![searchform].[qlot] Or [forms]![searchform].[qlot] Is Null));
 
Kind of the same thing as before, except that instead of saying it's too complicated, it just does not show any records, as though nothing matched.
 
It is working for me so:
Are you sure you have copied the SQL corectly.
Are you moving the focus away from the last text box that you enter a number into, before you open the query.
 
It works when you first open the form, but doesn't seem to work when you hit the clear parameters button, then try it.
 
It works when you first open the form, but doesn't seem to work when you hit the clear parameters button, then try it.
I think that is because the clear parameters button sets the value of each control to "". Replace the Quotes will Null
 
Works perfectly! Thank you so much for all your help.
 

Users who are viewing this thread

Back
Top Bottom