Recordset as Variable in SQL Statement

  • Thread starter Thread starter DoveArrow
  • Start date Start date
D

DoveArrow

Here's my problem: I want to use text from a field in a Recordset that
I've created as a variable in a SQL statement. However, the text I'm
referencing has a period in it. As a result, instead of appearing as
text ('BS.BUAC'), the computer thinks it's referencing a field ([BS].
[BUAC]). I've tried putting quotation marks around it, I've tried
putting the Chr(34) ASCII codes around it, I've tried using Like
statements, In statements, and equal statements, but the computer not
only ignores them, it removes them from the final SQL statement.

I'm not that familiar with trying to translate VBA into SQL and/or
back again, so I find myself stuck with this and with no idea how to
proceed. If anyone can help me with this issue, I would really
appreciate it. Oh, and if you're interested, here's the portion of the
SQL statement that I'm trying to run. Note: [rs]![Program] is the
field in my Recordset that I'm trying to reference.

WHERE (((jtblMajorSpecialization.CatalogYear)=[Forms]!
[frmProgramChange]![CatalogYear]) AND
((jtblMajorSpecialization.Program) " & Chr(34) & [rs]![Program] &
Chr(34) & ")
 
Here's my problem: I want to use text from a field in a Recordset that
I've created as a variable in a SQL statement. However, the text I'm
referencing has a period in it. As a result, instead of appearing as
text ('BS.BUAC'), the computer thinks it's referencing a field ([BS].
[BUAC]). I've tried putting quotation marks around it, I've tried
putting the Chr(34) ASCII codes around it, I've tried using Like
statements, In statements, and equal statements, but the computer not
only ignores them, it removes them from the final SQL statement.

I'm not that familiar with trying to translate VBA into SQL and/or
back again, so I find myself stuck with this and with no idea how to
proceed. If anyone can help me with this issue, I would really
appreciate it. Oh, and if you're interested, here's the portion of the
SQL statement that I'm trying to run. Note: [rs]![Program] is the
field in my Recordset that I'm trying to reference.

WHERE (((jtblMajorSpecialization.CatalogYear)=[Forms]!
[frmProgramChange]![CatalogYear]) AND
((jtblMajorSpecialization.Program) " & Chr(34) & [rs]![Program] &
Chr(34) & ")
Click to expand...

Nevermind. I decided that this wasn't the way to go and went a
different route.
 
Back
Top