Str where and muliple criteria problem

  • Thread starter Ceebaby via AccessMonster.com
  • Start date
C

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!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

Sorry for double posting but I clicked the wrong group in the first instance.

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 
P

Paolo

Hi Ceebaby,

First of all date "Date inputted", the value to which you initialise
strfield, is the name of a field in the table/query you use as source of you
report? In this case put it between square brackets i.e. "[Date inputted]"

Second put the hash sign(#) before and after the dates in your criteria
string in this way

strWhere = strWhere & strfield & " >= #" & Format(Me.StartDate,
conDateFormat) & "# And " etc.etc.

HTH Paolo

Ceebaby via AccessMonster.com said:
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

Sorry for double posting but I clicked the wrong group in the first instance.

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
 
J

John Spencer

The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you already
handle that case when both start and end dates are filled in with the >=
StartDate and <= End date section of the code. I'm not sure what you are
attempting to do with the chOption section, but it looks suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ceebaby via AccessMonster.com said:
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

Sorry for double posting but I clicked the wrong group in the first
instance.

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
 
P

Paolo

Ignore the second suggestion i gave you 'cause I didn't see that you put the
hash in the conDateFormat

Sorry, Paolo

Paolo said:
Hi Ceebaby,

First of all date "Date inputted", the value to which you initialise
strfield, is the name of a field in the table/query you use as source of you
report? In this case put it between square brackets i.e. "[Date inputted]"

Second put the hash sign(#) before and after the dates in your criteria
string in this way

strWhere = strWhere & strfield & " >= #" & Format(Me.StartDate,
conDateFormat) & "# And " etc.etc.

HTH Paolo

Ceebaby via AccessMonster.com said:
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

Sorry for double posting but I clicked the wrong group in the first instance.

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
J

John Spencer

Never mind about the chOption comment. I see now where I misread the code -
I got lost in all the quotes and apostrophes and didn't notice that the part
that confused me was actually commented out.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you
already handle that case when both start and end dates are filled in with
the >= StartDate and <= End date section of the code. I'm not sure what
you are attempting to do with the chOption section, but it looks
suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ceebaby via AccessMonster.com said:
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

Sorry for double posting but I clicked the wrong group in the first
instance.

Thanks in advance
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
C

cesima via AccessMonster.com

Hi John

Many thanks for your response and the cleaned up code.
Before I checked back I changed the name of the the date inputted field to
dateInputted - I hadnt previously noted the space before.

I tried the new code and still get an error message when I attempt to select
any option and the date, being "syntax error ( missing operator) in query
expression btw the combo box selected and the dates inputted".

I am baffled why the dates seem to be such a problem. Any thoughts your end?

Cheers
Ceebaby


John said:
The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you already
handle that case when both start and end dates are filled in with the >=
StartDate and <= End date section of the code. I'm not sure what you are
attempting to do with the chOption section, but it looks suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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
Hi Folks
Click to expand...
[quoted text clipped - 137 lines]
Thanks in advance
Ceebaby
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 
C

ceebaby via AccessMonster.com

Hi John

Many thanks for your response and the cleaned up code.
Before I checked back I changed the name of the the date inputted field to
dateInputted - I hadnt previously noted the space before.

I tried the new code and still get an error message when I attempt to select
any option and the date, being "syntax error ( missing operator) in query
expression btw the combo box selected and the dates inputted".

I am baffled why the dates seem to be such a problem. Any thoughts your end?

Cheers
Ceebaby


John said:
The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you already
handle that case when both start and end dates are filled in with the >=
StartDate and <= End date section of the code. I'm not sure what you are
attempting to do with the chOption section, but it looks suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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
Hi Folks
Click to expand...
[quoted text clipped - 137 lines]
Thanks in advance
Ceebaby
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 
P

Paolo

As I told you previously you can try to put between brackets date inputted in
this way:
strfield = "[Date inputted]"
If it doesn't work display the strWhere before the docmd.openreport
statement and post the result so it's easyer to understand what's going wrong

Cheers Paolo

ceebaby via AccessMonster.com said:
Hi John

Many thanks for your response and the cleaned up code.
Before I checked back I changed the name of the the date inputted field to
dateInputted - I hadnt previously noted the space before.

I tried the new code and still get an error message when I attempt to select
any option and the date, being "syntax error ( missing operator) in query
expression btw the combo box selected and the dates inputted".

I am baffled why the dates seem to be such a problem. Any thoughts your end?

Cheers
Ceebaby


John said:
The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you already
handle that case when both start and end dates are filled in with the >=
StartDate and <= End date section of the code. I'm not sure what you are
attempting to do with the chOption section, but it looks suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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
Hi Folks
Click to expand...
[quoted text clipped - 137 lines]
Thanks in advance
Ceebaby
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
 
C

Ceebaby via AccessMonster.com

Thanks Paolo

I changed the field name to dateInputted and encased it in brackets and "
[DateInputted]" and quotes, and it works if I enter a start date or end date
but not both dates. I needed the user to have the choice to enter

options only no dates
or options and a start date no end date
or options and an end date and no start date or
options and between start and end.

And with the code posted kindly by John Spencer above I keep getting a
"syntax error (missing operator) in query expression" when I try to enter a
start and end date with options selected in the combo boxes. I have tried
variations on adding different quotes etc but no luck yet.

I have had to modify the code for the time being, to accept only options
and/or between start and end dates to enable my users use the database, until
I resolve the code problems.

Any thoughts on where I am going wrong?

Thanks for the help, the help offered on this site is fantastic.

Ceebaby
As I told you previously you can try to put between brackets date inputted in
this way:
strfield = "[Date inputted]"
If it doesn't work display the strWhere before the docmd.openreport
statement and post the result so it's easyer to understand what's going wrong

Cheers Paolo
Hi John
Click to expand...
[quoted text clipped - 122 lines]
Click to expand...
 
P

Paolo

Strip away the apostrophe (') after the date

strWhere = strWhere & strfield & " Between " &
Format(Me.StartDate,
conDateFormat _
) & " And " & Format(Me.EndDate, conDateFormat)
End If

CU Paolo

Ceebaby via AccessMonster.com said:
Thanks Paolo

I changed the field name to dateInputted and encased it in brackets and "
[DateInputted]" and quotes, and it works if I enter a start date or end date
but not both dates. I needed the user to have the choice to enter

options only no dates
or options and a start date no end date
or options and an end date and no start date or
options and between start and end.

And with the code posted kindly by John Spencer above I keep getting a
"syntax error (missing operator) in query expression" when I try to enter a
start and end date with options selected in the combo boxes. I have tried
variations on adding different quotes etc but no luck yet.

I have had to modify the code for the time being, to accept only options
and/or between start and end dates to enable my users use the database, until
I resolve the code problems.

Any thoughts on where I am going wrong?

Thanks for the help, the help offered on this site is fantastic.

Ceebaby
As I told you previously you can try to put between brackets date inputted in
this way:
strfield = "[Date inputted]"
If it doesn't work display the strWhere before the docmd.openreport
statement and post the result so it's easyer to understand what's going wrong

Cheers Paolo
Hi John
Click to expand...
[quoted text clipped - 122 lines]
Thanks in advance
Ceebaby
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Revised the following code to eliminate the unwanted apostrophe in the & "'
And"

If Not IsNull(Me.StartDate) Then
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

Sorry I missed that. My old eyes didn't see the apostrophe tucked next to
the quote mark. Paolo made a good call there.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

cesima via AccessMonster.com said:
Hi John

Many thanks for your response and the cleaned up code.
Before I checked back I changed the name of the the date inputted field to
dateInputted - I hadnt previously noted the space before.

I tried the new code and still get an error message when I attempt to
select
any option and the date, being "syntax error ( missing operator) in query
expression btw the combo box selected and the dates inputted".

I am baffled why the dates seem to be such a problem. Any thoughts your
end?

Cheers
Ceebaby


John said:
The problem with you code is that you didn't follow the same pattern of
adding criteria. The following line
strWhere = strWhere & strfield & " >= " & Format(Me.StartDate,
conDateFormat) & "' And "
Added space + "AND" + space at the end instead of adding just Space + AND
(no space).

Also there is no need for the section to add between dates when you
already
handle that case when both start and end dates are filled in with the >=
StartDate and <= End date section of the code. I'm not sure what you are
attempting to do with the chOption section, but it looks suspicious to me

Private Sub CmdPrevw_Click()
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 (Me!ChWard) = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChArea = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChCaseOfficer = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChRoad = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChProp = "All" Then
DoCmd.OpenReport RptName, acViewPreview
ElseIf Me!ChOption = "All" Then
DoCmd.OpenReport RptName, acViewPreview

Else 'Apply criteria

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

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

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

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

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

'**********************************************
' ????????????????????????????????????????????????????
'**********************************************
If Not IsNull(Me!ChOption) Then
strWhere = strWhere & " [back into use status] = '" _
& Me.ChOption & "' And " '& Me.ChOption & "'"
End If
'**********************************************
'**********************************************
If Not IsNull(Me.StartDate) Then
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 Right(strWhere, 4) = " And" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strWhere

End If

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
Hi Folks
Click to expand...
[quoted text clipped - 137 lines]
Thanks in advance
Ceebaby
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
 
C

Ceebaby via AccessMonster.com

Many thanks to both of you Paolo and John, you have saved me many hours of
hair tearing. Worked a treat, I really need to read up on my apostraphes and
quotes.

Thanks once again for helping me keep my overtime to a minimum, and have a
good evening.

Cheers
Ceebaby
London

Strip away the apostrophe (') after the date

strWhere = strWhere & strfield & " Between " &
Format(Me.StartDate,
conDateFormat _
) & " And " & Format(Me.EndDate, conDateFormat)
End If

CU Paolo
Thanks Paolo
Click to expand...
[quoted text clipped - 35 lines]
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Filter Specific Date in Access. 0
Filter Specific Date in Access. 0
Run select query from VBA code where criteria comes from a multi select ListBox 2
Message Box to Display a count of Records 3
Error 3464 Data type mismatch in criteria expression for between 2 dates look up 3
creating search box in vba 7
laoding name from combo box to report 2
Trouble with Code for date filter on report... 9

Top