Problems with SQL Expressions

[Roselene]

Hi, everybody!

I’m having problems with a SQL expression:
I have already used the “DIM” command for the variables Pesq and DB
Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = " & TxtContador)
This expression causes the following error message:
Run time error ‘3061’ too few parameters, expected 1.
If I try:
Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = " & TxtContador, dbOpenForwardOnly), I receive the same message.
And what is strange is that the both expressions works in another Private sub!

Any ideas?
Thanks in advance,
Roselene

[FirstKnight]

If the Counter Field is a text field which seems to be the case here you have to enclose the value that you pass to it in single quotes. Thus your statement should look like this...


Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = '" & TxtContador & "'")

Hope it helps


------------------
Hennie Pieterse
Senior Programmer
Datcor International
hennie@nix.co.za
Telephone: +27 15 7816335
ICQ Number : 61636095
Today is yesterday's tomorrow

[Roselene]

Hennie,

I'm afraid it's not the case, cause Counter is a numeric field. Even tough, I've tried your suggestion and received a message about Type mismatch in criteria expression.

I can't understand why my expression:
Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = " & TxtContador)
doesn't work.

When I run the project using "Start with full compile" no error messages appear, but when I open the form which contains this SQL expression, VB shows that error message.

I really appreciate your attention!
Thanks,

Roselene

[FirstKnight]

Roselene,

The error that you get, definetly shows that the wrong data type is passed to the query. If you are sure that the counter field is a numeric field in the Database then maybe the following might work...


Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = " & CLng(TxtContador))

I tested both examples and it works. Let me know what happens. :)

[Roselene]

Hennie,

I've tried your suggestion and received this message:
Run time error '13' type mismatch.

I'm afraid that the object (TxtContador) may be empty or null.

It can be causing the error messages? I'm using the SQL expression in the Form load event and perhaps it runs before the Text box receives its value.

Thanks again,
Roselene

[netSurfer]

Roselene, well it depends on whether you load any value into the text box before the SQL statement runs. If you don't then the SQL statement will cause you errors. You need to either put a value into the text box as a default or loaded in, or move the SQL statement to after the text is loaded. You could put it in the text boxes LostFocus or Change subs. I would put a default value of 0 into the text box.

[Clunietp]

Use what FIRSTKNIGHT suggested, but use VAL instead of CLNG. That will fix your type mismatch error and allow your SQL to execute correctly.

Tom

[Roselene]

Hi, boys!

I guess I was wrong when I thought that the object (TxtContador) being empty or null could be causing the error messages.

As FirstKnight said, wrong data type was being passed to the query. When I used the SQL instruction with VAL (Clunietp's suggestion) it worked fine!

I'd like to thank all of you,
FirstKnight, NetSurfer and Clunietp, cause it's so good to me receiving that kind of help!

Regards,
Roselene