VBA SQLStatement & Quotation Marks

G

Guest

I am having trouble with the following SQL statement due to the quotation
marks involved. I seem to have a mental block when it comes to quotation
marks. Can somebody give me a hand here. Any help would be appreciated.

SQLStatement:="SELECT tbl_expirations.Expiration_Date, tbl_expirations.ID,
tbl_" & _
"expirations.Month_Year, tbl_expirations.Account_No,
tbl_expirations.Acct_Name, tbl_" & _
"expirations.Terr, tbl_expirations.Add1, tbl_expirations.Add2,
tbl_expirations.City1, tbl_" & _
"expirations.State, tbl_expirations.Zip, tbl_expirations.Rep_name,
tbl_expirations.Contract_No, tbl_" & _
"expirations.cnt_spec AS CS_Init, [CS_FName] & " " & [CS_LName] AS [Contract
Specialist], Tbl_" & _
"Contract_Spec.CS_Signature FROM tbl_expirations LEFT JOIN
Tbl_Contract_Spec ON tbl_" & _
"expirations.cnt_spec = Tbl_Contract_Spec.CS_Init WHERE (((tbl_" & _
"expirations.Month_Year) Like "*" & [Enter Month and Year or leave blank for
all:] & "*") And ((tbl_" & _
"expirations.terr) > 100) And ((tbl_expirations.Completed) = Yes) And
((tbl_expirations.Action) = "Expire") And ((tbl_" & _
"expirations.Snd_Ltr) = Yes)) ORDER BY tbl_expirations.Expiration_" & _
"Date, tbl_expirations.Acct_Name, tbl_expirations.Contract_No"

Thank you
 
G

Guest

Hi,

I would suggest you not separate the table names by a line. It is best to
have the whole table name on one line. For example, you have tbl_expirations
broken into 2 lines:

tbl_" & _
"expirations

Where if you had the table name(s) on the same line, it would be less
confusing for you and prevent headaches in the long run:

tbl_expirations

This is all assuming that you would like to keep your SQL in VBA code rather
than changing it into an actual saved query.

Hope this helps,
geebee
 
G

Guest

I am acutally trying use automation to run a mail merge in access and the
example on the website uses:

Connection:="TABLE...

Can I use

Connection:="QUERY...

So that I dont have have to use this lengthy SQL statement???


--
www.bardpv.com
Tempe, Arizona


geebee said:
Hi,

I would suggest you not separate the table names by a line. It is best to
have the whole table name on one line. For example, you have tbl_expirations
broken into 2 lines:

tbl_" & _
"expirations

Where if you had the table name(s) on the same line, it would be less
confusing for you and prevent headaches in the long run:

tbl_expirations

This is all assuming that you would like to keep your SQL in VBA code rather
than changing it into an actual saved query.

Hope this helps,
geebee


Emma Aumack said:
I am having trouble with the following SQL statement due to the quotation
marks involved. I seem to have a mental block when it comes to quotation
marks. Can somebody give me a hand here. Any help would be appreciated.

SQLStatement:="SELECT tbl_expirations.Expiration_Date, tbl_expirations.ID,
tbl_" & _
"expirations.Month_Year, tbl_expirations.Account_No,
tbl_expirations.Acct_Name, tbl_" & _
"expirations.Terr, tbl_expirations.Add1, tbl_expirations.Add2,
tbl_expirations.City1, tbl_" & _
"expirations.State, tbl_expirations.Zip, tbl_expirations.Rep_name,
tbl_expirations.Contract_No, tbl_" & _
"expirations.cnt_spec AS CS_Init, [CS_FName] & " " & [CS_LName] AS [Contract
Specialist], Tbl_" & _
"Contract_Spec.CS_Signature FROM tbl_expirations LEFT JOIN
Tbl_Contract_Spec ON tbl_" & _
"expirations.cnt_spec = Tbl_Contract_Spec.CS_Init WHERE (((tbl_" & _
"expirations.Month_Year) Like "*" & [Enter Month and Year or leave blank for
all:] & "*") And ((tbl_" & _
"expirations.terr) > 100) And ((tbl_expirations.Completed) = Yes) And
((tbl_expirations.Action) = "Expire") And ((tbl_" & _
"expirations.Snd_Ltr) = Yes)) ORDER BY tbl_expirations.Expiration_" & _
"Date, tbl_expirations.Acct_Name, tbl_expirations.Contract_No"

Thank you
Click to expand...
Click to expand...
 
G

Guest

hi,

I don't think so. But then again, I've never done mail merge/access
automation. If you want to use a table, I would sugest that you use a
make-table query, then base your addresses or merge on that table made at
runtime.

For more info, see:

articles:
http://support.microsoft.com/kb/813989/
http://www.mvps.org/access/modules/mdl0004.htm

similar post
http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

hope this helps,
geebee


Emma Aumack said:
I am acutally trying use automation to run a mail merge in access and the
example on the website uses:

Connection:="TABLE...

Can I use

Connection:="QUERY...

So that I dont have have to use this lengthy SQL statement???


--
www.bardpv.com
Tempe, Arizona


geebee said:
Hi,

I would suggest you not separate the table names by a line. It is best to
have the whole table name on one line. For example, you have tbl_expirations
broken into 2 lines:

tbl_" & _
"expirations

Where if you had the table name(s) on the same line, it would be less
confusing for you and prevent headaches in the long run:

tbl_expirations

This is all assuming that you would like to keep your SQL in VBA code rather
than changing it into an actual saved query.

Hope this helps,
geebee


Emma Aumack said:
I am having trouble with the following SQL statement due to the quotation
marks involved. I seem to have a mental block when it comes to quotation
marks. Can somebody give me a hand here. Any help would be appreciated.

SQLStatement:="SELECT tbl_expirations.Expiration_Date, tbl_expirations.ID,
tbl_" & _
"expirations.Month_Year, tbl_expirations.Account_No,
tbl_expirations.Acct_Name, tbl_" & _
"expirations.Terr, tbl_expirations.Add1, tbl_expirations.Add2,
tbl_expirations.City1, tbl_" & _
"expirations.State, tbl_expirations.Zip, tbl_expirations.Rep_name,
tbl_expirations.Contract_No, tbl_" & _
"expirations.cnt_spec AS CS_Init, [CS_FName] & " " & [CS_LName] AS [Contract
Specialist], Tbl_" & _
"Contract_Spec.CS_Signature FROM tbl_expirations LEFT JOIN
Tbl_Contract_Spec ON tbl_" & _
"expirations.cnt_spec = Tbl_Contract_Spec.CS_Init WHERE (((tbl_" & _
"expirations.Month_Year) Like "*" & [Enter Month and Year or leave blank for
all:] & "*") And ((tbl_" & _
"expirations.terr) > 100) And ((tbl_expirations.Completed) = Yes) And
((tbl_expirations.Action) = "Expire") And ((tbl_" & _
"expirations.Snd_Ltr) = Yes)) ORDER BY tbl_expirations.Expiration_" & _
"Date, tbl_expirations.Acct_Name, tbl_expirations.Contract_No"

Thank you
Click to expand...
Click to expand...
Click to expand...
 
N

Neil Sunderland

Emma said:
I am having trouble with the following SQL statement due to the quotation
marks involved. I seem to have a mental block when it comes to quotation
marks. Can somebody give me a hand here. Any help would be appreciated.
Click to expand...

Replace the double quotes within the SQL statement to single quotes.

You could also tidy it up a bit, so that you don't break table names
across lines:

SQLStatement:= _
"SELECT tbl_expirations.Expiration_Date, " & _
"tbl_expirations.ID, " & _
"tbl_expirations.Month_Year, " & _
"tbl_expirations.Account_No, " & _
"tbl_expirations.Acct_Name, " & _
"tbl_expirations.Terr, " & _
"tbl_expirations.Add1, " & _
"tbl_expirations.Add2, " & _
"tbl_expirations.City1, " & _
"tbl_expirations.State, " & _
"tbl_expirations.Zip, " & _
"tbl_expirations.Rep_name, " & _
"tbl_expirations.Contract_No, " & _
"tbl_expirations.cnt_spec AS CS_Init, " & _
"[CS_FName] & ' ' & [CS_LName] " & _
"AS [Contract Specialist], " & _
"Tbl_Contract_Spec.CS_Signature " & _
"FROM tbl_expirations " & _
"LEFT JOIN Tbl_Contract_Spec " & _
"ON tbl_expirations.cnt_spec = Tbl_Contract_Spec.CS_Init " & _
WHERE (((tbl_expirations.Month_Year) LIKE " & _
"'*' & [Enter Month and Year or leave blank for all:] & '*') " & _
"And ((tbl_expirations.terr) > 100) And " & _
"((tbl_expirations.Completed) = Yes) And " & _
"((tbl_expirations.Action) = 'Expire') And " & _
"((tbl_expirations.Snd_Ltr) = Yes)) " & _
"ORDER BY tbl_expirations.Expiration_Date, " & _
"tbl_expirations.Acct_Name, " & _
"tbl_expirations.Contract_No"
 
Top