Date format problem using INSERT INTO

[Jeff_NC]

I've searched these forums and haven't found a similar problem yet. It's probably something simple and stupid that i'm doing wrong.

I'm creating a video rental system in access and wish to insert a due date into one of my tables. For example I have a form which has three values account_number, product_id, and due_date.

I have a button that executes my procedure that then calculates the due date by using Date + 3 and sends it back to the form and then executes my SQL statement to insert those three values into my table. The date shows up correctly back in the form but it inserts some crazy value into the table and I can't figure out why.

Here is the procedure:

Private Sub check_out_item_Click()

Dim dueDate As Date
Dim pID, acctNum As Variant
dueDate = Date + 3
due_date.Value = dueDate

pID = product_id.Value
acctNum = account_number.Value

DoCmd.RunSQL "INSERT INTO Items_Checked_Out (account_number, product_id, due_date) VALUES (" & acctNum & ", " & pID & "," & dueDate & ")"

End Sub

Now for instance when the date gets calculated as 5/2/2002 it shows up in the table as 12/30/1899 and even weirder is that when I click on that field in the table it switches to a time value.

The due_date field in the table is set to Date/Time type using Short Date.

Any ideas what is going on?

 

[cpod]

You have to enclose dates with "#":

DoCmd.RunSQL "INSERT INTO Items_Checked_Out (account_number, product_id, due_date) VALUES (" & acctNum & ", " & pID & ",#" & dueDate & "#)"

 

[Rich]

You should not store a calculated value in a table, use a query. What if somebody wants to extend their rental, the only date you should store is the Hire date.

 

[David R]

Rich, what are you talking about? How would a hire date give you the rental due date?

Store one or the other, either the date checked out (Date()), or due date (Date() + 3). There is no need to store both. You can do this on your form directly from the AfterUpdate event of one of your required fields, or a command button. No need to use an update query.

[This message has been edited by David R (edited 04-29-2002).]

 

[Rich]

The Hire Date/DateOfHire how else can I put it, the date due back is calculated from that, like I said if someone wants to extend the length of hire then the Date-due calculated field is extended by the number of days to add to the hire date, if you store the date-due date and want to extend it, how do you know how long the hirer has had the video for? But then, where's the date returned field?

[This message has been edited by Rich (edited 04-29-2002).]

 

[raskew]

Jeff-

What Rich's trying to tell you is absolutely correct! Perhaps the terminology is getting in the way. Try substituting "DateRented" or "RentalDate" for "HireDate", and see if that makes more sense.

Bob

 

[Jeff_NC]

Wow thanks everyone.

I understand what you mean. Either storing the Current Date when checked out or the Due Date would work. I had actually tried it both ways but still resulted in the Date being stored with that crazy value.

What cpod mentioned about putting the #'s around the date may be what I was looking for. I remember now reading somewhere before about that.

However though today I realized that the KISS(Keep It Simple Stupid)

principle really applies to this. Instead of Inserting a date in that manner instead I realized that it is much more simple to just set the default value in that table to:

Date() + 3

Duh!!! I felt like smacking myself in the head about that.

Thanks for the help all. I'm sure I will be back asking more questions in the near future. This is a really nice Access forum.

 

[David R]

Heh, okay Rich, gotcha.

I was not familiar with using "hirer" for "renter", we were actually saying the same thing two ways.