Eval Function problem in a query

The following expression in a query returns #Error:

Eval([TechnologyClassID] & " In(" & [Forms]![frmLocalSelections]![TechnologySelection] & ")")

I used something very similar to this in a query from several years ago. I'm sure someone on EE helped me with it then. I am trying to use it in a new file and can't tell if I've got something wrong with the expression or if it's the query. Hopefully I've given enough information here to at least get started toward a solution.

Thanks, Dale
Dale LoganConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Could you paste the entire SQL?
0
Dale LoganConsultantAuthor Commented:
Here ya go:

SELECT qryLocalSelectionsAvg.VarietyName, qryLocalSelectionsAvg.Yield, qryLocalSelectionsAvg.TechnologyClassID, Eval([TechnologyClassID] & " In(" & Forms!frmLocalSelections!TechnologySelection & ")") AS Expr1
FROM qryLocalSelectionsAvg;
0
Mike EghtebasDatabase and Application DeveloperCommented:
Do you want to concatinate TechnologyClassID and the content of TechnologySelection? If so, consider using:

SELECT VarietyName, Yield, TechnologyClassID, ([TechnologyClassID] & Forms!frmLocalSelections!TechnologySelection ) AS Expr1 FROM qryLocalSelectionsAvg;

Or you want to do some addition?

Mike
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Eval function evalutates a string expression and returns that value ... for example, Eval("2+2") would return 4, Eval("9") would return 9 ...

What are you trying to do?
0
Mike EghtebasDatabase and Application DeveloperCommented:
Then, you just simply add them:

SELECT VarietyName, Yield, TechnologyClassID, ([TechnologyClassID] + Forms!frmLocalSelections!TechnologySelection ) AS Expr1 FROM qryLocalSelectionsAvg;

You may have to use:

SELECT VarietyName, Yield, TechnologyClassID, (cdbl([TechnologyClassID] )+ Cdbl(Forms!frmLocalSelections!TechnologySelection)) AS Expr1 FROM qryLocalSelectionsAvg;

If you want to use Eval(), Use:

SELECT VarietyName, Yield, TechnologyClassID, Eval([TechnologyClassID] + Forms!frmLocalSelections!TechnologySelection ) AS Expr1 FROM qryLocalSelectionsAvg;


Mike

0
Dale LoganConsultantAuthor Commented:
Sorry for not making myself clear. I am wanting to see if TechnologyClassID is selected in the multi-select list box Forms!frmLocalSelections!TechnologySelection.

In the queries criteria line under this expression I have True.

I only want to return those records that have a TechnologyClassID that is selected in Forms!frmLocalSelections!TechnologySelection
0
Dale LoganConsultantAuthor Commented:
I also want to apologize for not responding earlier. I was traveling all day today and will be doing the same tomorrow. I will be able to check back tomorrow evening. Thanks in advance for any help.
0
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT VarietyName, Yield, TechnologyClassID, fnTschClassID([TechnologyClassID]) As IdSelected FROM qryLocalSelectionsAvg


copy and paste the code below to a module.

Function fnTschClassID(IdVar) As Boolean

Dim ctr as ListBox
Dim i as integer

Set ctr= Forms!frmLocalSelections!TechnologySelection

For i = 0 to ctr.listcount - 1
    if  ctr.selected(i) and clng(ctr.itemdata(i))=IdVar then
          fnTschClassID=True
   end if
next i
Set ctr=nothing

End IF


Assuming that in the list box column header property is set to no. With Yes, change:

if  ctr.selected(i) and clng(ctr.itemdata(i))=IdVar then

to

if  ctr.selected(i+1) and clng(ctr.itemdata(i+1))=IdVar then

Good Luck
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You listbox exposes an ItemsSelected property which you can use:

Dim var As Variant
Dim ctr As Listbox
Dim sTech As String

Set ctr = Forms("frmLocalSelections")("TechnologySelection")

For each var in ctr.ItemsSelected
  '/gets the FIRST column in each selected row
  sTech = ctr.Columns(0, var) & "," & sTech
Next var
'/trim the string
sTech = Left(sTech, Len(sTech)-1)
'/now use sTech in your code:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT qryLocalSelectionsAvg.VarietyName, qryLocalSelectionsAvg.Yield, qryLocalSelectionsAvg.TechnologyClassID FROM qryLocalSelectionsAvg WHERE TechnologyClassID In (" & sTEch & ")", CurrentProject.Connection

NOte that if the TechnologyClassId is a TEXT value, you'll need to enclose each of those IDs in single quotes ...
0
Dale LoganConsultantAuthor Commented:
eghtebas,

I tried your suggestion and get the following error message:

End If without block IF.

There are 2 End If's with only 1 If. I am sorry to say I tried a couple of fixes, but don't know enough to make it work.

LSMConsulting:

With my very limited knowledge about VBA I don't know where to put all of the code you listed.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Try:

Function fnTschClassID(IdVar) As Boolean

Dim ctr as ListBox
Dim i as integer

Set ctr= Forms!frmLocalSelections!TechnologySelection

For i = 0 to ctr.listcount - 1
    if  ctr.selected(i) and clng(ctr.itemdata(i))=IdVar then
          fnTschClassID=True
   end if
next i
Set ctr=nothing
                         'v-- this is replace with End Function.
End Function 'End IF
0
Dale LoganConsultantAuthor Commented:
Oh wow. I think we're almost there. The query works just fine as long as something is selected in the list box. When nothing is selected I was expecting to get all records. I am wanting to use this list box as a filter and was thinking all I would need to do is to add "Is Null" on the second criteria line of the query. Any ideas where I am going wrong?
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> When nothing is selected I was expecting to get all records.

also

re:> I am wanting to see if TechnologyClassID is selected in the multi-select list box Forms!frmLocalSelections!TechnologySelection.

So, what I understand from the first statement is when nothing is selced you want to show all also say it is not selected. But-- from your second line-- if few get selected, show only the selected ones not those not selected.

right?


0
Mike EghtebasDatabase and Application DeveloperCommented:
If so,

SELECT VarietyName, Yield, TechnologyClassID FROM qryLocalSelectionsAvg Where fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount ()=0,False,fnTschClassID([TechnologyClassID]))

Add this one to your modu too:

Function fnListItemSelectedCount (IdVar) As Integer

Dim ctr as ListBox
Dim i as integer
Dim iCountSelected

Set ctr= Forms!frmLocalSelections!TechnologySelection

For i = 0 to ctr.listcount - 1
    if  ctr.selected(i) and clng(ctr.itemdata(i))=IdVar then
          iCountSelected=iCountSelected+1
   end if
next i
Set ctr=nothing
   fnListItemSelectedCount =iCountSelected
                     
End Function 'End IF
0
Mike EghtebasDatabase and Application DeveloperCommented:
some extra space is removed:

SELECT VarietyName, Yield, TechnologyClassID FROM qryLocalSelectionsAvg Where fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0,False,fnTschClassID([TechnologyClassID]))

                                v-- added
Dim iCountSelected As Integer

What the last solution is expected to do:

When nothing is selced all will be shown. But if few are selected, it is to show only the selected ones.

Mike
0
Dale LoganConsultantAuthor Commented:
I am now getting the following message:

Wrong number of arguments used in query expression

'(((fnTschClassID([tblTechnologyClasses].[TechnologyClassID])=iif(fnListItemSelectedCount()=0,
False,fnTschClassID([tblTechnologyClasses].[TechnologyClassID])))))'

I think I've got the correct number of parentheses shown here.

Here's the function:

Function fnListItemSelectedCount(IdVar) As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) And CLng(ctr.ItemData(i)) = IdVar Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function 'End IF
0
Mike EghtebasDatabase and Application DeveloperCommented:
try:

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, False,fnTschClassID([TechnologyClassID]))

or

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, 0,fnTschClassID([TechnologyClassID]))

