If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Acc97 Date Display as 12/30/1899 not "Today"
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 & ")" -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200704/1 |
#2
|
|||
|
|||
Acc97 Date Display as 12/30/1899 not "Today"
On Mon, 30 Apr 2007 23:00:43 GMT, "disneygoof via AccessMonster.com"
u32262@uwe wrote: 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] |
#3
|
|||
|
|||
Acc97 Date Display as 12/30/1899 not "Today"
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) John W. Vinson wrote: 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] -- David (Disneygoof) Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200705/1 |
#4
|
|||
|
|||
Acc97 Date Display as 12/30/1899 not "Today"
Thanks Aaron.
D. Aaron Kempf wrote: 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] John W. Vinson [MVP] http://www.accessmonster.com/Uwe/For...arted/200705/1 -- David (Disneygoof) Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|