SQL Statement - single quotes

G

Guest

In the "WHERE" section of the SQL string I have a problem with the
tblTransactions.[Category]='Behaviors'

I need Behaviors to be in double quotes, the query produces no results when
Behaviors is in single quotes.

strSQL = "SELECT tblTransactions.[EMPID], tblEmployees.[FName],
tblEmployees.[Village], tblTransactions.[Category], tblPayPeriod.[Date],
tblBehaviorsLookup.[Description], tblTransactions.[Notes],
tblPayPeriod.[EndDate], tblPayPeriod.[PayWeekID], tblPayPeriod.[PPID],
tblTransactions.[TransType], tblTransactions.[TransAmount],
tblTransactions.[TransID] " & _
"FROM tblPayPeriod INNER JOIN (tblEmployees INNER JOIN (tblTransactions
INNER JOIN tblBehaviorsLookup ON tblTransactions.[BEHID] =
tblBehaviorsLookup.[BEHID]) ON tblEmployees.[EMPID] =
tblTransactions.[EMPID]) ON tblPayPeriod.[PPID] = tblTransactions.[PPID] " & _
"WHERE tblPayPeriod.[PPID] IN(" & strCriteria3 & ") AND (" & strCriteria4 &
") AND tblTransactions.[Category]='Behaviors' " & _
"GROUP BY tblTransactions.[EMPID], tblEmployees.[FName],
tblEmployees.[Village], tblTransactions.[Category], tblpayPeriod.[Date],
tblBehaviorsLookup.[Description], tblTransactions.[Notes],
tblPayPeriod.[EndDate], tblPayPeriod.[PayWeekID], tblPayPeriod.[PPID],
tblTransactions.[Transtype], tblTransactions.[TransAmount],
tblTransactions.[TransID];"
 
D

Dirk Goldgar

jmd said:
In the "WHERE" section of the SQL string I have a problem with the
tblTransactions.[Category]='Behaviors'

I need Behaviors to be in double quotes, the query produces no
results when Behaviors is in single quotes.

strSQL = "SELECT tblTransactions.[EMPID], tblEmployees.[FName],
tblEmployees.[Village], tblTransactions.[Category],
tblPayPeriod.[Date], tblBehaviorsLookup.[Description],
tblTransactions.[Notes], tblPayPeriod.[EndDate],
tblPayPeriod.[PayWeekID], tblPayPeriod.[PPID],
tblTransactions.[TransType], tblTransactions.[TransAmount],
tblTransactions.[TransID] " & _ "FROM tblPayPeriod INNER JOIN
(tblEmployees INNER JOIN (tblTransactions
INNER JOIN tblBehaviorsLookup ON tblTransactions.[BEHID] =
tblBehaviorsLookup.[BEHID]) ON tblEmployees.[EMPID] =
tblTransactions.[EMPID]) ON tblPayPeriod.[PPID] =
tblTransactions.[PPID] " & _ "WHERE tblPayPeriod.[PPID] IN(" &
strCriteria3 & ") AND (" & strCriteria4 & ") AND
tblTransactions.[Category]='Behaviors' " & _ "GROUP BY
tblTransactions.[EMPID], tblEmployees.[FName],
tblEmployees.[Village], tblTransactions.[Category],
tblpayPeriod.[Date], tblBehaviorsLookup.[Description],
tblTransactions.[Notes], tblPayPeriod.[EndDate],
tblPayPeriod.[PayWeekID], tblPayPeriod.[PPID],
tblTransactions.[Transtype], tblTransactions.[TransAmount],
tblTransactions.[TransID];"
Click to expand...

I may be overlooking something, but I don't see any reason that single
or double quotes would make any difference in that particular
condition -- since "Behaviour" doesn't contain a single quote -- unless
there's something wrong with the contents of strCriteria3 or
strCriteria4. Since you're assigning the whole constructed SQL
statement to the variable strSQL, I suggest you examine (and, if
necessary, post here) the value of strSQL after you have executed the
above assignment statement.
 
Top