Str where and muliple criteria problem

[Ceebaby via AccessMonster.com]

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!ReportsSe lector!ReportNa me
strfield = "Date inputted"
If Not IsNull(Me!ChWar d) Then
strWhere = "Ward = '" & Me!ChWard & "' And"
End If

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

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

If (Me!ChArea) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChCas eOfficer) Then
strWhere = strWhere & " CaseOfficer = '" _
& Me!ChCaseOffice r & "' And"
End If

If (Me!ChCaseOffic er) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChRoa d) Then
strWhere = strWhere & " [Road] = '" _
& Me!ChRoad & "' And"
End If

If (Me!ChRoad) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChPro p) Then
strWhere = strWhere & " [Property Type] = '" _
& Me!ChProp & "' And"
End If

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

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

If (Me!ChOption) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me.Start Date) Then 'start no end date
strWhere = strWhere & strfield & " >= " & Format(Me.Start Date,
conDateFormat) & "' And "

End If

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

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

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

DoCmd.OpenRepor t RptName, acViewPreview, , strWhere

DoCmd.OpenForm "SelectReportCa tegory", , , , , acHidden
Exit_CmdPrevw_C lick:
Exit Sub

Prev_Err:
If Err = 2501 Then

Resume Exit_CmdPrevw_C lick
Else
MsgBox Err.Description
Resume Exit_CmdPrevw_C lick
End If
End Sub

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200801/1

[Salad]

Ceebaby via AccessMonster.c om 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!ReportsSe lector!ReportNa me
strfield = "Date inputted"
If Not IsNull(Me!ChWar d) Then
strWhere = "Ward = '" & Me!ChWard & "' And"
End If

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

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

If (Me!ChArea) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChCas eOfficer) Then
strWhere = strWhere & " CaseOfficer = '" _
& Me!ChCaseOffice r & "' And"
End If

If (Me!ChCaseOffic er) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChRoa d) Then
strWhere = strWhere & " [Road] = '" _
& Me!ChRoad & "' And"
End If

If (Me!ChRoad) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me!ChPro p) Then
strWhere = strWhere & " [Property Type] = '" _
& Me!ChProp & "' And"
End If

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

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

If (Me!ChOption) = "All" Then
DoCmd.OpenRepor t RptName, acViewPreview
End If
If Not IsNull(Me.Start Date) Then 'start no end date
strWhere = strWhere & strfield & " >= " & Format(Me.Start Date,
conDateFormat) & "' And "

End If

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

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

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

DoCmd.OpenRepor t RptName, acViewPreview, , strWhere

DoCmd.OpenForm "SelectReportCa tegory", , , , , acHidden
Exit_CmdPrevw_C lick:
Exit Sub

Prev_Err:
If Err = 2501 Then

Resume Exit_CmdPrevw_C lick
Else
MsgBox Err.Description
Resume Exit_CmdPrevw_C lick
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.Combo Name) Then
strFilter = "TableNameF ld = '" & Me.ComboName & "' And "
Endif
If Not IsNull(Me.FromD ate) Then
strFilter = strFilter & "DateFld >= #" & Me.FromDate & "# And "
Endif
If Not IsNull(Me.ToDat e) 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.OpenRepor t 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),Da y(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

[rojas]

On Jan 10, 4:04*am, "Ceebaby via AccessMonster.c om" <u6919@uwewrote :

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!ReportsSe lector!ReportNa me
* * * * strfield = "Date inputted"

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

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

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

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

* *If Not IsNull(Me!ChCas eOfficer) Then
* * * *strWhere = strWhere & " CaseOfficer = '" _
* * * * * *& Me!ChCaseOffice r & "' And"
* *End If

* * * * * * * *If (Me!ChCaseOffic er) = "All" Then
* * * * * * * * * * *DoCmd.OpenRepo rt RptName, acViewPreview
* * * * * * * *End If

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

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

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

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

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

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

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

* * * * * *End If

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

* * * * * *End If

* * *If Not IsNull(Me.Start Date) And Not IsNull(Me.EndDa te) Then
'Both start and end dates.
* * * * * *strWhere = strWhere & strfield & " Between " & Format(Me.Start Date,
conDateFormat _
* * * * * * * *) & " And " & Format(Me.EndDa te, conDateFormat) & "'"
* * * * * End If

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

* *DoCmd.OpenRepo rt RptName, acViewPreview, , strWhere

* * DoCmd.OpenForm "SelectReportCa tegory", , , , , acHidden

Exit_CmdPrevw_C lick:
* *Exit Sub

Prev_Err:
* If Err = 2501 Then

* * * Resume Exit_CmdPrevw_C lick
* * Else
* * * *MsgBox Err.Description
* * * *Resume Exit_CmdPrevw_C lick
* * End If
End Sub

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great atAccess

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2008...

Ceebaby:

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

Milton - In rainy California, USA