Results 1 to 8 of 8

Thread: Problems with SQL Expressions

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    Hi, everybody!

    Im 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

  2. #2
    Lively Member FirstKnight's Avatar
    Join Date
    Jul 1999
    Location
    Johannesburg, South Africa
    Posts
    95

    Post

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

    Code:
    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

  4. #4
    Lively Member FirstKnight's Avatar
    Join Date
    Jul 1999
    Location
    Johannesburg, South Africa
    Posts
    95

    Post

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

    Code:
    Set Pesq = DB.OpenRecordset("SELECT Destination from TBLTrips where Counter = " & CLng(TxtContador))
    I tested both examples and it works. Let me know what happens.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

  6. #6
    Hyperactive Member
    Join Date
    Jun 1999
    Location
    Calgary Alberta
    Posts
    359

    Post

    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.

  7. #7
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

    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

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width