Open Report Where condition 2

[smuthcrmnl777]

I have a form that has a button with a command to open a report. The WHERE condition of the OPENREPORT is where I am having some problem.

I have two different fields in the supporting query that I need to set the condition. The two fields are Dept and Group.

I need to retrieve records containing 43 and 44 for Dept
I need to retrieve records containing Wire Draw, Chem Proc, MES, PKG, and Misc for Group.

In the WHERE condtion of the OPENREPORT I have 

"[Dept] = 43 or [Dept] = 44 or [Group] = 'Wire Draw' or [Group] = 'Chem Proc' or [Group] = 'MES' or [Group] = 'PKG' or [Group] = 'Misc'"

I tried using AND instead of OR this and I get an error saying I have duplicate fields.

What should do?

 

[lynchg]

You probably want one of these:

"[Dept] IN (43,44) AND [Group] IN ('Wire Draw','Chem Proc','MES','PKG','Misc')"

"[Dept] IN (43,44) OR [Group] IN ('Wire Draw','Chem Proc','MES','PKG','Misc')

 

[MajP]

or
"([Dept] = 43 or [Dept] = 44) And ([Group] = 'Wire Draw' or [Group] = 'Chem Proc' or [Group] = 'MES' or [Group] = 'PKG' or [Group] = 'Misc')

 

[smuthcrmnl777]

Is there a way instead of putting in the text values for the Group IN can I assign a string to the group?

Dim WD As String
Dim CP As String
Dim MES As String
Dim PKG As String
Dim MISC As String

If Me!optWD = -1 Then WD = "Wire Drawing"
If Me!optCP = -1 Then CP = "Chemical Process"
If Me!optMES = -1 Then MES = "MES"
If Me!optPKG = -1 Then PKG = "PKG"
If Me!optMisc = -1 Then MISC = "MISC"

Could I have this in the WHERE?

"[Group] IN (WD,CP,MES,PKG,MISC)

 

[MajP]

No.

 

[MajP]

You could produce a function getGroupString
This would have to be public, but could return different group string depending on your choices.

such as "'Wire Draw','Chem Proc','MES','PKG','Misc'"
or
"'Wire Draw''MES','PKG'"

then you should be able to
[Group] IN (getGroupString)

 

[smuthcrmnl777]

Could I do this?

Dim WD As String
Dim CP As String
Dim MES As String
Dim PKG As String
Dim MISC As String
Dim GetGroupString As String


If Me!optWD = -1 Then WD = "Wire Drawing"
If Me!optCP = -1 Then CP = "Chemical Process"
If Me!optMES = -1 Then MES = "MES"
If Me!optPKG = -1 Then PKG = "PKG"
If Me!optMisc = -1 Then MISC = "MISC"

GetGroupString = "'" & WD & "'," & CP & "'," & MES & "'," & PKG & "'," & MISC & "'"

WHERE Condition will have

"[Group] IN (GetGroupString)"

I have tried this and I am getting an input box asking me to enter the value for GetGroupString.

 

[PHV]

you may try this:

Dim GetGroupString As String
If Me!optWD = -1 Then GetGroupString = GetGroupString & "," & "'Wire Drawing'"
If Me!optCP = -1 Then GetGroupString = GetGroupString & "," & "'Chemical Process'"
If Me!optMES = -1 Then GetGroupString = GetGroupString & "," & "'MES'"
If Me!optPKG = -1 Then GetGroupString = GetGroupString & "," & "'PKG'"
If Me!optMisc = -1 Then GetGroupString = GetGroupString & "," & "'MISC'"

WHERE Condition will have

"[Group] IN (" & Mid(GetGroupString, 2) & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smuthcrmnl777]

PHV:

It worked perfectly!! One last thing and I should be completed. I would like to add another criteria with in the WHERE condition.

Something like this...

"[b][Dept] = 43 and[/b] [Group] IN (" & Mid(GetGroupString, 2) & ")"

This WHERE does not work. What should be done to make sure I can have the Dept criteria included in this WHERE?

Thanks again!!

 

[smuthcrmnl777]

Can anyone assist me?

 

[MajP]

When you say it does not work, what does it do? Does it error, retun no records, return too many records?
Is Dept by chance a string field not an integer?
Are there records in Dept 43, that meet the specified criteria?

 

[smuthcrmnl777]

I get a runtime error 3075:
Syntax error (Missing Operator) in query expression '([Dept] = 43 AND [Group] IN ())'. I get no records and no report generated.

Dept is an INT

And yes there are records that meet the criteria.

*****************

I am basically trying to include multiple criterias along with the intitial criteria ([Group] IN ()).

 

[MajP]

Any chance you are not passing back any value from the GetStringFunction
Could be scoping reasons for that.
([Group] IN ()) may throw this error where the value for the function is an empty string

 

[smuthcrmnl777]

No I do not see anything.

 

[PHV]

Seems that NO option button was ticked ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886