Try to doCmd.runSQL with INSERT

[sonso]

I have the following code, to try to add records to a date table. I simply want to add sequential dates up to an end date and the corresponding day names (later adding holidays and weekends, but keeping it simple for now). I get a SQL error when I run this.

Dim myday As Date
myday = #1/1/02#
While myday < #1/1/05#
DoCmd.RunSQL (&quot;INSERT INTO Dates (Date) VALUES (myday)&quot
myday = myday + 1
Wend

 

[scking]

Since DoCmd is not a function and you are including your variable name inside a string you would need to construct it as follows:

DoCmd.RunSQL &quot;INSERT INTO Dates (Date) VALUES (&quot; & myday & &quot&quot;

I also would caution you against any use of keywords or function names as field names as this can cause real confusion and sometimes result in hard-to-debug errors.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[sonso]

Agreed on the reserved words... Where is the best source of the particular flavor of SQL used in Access? It seems that while SQL is &quot;universal&quot;, it's different in every product!

 

[scking]

If you follow the ANSI Standard for SQL (two flavors) you are pretty safe but ANSI 92 has not been fully implemented in Access. The format for the 'INSERT' SQL above is pretty standard. I always use my ORACLE SQL books to build Access SQL when it can't be developed using the query builder. The INSERT you used was formatted properly but inclosing the name of a variable in a string prevents the system from using the value of the variable and only uses the string value of the variable name. Whenever I have questions about whether it is constructed properly I set a breakpoint following the final constructor statement for the SQL and look at the value of the SQL variable. I generally DO NOT simply use a string SQL statement for that very reason. By the way; I forgot the date delimiters previously.

Dim strSQL As String
Dim myday As Date
myday = #1/1/02#
While myday < #1/1/05#
strSQL = &quot;INSERT INTO Dates (Date) VALUES (#&quot; & myday & &quot;#);&quot;
' Print out the value of the strSQL to the immediate window
Debug.Print strSQL
DoCmd.RunSQL strSQL
myday = myday + 1
Wend

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------