Query Question

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

In a query that I have, I have three input boxes where upon running
the query the 1st input box appears and you type what you need in it,
then the 2nd appears, etc then the 3rd appears then the results
appear.

On the 3rd box I have [Class] in the criteria line to prompt the input
box, but if I don't want to input anything in this box, i.e I only
want the 1st and 2nd input box as the filter, how do I bypass it or
click it where it won't pull any records. When I click ok on the 3rd
box, I'm assuming it brings up a search or "nothing" since I didn't
enter anything in there and no results come back. Does this make
sense? I was thinking that I could put the [Class] in the criteria and
also do an if statement or something stating that if [class] is null
then do "*##*" as a search. There's only 2 numbers in this field.

Ryan
 
In a query that I have, I have three input boxes where upon running
the query the 1st input box appears and you type what you need in it,
then the 2nd appears, etc then the 3rd appears then the results
appear.
Click to expand...

I'd really recommend using a Form (unbound, named frmCrit let's say) rather
than popup prompts. Use criteria like

=[Forms]![frmCrit]![controlname]

instead of [Class]. This lets you use the same criteria for multiple queries,
keeps your users happier, and lets you do things like use a Combo Box on the
form so the user can pick a class from a list rather than having to type it
exactly correct.
On the 3rd box I have [Class] in the criteria line to prompt the input
box, but if I don't want to input anything in this box, i.e I only
want the 1st and 2nd input box as the filter, how do I bypass it or
click it where it won't pull any records. When I click ok on the 3rd
box, I'm assuming it brings up a search or "nothing" since I didn't
enter anything in there and no results come back. Does this make
sense? I was thinking that I could put the [Class] in the criteria and
also do an if statement or something stating that if [class] is null
then do "*##*" as a search. There's only 2 numbers in this field.
Click to expand...

Use a criterion on the Class field of

= [Class] OR [Class] IS NULL

(replacing [Class] by the forms reference if you take my advice above, of
course). Access will make the query grid look REALLY wierd if you do this for
more than one field, but it should still work.
 
In a query that I have, I have three input boxes where upon running
the query the 1st input box appears and you type what you need in it,
then the 2nd appears, etc then the 3rd appears then the results
appear.

On the 3rd box I have [Class] in the criteria line to prompt the input
box, but if I don't want to input anything in this box, i.e I only
want the 1st and 2nd input box as the filter, how do I bypass it or
click it where it won't pull any records. When I click ok on the 3rd
box, I'm assuming it brings up a search or "nothing" since I didn't
enter anything in there and no results come back. Does this make
sense? I was thinking that I could put the [Class] in the criteria and
also do an if statement or something stating that if [class] is null
then do "*##*" as a search. There's only 2 numbers in this field.

Ryan
Click to expand...

Say I have 3 comboboxes (or textboxes, etc.) where I input criteria.

I would have something like this in the AfterUpdate event of each of the
controls.

==================
Dim s As String
Dim c As Variant

' add to WHERE clause criteria
If Not IsNull(Me.cbo_select_column1_id) Then
c = (c + " AND ") & "column1_id = '" & Me.cbo_select_column1_id & "'"
End If

' add to WHERE clause criteria
If Not IsNull(Me.cbo_select_column2_id) Then
c = (c + " AND ") & "column2_id = '" & Me.cbo_select_column2_id & "'"
End If

' add to WHERE clause criteria
If Not IsNull(Me.cbo_select_column3_id) Then
c = (c + " AND ") & "column3_id = '" & Me.cbo_select_column3_id & "'"
End If

' eliminate initial AND
If Left(c, 5) = " AND " Then
c = Mid(c, 6)
End If

' if c is empty then use zero
If IsNnull(c) Then c = "0"

' combine initial SQL string and WHERE clause criteria
s = "SELECT * FROM SOmeTable WHERE " & c & ";"


Me.RecordSource = s

===================

Maybe just put this in a Subprogram, called say "QueryControl().

Now in each of the controls AfterUpdate event, just put
Call QueryControl.

Note that the above is not real code. Your table name,control names, column
names, and datatypes are going to be different. This allows you to enter as
many criteria as you need and in any order, and have the form recordsource
update after each entry.
 
This worked perfectly. [Class] OR [Class] IS NULL

Exactly what I wanted thanks!

Ryan


In a query that I have, I have three input boxes where upon running
the query the 1st input box appears and you type what you need in it,
then the 2nd appears, etc then the 3rd appears then the results
appear.
Click to expand...

I'd really recommend using a Form (unbound, named frmCrit let's say) rather
than popup prompts. Use criteria like

=[Forms]![frmCrit]![controlname]

instead of [Class]. This lets you use the same criteria for multiple queries,
keeps your users happier, and lets you do things like use a Combo Box on the
form so the user can pick a class from a list rather than having to type it
exactly correct.
On the 3rd box I have [Class] in the criteria line to prompt the input
box, but if I don't want to input anything in this box, i.e I only
want the 1st and 2nd input box as the filter, how do I bypass it or
click it where it won't pull any records. When I click ok on the 3rd
box, I'm assuming it brings up a search or "nothing" since I didn't
enter anything in there and no results come back. Does this make
sense? I was thinking that I could put the [Class] in the criteria and
also do an if statement or something stating that if [class] is null
then do "*##*" as a search. There's only 2 numbers in this field.
Click to expand...

Use a criterion on the Class field of

= [Class] OR [Class] IS NULL

(replacing [Class] by the forms reference if you take my advice above, of
course). Access will make the query grid look REALLY wierd if you do this for
more than one field, but it should still work.
Click to expand...
 
Back
Top