470,614 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

vba Sql question

hi there,

StrSql = "UPDATE TblResults SET TblResults.DOHID = " & intnum1 & _
" ,TblResults!AHS = " & chr(34) & txtAHSto & chr(34) _
& " WHERE (((TblResults.DOHID)=& " Intnum2 & "));"

what's going wrong with this sql statement. It produces an error at the
textstring. saying ... 'AHStext". error??

any hints??

Regards

Norman
Nov 12 '05 #1
4 1365
I just did a quick cursory scan, so give this answer that amount of weight,
but it looks like the "!" should be a period in the section of your SQL: "
,TblResults!AHS = "

If this works out to be the problem, then formatting the code for your SQL
string a little differently should enable you to tell the difference between
the SQL portion of the string and the VBA code to supply values/arguments
dynamically.

--
Jeffrey R. Bailey
"Norman Fritag" <mt*****@ozemail.com.au> wrote in message
news:hQ***************@nnrp1.ozemail.com.au...
hi there,

StrSql = "UPDATE TblResults SET TblResults.DOHID = " & intnum1 & _
" ,TblResults!AHS = " & chr(34) & txtAHSto & chr(34) _
& " WHERE (((TblResults.DOHID)=& " Intnum2 & "));"

what's going wrong with this sql statement. It produces an error at the
textstring. saying ... 'AHStext". error??

any hints??

Regards

Norman

Nov 12 '05 #2
Correction
& " WHERE (((TblResults.DOHID)=" & Intnum2 & "));"

"Norman Fritag" <mt*****@ozemail.com.au> wrote in message news:<hQ***************@nnrp1.ozemail.com.au>...
hi there,

StrSql = "UPDATE TblResults SET TblResults.DOHID = " & intnum1 & _
" ,TblResults!AHS = " & chr(34) & txtAHSto & chr(34) _
& " WHERE (((TblResults.DOHID)=& " Intnum2 & "));"

what's going wrong with this sql statement. It produces an error at the
textstring. saying ... 'AHStext". error??

any hints??

Regards

Norman

Nov 12 '05 #3
"Norman Fritag" <mt*****@ozemail.com.au> wrote in message news:<hQ***************@nnrp1.ozemail.com.au>...
hi there,

StrSql = "UPDATE TblResults SET TblResults.DOHID = " & intnum1 & _
" ,TblResults!AHS = " & chr(34) & txtAHSto & chr(34) _
& " WHERE (((TblResults.DOHID)=& " Intnum2 & "));"

what's going wrong with this sql statement. It produces an error at the
textstring. saying ... 'AHStext". error??

any hints??

Regards

Norman


Hi Norman,

Just looking at you code briefly, it seems that the '&' is out of
place on your last line. Hope that's it???

& " WHERE (((TblResults.DOHID)= " & Intnum2 & "));"

FYI- I just recently learned that in SQL code you can replace Chr(34)
with a single quote. For example, your middle line:

" ,TblResults!AHS = '" & txtAHSto & "'"

Good Luck...

Dave
Nov 12 '05 #4
Da***************@SunLife.com (Dave @ SunLife) wrote in message news:<4e**************************@posting.google. com>...
"Norman Fritag" <mt*****@ozemail.com.au> wrote in message news:<hQ***************@nnrp1.ozemail.com.au>...
hi there,

StrSql = "UPDATE TblResults SET TblResults.DOHID = " & intnum1 & _
" ,TblResults!AHS = " & chr(34) & txtAHSto & chr(34) _
& " WHERE (((TblResults.DOHID)=& " Intnum2 & "));"

what's going wrong with this sql statement. It produces an error at the
textstring. saying ... 'AHStext". error??

any hints??

Regards

Norman


Hi Norman,

Just looking at you code briefly, it seems that the '&' is out of
place on your last line. Hope that's it???

& " WHERE (((TblResults.DOHID)= " & Intnum2 & "));"

FYI- I just recently learned that in SQL code you can replace Chr(34)
with a single quote. For example, your middle line:

" ,TblResults!AHS = '" & txtAHSto & "'"

Good Luck...

Dave


Make sure you test what happens when txtAHSto contains a single quote first.

James A. Fortune
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mohammed Mazid | last post: by
3 posts views Thread by Stevey | last post: by
10 posts views Thread by glenn | last post: by
53 posts views Thread by Jeff | last post: by
56 posts views Thread by spibou | last post: by
2 posts views Thread by Allan Ebdrup | last post: by
3 posts views Thread by Zhang Weiwu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.