Solved Incorrect Date formats

C

Chief

Registered User.
Local time
Yesterday, 20:45
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
 
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.
 
What Region settings do you have in Windows?
 
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.
 
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.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
www.access-programmers.co.uk
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
 
MajP said:
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.
Click to expand...
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.
 
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)
 
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
Click to expand...
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.
 
MajP said:
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.
Click to expand...
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?
 
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#
 
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.
 
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
 
You must log in or register to reply here.

Similar threads

C
Looking for best practices, Locking dates so they are not changed by code.
Replies
11
Views
1,096
Pat Hartman
P
C
Solved Item cannot be found in the collection corresponding to the request.....
2
Replies
24
Views
2,831
theDBguy
theDBguy
C
Solved Option Group, Sorting Form based on selection
Replies
11
Views
963
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom