Strange Error – SQL Works, except when it doesn't

Topic

New Reply

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.

???

Reply | Quote

Replies

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

well, what do you know… working now. i haven’t run into this issue before. thanks! maybe this will clear up the SQL issue….

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote