can somebody tell me what's wrong with this code:

[EdRev]

I have this piece of code that doesn't work properly. The object of this code is to pick-up the comparison operator from the input text (1st two character, i.e <=) and use it as a variable in my SQL statement. The sql works ok with =
sign, but that's just about it.

dim compop as string
dim intBook as integer
compop = Mid$(srchCriteria, 1,2)
intBook = Val(Trim(Mid$(srchCriteria, 2, Len(srchCriteria))))
strCriteria = &quot;Select * from &quot; & dbCase & &quot; where days_booked &quot; & compop & &quot;'&quot; & intBook & &quot;'&quot;

Any help will be greatly appreciated.

 

[zlovelife]

Seems like this

intBook = Val(Trim(Mid$(srchCriteria, 2, Len(srchCriteria))))

should start in position 3 not 2
ie
intBook = Val(Trim(Mid$(srchCriteria, 3, Len(srchCriteria))))

so I think when you have a two position operator such a >= and you paste it all together you get >== because you're repeating your second byte... I think

put a debug.print on strCriteria to see what your string is getting. That will usually point you to what's wrong

Hope this helps

 

[chiph]

strCriteria = &quot;Select * from &quot; & dbCase & &quot; where days_booked &quot; & compop & &quot;'&quot; & intBook & &quot;'&quot;

I'm guessing that the days_booked column is defined as being an integer type (i.e. not a character type). If so, drop the single-quotes around intBook:
[tt]
strCriteria = &quot;Select * from &quot; & dbCase & &quot; where days_booked &quot; & compop & &quot; &quot; & intBook
[/tt]

Chip H.

 

[EdRev]

Thanks guys for your quirck responses.
I re-coded my original code to below, also the days_booked is defined as a string data type.
I believe the problem is with the string data_type of the field. How can I convert it to an integer on my SQL.

compop = Mid$(srchCriteria, 1, 2)
intBook = Val(Trim(Mid$(srchCriteria, 3, Len(srchCriteria))))
MsgBox (&quot;What is srchCriteria? &quot; & compop & intBook)
'strCriteria = &quot;Select * from &quot; & dbCase & &quot; where days_booked &quot; & compop & &quot;'&quot; & intBook & &quot;'&quot;
strCriteria = &quot;Select * from &quot; & dbCase & &quot; where days_booked &quot; & compop & &quot; &quot; & &quot;'&quot; & intBook & &quot;'&quot;

thanks again....

 

[zlovelife]

If days_booked is defined as numeric in the database you need to lose the quotes around Intbook as Chiph said.

Hope this helps,

Pete
_____________
Real programs don't eat cache

 

[EdRev]

i think i got it working now. I just had to explicitly cast my database field into integer and trim the compop of any spaces just in case the user only type one comparison operator (i.e. =). This way the sql is comparing the same datatype. This is my new code.

compop = Trim(Mid$(srchCriteria, 1, 2))
intBook = Val(Trim(Mid$(srchCriteria, 3, Len(srchCriteria))))
'MsgBox (&quot;What is srchCriteria? &quot; & compop & intBook)
strCriteria = &quot;Select * from &quot; & dbCase & &quot; where val(days_booked) &quot; & compop & &quot; &quot; & intBook

if you foresee any problem with this code or if you thin this code can be improved, pls. let me know.


thanks again.