0
Dale LoganConsultantAuthor Commented:
I got the same message as above for each of those suggestions. I wish I knew how to attempt to figure it out so I could stop bothering you.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I am so sorry, my bad, try:
                                                                            v-- this was what was cuasing the error.
Function fnListItemSelectedCount() As Integer' IdVar) As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) And CLng(ctr.ItemData(i)) = IdVar Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function 'End IF
0
Mike EghtebasDatabase and Application DeveloperCommented:
On more change:

Function fnListItemSelectedCount() As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function

Mike
0
Dale LoganConsultantAuthor Commented:
OK, I am getting really confused. Do we have 2 functions going or has the original function been renamed?

This is what you said needs to be in my query:
try:

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, False,fnTschClassID([TechnologyClassID]))

or

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, 0,fnTschClassID([TechnologyClassID]))


This is the only function I have:
Function fnListItemSelectedCount() As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function
0
Mike EghtebasDatabase and Application DeveloperCommented:
Here is complete solution:  (You need to functions BTW)

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, False,fnTschClassID([TechnologyClassID]))

or

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, 0,fnTschClassID([TechnologyClassID]))

'Copy and paste following into a module replacing the old ones:

Function fnListItemSelectedCount() As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function

'and

Function fnListItemSelectedCount(IdVar) As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) And CLng(ctr.ItemData(i)) = IdVar Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function This should do it.

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
...

Here is complete the solution:  (You need two functions BTW)
0
Dale LoganConsultantAuthor Commented:
Did you mean to name both functions the same name.
0
Dale LoganConsultantAuthor Commented:
Because I get a compile error. Ambiguous function name.
0
Dale LoganConsultantAuthor Commented:
I also get undefined function: 'fnTschClassID'
0
Mike EghtebasDatabase and Application DeveloperCommented:
It hasn't been a good day form me, please forgive me. I am dealing with my teenage child at the same time and am totally distracted. Here we go again:

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, False,fnTschClassID([TechnologyClassID]))

or

fnTschClassID([TechnologyClassID])=iif(fnListItemSelectedCount()=0, 0,fnTschClassID([TechnologyClassID]))

'Copy and paste following into a module replacing the old ones:

Function fnListItemSelectedCount() As Integer

Dim ctr As ListBox
Dim i As Integer
Dim iCountSelected

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) Then
          iCountSelected = iCountSelected + 1
   End If
Next i
Set ctr = Nothing
   fnListItemSelectedCount = iCountSelected
                     
End Function

'and

Function fnTschClassID(IdVar) As Integer

Dim ctr As ListBox
Dim i As Integer

Set ctr = Forms!frmLocalSelections!TechnologySelection

For i = 0 To ctr.ListCount - 1
    If ctr.Selected(i) And CLng(ctr.ItemData(i)) = IdVar Then
          fnTschClassID = True
   End If
Next i
Set ctr = Nothing
                     
End Function

As I mentione once before, it is assumed your listbox header property is set to no.

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale LoganConsultantAuthor Commented:
We don't even need to get started on teenagers. I've got 2 of them and know exactly where you're coming from.

I made the changes you have and am getting no errors. It's not filtering out any records, but before I throw it back you way, I want to check it out myself.
0
Mike EghtebasDatabase and Application DeveloperCommented:
A ggod way to diagnose it to use an new temp query to see if the functions are functioning at all:

SELECT VarietyName, Yield, TechnologyClassID, fnTschClassID([TechnologyClassID]) As SelectItems, fnListItemSelectedCount() As TotalSelectedItems FROM qryLocalSelectionsAvg

To see if SelectItems from the list box are marked with Yes or -1 (the same).

And if  TotalSelectedItems field show 2 if there are only two fields selected in the list box.

FYI, SelectItems and TotalSelectedItems are two temporary fields to see how the funtions are behaving.

Mike
0
Dale LoganConsultantAuthor Commented:
Mike,

Thank you so much for all your help. It took us a while due to my lack of VBA knowledge combined with your teenager. Now go take care of your child and kick back and watch the Super Bowl.

Thanks, Dale
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.