AARRGGHHHH! Please help, syntax error! 1

[spiritwater2004]

The following code generates error 3075, syntax error, missing operation. Can anyone see what is wrong with this? Why won't it work and why am I getting error? Thanks ever so much in advance for any assistance!


stDocName = "frmReportCriteria"

strPTList = ""
Set ctl = fndPrgrmTypeID
If fndPrgrmTypeID.ItemsSelected.Count < 0 Then
For Each varItem In fndPrgrmTypeID.ItemsSelected
strPTList = strPTList & fndPrgrmTypeID.ItemData(varItem) & vbCrLf
Next varItem
strPTList = Left(strPTList, Len(strPTList) - 2)
Else
'
End If


strPUTList = ""

Set ctl = fndPrgrmUseTypeID
If fndPrgrmUseTypeID.ItemsSelected.Count < 0 Then
For Each varItem In fndPrgrmUseTypeID.ItemsSelected
strPUTList = strPUTList & fndPrgrmUseTypeID.ItemData(varItem) & vbCrLf
Next varItem
strPUTList = Left(strPUTList, Len(strPUTList) - 2)
Else
'
End If

DoCmd.OpenForm stDocName, acNormal, , "[PrgrmTypeID] IN (" & strPTList & ") Or [PrgrmUseTypeID] IN (" & strPUTList & ")

 

[BattleDroid42]

What line of code is the error breaking on?

BD42

 

[Zion7]

I am not familiar with the "IN" operator. After A LITTLE research I've concluded, it's for "special" SQL statements, Not your regular SELECT.

So your syntax, as far as I know, should be...

"([PrgrmTypeID]=" & strPTList & ") AND ([PrgrmUseTypeID]=" & strPUTList & ")"
....BUT, this is when you only have one criterion for each field. I just wanted to show that, when comparing two or more different fields, you use "AND", not "OR" (In general). So, in your case, where you have more than 1 criterion for each field...

"([PrgrmTypeID]=" & strPTList & " OR [PrgrmTypeID]=" & strPTList & ") AND ([PrgrmUseTypeID]=" & strPUTList & " OR [PrgrmUseTypeID]=" & strPUTList & ")"

Outside of the redundancy, this is just to show the syntax. Having said this, you need to build your strPTList & strPUTList differently...first of all, get rid of vbCrLf, that alone, will cause an error every time.

For Each varItem In fndPrgrmTypeID.ItemsSelected
strPTList = strPTList & "PrgrmTypeID=" & fndPrgrmTypeID.ItemData(varItem) & " OR "
Next varItem
strPTList = Left(strPTList, Len(strPTList) - 4)

For Each varItem In fndPrgrmUseTypeID.ItemsSelected
strPUTList = strPUTList & "fndPrgrmUseTypeID=" & fndPrgrmTypeID.ItemData(varItem) & " OR "
Next varItem
strPUTList = Left(strPUTList, Len(strPUTList) - 4)


DoCmd.OpenForm stDocName, acNormal, , strPTList & " AND " & strPUTList

I'm assuming your criterion are integers, not strings.

and again, not that familiar with the "IN" operator. Not sure if it precludes the need, to repeat the name of the same field, being compared? (I wish it did).

Hope this helps.
Good Luck!

 

[RoyVidar]

Some hints here thread702-787778.

Some others:
* you loop the selected items only when there are less than 0 items selected (...count<0)
* IN operator expects the list comma separated (1,2,3) - see the link
* now, the where condition contains "[PrgrmTypeID] IN... regardless of whether there are selections or not - perhaps test/concatinate the correct elements of the where condition before the docmd?


Roy-Vidar

 

[Zion7]

It appears my wish came true.
I always wanted a simpler, more succint way, to concatenate
"filter" criteria.
Thank-you Roy!

 

[Zion7]

As RoyVidar said...

If fndPrgrmTypeID.ItemsSelected.Count < 0 Then
should be
If fndPrgrmTypeID.ItemsSelected.Count > 0 Then

And vbCrLf, should be ", "

strPTList = strPTList & fndPrgrmTypeID.ItemData(varItem) & ", "

For brevity,(logic wise) maybe this...

If strPTList <>"" AND strPUTList <> "" Then
DoCmd.OpenForm stDocName, acNormal, , "[PrgrmTypeID] IN (" & strPTList & ") Or [PrgrmUseTypeID] IN (" & strPUTList & ")"
ElseIf If strPTList <>"" AND strPUTList = "" Then
DoCmd.OpenForm stDocName, acNormal, , "[PrgrmTypeID] IN (" & strPTList & ")"
ElseIf strPTList ="" AND strPUTList <> "" Then
DoCmd.OpenForm stDocName, acNormal, , [PrgrmUseTypeID] IN (" & strPUTList & ")"
Else
DoCmd.OpenForm stDocName, acNormal
End If

...and I'm sorry about the AND/OR issue. Totally depends on the circumstance (don't know what I was thinking?).

Outside of that, you were very close. Definately taught me something good.

Good Luck!

 

[spiritwater2004]

Thanks for the input guys, I'm still working on it. I've made a few changes, now getting a different error. I'll continue to play with it. It's a brand new day and the computer hasn't won yet!


stDocName = "frmReportCriteria"

strPTList = ""
Set ctl = fndPrgrmTypeID

With ctl
If .ItemsSelected.Count > 0 Then 'if there's nothing there then return *

For Each varItem In .ItemsSelected
strPTList = strPTList & .ItemData(varItem) & ", "
Next varItem
strPTList = Left(strPTList, Len(strPTList) - 2)
Else
fndPrgrmTypeID = "*"
End If

End With


Set ctl = fndPrgrmUseTypeID

With ctl
If .ItemsSelected.Count > 0 Then 'if there's nothing there then return *

For Each varItem In .ItemsSelected
strPUTList = strPUTList & .ItemData(varItem) & ", "
Next varItem
strPUTList = Left(strPUTList, Len(strPUTList) - 2)
Else
fndPrgrmUseTypeID = "*"
End If

End With

DoCmd.OpenForm stDocName, acNormal, , "[PrgrmTypeID]IN (" & strPTList & ")" Or "[PrgrmUseTypeID]IN (" & strPUTList & ")

 

[RoyVidar]

Might I suggest something along these lines - and please, if you need assistance, post also the error number/description, and which line it bombs on

[tt]dim strWhere as string
dim strTmp as string
stDocName = "frmReportCriteria"
Set ctl = fndPrgrmTypeID
With ctl
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strTmp = strTmp & .ItemData(varItem) & ", "
Next varItem
strWhere = "[PrgrmTypeID] IN (" & _
Left(strTmp, Len(strTmp) - 2) & ")"
End If
End With

Set ctl = fndPrgrmUseTypeID
strTmp=vbnullstring
With ctl
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strTmp = strTmp & .ItemData(varItem) & ", "
Next varItem
strTmp = Left(strTmp, Len(strTmp) - 2)
if len(strWhere)>0 then
strWhere = strWhere & " or [PrgrmUseTypeID] IN (" & _
Left(strTmp, Len(strTmp) - 2) & ")"
else
strWhere = "[PrgrmUseTypeID] IN (" & _
Left(strTmp, Len(strTmp) - 2) & ")"
end if
End If
End With

DoCmd.OpenForm stDocName, acNormal, , strWhere[/tt]

- typed not tested, idea - if no selected items, then there's no need to specify "*"

Roy-Vidar

 

[spiritwater2004]

Roy you are a gem!
It worked perfectly, Thanks so much!
Charlene