Eval Function problem in a query

Could you paste the entire SQL?

Here ya go:

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

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

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?

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

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

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.

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

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 ...

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.

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

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?

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?

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

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

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

try:

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

or

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

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.

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

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

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

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

...

Here is complete the solution:  (You need two functions BTW)

Did you mean to name both functions the same name.

Because I get a compile error. Ambiguous function name.

I also get undefined function: 'fnTschClassID'

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.

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

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