docmd.runsql only works when text is present

  • Thread starter Thread starter kallen via AccessMonster.com
  • Start date Start date
K

kallen via AccessMonster.com

I have the following code:

txtSQL = "SELECT mytbl.myA, mytbl.myB FROM mytbl where myA = " & strText

For some reason in vba, my query works when the strText has text present
(4567E), and doesn't when it is just a number(45643).

What am I doing wrong?

It is driving me batty.
 
Kallen

Since the field [myA] is obviously a text field, the first thing I would do
is modify your SQL as follows (you need to wrap the value of strText in
quotes, or single quotes).

txtSQL = "SELECT myTbl.myA, myTbl.myB " _
& "FROM myTbl " _
& "WHERE myTbl.myA = '" & strText & "'"

Now if what you really want is for the SELECT statement to return records
where the first part of myA matches whatever is entered in strText, then you
need to use:

txtSQL = "SELECT myTbl.myA, myTbl.myB " _
& "FROM myTbl " _
& "WHERE myTbl.myA LIKE '" & strText & "*'"

HTH
Dale
 
But I'm not sure what you are trying to accomplish with a RunSQL method and a
SELECT clause. RunSQL is meant for use with an action query (insert, update,
delete), not a SELECT query.

Why don't you post the rest of your VBA and we will see if we can provide a
little more help.

Dale
 
Back
Top