Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date year is 1899? (Access 2000)

    When inserting a record in VB (with the field format as short date) I am trying to use the value 'date' for the current date, but I get a odd date. Furthermore, if I try to use 'now', it does not work either. How can I get the short date inserted into a record?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date year is 1899? (Access 2000)

    If you get a date in the year 1899, the underlying value is 0 or 1 (The starting point for Windows date is 12/30/1899). The cause depends on the circumstances. Do you have a field called Date? That could cause confusion, because Date is also a data type and a function.

    If you post the (relevant part of the) code that inserts the date, someone may be able to give more specific help.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date year is 1899? (Access 2000)

    I have a field in a table called date, and I want to insert the actual current date:

    Here is a sample that I have:
    SqlInsert2 = "INSERT INTO OrderedProducts (InvoiceID, ProductID, QtyOrdered, SinglePrice," _
    & "ExtendedPrice, DateFilled, IsMixed, BlockColor, BlockLetter)" _
    & "VALUES (" & var_InvoiceID & ", " & var_ProductID & "," & txtbox_Qty & "," & var_Price & ", " _
    & var_ExtendedPrice & ", " & Date & ", false , " & var_Color & "," & var_Letter & ")"

    DoCmd.RunSQL SqlInsert2

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date year is 1899? (Access 2000)

    SQL expects dates to be surrounded with # signs and to be formatted in US date format. Try replacing

    & ", " & Date & ", false , "

    by

    & ", #" & Format(Date, "mm/dd/yyyy") & "#, false, "

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date year is 1899? (Access 2000)

    Assuming the "Date" in your SQL is actually a reference to the Date() function, try this, which forces SQL to recognize the value as a date rather than a number:

    & var_ExtendedPrice & ", #" & Date() & "#, false , " & var_Color & "," & var_Letter & ")"

    This will NOT work properly if your system settings use a short date format other than mm/dd/yy or mm/dd/yyyy, because SQL requires dates in US format. If your regional settings are different then change the SQL like this:

    & var_ExtendedPrice & ", #" & Format(Date(),"mm/dd/yyyy") & "#, false , " & var_Color & "," & var_Letter & ")"
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date year is 1899? (Access 2000)

    Thanks for the helps guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •