Date turns into 12/30/1899

[torok]

Date turns into 12/30/1899 ??

I've got a form where, among other things, users enter a date in a text box. The box is formatted to MM/DD/YY. In my VBA code (Access2k) I've got

Dim my_date As Date
my_date = SubFrm_invoice_2!txt_invoice_date.Value

Later on I go on to use an SQL statement (example follows) to stick that date into my table, but first I pop up a msgbox to show me that my_date has the correct value, which it does.

strSQL = "INSERT INTO tbl_invoices (invoice_id, invoice_date) VALUES (" & my_number & ", " & my_date & ");"

No matter what's in that textbox, though, or what the value of my_date is, it seems to put 12/30/1899 in the damn table. Anyone encounter this before?

Thanks!

 

[Rich]

You have to use the US date format when using strings, but I wonder why are you storing data in this way?

 

[torok]

Thanks Rich... I don't quite understand what you mean by "US Date Format" though - I thought that using "Short Date" as the format for that text field would work just fine. I'm now quite confused.

I'm storing it this way because I have a series of subforms asking the user for data, which I put into variables in the main form's VBA code. Once the user's gone through all the subforms, A button appears that lets her save the data into a record (using an insert statement or two), or cancel the operation.

This program shouldn't be all that complicated but it's gone through 4 redesigns, and I just want to get this working, get the reports done, and get it out the door I have much to learn, and I appreciate the help. (Still a student, this is a summer job)

Thanks....

 

[Rich]

Have you defined your variables in a function ie
Option Compare Database
Option Explicit

Public MyName As String
Public MyVal As Integer
Public MyDate As Date

 

[torok]

Well, I tried that, and I got the same results - it screws up the date. There are probably better ways to do this, and definitely better database designs (Every time I redesign it, I learn something, but you have to get version 1 out the door someday...) Here's the code:

Function create_invoice()
On Error GoTo myError

ReDim my_orders(50) As Integer
Dim my_number As Integer
Dim my_po As Variant
Dim my_date As Date
Dim my_client As Integer
Dim imperial_bool As Boolean
Dim counter As Integer
        
    my_number = SubFrm_invoice_1!txt_invoice_num.Value
    my_po = SubFrm_invoice_2!txt_invoice_po.Value
    my_date = SubFrm_invoice_2!txt_invoice_date.Value
    my_client = SubFrm_invoice_2!ClientNames.Value
    imperial_bool = SubFrm_invoice_2!chk_imperial.Value
    my_bool = imperial_bool 'Used for form 3
    Dim itm As Variant
    Dim strSQL As String
    DoCmd.SetWarnings False
    'Make the invoice record
    strSQL = "INSERT INTO tbl_invoices (invoice_id, invoice_date, invoice_po, imperial_invoice_bool) VALUES (" & my_number & ", " & my_date & ", '" & my_po & "', " & imperial_bool & ");"
    DoCmd.RunSQL strSQL
    'Check selected items, add them to the invoice
    For Each itm In SubFrm_invoice_2!List_not_invoiced.ItemsSelected
        'Set the Invoice# for that item to the current invoice
        strSQL = "UPDATE tbl_orders SET invoice_id = " & my_number & " WHERE order_id = " & SubFrm_invoice_2!List_not_invoiced.ItemData(itm) & ";"
        DoCmd.RunSQL strSQL
    Next itm
    'Run the update query that will update the Invoice total, gst, and pst. We're storing calculated values for a good reason.
    DoCmd.OpenQuery "QryUpdateInvoiceAmount"
    DoCmd.SetWarnings True
   
Exit_create_invoice:
    Exit Function
   
myError:
    MsgBox (" Error: " & Err.Description)
    Resume Exit_create_invoice
End Function

Thanks again!

 

[Pat Hartman]

I don't see the error in the code but the date you're seeing is a result of storing 0 in a date field. Remember, dates are NOT stored as text strings. They are stored as the integer portion of a double precision number with time being the decimal portion. Positive numbers represent the number of days AFTER Dec 30, 1899 and negative numbers represent the number of days prior to that date. So -1 = Dec 29, 1899 and +1 = Jan 1, 1900.

 

[The_Doc_Man]

Torok, Pat gave you part of the answer. I will fill in a blank or two for you.

The problem is, as Pat suggested, storing a 0 in a date field. But how does the date get to 0?

As a new Access user, you probably aren't used to thinking along these lines yet, so the question would be, where does the 0 come from? Here is how to find out what you are doing.

In the code segment you created, put a breakpoint just before you attempt to run your SQL INSERT query.

When the breakpoint triggers, open the Local Debug Window to examine the values visible to you at that point in your code. Visit each of the objects named in that code to see its value. MAKE SURE YOU NOTE THE DATA TYPE ATTRIBUTED TO THE ITEM.

Something that comes to mind is that if you have dates originally entered in a text format, you might need to enclose the text in # signs to force it to convert text to a date field. Or use the CDat() function.

I always get confused about that and have to tinker with date code, so treat this as a mild suggestion for an experiment, not as an absolute pronouncement. If you try to store data of the wrong type in a field, and particularly if warnings are set to False, you might not be notified of a format conversion error. But an FCE would, indeed, result in a zero/null value for a given field.

 

[torok]

Thanks very much, to both of you - your explanations were quite good. In the end, I had to store my_date as a string and insert the # marks before and after it. If I store it as a date, the debug window shows that it has the # marks there already, but Access apparently ignores them. Very strange. But it's done!

Where would I be without you guys? Besides a week or two behind schedule....

 

[David R]

Store it as a date anyway..

Now that you've got it working to store, period, it should be easy enough to convert it to a date. Otherwise you're going to make yourself crazy with trying to use string dates in otherwise simple Access functions, like getting chronological lists.

 

[danielleevandenbosch]

I was running into the same issue. I wanted to set it to a default value of 1/1/2014 and kept getting 12/30/1899. I tossed # signs around the date and it returned the value I desired.
like this #1/1/2014#

 

[Vergy39]

I am having the same issue. I have attached an image of the code with a breakpoint and it shows a date being stored. But when it is inserted into the table, it appears as 12/30/1899. I am not sure where to put the # signs. The date is entered into an unbound form, then moved to the table via the insert query. Any assistance is greatly appreciated.

Thanks
David V

 

[gemma-the-husky]

I was running into the same issue. I wanted to set it to a default value of 1/1/2014 and kept getting 12/30/1899. I tossed # signs around the date and it returned the value I desired.
like this #1/1/2014#

I presume 1/1/2014 gets processed as 1÷1÷2014, which is almost zero. Almost zero is 12/30/1899 plus a few seconds, but showing it as short date will mask the seconds.