EVAL() error in SQL statement

[Guest_imported]

I am trying to set up a report to print information according to a menu selection provided to a user. Therefore, the condition will not be known beforehand. So, using Select Case, the program stores a text string to the variable named rptcontrol. I then attempted to add the variable to the end of an existing SELECT statement using the EVAL() function. The code below is an example of what I am trying to do. The last EVAL function is the one that is producing an error. The text following the asterisk is an example of what value the rptcontrol variable may contain (including quotes (""))

*** rptcontrol = "len(ltrim(rtrim(accnum))) >=4"

---------------------------------------------------------

Set Rs = Db.OpenRecordset("SELECT Count([Accident Data].[City or County])
AS City
FROM [Accident Data] HAVING ((([Accident Data].Date) Between Eval('[forms]![CitySmryDates]![Beginning Date]') And Eval('[forms]![CitySmryDates]![Ending Date]')) AND (([Accident Data].[City or County])='City' AND eval(rptcontrol)))&quot
-----------------------------------------------------------

The error that I am getting is:
"Too few arguments". Expected 1"

Before I added the eval(rptcontrol) statement, I did not receive any errors. What am I doing wrong.

 

[ghubbell]

Hi Gene! Progressing I see!
Try:

Set Rs = db.OpenRecordset("SELECT Count([Accident Data].[City or County] AS City FROM [Accident Data] HAVING ((([Accident Data].Date) Between '" & [Forms]![CitySmryDates]![Beginning Date] & "' And '" & [Forms]![CitySmryDates]![Ending Date] & "')) AND (([Accident Data].[City or County]) = 'City' AND " & Len(LTrim(RTrim([Forms]![CitySmryDates]![accnum]))) & ">=4))&quot

That's one big long horrific line and you might have to remove the single quotes around your dates (or so) but I think if you take a look at it you'll see what's going on.
Good luck, Gord
ghubbell@total.net

 

[Guest_imported]

Thanks Gord, but, in this case, that Len(...) statement is only one of a possible 22 selections from a list box. I am trying to avoid writing a separate procedure for each possible selection. The reason that I was using the eval() function was because it was my understanding that it would evaluate a string being passed from another procedure or function. Some other strings that could possibly be passed are:

"(TNOV = 0)""
"(([VT CODE1] = 22) OR ([VT CODE2 = 22))"
"(VEHCOUNT = 1)"

The above three items are just 3 of 22 possibilities. That's why I was looking for some sort of shortcut. I thought storing the string into EVAL() would work. Do you know of any other alternatives???

 

[ghubbell]

Hi again Gene, This one might suite your needs. Again though, you might have to play with the little quotes and a few other (hopefully little) adjustments. I don't have data here to test it so you get to do it on your own!

Dim x As Integer
Dim strRptControl As String
x = Len(LTrim(RTrim([Forms]![CitySmryDates]![accnum])))
strRptControl = x & " >= 4"
Set Rs = db.OpenRecordset("SELECT Count([Accident Data].[City or County] AS City FROM [Accident Data] HAVING ((([Accident Data].Date) Between '" & [Forms]![CitySmryDates]![Beginning Date] & "' And '" & [Forms]![CitySmryDates]![Ending Date] & "')) AND (([Accident Data].[City or County]) = 'City' AND " & strRptControl & &quot)&quot

Let's know if it runs or stalls...P.S. Put a stop (red dot in left margin) to hold the code and you'll see which value is which by holding your cursor over each.
Gord
ghubbell@total.net