How to run an SQL Insert statement in a Module using variables

[Guest]

Option Compare Database
Option Explicit
Sub adddate()

Dim dtestart As Date, dteend As Date, dterun As Date, intcount As Integer, intperiods As Integer
Dim Period As Integer, vYear As Integer, vName As String
Dim strnum As String
intcount = 1
intperiods = 13
dtestart = #4/1/2004# 'US date format
vName = "BOPSWSOR"
vYear = 2004


If 13 / 4 > Int(13 / 4) Then

strnum = Int(13 / 4) + 1

Else

strnum = Int(13 / 4)

End If


Do While intcount <= intperiods

dteend = DateAdd("d", 28, dtestart)
dterun = DateAdd("d", 2, dteend)

If intperiods = 13 Then
Period = intcount
Else
Period = intcount / 4
End If



DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year], [Period], [startdate], [enddate]) values (vName, dterun, vYear, Period, dtestart, dteend)"


intcount = intcount + 1
dtestart = DateAdd("d", -1, dteend)


Loop

End Sub


I am being prompted to enter values for the variables although they are declared and assigned. I am a newbie. Sorry if this is a bit basic

 

[Roger Carlson]

Something like thisL

DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year], [Period],
[startdate], [enddate]) values ('" & vName & "', #" & dterun & "#, " & vYear
& ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)"

String and date values need to be delimited. Strings with an apostrophe (')
and dates with a pound sign (#). Number like integers do not need
additional delimiters. Strings that hold names can be a particular problem,
because they can have apostrophes in them. This will cause problems, so you
can replace the apostrophe with TWO quote marks, like this:

DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year], [Period],
[startdate], [enddate]) values (""" & vName & """, #" & dterun & "#, " &
vYear & ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)"

However, I would suggest building a string variable to hold the Insert
Statement. Then you can use Debug.Print to see what is being evaluated.
Like this:

strSQL = "insert into kevfeeder ([Name], [Rundate], [Year], [Period],
[startdate], [enddate]) values (""" & vName & """, #" & dterun & "#, " &
vYear & ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)"

DoCmd.RunSQL strSQL


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Option Compare Database
Option Explicit
Sub adddate()

Dim dtestart As Date, dteend As Date, dterun As Date, intcount As Integer, intperiods As Integer
Dim Period As Integer, vYear As Integer, vName As String
Dim strnum As String
intcount = 1
intperiods = 13
dtestart = #4/1/2004# 'US date format
vName = "BOPSWSOR"
vYear = 2004


If 13 / 4 > Int(13 / 4) Then

strnum = Int(13 / 4) + 1

Else

strnum = Int(13 / 4)

End If


Do While intcount <= intperiods

dteend = DateAdd("d", 28, dtestart)
dterun = DateAdd("d", 2, dteend)

If intperiods = 13 Then
Period = intcount
Else
Period = intcount / 4
End If



DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year],

[Period], [startdate], [enddate]) values (vName, dterun, vYear, Period,
dtestart, dteend)"

intcount = intcount + 1
dtestart = DateAdd("d", -1, dteend)


Loop

End Sub


I am being prompted to enter values for the variables although they are

declared and assigned. I am a newbie. Sorry if this is a bit basic

 

[solex]

I just ran into a problem when trying to use the ADODB.Command to insert
multiple rows. The function runs but does not do the insert or return any
error message. I change the function to use the DoCMD.RunSQL and it works
with no change in the SQL statement.

Any Ideas?

Dan

Option Compare Database
Option Explicit
Sub adddate()

Dim dtestart As Date, dteend As Date, dterun As Date, intcount As Integer, intperiods As Integer
Dim Period As Integer, vYear As Integer, vName As String
Dim strnum As String
intcount = 1
intperiods = 13
dtestart = #4/1/2004# 'US date format
vName = "BOPSWSOR"
vYear = 2004


If 13 / 4 > Int(13 / 4) Then

strnum = Int(13 / 4) + 1

Else

strnum = Int(13 / 4)

End If


Do While intcount <= intperiods

dteend = DateAdd("d", 28, dtestart)
dterun = DateAdd("d", 2, dteend)

If intperiods = 13 Then
Period = intcount
Else
Period = intcount / 4
End If



DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year],

[Period], [startdate], [enddate]) values (vName, dterun, vYear, Period,
dtestart, dteend)"

intcount = intcount + 1
dtestart = DateAdd("d", -1, dteend)


Loop

End Sub


I am being prompted to enter values for the variables although they are

declared and assigned. I am a newbie. Sorry if this is a bit basic

 

[Guest]

Thank you. That works fine

----- Roger Carlson wrote: ----

Something like this

DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year], [Period]
[startdate], [enddate]) values ('" & vName & "', #" & dterun & "#, " & vYea
& ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)

String and date values need to be delimited. Strings with an apostrophe ('
and dates with a pound sign (#). Number like integers do not nee
additional delimiters. Strings that hold names can be a particular problem
because they can have apostrophes in them. This will cause problems, so yo
can replace the apostrophe with TWO quote marks, like this

DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year], [Period]
[startdate], [enddate]) values (""" & vName & """, #" & dterun & "#, "
vYear & ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)

However, I would suggest building a string variable to hold the Inser
Statement. Then you can use Debug.Print to see what is being evaluated
Like this

strSQL = "insert into kevfeeder ([Name], [Rundate], [Year], [Period]
[startdate], [enddate]) values (""" & vName & """, #" & dterun & "#, "
vYear & ", " &Period & ", #" & dtestart & "#, #" & dteend & "#)

DoCmd.RunSQL strSQ


--
--Roger Carlso
www.rogersaccesslibrary.co
Reply to: Roger dot Carlson at Spectrum-Health dot Or

Option Compare Databas
Option Explici
Sub adddate(

Dim dtestart As Date, dteend As Date, dterun As Date, intcount As Integer

intperiods As Intege
Dim Period As Integer, vYear As Integer, vName As Strin
Dim strnum As Strin
intcount =
intperiods = 1
dtestart = #4/1/2004# 'US date forma
vName = "BOPSWSOR
vYear = 200

strnum = Int(13 / 4) +
Els
strnum = Int(13 / 4
End I
dteend = DateAdd("d", 28, dtestart

dterun = DateAdd("d", 2, dteend

If intperiods = 13 The

Period = intcoun
Els
Period = intcount /
End I

DoCmd.RunSQL "insert into kevfeeder ([Name], [Rundate], [Year]

[Period], [startdate], [enddate]) values (vName, dterun, vYear, Period
dtestart, dteend)declared and assigned. I am a newbie. Sorry if this is a bit basi