Using Double Quotes inside a Module

[Calvin Willman]

How do I deal with double quotes in a string in VBA.

I've created a SQL Query as string to return a Recordset. The string is as

strRs = "SELECT tblKPIs.Customer, DatePart(""M"", [tblKPIs.Date]) as
DateMonth, tblKPIs.TotSales, tblKPIs.Internet, tblKPIs.Email, tblKPIs.Fax,
tblKPIs.Phone, tblKPIs.SameDay, tblKPIs.[24Hrs], tblKPIs.[48Hrs],
tblKPIs.Over48Hrs, tblKPIs.Special" _
& "FROM tblCustomers INNER JOIN tblKPIs ON tblCustomers.CustomerID =
tblKPIs.Customer" _
& "WHERE (((tblKPIs.Customer)= " & intCustomer & "));"

I don't think this is working because of the double quotes in the first line
for DatePart, how do I do this??

Thanks

 

[John Spencer]

Double quotes looks fine. However, you misapplied the brackets around

DatePart(""M"", [tblKPIs.Date]) Should be
DatePart(""M"", [tblKPIs].[Date])

Also you are missing spaces before FROM and WHERE

strRs = "SELECT tblKPIs.Customer, DatePart(""M"", [tblKPIs].[Date]) as
DateMonth, tblKPIs.TotSales, tblKPIs.Internet, tblKPIs.Email, tblKPIs.Fax,
tblKPIs.Phone, tblKPIs.SameDay, tblKPIs.[24Hrs], tblKPIs.[48Hrs],
tblKPIs.Over48Hrs, tblKPIs.Special" _
& " FROM tblCustomers INNER JOIN tblKPIs ON tblCustomers.CustomerID
=
tblKPIs.Customer" _
& " WHERE (((tblKPIs.Customer)= " & intCustomer & "));"

A good method to troubleshoot this is to use Debug.Print strRs
and then copy that string into a blank query.

When you try to execute the query, you will get better error messages on
where the problem(s) exist.

 

[Tom Lake]

How do I deal with double quotes in a string in VBA.

Double them.

Debug.Print "He said, ""I'm fine!""."

will print

He said, "I'm fine!".

Tom Lake

 

[Calvin Willman]

Thanks John.
I understand what your saying. Created the query in Access, Query designer,
but I'll have a look at this. At the moment, I can't seem to populate the
recordset with anything at all... which is another topic I'll post a message
for.

Double quotes looks fine. However, you misapplied the brackets around

DatePart(""M"", [tblKPIs.Date]) Should be
DatePart(""M"", [tblKPIs].[Date])

Also you are missing spaces before FROM and WHERE

strRs = "SELECT tblKPIs.Customer, DatePart(""M"", [tblKPIs].[Date]) as
DateMonth, tblKPIs.TotSales, tblKPIs.Internet, tblKPIs.Email, tblKPIs.Fax,
tblKPIs.Phone, tblKPIs.SameDay, tblKPIs.[24Hrs], tblKPIs.[48Hrs],
tblKPIs.Over48Hrs, tblKPIs.Special" _
& " FROM tblCustomers INNER JOIN tblKPIs ON tblCustomers.CustomerID
=
tblKPIs.Customer" _
& " WHERE (((tblKPIs.Customer)= " & intCustomer & "));"

A good method to troubleshoot this is to use Debug.Print strRs
and then copy that string into a blank query.

When you try to execute the query, you will get better error messages on
where the problem(s) exist.

How do I deal with double quotes in a string in VBA.

I've created a SQL Query as string to return a Recordset. The string is
as

strRs = "SELECT tblKPIs.Customer, DatePart(""M"", [tblKPIs.Date]) as
DateMonth, tblKPIs.TotSales, tblKPIs.Internet, tblKPIs.Email,
tblKPIs.Fax, tblKPIs.Phone, tblKPIs.SameDay, tblKPIs.[24Hrs],
tblKPIs.[48Hrs], tblKPIs.Over48Hrs, tblKPIs.Special" _
& "FROM tblCustomers INNER JOIN tblKPIs ON tblCustomers.CustomerID
= tblKPIs.Customer" _
& "WHERE (((tblKPIs.Customer)= " & intCustomer & "));"

I don't think this is working because of the double quotes in the first
line for DatePart, how do I do this??

Thanks