By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,099 Members | 947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,099 IT Pros & Developers. It's quick & easy.

vba Sql question

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
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.