Query (1 Viewer)

rainbows · Jun 27, 2018

HI

could you please help me with this . I also need it to give me a result of "late" if the "closed date" is empty

Expr1: IIf([Closed date]>[Completion Date],"Late","ontime")

thanks you

steve

MackMan · Jun 27, 2018

Steve, given the information..

Code:

SELECT [YourTable].[Closed Date], [YourTable].[completion date], IIf([closed date]>[completion date],"Closed",IIf(IsNull([Closed date]),"Late")) AS status
FROM [YourTable];

Always good to NOT to have spaces in Field Names as this can cause complications down the line.

isladogs · Jun 27, 2018

This should work:

Code:

Expr1: IIf(IsNull([Closed Date]) Or [Closed Date]>[Completion Date],"Late","On Time")

rainbows · Jun 27, 2018

hi thank you

these are my result they should all read late it is alos asking me for the completion date

Completion Date Closed date Expr2
07-Jun-18 27-Jun-18 On Time
27-Jun-18 Late
04-Apr-18 27-Jun-18 On Time
29-Jun-18 Late

this in the sql if it helps

SELECT Issues.ARNO, Issues.[Assigned To], IIf([Status]="closed",Now()) AS [Closed date], Issues.[Completion Date], Issues.Status, IIf([Closed date]>[Completion Date],"Late","ontime") AS Expr1, IIf([Expr1]="late",1,0) AS late, IIf(IsNull([Closed Date]) Or [Closed Date]>[Completion Date],"Late","On Time") AS Expr2
FROM Issues
WHERE (((Issues.[Completion Date]) Is Not Null));

steve

isladogs · Jun 27, 2018

Have you tried the expression from my first reply?

rainbows · Jun 27, 2018

hi . yes sorry I posted the wrong one before

it still prompts me for the completion date and I think the 3rd one should be late
and I have just thought the ones that have no closed date must be late as from today

Completion Date Closed date late Expr2
07-Jun-18 27-Jun-18 1 On Time
26-Jun-18 0 Late
30-Jun-18 27-Jun-18 0 On Time
26-Jun-18 0 Late

SELECT Issues.ARNO, Issues.[Assigned To], IIf([Status]="closed",Now()) AS [Closed date], Issues.[Completion Date], Issues.Status, IIf([Closed date]>[Completion Date],"Late","ontime") AS Expr1, IIf([Expr1]="late",1,0) AS late, IIf(IsNull([Closed Date]) Or [Closed Date]>[Completion Date],"Late","On Time") AS Expr2
FROM Issues
WHERE (((Issues.[Completion Date]) Is Not Null));

again thanks for help

rainbows · Jun 27, 2018

hi Ridders

the completion date was the issue , when I retyped it in it was working

but I am wrong in the question asking as it will be late if the closed date is null and the completion date is at now() or before

Completion Date Closed date late Expr2
30-Jun-18 0 Late

this would still be on time not late
thanks for your help

isladogs · Jun 27, 2018

Slightly confused by your last two posts. Is it now correct or not?
If not, please state what the results should be for several examples so the expression can be modified

rainbows · Jun 28, 2018

Hi RIDDERS

Yes getting confused myself the more I tried to get it to work

as you can see if the completion date and closed date are completed then the expression is correct
if the closed date in blank it can be wrong
because the completed date could be in the future as you can see on the 4th line therefore this line should be still on time but if the completed date has passed but the closed date still blank it would be late

below the second line is correct but the 4th line is wrong until we reach the date of the 30th june

Completion Date Closed date late Expr2
09-May-18 28-Jun-18 1 Late
06-Jun-18 1 Late
14-Jul-18 28-Jun-18 0 On Time
30-Jun-18 1 Late

SELECT Issues.ARNO, Issues.[Assigned To], IIf([Status]="closed",Now()) AS [Closed date], Issues.[Completion Date], Issues.Status, IIf([Expr2]="late",1,0) AS late, IIf(IsNull([Closed Date]) Or [Closed Date]>[Completion Date],"Late","On Time") AS Expr2
FROM Issues
WHERE (((Issues.[Completion Date]) Is Not Null));

hope this helps
thanks for you help

steve

Pat Hartman · Jun 28, 2018

rainbows,
Functions are NOT interchangeable. Each is designed to do something specific and different from all other functions.

Now() = current date + time of day
Date() = current date

When you add a time element to a date, you will alter the result of the comparison. That means that 6/27/18 11:28 will be > 6/27/18 00:00 - Is that what you really want? I doubt it.Is [Closed date] a table column? If it is, you can;t calculate a value for it in a select query.

