UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Advise On Handling Null Dates In Update Query, Access 2016    
 
   
hemant
post Jun 2 2020, 01:52 AM
Post#1



Posts: 89
Joined: 19-February 20
From: Mumbai, India


I have a totals query which finds Min and Max dates for a project. The Min or Max date found by the Totals query can be Null or a Date. Accordingly need to update the table.

Is there a better way of writing the below code? There are around 5 such pieces in the code. I tried writing one UPDATE statement covering all the five dates, but it gave error when any of the date was Null. Hence, I have to check each date and write two separate UPDATE statements - one when the date exists and the other when it is null.

CODE
    ' All the valriables ending with Dt are Variant type as I can assign Null to only Variant
    '... the code above this runs the Totals query. rst2 is the recordset opened using that query.

    If Nz(rst2!MinOfPlanStDt) <> 0 Then
        ProjPlanStDt = rst2!MinOfPlanStDt
        ProjPlanStDt = Format(ProjPlanStDt, "dd-mmm-yyyy")    
                'the above statement is to ensure that correct date is updated in the table. Otherwise the date updated is as per US format.
                'so if the date is 1-Jun-2020, without the above statement the date updated in the table is 6-Jan-2020  

        CurrentDb.Execute "UPDATE ProjList SET ProjList.ProjPlanStDt =#" & ProjPlanStDt & "# " & _
                          "WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"
        If BeingApproved Then
            CurrentDb.Execute "UPDATE ProjList SET ProjList.LastApprPlanStDt =#" & ProjPlanStDt & "# " & _
                              "WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"
        End If
    Else
        ProjPlanStDt = Null
        CurrentDb.Execute "UPDATE ProjList SET ProjList.ProjPlanStDt = Null " & _
                          "WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"
        If BeingApproved Then
            CurrentDb.Execute "UPDATE ProjList SET ProjList.LastApprPlanStDt = Null " & _
                              "WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"
        End If
    End If

Go to the top of the page
 
June7
post Jun 2 2020, 02:40 AM
Post#2



Posts: 1,519
Joined: 25-January 16
From: The Great Land


Can use IIf()
CODE
        CurrentDb.Execute "UPDATE ProjList SET ProjList.ProjPlanStDt =" & IIf(IsNull(ProjPlanStDt), Null, "#" & ProjPlanStDt & "#") & _
                          " WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cheekybuddha
post Jun 2 2020, 02:45 AM
Post#3


UtterAccess Moderator
Posts: 13,007
Joined: 6-December 03
From: Telegraph Hill


Hi,

It's not clear from your description, but are you sure you really need to be storing these calculated dates?

If you are able to calculate them, then just do so in the fly when you need them. This will prevent data becoming stale and out of sync when any of the original inputs change.

Otherwise, please explain in more detail the relationship between table ProjList and your recordset rst2. Also, what is the SQL of rst2?

--------------------


Regards,

David Marten
Go to the top of the page
 
hemant
post Jun 2 2020, 05:35 AM
Post#4



Posts: 89
Joined: 19-February 20
From: Mumbai, India


Thanks June.

@David: The Maker of the Plan and Approver of the plan are two different roles in the company. The Maker can change the dates after the plan was Approved. This is a requirement of the Approver, where he wants to have a log of key approved dates of the plan.
Go to the top of the page
 
cheekybuddha
post Jun 2 2020, 06:22 AM
Post#5


UtterAccess Moderator
Posts: 13,007
Joined: 6-December 03
From: Telegraph Hill


>> This is a requirement of the Approver, where he wants to have a log of key approved dates of the plan. <<

OK!

In which case, I would suggest a slight update to June's suggestion:
CODE
        CurrentDb.Execute "UPDATE ProjList SET ProjList.ProjPlanStDt = " & IIf(IsNull(ProjPlanStDt), "NULL", "#" & ProjPlanStDt & "#") & _
                          " WHERE (((ProjList.ProjID)=" & Me.cmbProjID & "));"


Your SQL statement needs to explicitly say 'NULL' (in the case where ProjPlanStDt is null) - as it was the SQL statement would have a blank and would fail.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
hemant
post Jun 2 2020, 07:35 AM
Post#6



Posts: 89
Joined: 19-February 20
From: Mumbai, India


Thanks. Noted.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 01:54 AM