Form to pass strWhere to open report

W

WestWingFan

Hi!

Thanks in advance for your help. I have an issue and have not been able to
find a similar one on the forums.

I have a form which collects user requirements for a report through a
combobox (with a union all query id value = -1) and an option group (value =
5 is all subjects). The command button's on click event has this code:

Dim strWhere As String

If IsNull(Me.cboIndustrySelect) Then
MsgBox "Please select an Industry."
Me!cboIndustrySelect.SetFocus
Cancel = True
ElseIf IsNull(SubjectOption) Then
MsgBox "Please select a Subject."
Cancel = True

ElseIf Me.cboIndustrySelect = -1 And SubjectOption = 5 Then

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview

ElseIf Not IsNull(Me.cboIndustrySelect) Then

If Me.cboIndustrySelect = -1 Then
strWhere = "[IndustryID]=*"
Else
strWhere = "[IndustryID]=" & Me.cboIndustrySelect
End If

If Not IsNull(SubjectOption) Then
If SubjectOption = 5 Then
strWhere = AddAnd(strWhere)
strWhere = "[JCSubjID]=*"
Else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[JCSubjID]=" & Me.SubjectOption.Value
End If
End If

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview, , strWhere
End If

I can run the report when: I select a industry from the combo box and a
subject area OR I select all industries and all subject areas. When I try to
select either all industries and a subject area OR an industry with all
subject areas, I get the same error about "missing operator in query
expression." Any ideas where I'm going wrong?
 
S

Steve Sanford

Hi WestWingFan,

I modified your code the way I do searching. This is untested, but it should
work.


'---------------------------------------------
Dim strWhere As String

If IsNull(Me.cboIndustrySelect) Then
MsgBox "Please select an Industry."
Me!cboIndustrySelect.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(SubjectOption) Then
MsgBox "Please select a Subject."
Cancel = True
Exit Sub
End If

strWhere = ""

'add condition for cboIndustrySelect
If Me.cboIndustrySelect <> -1 Then
strWhere = strWhere & "[IndustryID]=" & Me.cboIndustrySelect & " AND "
End If

'add condition for SubjectOption
If Me.SubjectOption < 5 Then
strWhere = strWhere & "[JCSubjID]=" & Me.SubjectOption.Value & " AND "
End If

If Len(Trim(strWhere)) > 0 Then
'remove last 5 chars from strWhere (" AND ")
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview, , strWhere
'---------------------------------------------


HTH
 
W

WestWingFan

Works great! Many thanks and kudos to you. It's good to know that strwhere
can = ""

Steve Sanford said:
Hi WestWingFan,

I modified your code the way I do searching. This is untested, but it should
work.


'---------------------------------------------
Dim strWhere As String

If IsNull(Me.cboIndustrySelect) Then
MsgBox "Please select an Industry."
Me!cboIndustrySelect.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(SubjectOption) Then
MsgBox "Please select a Subject."
Cancel = True
Exit Sub
End If

strWhere = ""

'add condition for cboIndustrySelect
If Me.cboIndustrySelect <> -1 Then
strWhere = strWhere & "[IndustryID]=" & Me.cboIndustrySelect & " AND "
End If

'add condition for SubjectOption
If Me.SubjectOption < 5 Then
strWhere = strWhere & "[JCSubjID]=" & Me.SubjectOption.Value & " AND "
End If

If Len(Trim(strWhere)) > 0 Then
'remove last 5 chars from strWhere (" AND ")
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview, , strWhere
'---------------------------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


WestWingFan said:
Hi!

Thanks in advance for your help. I have an issue and have not been able to
find a similar one on the forums.

I have a form which collects user requirements for a report through a
combobox (with a union all query id value = -1) and an option group (value =
5 is all subjects). The command button's on click event has this code:

Dim strWhere As String

If IsNull(Me.cboIndustrySelect) Then
MsgBox "Please select an Industry."
Me!cboIndustrySelect.SetFocus
Cancel = True
ElseIf IsNull(SubjectOption) Then
MsgBox "Please select a Subject."
Cancel = True

ElseIf Me.cboIndustrySelect = -1 And SubjectOption = 5 Then

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview

ElseIf Not IsNull(Me.cboIndustrySelect) Then

If Me.cboIndustrySelect = -1 Then
strWhere = "[IndustryID]=*"
Else
strWhere = "[IndustryID]=" & Me.cboIndustrySelect
End If

If Not IsNull(SubjectOption) Then
If SubjectOption = 5 Then
strWhere = AddAnd(strWhere)
strWhere = "[JCSubjID]=*"
Else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[JCSubjID]=" & Me.SubjectOption.Value
End If
End If

DoCmd.OpenReport "rptMasterListofStandards", acViewPreview, , strWhere
End If

I can run the report when: I select a industry from the combo box and a
subject area OR I select all industries and all subject areas. When I try to
select either all industries and a subject area OR an industry with all
subject areas, I get the same error about "missing operator in query
expression." Any ideas where I'm going wrong?
Click to expand...
Click to expand...
 

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

Combo box filter 4
Extra ) error in search form 4
Open Report with the parameter 1
Open form unfiltered if strWhere is blank 7
Modified Allenbrowne Search Form displays error 1
Error on modified Allenbrowne SearchForm 6
Create Report On the Fly 1
error 3464 data type mismatch in criteria expression 0

Top