Please help with sql.

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have a very interesting problem that I do not know why it is happening. The
following sql statment:
**********************************************************
sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID," & _
"ActivityMeasure, BD, IntakeTargetDate, Status ) " & _
"SELECT " & Trim(Me.PaperReqID) & ", RequestTypeID, Activities,
BD, " & _
"DateAdd(""d"", [tblkpActivity].[BD], Date()),'Not Due' " & _
" FROM tblkpActivity" & _
" WHERE RequestTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
Set db = Nothing
Me.Refresh
****************************************************
Works only if PaperReqID does not have letters in the beginning. For example:

PaperReqID = 20071203 the sql works
PaperReqID = PO20071203 the sql does not work

PaperReqID is a Char 50 field and it is a PK.
The database backend is in sql2000 and the front end is in Access 2003 not
in access project. Can some one explain to me why it behaves this way? I have
many dbs like that and never seen this before.
thanks
Al
 
I'm surprised it works at all (although I guess some funky data type
coercion is happening).

Try putting the value of PaperReqID in quotes, like you are in the Where
clause:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID," & _
"ActivityMeasure, BD, IntakeTargetDate, Status ) " & _
"SELECT '" & Trim(Me.PaperReqID) & "', RequestTypeID, Activities, BD, " &
_
"DateAdd(""d"", [tblkpActivity].[BD], Date()),'Not Due' " & _
" FROM tblkpActivity" & _
" WHERE RequestTypeID ='" & Me.ReqTypeID & "'"
 
Yes! that was exactly the problem. Thanks

Douglas J. Steele said:
I'm surprised it works at all (although I guess some funky data type
coercion is happening).

Try putting the value of PaperReqID in quotes, like you are in the Where
clause:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID," & _
"ActivityMeasure, BD, IntakeTargetDate, Status ) " & _
"SELECT '" & Trim(Me.PaperReqID) & "', RequestTypeID, Activities, BD, " &
_
"DateAdd(""d"", [tblkpActivity].[BD], Date()),'Not Due' " & _
" FROM tblkpActivity" & _
" WHERE RequestTypeID ='" & Me.ReqTypeID & "'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al said:
I have a very interesting problem that I do not know why it is happening.
The
following sql statment:
**********************************************************
sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,"
& _
"ActivityMeasure, BD, IntakeTargetDate, Status ) " & _
"SELECT " & Trim(Me.PaperReqID) & ", RequestTypeID, Activities,
BD, " & _
"DateAdd(""d"", [tblkpActivity].[BD], Date()),'Not Due' " & _
" FROM tblkpActivity" & _
" WHERE RequestTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
Set db = Nothing
Me.Refresh
****************************************************
Works only if PaperReqID does not have letters in the beginning. For
example:

PaperReqID = 20071203 the sql works
PaperReqID = PO20071203 the sql does not work

PaperReqID is a Char 50 field and it is a PK.
The database backend is in sql2000 and the front end is in Access 2003 not
in access project. Can some one explain to me why it behaves this way? I
have
many dbs like that and never seen this before.
thanks
Al
Click to expand...
Click to expand...
 
Back
Top