Modifying Access Queries for MS Query in Excel To Prevent Errors

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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):
Code:
= "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:
Code:
= "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
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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:
Code:
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
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:
Code:
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,099,043
Messages
5,466,203
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top