Solved Incorrect Date formats (1 Viewer)

Chief

Registered User.
Local time
Today, 02:05
Joined
Feb 22, 2012
Messages
156
Hello,
Having some random issues with dates.
I have removed any date formatting in the actual table for dates.
I have set the form properties to show Med Date format.
Where I am we use DD/MM/YYYY as opposed to the American format.
Some dates are coming thru correct as per sample below: (Look at "Target Dates, IFA and Sample Subm" these dates are generated from the Job Confirmed Date at top)

1635893041576.png


The amount of days are coming from a table.
If I look at another record the dates are are correct but around the wrong way, as in 8/10/21 but reading 10/8/21. (See below)

1635893249169.png


If I look into the immediate window the dates are formatting as I would like

1635893530286.png


Here is the code: (where can I add a format date function to these dates?)
Code:
Private Sub BtnUpdate_Click()
    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dDate As Date
    Dim iLoop As Integer, iDays As Integer, iAcct As Integer

    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb

    CboJobTypeID.SetFocus

    If CboJobTypeID.Text = "" And Nz(Me.Lead_Date, "") = "" Then
        Exit Sub
    End If

    If CboJobTypeID.Text = "" Then
        MsgBox "Please select a Job Type from the list", vbInformation, "Select Job Type"
        Exit Sub
    End If

    If (Lead_Date.Value & "" = "") And (ChkIFA_App = True) Then
        Lead_Date.Value = Now()
    End If

    Me.Refresh
    
    iDays = 0

    If ChkIFA_App = True Then
        sql = "UPDATE JobInfoT SET IFA_Due = #" & AddDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due"), 2) & _
        "#,IFC_Due = #" & AddDays(GetDays("IFC_Due"), 1) & "#,SetOutDue = #" & AddDays(GetDays("SetOutDue"), 1) & _
        "#,CarcassCut_Due = #" & AddDays(GetDays("CarcassCut_Due"), 1) & "#,CarcassEdge_Due = #" & AddDays(GetDays("CarcassEdge_Due"), 1) & _
        "#,PFBCut_Due = #" & AddDays(GetDays("PFBCut_Due"), 1) & "#,PFBEdge_Due = #" & AddDays(GetDays("PFBEdge_Due"), 1) & _
        "#,WhiteSatinCut_Due = #" & AddDays(GetDays("WhiteSatinCut_Due"), 1) & "#,TwoPakPartsOut_Due = #" & AddDays(GetDays("TwoPakPartsOut_Due"), 1) & _
        "#,PickHW_Due = #" & AddDays(GetDays("PickHW_Due"), 1) & "#,HingeDrill_Due = #" & AddDays(GetDays("HingeDrill_Due"), 1) & _
        "#,MachineShop_Due = #" & AddDays(GetDays("MachineShop_Due"), 1) & "#,DrawerAss_Due = #" & AddDays(GetDays("DrawerAss_Due"), 1) & _
        "#,AssemblyDue = #" & AddDays(GetDays("AssemblyDue"), 1) & "#,TwoPakUnderC_Due = #" & AddDays(GetDays("TwoPakUnderC_Due"), 1) & _
        "#,TwoPakPaint_Due = #" & AddDays(GetDays("TwoPakPaint_Due"), 1) & "#,WrapQC_Due = #" & AddDays(GetDays("WrapQC_Due"), 1) & _
        "#,Delivery_Due = #" & AddDays(GetDays("Delivery_Due"), 1) & "# WHERE JobID = " & Me.JobID
    Else
        sql = "UPDATE JobInfoT SET IFA_Due = #" & AddDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due"), 2) & _
        "#,IFC_Due = """",SetOutDue = """",CarcassCut_Due = """",CarcassEdge_Due = """",PFBCut_Due = """",PFBEdge_Due = """",WhiteSatinCut_Due = """",TwoPakPartsOut_Due = """", PickHW_Due = """",HingeDrill_Due = """",MachineShop_Due = """",DrawerAss_Due = """",AssemblyDue = """",TwoPakUnderC_Due = """",TwoPakPaint_Due = """",WrapQC_Due = """",Delivery_Due = """" WHERE JobID = " & Me.JobID
    
    End If
    
    db.Execute sql

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh

End Sub

Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:05
Joined
Sep 21, 2011
Messages
14,037
Dates need to be in either mm/dd/yyyy or yyyy-mm-dd format? Perhaps yyyy/mm/dd as well, but I just use the first version.
 

Cronk

Registered User.
Local time
Today, 20:05
Joined
Jul 4, 2013
Messages
2,770
What Region settings do you have in Windows?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
I can not wrap my head around these functions
SET IFA_Due = #" & AddDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due"), 2)

I would think this resolves to something like
SET IFA Due = #1/1/2021#,SampleSubm_Due = #1/2/2021#
Which does not look like anything that could possibly resolve to a working value.
Am I missing something? Because if you are getting wrong dates, that is a lot farther then I would have thought you could get.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
Either way, this seems like a real PITA to do. It seems it would be way easier through a recordset update or a parameterized query def.
I would wrap every date in the CSQL function and then get rid of the delimiters.
or a similar
Code:
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
 

Chief

Registered User.
Local time
Today, 02:05
Joined
Feb 22, 2012
Messages
156
I can not wrap my head around these functions
SET IFA_Due = #" & AddDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due"), 2)

I would think this resolves to something like
SET IFA Due = #1/1/2021#,SampleSubm_Due = #1/2/2021#
Which does not look like anything that could possibly resolve to a working value.
Am I missing something? Because if you are getting wrong dates, that is a lot farther then I would have thought you could get.
Hello,
There are other functions (AddDays, GetDays) that look at dates to see what dates are working days, weekends, holidays. Adds or minus a day depending.
 

Chief

Registered User.
Local time
Today, 02:05
Joined
Feb 22, 2012
Messages
156
Is there a way to include (wrap around) each of these functions for format date? Would that work?

I've added this code in the AddDays and also the GetDays function. But doesn't solve problem.

Code:
AddDays = Day(dtdate) & "/" & Month(dtdate) & "/" & Year(dtdate)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
here are other functions (AddDays, GetDays) that look at dates to see what dates are working days, weekends, holidays. Adds or minus a day depending
I think I get that part. I do not understand the concatenation

Can you run the code and but a debug.print SQL so we can see how that string resolves. I do not get how that can be a viable string.
 

Chief

Registered User.
Local time
Today, 02:05
Joined
Feb 22, 2012
Messages
156
I think I get that part. I do not understand the concatenation

Can you run the code and but a debug.print SQL so we can see how that string resolves. I do not get how that can be a viable string.
sure

UPDATE JobInfoT SET IFA_Due = #8/10/2021#,SampleSubm_Due = #8/10/2021#,IFC_Due = #8/11/2021#,SetOutDue = #9/11/2021#,CarcassCut_Due = #19/11/2021#,CarcassEdge_Due = #19/11/2021#,PFBCut_Due = #19/11/2021#,PFBEdge_Due = #19/11/2021#,WhiteSatinCut_Due = #10/11/2021#,TwoPakPartsOut_Due = #11/11/2021#,PickHW_Due = #23/11/2021#,HingeDrill_Due = #1/12/2021#,MachineShop_Due = #22/11/2021#,DrawerAss_Due = #23/11/2021#,AssemblyDue = #26/11/2021#,TwoPakUnderC_Due = #18/11/2021#,TwoPakPaint_Due = #23/11/2021#,WrapQC_Due = #29/11/2021#,Delivery_Due = #30/11/2021# WHERE JobID = 318

Here is a snapshot of form showing the dates are all over the place. (back to front)

1635900275776.png

here is the table information where "Days" are coming from.

1635900384073.png


Does that help?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
So that looks like it resolves correct. I get it know. But the dates have to be in MM/DD/YYYY or another unambiguous format. Regardless of location

If your function are already making strings then
adddays = Month(dtdate) & "/" & Day(dtdate) & "/" & Year(dtdate)
But easier is my Csql function
Csql(dtDate) returns
#MM/DD/YYYY#
 

Chief

Registered User.
Local time
Today, 02:05
Joined
Feb 22, 2012
Messages
156
Thanks mate,
I have added the following code and I think it is all working :)

"AddDays"
Code:
            AddDays = Month(dtdate) & "/" & Day(dtdate) & "/" & Year(dtdate)

"AddOrderDays" (another function)
Code:
            AddOrderDays = Month(dtdate) & "/" & Day(dtdate) & "/" & Year(dtdate)

"AddOrderDaysReq" (another Function)
Code:
    If Day(dtdate) > 12 Then
        AddOrderDaysReq = Month(dtdate) & "/" & Day(dtdate) & "/" & Year(dtdate)
    Else
        AddOrderDaysReq = Day(dtdate) & "/" & Month(dtdate) & "/" & Year(dtdate)
    End If

I think we are good to go...

Thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:05
Joined
Feb 19, 2002
Messages
42,970
I don't know how that update query could have helped you. When your use STRING dates in SQL, they MUST be in ymd order or something unambiguous like yyyy/mm/dd. Therefore #8/10/2021# is Aug 10th, Not Oct 8th.

Are you sure that the date fields in question are defined as date data types? Because even if I added data to your database with my US formats, it should still be correct with your Australian format BECAUSE - date data types do NOT store dates as strings. Dates are stored as double precision numbers. Access (Jet/ACE) use an origin date of Dec 30, 1899. That is date zero. 12/31/1899 = 2 and 12/29/1899 = -1. The decimal portion is the elapsed time since midnight so .0 = midnight, .5 = 6 AM, and .75 = 6 PM
 

Users who are viewing this thread

Top Bottom