Modifying Access Queries for MS Query in Excel To Prevent Errors

[bs0d]

I'm having an issue that really makes you want to pull your hair out.

I have constructed a query in MS Access, which executes as expected.

If I use the same exact query using the MS Query method, I get a General ODBC Error if one of the parameters is a string, and works just fine if the parameter is numeric (the string query refers to a field containing text, the query with a number refers to a field containing numbers).

Here is my SQL. Maybe it's just a syntax error, or perhaps some sort of editing is required to make MS Query accept the data just like Access would.

Any feedback is appreciated. Thanks,

Successful Query (with Numeric Values):

= "SELECT Sum(DSum('Sales','tblDailyReadings','[KEY]=' & [KEY] & ' And [ReadingDate]<=#' & [ReadingDate] & '#')) AS mySUM " _
    & "FROM tblInfo INNER JOIN tblDailyReadings ON tblInfo.WH_IDX = tblDailyReadings.Well_KEY " _
    & "WHERE (((tblInfo.WH_Field) = 'MY_FIELD') And ((tblInfo.Test_Field_A)=12)) " _
    & "GROUP BY tblDailyReadings.ReadingDate " _
    & "ORDER BY tblDailyReadings.ReadingDate;"

Below generates a General ODBC Error where the only thing that changes is Test_Field_A to B, and the value is a string rather than long:

= "SELECT Sum(DSum('Sales','tblDailyReadings','[KEY]=' & [KEY] & ' And [ReadingDate]<=#' & [ReadingDate] & '#')) AS mySUM " _
    & "FROM tblInfo INNER JOIN tblDailyReadings ON tblInfo.WH_IDX = tblDailyReadings.Well_KEY " _
    & "WHERE (((tblInfo.WH_Field) = 'MY_FIELD') And ((tblInfo.Test_Field_B)='test')) " _
    & "GROUP BY tblDailyReadings.ReadingDate " _
    & "ORDER BY tblDailyReadings.ReadingDate;"

I've tried replacing ' with chr(34), omitting ' , adding [ and ] around certain fields, and nothing seems to ring the bell. Hopefully it's a simple solution. Thanks again

 

[bs0d]

More info on the error:

I used the MS Query editor and pasted the query, and if I swap fields (Test_Field_A (number, long), with Test_Field_B (short text, string)) it give an error:

The Microsoft Access database engine cannot find the input table or query 'tblDailyReadings' ...

Even more interesting, it only throws the error if my conditional value for Test_Field_B is a value that exists in the database. If I use some obscure string, "asdfasdfasdf" -- then the query executes and returns nothing. Bizarre!

 

[bs0d]

More info on the error:

I used the MS Query editor and pasted the query, and if I swap fields (Test_Field_A (number, long), with Test_Field_B (short text, string)) it give an error:

The Microsoft Access database engine cannot find the input table or query 'tblDailyReadings' ...

Even more interesting, it only throws the error if my conditional value for Test_Field_B is a value that exists in the database. If I use some obscure string, "asdfasdfasdf" -- then the query executes and returns nothing. Bizarre!

Yet more debugging data:

I tested again on a different text field, and it worked. But this may raise even more questions. What properties of a field would typically cause MS Query to return the error(s) I was seeing? This would be helpful to prevent future occurrences. Hmm..