Str where and muliple criteria problem

Hi Folks

I wondered if someone could point me in the right direction before I
completely tear my hair out.
I have a user selection form where options can be selected for a report.
Users now want to also filter the options by date selections or not if they
wish.

I added to unbound text fields to input the start and end dates and inserted
them into my str Where code. It was working fine until the these were added.
Now I get a missing syntax error, or it completely ignores the dates entered
if other options and the dates are selected.I cannot seem to make this work
if a user selects options and dates, it only works if the date input boxes
are ignored.

I am in england and use dd/m/yyyy format but have included a const to change
it to the american format.Could this be a problem or can you not have string
and dates selections in a str where clause

Can anyone see where I am going wrong? Thanks in advance for any help offered

Dim RptName As String
Dim strWhere As String
Dim strInput As String 'Date inputted field.
Dim strBIU As String 'BIU date field
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strID As String
Dim strfield As String

RptName = Forms!ReportsSelector!ReportName
strfield = "Date inputted"


If Not IsNull(Me!ChWard) Then
strWhere = "Ward = '" & Me!ChWard & "' And"
End If

If (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If

If Not IsNull(Me!ChArea) Then
strWhere = strWhere & " Area = '" _
& Me!ChArea & "' And"
End If

If (Me!ChArea) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If


If Not IsNull(Me!ChCaseOfficer) Then
strWhere = strWhere & " CaseOfficer = '" _
& Me!ChCaseOfficer & "' And"
End If

If (Me!ChCaseOfficer) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If


If Not IsNull(Me!ChRoad) Then
strWhere = strWhere & " [Road] = '" _
& Me!ChRoad & "' And"
End If

If (Me!ChRoad) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If


If Not IsNull(Me!ChProp) Then
strWhere = strWhere & " [Property Type] = '" _
& Me!ChProp & "' And"
End If

If (Me!ChProp) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If

If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And" '& Me.ChOption & "'"
End If

If (Me!ChOption) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
End If


If Not IsNull(Me.StartDate) Then 'start no end date
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "

End If

If Not IsNull(Me.EndDate) Then 'end date no start
strWhere = strWhere & strfield & " <= " & Format(Me.EndDate,
conDateFormat) & "' And "

End If


If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
'Both start and end dates.
strWhere = strWhere & strfield & " Between " & Format(Me.StartDate,
conDateFormat _
) & " And " & Format(Me.EndDate, conDateFormat) & "'"
End If

If Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

DoCmd.OpenForm "SelectReportCategory", , , , , acHidden


Exit_CmdPrevw_Click:
Exit Sub

Prev_Err:
If Err = 2501 Then

Resume Exit_CmdPrevw_Click
Else
MsgBox Err.Description
Resume Exit_CmdPrevw_Click
End If
End Sub

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Str where and muliple criteria problem

Ceebaby via AccessMonster.com wrote:
> Hi Folks
>
> I wondered if someone could point me in the right direction before I
> completely tear my hair out.
> I have a user selection form where options can be selected for a report.
> Users now want to also filter the options by date selections or not if they
> wish.
>
> I added to unbound text fields to input the start and end dates and inserted
> them into my str Where code. It was working fine until the these were added.
> Now I get a missing syntax error, or it completely ignores the dates entered
> if other options and the dates are selected.I cannot seem to make this work
> if a user selects options and dates, it only works if the date input boxes
> are ignored.
>
> I am in england and use dd/m/yyyy format but have included a const to change
> it to the american format.Could this be a problem or can you not have string
> and dates selections in a str where clause
>
> Can anyone see where I am going wrong? Thanks in advance for any help offered
>
> Dim RptName As String
> Dim strWhere As String
> Dim strInput As String 'Date inputted field.
> Dim strBIU As String 'BIU date field
> Const conDateFormat = "\#mm\/dd\/yyyy\#"
> Dim strID As String
> Dim strfield As String
>
> RptName = Forms!ReportsSelector!ReportName
> strfield = "Date inputted"
>
>
> If Not IsNull(Me!ChWard) Then
> strWhere = "Ward = '" & Me!ChWard & "' And"
> End If
>
> If (Me!ChWard) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
> If Not IsNull(Me!ChArea) Then
> strWhere = strWhere & " Area = '" _
> & Me!ChArea & "' And"
> End If
>
> If (Me!ChArea) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
>
> If Not IsNull(Me!ChCaseOfficer) Then
> strWhere = strWhere & " CaseOfficer = '" _
> & Me!ChCaseOfficer & "' And"
> End If
>
> If (Me!ChCaseOfficer) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
>
> If Not IsNull(Me!ChRoad) Then
> strWhere = strWhere & " [Road] = '" _
> & Me!ChRoad & "' And"
> End If
>
> If (Me!ChRoad) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
>
> If Not IsNull(Me!ChProp) Then
> strWhere = strWhere & " [Property Type] = '" _
> & Me!ChProp & "' And"
> End If
>
> If (Me!ChProp) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
> If Not IsNull(Me!ChOption) Then
> strWhere = strWhere & " [back into use status] = '" _
> & Me.ChOption & "' And" '& Me.ChOption & "'"
> End If
>
> If (Me!ChOption) = "All" Then
> DoCmd.OpenReport RptName, acViewPreview
> End If
>
>
> If Not IsNull(Me.StartDate) Then 'start no end date
> strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
> conDateFormat) & "' And "
>
> End If
>
> If Not IsNull(Me.EndDate) Then 'end date no start
> strWhere = strWhere & strfield & " <= " & Format(Me.EndDate,
> conDateFormat) & "' And "
>
> End If
>
>
> If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
> 'Both start and end dates.
> strWhere = strWhere & strfield & " Between " & Format(Me.StartDate,
> conDateFormat _
> ) & " And " & Format(Me.EndDate, conDateFormat) & "'"
> End If
>
> If Right(strWhere, 4) = " And" Then
> strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
> Else
> strWhere = Trim(strWhere)
> End If
>
> DoCmd.OpenReport RptName, acViewPreview, , strWhere
>
> DoCmd.OpenForm "SelectReportCategory", , , , , acHidden
>
>
> Exit_CmdPrevw_Click:
> Exit Sub
>
> Prev_Err:
> If Err = 2501 Then
>
> Resume Exit_CmdPrevw_Click
> Else
> MsgBox Err.Description
> Resume Exit_CmdPrevw_Click
> End If
> End Sub
>
> Thanks in advance
> Ceebaby
>

Here's an example from I reply I gave yesterday

Dim strFilter as string
If Not IsNull(Me.ComboName) Then
strFilter = "TableNameFld = '" & Me.ComboName & "' And "
Endif
If Not IsNull(Me.FromDate) Then
strFilter = strFilter & "DateFld >= #" & Me.FromDate & "# And "
Endif
If Not IsNull(Me.ToDate) Then
strFilter = strFilter & "DateFld >= #" & Me.ToDate & "# And "
Endif

'if there is a filter, remove the trailing 'And'
If strFilter > "" Then strFilter = Left(strFilter,Len(strFilter)-5)
Docmd.OpenReport YourReportName,,,strFilter

Remember, strings are surrounded by ' or ", dates by #, and numbers by
nothing.

If you need to convert dates, perhaps you should consider the DateSerial
function. I don't know if this will help. I don't think formatting
your dates will help in your case.
d = date()
? DateSerial(Year(d),Month(d),Day(d))
1/10/2008
Here's some info on international dates in case you need it.
http://allenbrowne.com/ser-36.html

Regarding your code...I'd go thru all of the criteria list first and
build your filter then call the report.

Mermaid
http://www.youtube.com/watch?v=cucKok05nEI

Re: Str where and muliple criteria problem

On Jan 10, 4:04=A0am, "Ceebaby via AccessMonster.com" wrote:
> Hi Folks
>
> I wondered if someone could point me in the right direction before I
> completely tear my hair out.
> I have a user selection form where options can be selected for a report.
> Users now want to also filter the options by date selections or not if the=
y
> wish.
>
> I added to unbound text fields to input the start and end dates and insert=
ed
> them into my str Where code. It was working fine until the these were adde=
d.
> Now I get a missing syntax error, =A0or it completely ignores the dates en=
tered
> if other options and the dates are selected.I cannot seem to make this wor=
k
> if a user selects options and dates, it only works if the date input boxes=

> are ignored.
>
> I am in england and use dd/m/yyyy format but have included a const to chan=
ge
> it to the american format.Could this be a problem or can you not have stri=
ng
> and dates selections in a str where clause
>
> Can anyone see where I am going wrong? Thanks in advance for any help offe=
red
>
> =A0 =A0 =A0 =A0 Dim RptName As String
> =A0 =A0 =A0 =A0 Dim strWhere As String
> =A0 =A0 =A0 =A0 Dim strInput As String =A0 =A0 =A0'Date inputted field.
> =A0 =A0 =A0 =A0 Dim strBIU As String =A0 =A0 =A0 'BIU date field
> =A0 =A0 =A0 =A0 Const conDateFormat =3D "\#mm\/dd\/yyyy\#"
> =A0 =A0 =A0 =A0 Dim strID As String
> =A0 =A0 =A0 =A0 Dim strfield As String
>
> =A0 =A0 =A0 =A0 RptName =3D Forms!ReportsSelector!ReportName
> =A0 =A0 =A0 =A0 strfield =3D "Date inputted"
>
> =A0If Not IsNull(Me!ChWard) Then
> =A0 =A0 =A0 =A0strWhere =3D "Ward =3D '" & Me!ChWard & "' And"
> =A0 End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 If (Me!ChWard) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 DoCmd.OpenReport RptName, acViewPr=
eview
> =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0If Not IsNull(Me!ChArea) Then
> =A0 =A0 =A0 =A0strWhere =3D strWhere & " Area =3D '" _
> =A0 =A0 =A0 =A0 =A0 =A0& Me!ChArea & "' And"
> =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 If (Me!ChArea) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 DoCmd.OpenReport RptName, acViewPreview
> =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0If Not IsNull(Me!ChCaseOfficer) Then
> =A0 =A0 =A0 =A0strWhere =3D strWhere & " CaseOfficer =3D '" _
> =A0 =A0 =A0 =A0 =A0 =A0& Me!ChCaseOfficer & "' And"
> =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0If (Me!ChCaseOfficer) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0DoCmd.OpenReport RptName, acVie=
wPreview
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 If Not IsNull(Me!ChRoad) Then
> =A0 =A0 =A0 =A0strWhere =3D strWhere & " [Road] =3D '" _
> =A0 =A0 =A0 =A0 =A0 =A0& Me!ChRoad & "' And"
> =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0If (Me!ChRoad) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0DoCmd.OpenReport RptName, acVie=
wPreview
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 If Not IsNull(Me!ChProp) Then
> =A0 =A0 =A0 =A0strWhere =3D strWhere & " [Property Type] =3D '" _
> =A0 =A0 =A0 =A0 =A0 =A0& Me!ChProp & "' And"
> =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0If (Me!ChProp) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0DoCmd.OpenReport RptName, acVie=
wPreview
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 If Not IsNull(Me!ChOption) Then
> =A0 =A0 =A0 =A0strWhere =3D strWhere & " [back into use status] =3D '" _
> =A0 =A0 =A0 =A0 =A0& Me.ChOption & "' And" =A0'& Me.ChOption & "'"
> =A0 =A0 =A0 End If
>
> If (Me!ChOption) =3D "All" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0DoCmd.OpenReport RptName, acVie=
wPreview
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0If Not IsNull(Me.StartDate) Then =A0 =A0 =A0'start no end date
> =A0 =A0 =A0 =A0 =A0 =A0strWhere =3D strWhere & strfield & " >=3D " & Forma=
t(Me.StartDate,
> conDateFormat) & "' And "
>
> =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 If Not IsNull(Me.EndDate) Then =A0 =A0 =A0 'end date no start
> =A0 =A0 =A0 =A0 =A0 =A0strWhere =3D strWhere & strfield & " <=3D " & Forma=
t(Me.EndDate,
> conDateFormat) & "' And "
>
> =A0 =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 =A0If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
> 'Both start and end dates.
> =A0 =A0 =A0 =A0 =A0 =A0strWhere =3D strWhere & strfield & " Between " & Fo=
rmat(Me.StartDate,
> conDateFormat _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0) & " And " & Format(Me.EndDate, conDateFor=
mat) & "'"
> =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0If Right(strWhere, 4) =3D " And" Then
> =A0 =A0 =A0 =A0strWhere =3D Trim(Left(strWhere, Len(strWhere) - 4))
> =A0 =A0Else
> =A0 =A0 =A0 =A0strWhere =3D Trim(strWhere)
> =A0 =A0End If
>
> =A0 =A0DoCmd.OpenReport RptName, acViewPreview, , strWhere
>
> =A0 =A0 DoCmd.OpenForm "SelectReportCategory", , , , , acHidden
>
> Exit_CmdPrevw_Click:
> =A0 =A0Exit Sub
>
> Prev_Err:
> =A0 If Err =3D 2501 Then
>
> =A0 =A0 =A0 Resume Exit_CmdPrevw_Click
> =A0 =A0 Else
> =A0 =A0 =A0 =A0MsgBox Err.Description
> =A0 =A0 =A0 =A0Resume Exit_CmdPrevw_Click
> =A0 =A0 End If
> End Sub
>
> Thanks in advance
> Ceebaby
>
> --
> Ceebaby
>
> Trying to be great atAccess
>
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forum=
s.aspx/databases-ms-access/2008...

Ceebaby:

Try enclosing the input dates with #. For example: ..."Between #" &
Me.StartDate & "# and #"...

Milton - In rainy California, USA