By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Str where and muliple criteria problem

P: n/a
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/For...ccess/200801/1

Jan 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jan 10 '08 #2

P: n/a
On Jan 10, 4:04*am, "Ceebaby via AccessMonster.com" <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!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 atAccess

Message posted via AccessMonster.comhttp://www.accessmonster.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
Jan 10 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.