insert into

J

jsrogol

I'm trying to create a date in a form and then insert the date into a table
but can't seem to get the coding correct. I know the first part works but the
insert into command generates the error: "too few parameters. Expected 1".
I would appreciate any help. This code is on an exit event in a form.

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

Set db = CurrentDb()
db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" SELECT SearchDate AS SearchDate;"
 
D

Douglas J. Steele

db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" VALUES(" & Format(SearchDate, "\#mm\/dd\/yyyy\#") & ")"

BTW, there's no need for the comparison you're doing. The DateAdd function
will automatically handle the year increase when adding 1 month to a date in
December.

In other words, you can replace:

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

with

Dim SearchDate As Date
Dim db As DAO.Database

SearchDate = DateAdd("m", 1, DateSerial(Me.cboYear, Me.cboMonth, 1))

Note that you should never use month and year as variable names (or field
names, or control names, or anything else that you name). Those are both
reserved words, and using them for your own purposes can lead to problems.
I'd also highly recommend using the DateSerial function, rather than
assuming that all your users are going to have their Short Date format set
to mm/dd/yyyy.
 
J

jsrogol

Thanks so very much. It work great.

By the way, do you know of a reference that explains how to manipulate time?
I'm trying to subtract time (arrival time-departure time) and get some very
weird numbers.

Jsrogol

Douglas J. Steele said:
db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" VALUES(" & Format(SearchDate, "\#mm\/dd\/yyyy\#") & ")"

BTW, there's no need for the comparison you're doing. The DateAdd function
will automatically handle the year increase when adding 1 month to a date in
December.

In other words, you can replace:

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

with

Dim SearchDate As Date
Dim db As DAO.Database

SearchDate = DateAdd("m", 1, DateSerial(Me.cboYear, Me.cboMonth, 1))

Note that you should never use month and year as variable names (or field
names, or control names, or anything else that you name). Those are both
reserved words, and using them for your own purposes can lead to problems.
I'd also highly recommend using the DateSerial function, rather than
assuming that all your users are going to have their Short Date format set
to mm/dd/yyyy.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jsrogol said:
I'm trying to create a date in a form and then insert the date into a
table
but can't seem to get the coding correct. I know the first part works but
the
insert into command generates the error: "too few parameters. Expected 1".
I would appreciate any help. This code is on an exit event in a form.

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

Set db = CurrentDb()
db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" SELECT SearchDate AS SearchDate;"
Click to expand...
Click to expand...
 
D

Douglas J. Steele

How are you attempting to subtract the time?

Date/Times are stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec, 1899, and
the decimal part represents the time as a fraction of a day. If you're
simply using time1 - time2, then yes, you would see an unusual result. <g>

In general, you use the DateDiff function to determine differences, or the
DateAdd function to add (or subtract) from a given date/time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


jsrogol said:
Thanks so very much. It work great.

By the way, do you know of a reference that explains how to manipulate time?
I'm trying to subtract time (arrival time-departure time) and get some very
weird numbers.

Jsrogol

Douglas J. Steele said:
db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" VALUES(" & Format(SearchDate, "\#mm\/dd\/yyyy\#") & ")"

BTW, there's no need for the comparison you're doing. The DateAdd function
will automatically handle the year increase when adding 1 month to a date in
December.

In other words, you can replace:

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

with

Dim SearchDate As Date
Dim db As DAO.Database

SearchDate = DateAdd("m", 1, DateSerial(Me.cboYear, Me.cboMonth, 1))

Note that you should never use month and year as variable names (or field
names, or control names, or anything else that you name). Those are both
reserved words, and using them for your own purposes can lead to problems.
I'd also highly recommend using the DateSerial function, rather than
assuming that all your users are going to have their Short Date format set
to mm/dd/yyyy.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jsrogol said:
I'm trying to create a date in a form and then insert the date into a
table
but can't seem to get the coding correct. I know the first part works but
the
insert into command generates the error: "too few parameters. Expected 1".
I would appreciate any help. This code is on an exit event in a form.

Dim year As Integer
Dim month As Integer
Dim NextMonth As Integer
Dim SearchDate As Date
Dim db As DAO.Database

month = Me.cboMonth
year = Me.cboYear

If month = 12 Then
NextMonth = 1
year = year + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
Else
NextMonth = month + 1
SearchDate = CDate([NextMonth] & "/1/" & year)
End If

Set db = CurrentDb()
db.Execute "INSERT INTO refLookupSearchDate (SearchDate)" & _
" SELECT SearchDate AS SearchDate;"
Click to expand...
Click to expand...
Click to expand...
 
Top