Results 1 to 4 of 4
  1. #1
    todmac is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    20

    Date changes format when inserted into table.

    Hi Everyone,
    I have a database I built dates which has a date entry problem. Dates are entered on the entry form (named date1) as dd/mm/yyyy, and inserted using the code below. However from 1st of the month to the 12 the dates are reversed to mm/dd/yyyy all information starting on the 13 of the month is fine.
    Can anyone explain why this happens, and how to fix it?
    Many thanks.



    Code:
     Option Compare Database
    Option Explicit
    Global Currentuser As String
    
    Public Sub New_Measurement(WP)
    Dim Strsql As String
    Dim measurement As Integer
    
    measurement = DLookup("[Measurement]", "Brake Lining", "[Unit Type]= '" & Forms!addreplacements.unittype & "' AND [Wheel Position]=  '" & WP & "'  AND [New]=True")
    Strsql = "Insert into MAIN (unitnumber, Date1 ,kilometers, username, DateEntered, Item, WheelPosition, measurement) Values (" & Forms!addreplacements.Unit.Value & " , #" & (Forms!addreplacements.Done.Value) & "# ,  " & Forms!addreplacements.KMS.Value & " ,  '" & Currentuser1 & "' , #" & Now() & "#,'" & Forms!addreplacements.Thing.Value & "' , '" & WP & "', " & measurement & " )"
    CurrentDb.Execute (Strsql)
    End Sub
    
    Public Sub New_Tire_Measurement(WP)
    Dim Strsql As String
    Dim measurement As Integer
    measurement = DLookup("[Measurement]", "Tread Depth", "[Unit Type]= '" & Forms!addreplacements.unittype & "' and [Wheel Position]= '" & WP & "' and [New]=True")
    Strsql = "Insert into MAIN (unitnumber, Date1 ,kilometers, username, DateEntered,Item, WheelPosition, measurement) Values (" & Forms!addreplacements.Unit.Value & " , #" & (Forms!addreplacements.Done.Value) & "# ,  " & Forms!addreplacements.KMS.Value & " , '" & Currentuser1 & "', #" & Now() & "# ,'" & Forms!addreplacements.Thing.Value & "' , '" & WP & "', " & measurement & " )"
    CurrentDb.Execute (Strsql)
    End Sub
    
    Public Sub Add_Measurement(Item, WP, measurement)
    Dim Strsql As String
    Strsql = "insert into MAIN (Unitnumber,Date1, Kilometers, username, DateEntered, item, WheelPosition,measurement)  values (" & Forms!AddMeasurement2.Unit.Value & ", #" & Forms!AddMeasurement2.Done.Value & " #, " & Forms!AddMeasurement2.KMS.Value & ", '" & Currentuser1 & "', #" & Now() & " #, '" & Item & "', '" & WP & "' ," & measurement & ")"
    CurrentDb.Execute (Strsql)
    End Sub
    
    Public Sub Add_Measurement_update(Item, WP, measurement)
    Dim Strsql As String
    Strsql = "Insert into MAIN (UnitNumber,format,Date1,Kilometers,Username,DateEntered,Item,WheelPosition,Measurement) Values ( " & Forms!New_Measurement.Unit_Number.Value & ", #" & Forms!New_Measurement.Date_Done.Value & "#, " & Forms!New_Measurement.KMS.Value & ", '" & Currentuser1 & "',#" & Now() & "#, '" & Item & "','" & WP & "', " & measurement & ")"
    CurrentDb.Execute (Strsql)
    End Sub

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    MS Access uses USA date format MM/DD/YYYY for entry by default. Dates are stored as a number.
    See more about Dates in this Allen Browne article.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It sounds like the dates are not "reversed" - they are actually wrong in the database. Access can be miserably picky when it comes to working with dates.

    If you are not in the US, what is the date format setting in the regional settings of windows? By default, Access will use that for displaying dates, but it will not use it when getting data in an Insert statement and dates delimited by # characters - they have to be mm/dd/yyyy (unless the day is > 12, in which case Access knows what to do with it anyway).

    Try using format(Forms!New_Measurement.Date_Done.Value,"mm/dd/yyyy") for dates in your Insert statements. If the dates are still wrong, then the form is not saving them correctly.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    dates are a special type of number which can be formatted in many ways - the mm/dd/yyyy format is the US format and the default for access.

    Dates are stored like decimal numbers, the bit before the dp is the date and is a number which counts the number of days up from way back when (31/12/1899) so today is 42559. The bit after the dp relates to the time and is expressed as a decimal calculated time now in seconds/number of seconds in a day (86400) so now is 17:41 - the decimal is 0.7373611111

    So that is how it is stored, what it looks like is up to you - through formatting. Note that format properties of a field, a column in a query or a control in a form or report are not passed up the line except at the time of creation. i.e. Subsequent changes to the format of a field will not automatically change the format of control which already has that field as a controlsource. Similarly, fixing the format of a column in a query will not be reflected in a control based on that query once that control has been created.


    so for example

    ...#" & (Forms!addreplacements.Done.Value) & "#,....

    The #'s are telling access to convert the text between them to a date.

    What is happening in your case is that you are effectively giving access a date string from a textbox on your form which is being interpreted as mm/dd/yyyy. When the date is 'illegal' i.e. there is no month 13 for example, the system is smart enough to correct itself, but not to say 'this date is dd/mm/yyyy format so I'll treat the whole column that way'. To get round this you need to convert it to the correct format using the format function ie..

    ...#" & format(Forms!addreplacements.Done, "mm/dd/yyyy") & "#,....

    Note you do not need the .value - that is the default and all you are doing is creating extra work for yourself and increasing the risk of errors.

    Alternatively you can convert your date string using the cdate function to a 'proper' date value and you don't then need them in your code


    ..." & CDate(Forms!addreplacements.Done) & ",....

    CDate uses system settings to determine how to treat the string

    You also do not need to have the # around the Now() function - it returns a date/time value anyway. It won't do any harm to leave it. If it is just the date you want, use the Date() function

    Finally, just to round things off, text such as '1 Feb 2016' will be interpreted correctly

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-16-2015, 07:04 PM
  2. Replies: 9
    Last Post: 08-07-2014, 06:17 PM
  3. UK Date Format In Linked Table ("dd/mm/yyyy")
    By smoothlarryhughes in forum Queries
    Replies: 1
    Last Post: 06-18-2013, 08:29 PM
  4. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  5. text box expression inserted to table
    By normie in forum Access
    Replies: 3
    Last Post: 03-13-2012, 04:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums