Variable not working on SQL UPDATE 1

[only412c]

Before explaining the subject at hand, I would first like to thank those who have provided me with support in Tek-Tips forums. I venture into learning MS Access a few month back and took a greater interest in coding again. WIthout your support I would have found it harder to understand, as most books do not tell the whole story.

Well back to the subject. I am trying to update records into a table using

DoCmd.RunSQL &quot;UPDATE TEMPTBL SET TEMPTBL.F6 = StrTablename, TEMPTBL.F8 = IIF(month(DATE())< 10,0) & Month(date()) & IIF(Day(DATE()) < 10,0) & Day(Date()) & Year(Date())&quot;

The issue is that StrTablename string is not accepting automatically and a Msgbox keeps appearing stating &quot;Enter a parameter value&quot; for strtablename. I enter the parameter value and the update completes it mission. However, I don't want a MsgBox to appear and want the process to run automatically.

Prior to running SQL UPDATE:
I assign the variable Strtablename as String
I then code it with a string Strtablename = &quot;Y&quot;

Is the syntax correct in the SQL Update command?
How can I get it to work without any msgbox interferring?


Sincerely

Juan

 

[dhookom]

Try get your update SQL into a memory variable.
Dim strSQL as String
Dim strTableName as String
strTableName = &quot;Y&quot;
strSQL = &quot;UPDATE TempTbl SET F6='&quot; & strTableName & _
&quot;', F8 = IIf(Month(Date())<10,0) ...&quot;
Debug.Print strSQL
DoCmd.RunSQL strSQL
I'm not sure of your syntax or logic with the all the date stuff. It looks a little convoluted.

Duane
MS Access MVP

 

[only412c]

Thanks Duane,

I will give it a try when I get to work. I think it is a matter of where the quotations need to be placed. As far as the date stuff it is working fine. The Date() function does not put a zero in front of the date it generates when the month or day is less than 10, so I did an IIF statement to take care of this.

thanks.

Juan

 

[dhookom]

Your entire date() expression is more complex than necessary. First, your IIf() expressions should contain both a True and a False expression:
IIF(month(DATE())< 10,&quot;0&quot;,&quot;&quot
However, I think you are attempting to place a value into F8 like:
, TEMPTBL.F8=Format(Date(),&quot;mmddyyyy&quot

Duane
MS Access MVP

 

[only412c]

Thanks Duane,

I have re-did the string again and date format. Thanks alot for your help. The string is working fine.