On a form I want to on load create in SQL records to be added to a
table. The records consist of todays date so that is a variant in the
sql and then there isa loop that goes 7 times. I end up with 7 records
showing blank date.
Code below

Private Sub Form_Load()
Dim dbs As Database
Dim Datenow As Date

Dim count As Integer
Set dbs = CurrentDb

Datenow = Date - 1
count = 0
Do Until count = 7
Datenow = Datenow + 1

' sql = "INSERT INTO Datestoselect (dates) " & " Values (" &
Datenow & ");"
Dates = Datenow
dbs.Execute ("INSERT INTO Datestoselect (dates) " & " Values (" &
Datenow & ");")

count = count + 1
Loop

End Sub

Any ideas ?

Barry wrote:
"variable", not "variant"

About what? What is the question? Oh wait. Is the question "why am I getting
blank dates"?

I do see that yu define a variable (sql) that you don't subsequently use
(don't worry about appending the semicolons - they aren't needed). Why is
that? Assigning the sql to a variable is a great idea because it allows you
to inspect it while steppping through the code. Only you should then execute
it like this:

dbs,Execute sql

What's supposed to be the purpose of this line: "Dates = Datenow"? It seems
to have no purpose at all.

I suggest putting a breakpoint in that code and stepping through it (this is
a great time to learn how to debug).

If I was doing this task, I would not be using a loop. I would use a utility
table called Numbers, a table with a single column called Num containg
numbers 0-1000 (1001 rows).. This, in addition to many other uses, would
allow me to do this:

INSERT DateToSelect (dates) SELECT Date() + Num FROM Numbers where Num
between 0 and 6

You could either save this in a query to be executed in VBA or assign the
statement to a string variable to be executed. No loop needed.

Barry wrote:

I think Bob's use of the Numbers table is clearly the best
way to do that.

But to try to answer your specific question, the problem I
see is that the date value ends up being an expression when
the query is executed:
. . . Values (4/3/2010)
and 4 divided by 3 divided by 2010 evaluates to a pretty
small number, i.e. just a few seconds.

To concatenate a date into your SQL you need the result to
be a date literal. I would do it this way:

sql = "INSERT INTO Datestoselect (dates) Values (" _
& Format(Datenow, "\#yyyy-m-d\#") & ")"

--
Marsh