Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #789

Re: SQL In VBA

Show all headers | View raw

Barry wrote:
> 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

"variable", not "variant"

> 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 ?

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. 

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SQL In VBA Barry <Barry@actson.demon.co.uk> - 2011-04-03 08:49 -0700
  Re: SQL In VBA "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-03 12:44 -0400
  Re: SQL In VBA Marshall Barton <marshbarton@wowway.com> - 2011-04-03 13:15 -0500

csiph-web