rainbows · Jun 28, 2018

HI,
closed date is an expression that gives that date when the "status" field in set to closed in a table and you are correct I don't need the time just the date() function adding some how to the formula

thanks

steve

rainbows · Jun 28, 2018

hi

I think this takes care of it unless anyone can see a problem with it

late: IIf(Date()>[Completion Date],"late" ,"ontime" )

steve

isladogs · Jun 28, 2018

Code:

late: IIf(Date()>[Completion Date],"late" ,"ontime" )

Definitely a problem with that
1. If completion date is null, result is on time (WRONG). Should be null????
2. If completion date is today, result is on time (CORRECT)
BUT if query run again tomorrow, result is late (WRONG)

I think you need to review what you want ...again!

rainbows · Jun 28, 2018

hi Ridders

Thanks for the response

completion date in mandatory in the form so it has to be completed. this is the date the job has to be completed. but the closed date is when it really finished

if the completion date is expected to be completed on the 29th but the closed date is null then it will be on time

tomorrow when I run it and if the closed date is still null then it is still ontime.

Completion Date Closed date late1
23-May-18 28-Jun-18 Late
10-Apr-18 Late
06-Jun-18 28-Jun-18 Late
29-Jun-18 ontime

when I change the completion date to the 27th on line 4 it then changes to late

Completion Date Closed date late1
23-May-18 28-Jun-18 Late
10-Apr-18 Late
06-Jun-18 28-Jun-18 Late
27-Jun-18 Late

do you still think this wrong ?

thanks for all your help with this

steve

isladogs · Jun 28, 2018

Yes I still think this is wrong.

I originally thought Closed Date was a table field but it appears to just be an expression in your query based on status field so the value will change ....
If status -= 'closed', Closed Date = current date
If closed date > completion date then expr2 is late
BUT because closed date isn't fixed for each record, Expr2 will also change

I've copied your 4 records & your query into the attached database and added a new completed record for today's date 28/06/2018

This is what I think the results should be:
Records 1 & 3 should be on time if they were completed before 28/06/2018
Record 2 is clearly late.
Record 4 is currently on time but will be late if its not closed on 29/06
Record 5 is designed to illiustrate my previous point. Its closed so its on time but using your query it will be shown as late tomorrow

In my opinion, Closed Date (preferably with no space) needs to be a table field.
When the task is completed, the current date should be assigned to it.
The status field can be determined from that if it is needed.
The Expr2 field in my view should have 3 options: Later/On Time and Open (or Null)
The Expr2 field will be set when the task is closed and the date stored. It should not change when thew query is run on subsequent dates

The whole thing might be clearer if you rename fieds:
Completed Date => DueDate
Closed date => CompletedDate

Hope that makes sense
:

rainbows · Jun 29, 2018

hi ridders

you are correct it is wrong .

can you please advise what formular i should use now to solve my problem

i cn make the closed date an input field . i was just asked if it could have been auto when i changed the status field to closed

Expr2: IIf(IsNull([Closed Date]),"Open",IIf([Closed date]>[Completion Date],"late","ontime"))

is it possible to count the open , the late and ontime in the report somehow

thanks
steve

isladogs · Jun 29, 2018

See attached for updated version.
NOTE: I've replaced spaces in field names with underscores

Changes made:
1. Completion Date renamed as Due_Date,
2. Closed Date renamed as Completion_Date
3. Added form for data entry
3. Status field now updated automatically on form. Closed when Completion_Date populated, otherwise Open
4. On_Time field now has this formula:
On_Time: IIf(IsNull([Due_Date]),"",IIf([Completion_Date]>[Due_Date] Or (([Due_Date]<Date() And IsNull([Completion_Date]))),"Late","On Time"))
This means it is
a) blank until Due_Date is populated
b) On Time if not completed and before Due_Date
c) On Time if completed on or before Due Date
d) Late if completed after Due Date or not completed by Due Date
5. Added conditional formatting to Status & On_Time fields
6. Added a few more records to illustrate outcomes

NOTE: The On_Time query expression is now quite complex. TBH I would prefer to use a function to derive this but as you wanted a query, I've left it as a query expression

HTH

rainbows · Jun 30, 2018

Hi Ridders,

thank you for all your help ,
I have changed my forms, queries in line with what you suggested . Much better and all seems to be working great
I will not put spaces again. and have learnt a lot from you

much appreciated

steve

isladogs · Jun 30, 2018

You're welcome.
Good luck with the rest of your project

Query (1 Viewer)

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

Super Moderator

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Registered User.

MVP / VIP

Attachments

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread