Problem Automating SQL 2

[alr0]

Hi All,

I am trying to move many queries to be operated by command buttons on a form by using the DoCmd.runSQL command. Most of them work just fine but several have syntax problems.

When the command button is clicked an error message reads:

Syntax error (missing operator) in query expression "ImpLabelLines.NA7FROM ImpLabelLinesWhere (((ImpLabelLines(ImpLabelLines.NA7 <>"));'

The problems seem to be in complex WHERE clauses that are filled with (not isnull and <> ""). Is there a syntax difference for Access SQL and SQL in VBA code?

I tried using the SQL statement directly with the runSQL and putting the SQL all on one line and there is no difference.

I could not find a way to run named queries but this might solve the problem as well. The full statement is below:

Sql1a = "INSERT INTO WkDt ( IdSource, NAME1, NAME2, NAME3, NAME4, ADDR1, ADDR2, CSZ )" & _
"SELECT ImpLabelLines.IdSource, ImpLabelLines.NA1, ImpLabelLines.NA2, ImpLabelLines.NA3, ImpLabelLines.NA4, ImpLabelLines.NA5, ImpLabelLines.NA6, ImpLabelLines.NA7" & _
"FROM ImpLabelLines" & _
"WHERE (((ImpLabelLines.NA7) is Not Null And (ImpLabelLines.NA7)<>""));"

Many thanks for some direction here,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

Lack of space before the FROM and the WHERE keywords.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

Hi PHV,

I believe that is just the way the error message prints because other queries are in the same format. It is only the one with complex WHERE clauses. I think that is the source of my issue.

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[avolkert]

Looks to me like you're confusing Double Quotes with Single quotes. VBA recognizes double quotes around text strings (single quotes are enclosed in the text string) and work just fine in SQL.

Dim MYSQL as String
MYSQL = "select COMPANY, COMPANY_ADDR from MYCORPTABLE where (COMPANY_CODE = '" & me.COMPANYCODETEXTBOX & "');"
DoCmd.RunSql MYSQL

Note that:
MsgBox MYSQL
shows (assuming the COMPANYCODETEXTBOX contained the value C001):
----------------------------------------
select COMPANY, COMPANY_ADDR from MYCORPTABLE where (COMPANY_CODE = 'C001');
----------------------------------------

Another neat trick: You can use CHR(34) to include a double quote in a text string as in the following example:

dim MYTEXTSTR as String
MYTEXTSTR = Chr(34) & "HELLO WORLD'S GREATEST ACCESS GURU"
MsgBox MYTEXTSTR

The message displayed will be: "HELLO WORLD'S GREATEST ACCESS GURU"

... try it ... the double quotation marks are part of the text string. I've used this a number of times to get me out of a pickle when I absolutely had to have a double quotation mark included INSIDE a text string.




Alan J. Volkert
Fleet Services
GE Commercial Finance Capital Solutions
(World's longest company title)
Eden Prairie, MN

 

[PHV]

I believe that is just the way the error message prints
This is also just the way Sql1a is populated by your code:
Modify your code either like this:
Sql1a = "INSERT INTO WkDt ( IdSource, NAME1, NAME2, NAME3, NAME4, ADDR1, ADDR2, CSZ )[highlight] [/highlight]" & _
"SELECT ImpLabelLines.IdSource, ImpLabelLines.NA1, ImpLabelLines.NA2, ImpLabelLines.NA3, ImpLabelLines.NA4, ImpLabelLines.NA5, ImpLabelLines.NA6, ImpLabelLines.NA7[highlight] [/highlight]" & _
"FROM ImpLabelLines[highlight] [/highlight]" & _
"WHERE (((ImpLabelLines.NA7) is Not Null And (ImpLabelLines.NA7)<>""));"
Or this:
Sql1a = "INSERT INTO WkDt ( IdSource, NAME1, NAME2, NAME3, NAME4, ADDR1, ADDR2, CSZ )" & _
"[highlight] [/highlight]SELECT ImpLabelLines.IdSource, ImpLabelLines.NA1, ImpLabelLines.NA2, ImpLabelLines.NA3, ImpLabelLines.NA4, ImpLabelLines.NA5, ImpLabelLines.NA6, ImpLabelLines.NA7" & _
"[highlight] [/highlight]FROM ImpLabelLines" & _
"[highlight] [/highlight]WHERE (((ImpLabelLines.NA7) is Not Null And (ImpLabelLines.NA7)<>""));"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Alan is on the target:
Replace this:
And (ImpLabelLines.NA7)<>""));"
With this:
And (ImpLabelLines.NA7)<>''));"
Or this:
And (ImpLabelLines.NA7)<>""""));"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

Hi Everyone,

Thanks for your input!

I probably should have been clearer that the SQL code was copied from queries that have been working properly for over a year. The issue is how SQL statements in VBA must be different from what Access queries generate.

I tried adding spaces and there was no change in the result.

I tried replacing "" with ", '',""", and '''. Things do not compile after these changes.

Changing "" to chr(34) produced the same error.

Just for a little background, we import large volumes of mailing information as address label lines. The simplest are 3 lines consisting of: name, address, city state zip. Of course some are foreign, others have 2 address fields or many name fields. The series of queries is to begin the process of normalizing the data for preparation to do mailings.

Again, as queries, this does exactly what I want. It is the translation to VBA that is not working properly.

Thanks again,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[alr0]

In case it was not obvious, I meant to add that the purpose of these many Where clauses is to determine if a field's contents are NULL or an Empty String. Both are necessary because of variations in source data. I may have to convert empty strings to nulls but I would prefer not to add another step unless necessary.

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

Copy'n'Paste the following:

Sql1a = "INSERT INTO WkDt (IdSource, NAME1, NAME2, NAME3, NAME4, ADDR1, ADDR2, CSZ)" & _
" SELECT IdSource, NA1, NA2, NA3, NA4, NA5, NA6, NA7" & _
" FROM ImpLabelLines" & _
" WHERE NA7 Is Not Null And NA7<>''"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

Thanks again to all.

I guess it is a combination of spaces between clauses, single quotes instead of doubles, and removing the extra parenthesis. I have a few more difficult ones to go but I have a strong starting point.

Ho Ho Ho and Happy Holidays,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.