Update Query to record working days by logical test (1 Viewer)

[mba_110]

Hi

I am trying query to update my table value field name is [WorkingDays] and [UnpaidLeave] let's go one by one.

First and the most important is table name i have tblStaffLeaves on which i need these to be updated.

Fields that i am going to use in this event are as follows

1. "tblStaffLeaves", [LeaveID],[LeaveType],[ManagerApproval],[StartDate],[EndDate],[WorkingDays],[UnpaidLeave]

2. "tblCalendar", [NWD] it is net working day for each day in calendar table

Now we need to count this net working days in query based on startDate & EndDate in tblStaff Leaves if ManagerApproval is "Yes" otherwise calculate the StartDate & EndDate and store the result in UpaidLeave.


'First we need to see whether [StartDate] is empty or not if empty than [WorkingDays] and as well as [UnpaidLeave] should be zero 0.
and same for either fields is empty whether startDate or EndDate anyone of these.

'Secondly we need to see if [StartDate] and [EndDate] is selected than we need to check [ManagerApproval] if its "No" than value should be stored in [UnpaidLeave] it should sum the values between startdate & endDate based on tblCalender's [NWD] field value.

for example startDate is 01/01/2018 and EndDate is 10/01/2018 then function to see in tblCalender [CalendarDate] row for same date to Sum [NWD] values between these dates

[CalendarDate] [NWD]
01/01/2018 0
02/01/2018 1
03/01/2018 1
04/01/2018 1
05/01/2018 1
06/01/2018 1
07/01/2018 0
08/01/2018 0
09/01/2018 1
10/01/2018 1

The result should be 7 in either [WorkingDays] or [UnpaidLeave] if [ManagerApproval] is "No" in tblStaffLeaves.

below is my code so for and i believe that its required Update Query.

in Criteria of WorkingDays query column

IIf([tblStaffLeaves]![StartDate] =0 and [tblStaffLeaves]![EndDate] =0, [tblStaffLeaves]![WorkingDays] =0,Sum( [tblCalendarTable]![NWD] )

in Criteria of UnpaidLeaveDays Query Column

IIf( [tblStaffLeaves]![ManagerApproval] ="No", [tblStaffLeaves]![EndDate] - [tblStaffLeaves]![StartDate] * [tblCalendarTable]![NWD] , [tblStaffLeaves]![UnpaidLeave] =0)

I hope i make things clear to understand.

 

[plog]

I am trying query to update my table value

This is not how databases work, you don't store data you can calculate. Instead of saving this data you should use a SELECT query to calculate it and then reference that query whenever you need the calculated value.


As for your 1st line of code issue, dates never have a 0 value. They can be null, and to test for that you would use the IsNull function (https://www.techonthenet.com/access/functions/advanced/isnull.php).

For the second code issue, you've forgotten about the order of mathematical operaitions:

Multiplication->Division->Addition->Subtraction

You are multiplying StartDate by NWD (which seems odd, because what's 17 * 11/11/2018?). Then you are subtracting that from EndDate. When you mix multiplication/division with addition/subtraction you need to use parenthesis to make sure they calculate in the order you want.