Problem with form entering any of 6 criteria 1

[jimbo62]

I have a table with date field(-Date Range-), warehouse, part number field, reason code field, Catalog Number, Dollar ammount field.

I want to use a form to Pass any Entered Input for the multiple criteria in a query from a single form.

Example ..If the user wants to have all records with the specified part number but chooses not to enter any of the other data he would recieve all records referencing the part number regardless of dates or any other criteria with nothing entered into the form in other criteria referenced fields.

In other words user has one for and can specify any of the criteria ignoring the empty criteria.

I have a good idea and have tried many approaches but am unable to get my statements set up to to ignore the empty criteria.


I hope I described it well enough. if anyone coul suggest a good way please respond

Thanks,

Jim

 

[cmmrfrds]

The easiest way is to make global variables in the standard module for each of the search criteria and then build the sql statement dynamically in the on open event of the form.

abbeviated example.
Dim sql1 as String

sql1 = "select * from yourtable where "
If var1 > "" then
sql1 = sql1 & "txtvar1 = " & """ & var1 & """
end if
If var2 > "" then
if var1 > "" then
sql1 = sql1 & " AND "
end if
sql1 = sql1 & "txtvar2 = " & """ & var2 & """
end if
etc......
Me.RecordSource = sql1

 

[WayneRyan]

Jim,


strSQL = "Select * from yourtable " & _
"Where (Field1 = '" & txtVar1 & "' or txtVar1 = '') And " & _
" (Field2 = '" & txtVar2 & "' or txtVar2 = '') And " & _
" (Field3 = '" & txtVar3 & "' or txtVar3 = '') And " & _
" (Field4 = '" & txtVar4 & "' or txtVar4 = '') And " & _
" (Field5 = '" & txtVar5 & "' or txtVar5 = '') And " & _
" (Field6 = '" & txtVar6 & "' or txtVar6 = '');"

This assumes that you default the txtVar fields to "".
You can also use LIKE for more flexibility.
You can also use IsNull.
Sorry about the text wrapping.

hth,
Wayne

 

[jimbo62]

Here is how I did it!!!

Thanks for everyone who responded..

Under a run report command button...

stDocName = "LD_ByDollarAmt"

Dim sWhere As String
'make sure we have an end date and a start date - assumming they are
'required.
If IsNull(Me!StartDate) = True Then
MsgBox "You must enter a start date", , "LD Report Generator"
End If

If IsNull(Me!EndDate) = True Then
MsgBox "You must enter an end date.", , "LD Report Generator"

End If


sWhere = &quot;[qryLD_Dataq_byDollar].date >= #&quot; & Me!StartDate & &quot;# and [qryLD_Dataq_byDollar].date <= #&quot; & Me!EndDate & &quot;#&quot;

If IsNull(Me!Warehouse) = False Then
sWhere = sWhere & &quot; and [qryLD_Dataq_byDollar].WHSE = '&quot; & Me!Warehouse & &quot;'&quot;
End If

And so on for each text box on the record selection form.


DoCmd.OpenReport stDocName, acViewPreview, , sWhere

Enjoy Jim