Need help adding date parameters

[palgya]

I have a form the user enters criteria on that searches a table for information with a query that I need to add date parameters to. I have an idea how to do it but can't quite get the syntax to work properly.

Private Sub Search_Click()
Dim trainerList As String, schoolList As String, townList As String, sportList As String, bodypartList As String, injList As String, sourceList As String
Dim item As Variant, whereClause As String

'loop thru each combo box and build a comma separated list of selected options
'check for (All) and clear criteria when present
For Each item In trainer.ItemsSelected
If trainerList <> &quot;&quot; Then trainerList = trainerList & &quot;,&quot;
trainerList = trainerList & &quot;'&quot; & trainer.ItemData(item) & &quot;'&quot;
If trainer.ItemData(item) = &quot;All&quot; Then
trainerList = &quot;&quot;
Exit For
End If
Next

For Each item In school.ItemsSelected
If schoolList <> &quot;&quot; Then schoolList = schoolList & &quot;,&quot;
schoolList = schoolList & &quot;'&quot; & school.ItemData(item) & &quot;'&quot;
If school.ItemData(item) = &quot;All&quot; Then
schoolList = &quot;&quot;
Exit For
End If
Next



For Each item In town.ItemsSelected
If townList <> &quot;&quot; Then townList = townList & &quot;,&quot;
townList = townList & &quot;'&quot; & town.ItemData(item) & &quot;'&quot;
If town.ItemData(item) = &quot;All&quot; Then
townList = &quot;&quot;
Exit For
End If
Next

For Each item In sport.ItemsSelected
If sportList <> &quot;&quot; Then sportList = sportList & &quot;,&quot;
sportList = sportList & &quot;'&quot; & sport.ItemData(item) & &quot;'&quot;
If sport.ItemData(item) = &quot;All&quot; Then
sportList = &quot;&quot;
Exit For
End If
Next

For Each item In bodypart.ItemsSelected
If bodypartList <> &quot;&quot; Then bodypartList = bodypartList & &quot;,&quot;
bodypartList = bodypartList & &quot;'&quot; & bodypart.ItemData(item) & &quot;'&quot;
If bodypart.ItemData(item) = &quot;All&quot; Then
bodypartList = &quot;&quot;
Exit For
End If
Next

For Each item In inj.ItemsSelected
If injList <> &quot;&quot; Then injList = injList & &quot;,&quot;
injList = injList & &quot;'&quot; & inj.ItemData(item) & &quot;'&quot;
If inj.ItemData(item) = &quot;All&quot; Then
injList = &quot;&quot;
Exit For
End If
Next

For Each item In Source.ItemsSelected
If Source <> &quot;&quot; Then sourceList = sourceList & &quot;,&quot;
sourceList = sourceList & &quot;'&quot; & Source.ItemData(item) & &quot;'&quot;
If Source.ItemData(item) = &quot;All&quot; Then
sourceList = &quot;&quot;
Exit For
End If
Next

'now we will build a where clause with fields mapped to SearchQuery
'if list is empty, this means that &quot;All&quot; was selected so no criteria is needed;
'do not add to whereclause
If trainerList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; trainer in (&quot; & trainerList & &quot &quot;
End If
If schoolList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; school in (&quot; & schoolList & &quot &quot;
End If
If townList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; town in (&quot; & townList & &quot &quot;
End If
If sportList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; sport in (&quot; & sportList & &quot &quot;
End If
If bodypartList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; bodypart in (&quot; & bodypartList & &quot &quot;
End If
If injList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; inj in (&quot; & injList & &quot &quot;
End If
If sourceList <> &quot;&quot; Then
If whereClause <> &quot;&quot; Then whereClause = whereClause & &quot; and &quot;
whereClause = whereClause & &quot; Source in (&quot; & sourceList & &quot &quot;
End If

'MsgBox whereClause

DoCmd.OpenReport &quot;rptSearchQuery&quot;, acViewPreview, , whereClause


End Sub


Can someone lend me a hand? Thanks in advance.
Paul

 

[zemp]

Try surrounding the date parameter with '#'.

Where <datefield.=#&quot; & date & &quot;#&quot; Thanks and Good Luck!

zemp

 

[palgya]

Thanks zemp,
If I were to specify a startdate and an enddate would it be like this?

Where <datefield.=between #&quot; & startdate & &quot;#&quot; and
#&quot; & enddate & &quot;#&quot;

And what if the field is left null by the user? Sorry for being incompetant but I'm new at this.
Thanks again
Paul

 

[palgya]

Here's what I did to solve the problem of date's.
In the query sql:

SELECT Table1.date, Table1.school, Table1.town, Table1.trainer, Table1.bodypart, Table1.sport, Table1.inj, Table1.Source
FROM Table1
WHERE (((Table1.date)>=[Forms]![queryform]![startdate] And (Table1.date)<=[Forms]![queryform]![enddate])) OR (((Table1.date)<=[Forms]![queryform]![enddate]) AND (([Forms]![queryform]![startdate]) Is Null)) OR (((Table1.date)>=[Forms]![queryform]![startdate]) AND (([Forms]![queryform]![enddate]) Is Null)) OR ((([Forms]![queryform]![startdate]) Is Null) AND (([Forms]![queryform]![enddate]) Is Null))
ORDER BY Table1.date;