Inserting Data

K

Kerry Purdy

Hiya

I have been given 2 conflicting sets of advice about inserting data into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied (delete
query), then filled with a list of dates between the start and end dates
entered by the user.

I was advised to use ADO for inserting records and have also been advised to
use an append query. Which is best? I would realy appreciate some help witht
he current code, or if its the inserting records using ADO I don't have any
experience using this atall.

Thank you very much for your time.

Kerry

The code below is as far as I have got with using the SQL from an append
query. Up to the msgbox new day all works well, it shows the correct days in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
 
J

John Spencer

Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub
 
K

Kerry Purdy

Hi John

Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"

any ideas?

Thanks very much

Kerry

John Spencer said:
Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

Kerry Purdy said:
Hiya

I have been given 2 conflicting sets of advice about inserting data into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied (delete
query), then filled with a list of dates between the start and end dates
entered by the user.

I was advised to use ADO for inserting records and have also been advised
to
use an append query. Which is best? I would realy appreciate some help
witht
he current code, or if its the inserting records using ADO I don't have
any
experience using this atall.

Thank you very much for your time.

Kerry

The code below is as far as I have got with using the SQL from an append
query. Up to the msgbox new day all works well, it shows the correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
 
M

Marshall Barton

I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry said:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"


John Spencer said:
Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

Kerry Purdy said:
I have been given 2 conflicting sets of advice about inserting data into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied (delete
query), then filled with a list of dates between the start and end dates
entered by the user.

I was advised to use ADO for inserting records and have also been advised
to
use an append query. Which is best? I would realy appreciate some help
witht
he current code, or if its the inserting records using ADO I don't have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an append
query. Up to the msgbox new day all works well, it shows the correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Kerry Purdy

Hi Marshall

