Using multiple field criteria from textboxs on Query form

[FNA access]

Hello to the world of the wise,

I am a CSOM student at TRU. I am trying to design a database to improve my understanding.

The problem I am having is with setting up a query. I have a Query Form that has multilple controls to narrow the results of the query.

The functionality I am looking for is to allow a user to enter text into each of the textboxes or into couple or into none. Each textbox corresponds to a field in a Table.

I have tried:

Like '[Forms]![ReportPage]![StatusTextbox]' & '*' entered in a criteria line corresponding to the Status field.

I have also tried using doulbe quotes ( " ) instead of the single quotes ( ' ).

If I have information in each text box it returns the query executes perfectly. If I leave one textbox blank, no results are returned.

I am reletively new with access, but I do have some VB 2005 knowledge.

Once I have the correct format I will be able to apply it to each field

Any input would be greatly appreciated.

Thanks in advance

[JAM986]

Hello to the world of the wise,

I am a CSOM student at TRU. I am trying to design a database to improve my understanding.

The problem I am having is with setting up a query. I have a Query Form that has multilple controls to narrow the results of the query.

The functionality I am looking for is to allow a user to enter text into each of the textboxes or into couple or into none. Each textbox corresponds to a field in a Table.

I have tried:

Like '[Forms]![ReportPage]![StatusTextbox]' & '*' entered in a criteria line corresponding to the Status field.

I have also tried using doulbe quotes ( " ) instead of the single quotes ( ' ).

If I have information in each text box it returns the query executes perfectly. If I leave one textbox blank, no results are returned.

I am reletively new with access, but I do have some VB 2005 knowledge.

Once I have the correct format I will be able to apply it to each field

Any input would be greatly appreciated.

Thanks in advance

Just curious, but how many fields are there for this particulary form? I have run into this situation some time back and will look to see if I can get specifics on it.

[FNA access]

Just curious, but how many fields are there for this particulary form? I have run into this situation some time back and will look to see if I can get specifics on it.

I have a :
- Date Start Textbox
- Date End Textbox
- Status Textbox
- Location Combobox
- Type Combobox
- Product # TextBox
- Model Name Combobox

[JAM986]

I have a :
- Date Start Textbox
- Date End Textbox
- Status Textbox
- Location Combobox
- Type Combobox
- Product # TextBox
- Model Name Combobox

1 last question (sorry, I should have included it in the original post). Is all the data in one table or spread across multiple tables?

[FNA access]

No problem, the help is greatly appreciated. All the information except the date field is stored in one table. The two tables are linked through an autonumber ID.

Thank you for taking the time to help me.

[JAM986]

No problem, the help is greatly appreciated. All the information except the date field is stored in one table. The two tables are linked through an autonumber ID.

Thank you for taking the time to help me.

No prob. I hope that I can. Anyway, in the code below I am searching for individuals by first AND last name OR by first name only or last name only. It then opens another form and the queried data is displayed on the newly opened form. I believe that it what you are doing with your data. Because you are using more criteria, it may be a bit more lengthy but this logic does work.

Options

1.You may want to consider using additional command buttons to narrow the criteria. For example, search by product ID and date or Model and Name brand...etc.
2. Another way that may help is to limit the search criteria to return more rows and let the user pick from that queried set.
3. In your code, check first to see if the fields are blank or not. Assign the textbox and combobox values to a variable(s) in the code. Which ever field is blank, assign a min/max value to that variable to return all rows. For example,

If !forms!frmGetDa ta!textbox.Mode lName = " " then
myVariableForMo delName = "ZZZZZZZZZZ "
EndIf

Then execute your query after assigning all the blank form fields a value.

Hope This helps. Let me know.
----------------------------------------------------
Heres the code

Private Sub cmdSubmitINQ_Cl ick()
On Error GoTo Err_cmdSubmitIN Q_Click
'This procedure queries the for hire drivers based on their name
Dim stDocName As String
Dim stLinkCriteria As String
Dim strsql As String
Dim dbsn As Database
Dim NameFirst As String
Dim NameLast As String

NameFirst = Me!txtFHfirst & " "
NameFirst = "'" & NameFirst & "'" <----Single quote between two double quotes
NameLast = Me!txtFHlast & " "
NameLast = "'" & NameLast & "'" <----Single quote between two double quotes

Set dbsn = CurrentDb

strsql = "Select fname, lname, dob,fhNumber into LookupFH"
strsql = strsql & " From tblForHirePerso nal"
strsql = strsql & " Where tblForHirePerso nal.fname= " & NameFirst
strsql = strsql & " or tblForHirePerso nal.lname= " & NameLast
dbsn.Execute strsql

DoCmd.Close
stDocName = "frmForHirePick "
DoCmd.OpenForm stDocName, , , stLinkCriteria

[FNA access]

I will work with this in my code and try it out. With all my parameters it will be pretty big. Too bad I can't use a like in the criteria box in a query design. I'm a little confused with some parts.

How does ZZZZZZ return all values? Is there a certain number of them you should use? (ie 1 Z over 3 ZZZ)

Where does your code test for blanks so to return all values?
I see the if statement above, but don't see it implemented.

Thanks again

[FNA access]

I will work with this in my code and try it out. With all my parameters it will be pretty big. Too bad I can't use a like in the criteria box in a query design. I'm a little confused with some parts.

How does ZZZZZZ return all values? Is there a certain number of them you should use? (ie 1 Z over 3 ZZZ)

Where does your code test for blanks so to return all values?
I see the if statement above, but don't see it implemented.

Thanks again

I figured out some code to place in the criteria lines of the query builder to save the manual coding.

I used :

Criteria Like [Forms]![ReportPage]![LocationCombobo x] & '*'
or [Forms]![ReportPage]![LocationCombobo x] is Null

With this I can either have data in to limit the scope or I can leave blank to retrieve all info.

I placed this code in each one of my criteria boxes. One of my criteria must be entered (TypeCombobox).

Thank you very much for your time

[FNA access]

I figured out some code to place in the criteria lines of the query builder to save the manual coding.

I used :

Criteria Like [Forms]![ReportPage]![LocationCombobo x] & '*'
or [Forms]![ReportPage]![LocationCombobo x] is Null

With this I can either have data in to limit the scope or I can leave blank to retrieve all info.

I placed this code in each one of my criteria boxes. One of my criteria must be entered (TypeCombobox).

Thank you very much for your time

Never Mind it doesn't work all the time. I found some faults in it. Will keep working on it.

[JAM986]

Never Mind it doesn't work all the time. I found some faults in it. Will keep working on it.

Sorry,
been away from the computer for a while. Let me give you an example that I experimented with using first and last names.

If the first name is left blank and only the last name has a value, I would assign the value "ZZZZZZZZ" to the first name then query everything where the first name is LESS that the value I have assigned it thus returning all rows where I have a match on the last name. Same sort of way you would do with 2 date fields by assigning a blank beginning date the value 01/01/1001 and the ending date field a date in the future.

Hope this makes it a little clearer.

I'll check in more frequently with ya. Its been a busy last couple of days

[MMcCarthy]

Without seeing your full query it's difficult to say but I would guess something like the following would work

Like IIf([Forms]![ReportPage]![StatusTextbox]="","*",[Forms]![ReportPage]![StatusTextbox] & "*")