Updating Code from Access 2000 to 2003/2007

[Guest]

Hi All,
First please let me disclaim that I am not an access person, nor am I a sql
person. I work tier 3 support for an enterprise client, therefore it is
sometimes my job to figure out some things in waters I have never dipped my
toes in.

I have an application developed in Access 2000 which works just fine when
Access 2000 is preserved when upgrading the rest of office.

I have after many several hours narrowed down the exact lines which are
causing me grief. And here they are:

strTNO = Forms!Tasklog!TASKER_NO
dtOrigSusp = Forms!Tasklog!SUSPENSE

These two variables are called from these lines below which causes the
variables to populate from the form to populate the tables.

DoCmd.RunSQL "Insert into [TempNewTSS] select * from tasklog where
Tasker_no=strTNO"
DoCmd.RunSQL "update [TempNewTSS] set OrigSuspense = dtOrigSusp"

However, when I run these macros in Access 2003 with or without 2007 compat
pack, or when running in Access 2007, the variables do NOT populate, and the
user is prompted to provide the information manually in a msgbox like
windowed prompt.

Is there a syntactical change I can make, to make these variable
declarations operate in Office 2003/7 ?

Thank you

 

[John W. Vinson]

On Wed, 14 Nov 2007 08:40:02 -0800, Scot McPherson <Scot

Is there a syntactical change I can make, to make these variable
declarations operate in Office 2003/7 ?

I'll assume that strTNO and dtOrigSusp are a string and date VBA variable
respectively.

Try putting in delimiters:

DoCmd.RunSQL "Insert into [TempNewTSS] select * from tasklog where
Tasker_no='" & strTNO & "'"

For clarity, that's including a singlequote character ' before and after the
value of strTNO.

Date fields need # as a date delimiter: try

DoCmd.RunSQL "update [TempNewTSS] set OrigSuspense = #" & Format(dtOrigSusp,
"mm/dd/yyyy" & "#"

I'm a bit surprised this ever worked.

John W. Vinson [MVP]

 

[Guest]

Tasker_no='" & strTNO & "'"

John,
This was very helpful, I was able to fix all the problems because of your
help. Thank you very much.

Scot