Open Query From Form Data

[hockeyman9474]

I have a form with a few different boxes for user input. One of which is a combobox. There is a button on a form that triggers a query. The information that is queried is what the user has entered into the control boxes on the form. The user can enter information into as many or as little fields as they wish, with at least one field being required. I have coded the criteria field on the query with (i.e. =[FORMS]![MainMenu]![CarLine]). So far it works fine. I have also coded other criteria fields in the query the same way (i.e. =[FORMS]![MainMenu]![PartNum]). If carline is filled in and partnum is left blank, then my query returns no data.

I think I need an iif statement that returns a wild card(*) if left blank.

can someone assist?

[offie]

I think:

=[FORMS]![MainMenu]![PartNum] & "*"

so that if [PartNum] is nothing then it will add "*" (everything)

[hockeyman9474]

Seems Logical but let's assume I use logic on both boxes and fill none of the boxes in, then it should return all records correct? That's what I did to test, and received no Data. I remember reading something similar in regards to passing variables to SQL and using LIKE in there. I decided that that approach was too complicated and to only link to SQL and do all the querying on access front end. I believe LIKE is a SQL keyword. Can't be used in Aceess, can it?

[hockeyman9474]

BTW my coding looks like this:
IIf([Forms]![MainMenu]![PartNum]="","*" & [Forms]![MainMenu]![PartNum] & "*",[Forms]![MainMenu]![PartNum])

[offie]

So what exactly are you trying to do?

Yes you can use Like in access, I do it ALL the time, yes you can do it in the SQL view but also the Design view.

In the 'Field:' row in the deisng view you can add
Name: Like [Forms]![MainMenu]![PartNum] & "*"

What are you trying to do with IIF?

[hockeyman9474]

I saw an example somewhere using iif to follow this logic:

if the field is blank/null(whichever it may be)then search all records.

By doing this, I can search records based on all user input fields filled in or not. More specifically:

I have 4 fields:
1. Carline
2. Accessory Code
3. Part Number
4.PO Number.
The user can enter any or all into these boxes on a form and click a submit button. A query will take this the user input and search a table that is actually a linked table to a SQL DB.

[hockeyman9474]

can't add that data to criteria field. Huh?

[offie]

Not the croteria field, the "Field" field, where the names go, this goes into the SQL as the SELECT

[offie]

Alright sorry didn't see the fist post

Assuming you have a form named MainMenu
And unbound text boxes, with names:
Carline
PartNum
PONum
ACode

Make a query: Add the table you want to use: Double click on all the fields you want to show up.

In the Criteria fields add
Like [Forms]![MainMenu]![Carline] & "*"
to any of the fields that you are searching though

See what happens, I'm pretty sure this will work

[hockeyman9474]

Let me ask you, given the information that I supplied, how would you approach?

[hockeyman9474]

oh wait I think it worked by putting like in the criteria field. I'll play around from there. Thanks for your help