Acc97 Date Display as 12/30/1899 not "Today"

[disneygoof via AccessMonster.com]

I have been messing with this for a while and to no prevail...I am usning an
INSERT INTO SQL statement to write date to a table. When I try to write
"todays" date to the table Access, or some unknown force, keeps writing
12/30/1899 to the table. When I run debug and check my variables(RR and RD),
they show the proper date...I am not use to working with a 97 database,
unfortunitly, the company does not own anyother version of Access and
upgrading will have to wait.

When I first wrote the code I did not use any variables, I thought maybe I
made a few mistakes so I broke it down to what you see below...still getting
the same issue...HELP!

I've tried ', ", # ...nothing seems to help

Dim RFI As Integer
Dim ITN As Integer
Dim RR As Date 'or String
Dim RD As Date 'or String

RFI = DMax("[RFINumber]", "tblRFI_Log", "[ProjectNo] =" & cboJobs) + 1
ITN = DMax("[ItemNumber]", "tblRFI_Log", "[ProjectNo] =" & cboJobs) + 1
RR = Date + 14
RD = Date

DoCmd.RunSQL "insert into tblRFI_Log (ProjectNO, RFINumber, ItemNumber,
rfidate,_
ResponseRequired) values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
" & RD &_
"," & RR & ")"

 

[John W. Vinson]

I have been messing with this for a while and to no prevail...I am usning an
INSERT INTO SQL statement to write date to a table. When I try to write
"todays" date to the table Access, or some unknown force, keeps writing
12/30/1899 to the table. When I run debug and check my variables(RR and RD),
they show the proper date...I am not use to working with a 97 database,
unfortunitly, the company does not own anyother version of Access and
upgrading will have to wait.

When I first wrote the code I did not use any variables, I thought maybe I
made a few mistakes so I broke it down to what you see below...still getting
the same issue...HELP!

I've tried ', ", # ...nothing seems to help

Dim RFI As Integer
Dim ITN As Integer
Dim RR As Date 'or String
Dim RD As Date 'or String

RFI = DMax("[RFINumber]", "tblRFI_Log", "[ProjectNo] =" & cboJobs) + 1
ITN = DMax("[ItemNumber]", "tblRFI_Log", "[ProjectNo] =" & cboJobs) + 1
RR = Date + 14
RD = Date

DoCmd.RunSQL "insert into tblRFI_Log (ProjectNO, RFINumber, ItemNumber,
rfidate,_
ResponseRequired) values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
" & RD &_
"," & RR & ")"

Delimit the RR and RD values with # in the VALUES clause. What's happening is
that 4/30/2007 is being interpreted as a division operation yielding
..000066434..., which corresponds to 12:00:06 on that long-ago date.

Try
DoCmd.RunSQL "insert into tblRFI_Log (ProjectNO, RFINumber, ItemNumber,
rfidate,_
ResponseRequired) values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & RD & "#,#" & RR & "#)"

or - safer -

values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & Format(RD,"mm/dd/yyyy") & "#,#" & Format(RR,"mm/dd/yyyy") & "#)"


John W. Vinson [MVP]

 

[disneygoof via AccessMonster.com]

Thanks John...both worked perfectly. I tried the # sign, but had it in the
wrong locations, explains why mine wasn't working. Thanks again.
David (disneygoof)

I have been messing with this for a while and to no prevail...I am usning an
INSERT INTO SQL statement to write date to a table. When I try to write

[quoted text clipped - 25 lines]

" & RD &_
"," & RR & ")"

Delimit the RR and RD values with # in the VALUES clause. What's happening is
that 4/30/2007 is being interpreted as a division operation yielding
.000066434..., which corresponds to 12:00:06 on that long-ago date.

Try
DoCmd.RunSQL "insert into tblRFI_Log (ProjectNO, RFINumber, ItemNumber,
rfidate,_
ResponseRequired) values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & RD & "#,#" & RR & "#)"

or - safer -

values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & Format(RD,"mm/dd/yyyy") & "#,#" & Format(RR,"mm/dd/yyyy") & "#)"

John W. Vinson [MVP]

 

[Aaron Kempf]

CORRECTION

you don't need the # sign for dates in SQL Server

Thanks John...both worked perfectly. I tried the # sign, but had it in the
wrong locations, explains why mine wasn't working. Thanks again.
David (disneygoof)

I have been messing with this for a while and to no prevail...I am usning an
INSERT INTO SQL statement to write date to a table. When I try to write

[quoted text clipped - 25 lines]

" & RD &_
"," & RR & ")"

Delimit the RR and RD values with # in the VALUES clause. What's happening is
that 4/30/2007 is being interpreted as a division operation yielding
.000066434..., which corresponds to 12:00:06 on that long-ago date.

Try
DoCmd.RunSQL "insert into tblRFI_Log (ProjectNO, RFINumber, ItemNumber,
rfidate,_
ResponseRequired) values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & RD & "#,#" & RR & "#)"

or - safer -

values (" & Me.cboJobs & "," & RFI & "," & ITN & ",
#" & Format(RD,"mm/dd/yyyy") & "#,#" & Format(RR,"mm/dd/yyyy") & "#)"

John W. Vinson [MVP]

 

[disneygoof via AccessMonster.com]

Thanks Aaron.
D.

CORRECTION

you don't need the # sign for dates in SQL Server

Thanks John...both worked perfectly. I tried the # sign, but had it in the
wrong locations, explains why mine wasn't working. Thanks again.

[quoted text clipped - 22 lines]