Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's the code:

    [codebox] Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim mySQL As String

    mySQL = "Select count(*) from [EditMDS] Where [Form Dln] = '" & SelectedRow & "';"

    Debug.Print "SQL is " & mySQL

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(mySQL)[/codebox]

    this generates the error "the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

    Fine. Debug.Print outputs

    Select count(*) from [EditMDS] Where [Form Dln] = '83197501130';

    which, if i run as a SQL Query, executes just fine.

    ???

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is SelectedRow a text field or a numeric field? And is [Form Dln] numeric or text? The query design tools will do some conversions that VBA executed queries may not. If that's not the issue then I'm stumped.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    currently the Form Dln is text, which is not what i want. when i try to convert it to number every one of them gets deleted. i tried Cint([Form Dln]) just for kicks and...I get an Overflow error. Weird.

    Also tried the same thing using ADO and the same error occurs.

    I think I'll work on getting the DLN to load as integer value as that should be happening anyway.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Your issue in that respect is that the value is too large for a Long Integer - they are limited to 2 to the 32nd, but one bit is reserved for the sign.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well, what do you know... working now. i haven't run into this issue before. thanks! maybe this will clear up the SQL issue....

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope... same issue. i change the datatype to 'double'. the table i am querying currently has no records in it, if that helps. although why it should matter, got me! here's the code with ADO:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim mySQL As String

    mySQL = "Select count(*) from [EditMDS] Where [Form Dln] = '" & SelectedRow & "';"

    Debug.Print "SQL is " & mySQL

    Set cnn = CurrentProject.Connection

    rst.Open mySQL, cnn, adOpenDynamic, adLockOptimistic, adCmdText

    same issue -- debug.print of mySQL runs as a query with no problems, just refuses to in code.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='797752' date='13-Oct-2009 20:27']Is SelectedRow a text field or a numeric field? And is [Form Dln] numeric or text? The query design tools will do some conversions that VBA executed queries may not. If that's not the issue then I'm stumped.[/quote]

    I set the field to double and that didn't help. nothing is working... i even created a new access project and imported all the objects, same error. this is extremely weird.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I believe it is the same issue with Double - it is a floating point number with a mantissa and an exponent, and I don't think it will give you 11 digits of precision either - there you encounter rounding errors.
    Wendell

Posting Permissions

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