Thanks for the inf, still no joy though - this is my code (variables are
declared at the top of the module

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'Set con = Application.CurrentProject.Connection

'xxx Run date query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"

Next x

End sub

Marshall Barton said:
I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry said:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"


John Spencer said:
Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

I have been given 2 conflicting sets of advice about inserting data into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied (delete
query), then filled with a list of dates between the start and end dates
entered by the user.

I was advised to use ADO for inserting records and have also been advised
to
use an append query. Which is best? I would realy appreciate some help
witht
he current code, or if its the inserting records using ADO I don't have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an append
query. Up to the msgbox new day all works well, it shows the correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try the following to troubleshoot.

Dim StrSQL as string 'Assign string to variable

For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
strSQL = "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"
Debug.print StrSQL
Stop 'Since this is erroring, stop the code right here until we fix the
error
' Open VBA window and copy the SQL statement into a blank query.
' Try to run the query and see what error occurs.
' Once you've done this then remove the debugging code.
'DoCmd.RunSQL strSQL
Next x
Kerry Purdy said:
Hi Marshall

Thanks for the inf, still no joy though - this is my code (variables are
declared at the top of the module

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'Set con = Application.CurrentProject.Connection

'xxx Run date query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"

Next x

End sub

Marshall Barton said:
I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry said:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"


:

Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code
module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and
EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

I have been given 2 conflicting sets of advice about inserting data
into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied
(delete
query), then filled with a list of dates between the start and end
dates
entered by the user.

I was advised to use ADO for inserting records and have also been
advised
to
use an append query. Which is best? I would realy appreciate some
help
witht
he current code, or if its the inserting records using ADO I don't
have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an
append
query. Up to the msgbox new day all works well, it shows the
correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Kerry Purdy

Hiya

I have sorted the code but now the variable EachNewDay is not being sent to
the DummyMatrixData table. It shows an input box asking for EachNewDay. If
I enter the dates manually into each input box it works perfectly. Do you
have any idea why the variable is not being used please??

Thanks very much for your time.

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrixTable
For x = 0 To RptDur
EachNewDay = RptStart + x
'MsgBox NewDay
StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT EachNewDay AS MatrixDate"

DoCmd.RunSQL StrSQL
Next x


John Spencer said:
Try the following to troubleshoot.

Dim StrSQL as string 'Assign string to variable

For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
strSQL = "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"
Debug.print StrSQL
Stop 'Since this is erroring, stop the code right here until we fix the
error
' Open VBA window and copy the SQL statement into a blank query.
' Try to run the query and see what error occurs.
' Once you've done this then remove the debugging code.
'DoCmd.RunSQL strSQL
Next x
Kerry Purdy said:
Hi Marshall

Thanks for the inf, still no joy though - this is my code (variables are
declared at the top of the module

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'Set con = Application.CurrentProject.Connection

'xxx Run date query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"

Next x

End sub

Marshall Barton said:
I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry Purdy wrote:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134, syntax
error in INSERT INTO statement"


:

Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code
module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and
EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

I have been given 2 conflicting sets of advice about inserting data
into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied
(delete
query), then filled with a list of dates between the start and end
dates
entered by the user.

I was advised to use ADO for inserting records and have also been
advised
to
use an append query. Which is best? I would realy appreciate some
help
witht
he current code, or if its the inserting records using ADO I don't
have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an
append
query. Up to the msgbox new day all works well, it shows the
correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate) VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Because the SQL engine has no idea of what EachNewDay is. It cannot see the
variable you have created and populated. You have to pass the value in

Question that should have been asked long ago. Is this an Access Project
(.adp) or is it an Access database (.mdb)? If the former, you might try
changing the SQL string to the following. Since I don't work with .adp very
often I am only guessing that the following would be correct syntax.

StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT '" & EachNewDay & "' AS MatrixDate"




Kerry Purdy said:
Hiya

I have sorted the code but now the variable EachNewDay is not being sent
to
the DummyMatrixData table. It shows an input box asking for EachNewDay.
If
I enter the dates manually into each input box it works perfectly. Do you
have any idea why the variable is not being used please??

Thanks very much for your time.

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrixTable
For x = 0 To RptDur
EachNewDay = RptStart + x
'MsgBox NewDay
StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT EachNewDay AS MatrixDate"

DoCmd.RunSQL StrSQL
Next x


John Spencer said:
Try the following to troubleshoot.

Dim StrSQL as string 'Assign string to variable

For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
strSQL = "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"
Debug.print StrSQL
Stop 'Since this is erroring, stop the code right here until we fix
the
error
' Open VBA window and copy the SQL statement into a blank query.
' Try to run the query and see what error occurs.
' Once you've done this then remove the debugging code.
'DoCmd.RunSQL strSQL
Next x
Kerry Purdy said:
Hi Marshall

Thanks for the inf, still no joy though - this is my code (variables
are
declared at the top of the module

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'Set con = Application.CurrentProject.Connection

'xxx Run date query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"

Next x

End sub

:

I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry Purdy wrote:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"


:

Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code
module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and
EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

I have been given 2 conflicting sets of advice about inserting
data
into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied
(delete
query), then filled with a list of dates between the start and
end
dates
entered by the user.

I was advised to use ADO for inserting records and have also been
advised
to
use an append query. Which is best? I would realy appreciate
some
help
witht
he current code, or if its the inserting records using ADO I
don't
have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an
append
query. Up to the msgbox new day all works well, it shows the
correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)
VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Ferguson

StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT '" & EachNewDay & "' AS MatrixDate"
Click to expand...


The whole point of this thread is that you can't trust VBA's default date
coversion to do anything right or consistent. ITYM this:-

StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT " & Format(EachNewDay," 'yyyy\-mm\-dd' ") & _
"AS MatrixDate"

All the best


Tim F
 
K

Kerry Purdy

It works it works it works!! I hadn't declared my EachNewDay variable, I
also copied and pasted your strSQL syntax.

Fantastic - Thanks so very much for your help.

Kerry


John Spencer said:
Because the SQL engine has no idea of what EachNewDay is. It cannot see the
variable you have created and populated. You have to pass the value in

Question that should have been asked long ago. Is this an Access Project
(.adp) or is it an Access database (.mdb)? If the former, you might try
changing the SQL string to the following. Since I don't work with .adp very
often I am only guessing that the following would be correct syntax.

StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT '" & EachNewDay & "' AS MatrixDate"




Kerry Purdy said:
Hiya

I have sorted the code but now the variable EachNewDay is not being sent
to
the DummyMatrixData table. It shows an input box asking for EachNewDay.
If
I enter the dates manually into each input box it works perfectly. Do you
have any idea why the variable is not being used please??

Thanks very much for your time.

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrixTable
For x = 0 To RptDur
EachNewDay = RptStart + x
'MsgBox NewDay
StrSQL = "INSERT INTO tblMatrixDummyData ( MatrixDate )" & _
"SELECT EachNewDay AS MatrixDate"

DoCmd.RunSQL StrSQL
Next x


John Spencer said:
Try the following to troubleshoot.

Dim StrSQL as string 'Assign string to variable

For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
strSQL = "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"
Debug.print StrSQL
Stop 'Since this is erroring, stop the code right here until we fix
the
error
' Open VBA window and copy the SQL statement into a blank query.
' Try to run the query and see what error occurs.
' Once you've done this then remove the debugging code.
'DoCmd.RunSQL strSQL
Next x
Hi Marshall

Thanks for the inf, still no joy though - this is my code (variables
are
declared at the top of the module

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'Set con = Application.CurrentProject.Connection

'xxx Run date query to empty all records from DummyMatrix Table xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay, "\#yyyy-mm-dd\#") & ")"

Next x

End sub

:

I think John inadvertantly left out the \ in front of the #

" VALUES (" & Format(NewDay,"\#yyyy-mm-dd\#") & ")"
--
Marsh
MVP [MS Access]


Kerry Purdy wrote:
Thanks for the code, I am still getting the same error

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"


:

Sub DummyMatrix()
'Declare your variables
'Recommendation always use Option Explicit at the top of your code
module(s)
Dim RptStart as Date, RptEnd as Date, NewDay as Date
Dim RptDur as Long, x as Long

'Insert code to check if you have dates in StartDateEntry and
EnddateEntry
If IsDate(Forms![Frm Main SB]![StartDateEntry]) and _
IsDate(Forms![Frm Main SB]![EndDateEntry]) Then

'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)" & _
" VALUES (" & Format(NewDay,"#yyyy-mm-dd#") & ")"
Next x
Else
Msgbox "Need two dates on main form"
End if

End Sub

I have been given 2 conflicting sets of advice about inserting
data
into a
table.

I have a table (tbl dummymatrixdata) which needs to be emptied
(delete
query), then filled with a list of dates between the start and
end
dates
entered by the user.

I was advised to use ADO for inserting records and have also been
advised
to
use an append query. Which is best? I would realy appreciate
some
help
witht
he current code, or if its the inserting records using ADO I
don't
have
any
experience using this atall.


The code below is as far as I have got with using the SQL from an
append
query. Up to the msgbox new day all works well, it shows the
correct days
in
the message box up to the end date.

Its the DoCmd.RunSql that shows an error of "Run time error 3134,
syntax
error in INSERT INTO statement"

Sub DummyMatrix()
'xxx Pull dates from the Main Switchboard - fill variables xxx
RptStart = Forms![Frm Main SB]![StartDateEntry]
RptEnd = Forms![Frm Main SB]![EndDateEntry]

RptDur = DateDiff("d", RptStart, RptEnd)

'xxx Run delete query to empty all records from DummyMatrix Table
xxx
DoCmd.OpenQuery ("qry DelMatrixDData")

'xxx Run loop to insert each date btwn RptStart & RptEnd into
DummyMatrix
table xxx
For x = 0 To RptDur
NewDay = RptStart + x
'MsgBox NewDay
DoCmd.RunSQL "INSERT INTO tbl MatrixDummyData (MatrixDate)
VALUES
(NewDay)"
Next x

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Picker Crazy! 0
Show All Dates 1
SQL / query syntax, and code about inserting records 1
Initiate an action only if query returns data 2
Make Table from external DB Connection 1

Top