Getting and/or parameter queries to work

[mindbender]

Hi again,

I have a form with 3 unbound fields on them that pops up when a user logs in.


When the user clicks ok it opens the main form which is run off of a query.

My issue is, the users will at times populate 1, 2 or all 3 fields for searching and I cannot figure out how to get the query to run right as it is either and and or an or.

Is there a way to open the form using VB that will say if 1 field is populated open with that criteria, if 2 fields are populated open with both of those criteria combined, if all 3 fields are populated open with all three combined? It could be any combination of the three fields.

Thanks as always

[ranman256]

If you have a continuous form that shows all records,
build the 'where' clause by cycling thru all the controls,then it filters the list.

it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Or, alter an existing query on the fly to open a select query

'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef

sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name

end sub

[mindbender]

If you have a continuous form that shows all records,
build the 'where' clause by cycling thru all the controls,then it filters the list.

it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Or, alter an existing query on the fly to open a select query

'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef

sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name

end sub

Thank you for the help again ranman256.

I have the first part set

Private Sub OK_BUTTON_Click()


Dim sWhere As String
Dim qdf As QueryDef


sWhere = " where 1=1"
If Not IsNull(PRODCODE) Then sWhere = sWhere & " and [PROD_CD]='" & PRODCODE & "'"
If Not IsNull(CARRIER) Then sWhere = sWhere & " and [SUB_LOC_CD]='" & CARRIER & "'"
If Not IsNull(MKTCODE) Then sWhere = sWhere & " and [MKT_CD]='" & MKTCODE & "'"

and it seems to like it but When I try to use the second half to call the query it does not seem to like it.

The qry to call is qry_USED so I have this entered

 set qdf = currentdb.querydefs("qry_USED")

And the sql to the query is as follows

"SELECT DISTINCTROW WHISC_AECS_EDB.MAINFR_SER_NO, WHISC_AECS_EDB.PROD_CD, WHISC_AECS_EDB.SUB_LOC_CD, ALL_ACCESSORIES.MKT_CD, WHISC_AECS_EDB.MOD_CD, WHISC_AECS_EDB.REF_NO, WHISC_AECS_EDB.PHYS_BUDG_CTR, WHISC_AECS_EDB.EQUIP_STAT_CD, WHISC_AECS_EDB.PHYS_STAT_CD, WHISC_AECS_EDB.RECVD_DT, ALL_ACCESSORIES.SO_NO _FROM (WHISC_AECS_EDB INNER JOIN WHISC_JIT_CARRIER ON WHISC_AECS_EDB.SUB_LOC_CD = WHISC_JIT_CARRIER.CARRIER) INNER JOIN ALL_ACCESSORIES ON WHISC_AECS_EDB.MAINFR_SER_NO = ALL_ACCESSORIES.MAINFR_SER_NO
GROUP BY WHISC_AECS_EDB.MAINFR_SER_NO, WHISC_AECS_EDB.PROD_CD, WHISC_AECS_EDB.SUB_LOC_CD, ALL_ACCESSORIES.MKT_CD, WHISC_AECS_EDB.MOD_CD, WHISC_AECS_EDB.REF_NO, WHISC_AECS_EDB.PHYS_BUDG_CTR, WHISC_AECS_EDB.EQUIP_STAT_CD, WHISC_AECS_EDB.PHYS_STAT_CD, WHISC_AECS_EDB.RECVD_DT, ALL_ACCESSORIES.SO_NO
HAVING (((WHISC_AECS_EDB.PHYS_BUDG_CTR) <> "1XF0") And ((WHISC_AECS_EDB.PHYS_STAT_CD) = "024"))
ORDER BY WHISC_AECS_EDB.SUB_LOC_CD "

Then the final piece

 & swhere qdf.close docmd.openquery qdf.name

Again I appreciate your help on this.

[mindbender]

Thank you for the help again ranman256.

I have the first part set

Private Sub OK_BUTTON_Click()


Dim sWhere As String
Dim qdf As QueryDef


sWhere = " where 1=1"
If Not IsNull(PRODCODE) Then sWhere = sWhere & " and [PROD_CD]='" & PRODCODE & "'"
If Not IsNull(CARRIER) Then sWhere = sWhere & " and [SUB_LOC_CD]='" & CARRIER & "'"
If Not IsNull(MKTCODE) Then sWhere = sWhere & " and [MKT_CD]='" & MKTCODE & "'"

and it seems to like it but When I try to use the second half to call the query it does not seem to like it.

The qry to call is qry_USED so I have this entered

 set qdf = currentdb.querydefs("qry_USED")

And the sql to the query is as follows

"SELECT DISTINCTROW WHISC_AECS_EDB.MAINFR_SER_NO, WHISC_AECS_EDB.PROD_CD, WHISC_AECS_EDB.SUB_LOC_CD, ALL_ACCESSORIES.MKT_CD, WHISC_AECS_EDB.MOD_CD, WHISC_AECS_EDB.REF_NO, WHISC_AECS_EDB.PHYS_BUDG_CTR, WHISC_AECS_EDB.EQUIP_STAT_CD, WHISC_AECS_EDB.PHYS_STAT_CD, WHISC_AECS_EDB.RECVD_DT, ALL_ACCESSORIES.SO_NO _FROM (WHISC_AECS_EDB INNER JOIN WHISC_JIT_CARRIER ON WHISC_AECS_EDB.SUB_LOC_CD = WHISC_JIT_CARRIER.CARRIER) INNER JOIN ALL_ACCESSORIES ON WHISC_AECS_EDB.MAINFR_SER_NO = ALL_ACCESSORIES.MAINFR_SER_NO
GROUP BY WHISC_AECS_EDB.MAINFR_SER_NO, WHISC_AECS_EDB.PROD_CD, WHISC_AECS_EDB.SUB_LOC_CD, ALL_ACCESSORIES.MKT_CD, WHISC_AECS_EDB.MOD_CD, WHISC_AECS_EDB.REF_NO, WHISC_AECS_EDB.PHYS_BUDG_CTR, WHISC_AECS_EDB.EQUIP_STAT_CD, WHISC_AECS_EDB.PHYS_STAT_CD, WHISC_AECS_EDB.RECVD_DT, ALL_ACCESSORIES.SO_NO
HAVING (((WHISC_AECS_EDB.PHYS_BUDG_CTR) <> "1XF0") And ((WHISC_AECS_EDB.PHYS_STAT_CD) = "024"))
ORDER BY WHISC_AECS_EDB.SUB_LOC_CD "

Then the final piece

 & swhere qdf.close docmd.openquery qdf.name

Again I appreciate your help on this.

to add more info - the button on the parameter form opens another form that is driven from the query qry_USED.

[JSR]

Back to the beginning... I see a problem:

product code 1234
AND
carrier code 5678

vs

product code 1234
OR
carrier code 5678

are the same entries in the form, but the results are different. Access needs to know from the user whether to use "and" or "or". Further complicating this is that you have three data entries. A and B or C (also A or B and C) are not clear as to which goes first, "and" or "or".

[ssanfu]

Here is an example of a search form that allows you to select "And" and "Or" and the fields, then generates the SQL. Download the "findrecord2k" version. In the form, click the "?" button for Help.

Adding a Search form to a database