where condition

[digs]

I've got a form with unbound fields called O, C, JT, SL, SH, D1 and D2. what I'm trying to do is let users choose 1 or more records to serach on, which I've managed to do with

DoCmd.OpenReport "External Post Ads", acViewPreview, , "[Ad_Date] BETWEEN #" & Format$(Me![d1], "yyyy-mm-dd") & "# AND #" & Format$(Me![d2], "yyyy-mm-dd") & "# and [Organisation] Like '*" & Me.[O] & "*' and [Job_Title] Like '*" & Me.[JT] & "*' and [contract] Like '*" & Me.[C] & "*' AND [Salary_Range_low] >= '" & Me.[sl] & "' or [Salary_Range_HIGH] <= '" & Me.[sh] & "'", acWindowNormal

What I'm having problem with is if they do not enter any information on the form and press the search button i want the report to show all records but all I'm getting at the moment is a blank report even though I've got data in the table.

Can anyone help me its driving me mad.

 

[SammyJ]

You need to build up your criteria based only on the fields where there is a value.

Taking just a couple as an example I'd do this:

Dim strWhere As String

if len([d1])>0 And len([d2])>0 then
  strWhere = strWhere & " AND [Ad_Date] BETWEEN #" & Format$(Me![d1], "yyyy-mm-dd") & "# AND #" & Format$(Me![d2], "yyyy-mm-dd") & "#"
end if 

if len([O])>0 then
  strWhere = strWhere & " AND [Organisation] Like '*" & Me.[O] & "*'"
end if

'Then all the others

strWhere = mid(strWhere, 6)  'Strips out the first AND

And then pass this to your OpenReport

Might not exactly run as I haven't tested it, but you get the idea.

BTW, I always throw this into my databases. Very handy for simplifying code which needs to insert dates into strings which will be treated as SQL:

Public Function USDate(dtmDate As Date) As String
    USDate = Format(dtmDate, "\#mm/dd/yyyy\#")
End Function

The long line would then look like:

strWhere = strWhere & " AND [Ad_Date] BETWEEN " & USDate(Me![d1]) & " AND " & USDate(Me![d2])