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?
 
Mike EghtebasConnect With a Mentor Database 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.