Flagging up dates

[ChrisSedgwick]

Hi all,

I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.

For example,

10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.

Could you recommend a way in which I can do this?

Regards,
Chris.

 

[CJ_London]

either by formatting the orders in bold, a different colour or under their own heading/group.

Whichever flagging method you decide, the basic formula would be

<dateAdd("w",4,Date())

So if you wanted a separate column it would be

Prioritise: orderdeliverydate<dateAdd("w",4,Date())

if you wanted to use conditional formatting, for the orderdeliverydate control you would use

FieldValueIs.....lessthan....dateAdd("w",4,Date())

and then set the format to bold, or backcolour red or whatever

 

[ChrisSedgwick]

Hi,

Thanks for your reply. I've realised that it's not actually a report that I want the flagging up used on. Its a query that's displayed within a form as a datasheet (I've attached a snapshot).

Would the formula you described in your response still be used if the query? If you see from the screenshot, I want all the records that are within 4 weeks of the input date to be flagged up in some way.

So the query shows various fields, however some of the important ones that will be used are Job No, Allocated Date, Input Date, Week No.

I would hope that the query works by taking the jobs/records where the week number = 0 (these are jobs that are not booked in). I then want the query to 'flag up' jobs where the given allocated date is within 4 weeks of the input date.

Can this be done?

 

[CJ_London]

You could have another column in your query - something like

Urgent:iif(allocateddate<dateAdd("w",4,Date()),"Yes","No")

 

[CJ_London]

or if you want to use conditional formatting then in the Job No, Allocated Date, Input Date, Week No controls you would put

Expression Is....[allocated date]<dateAdd("w",4,Date()),"Yes","No")

then set background etc to say red

Note, you can select all these controls at the same time before clicking on the conditional formatting button so you only have to enter the above once

 

[CJ_London]

or perhaps I have misunderstood and you want to filter the query?

If so then set criteria for weekno to be and orderdeliverydate to be <dateAdd("w",4,Date())

 

[ChrisSedgwick]

Hi,

Thanks again for the support, I'm half way there to achieving the desired result.

What I'm now looking to do, and I'm not entirely sure whether this would need a filter of not within the query. I'm only interested in Jobs that aren't booked in. The way that we indicate this is if the jobs have a 'Production week no' attached to them, this indicates that they are booked in.

I want to format/filter jobs that do not have a week no attached to them, or show a '0' in the 'Production week no', so either blank or '0'.

These are the records that I need to flag up as such. So something along the lines of...

If Production week no = blank or 0 and Allocated Date is within 4 weeks of Job Inputted Date then show as Urgent.

All the above fields are already included within the query that displays on the form, so I'm sure it would just be a case of adding a new field and using an expression or code of some sort. This is where I'm hopeful that you can offer some support on what expression I would need to use. I've given an example below.

Production Wk No Allocated Date Job Inputted Date Urgent
1 05/03/2015 27/02/2015 No
3 20/03/2015 27/02/2015 No
0 05/03/2015 27/02/2015 Yes
05/03/2015 27/02/2015 Yes

Above is how I'm hoping the query will work.

Thanks,
Chris

 

[ChrisSedgwick]

What about adding the Urgent field using the formula you gave and then use another query to pick out the fields that are urgent and where the Allocated Date is within 4 weeks of the Job Inputted Date?

Would that work?

 

[CJ_London]

Would that work?

Yes, I've given you all the solutions - try them

 

[ChrisSedgwick]

Does this expression look correct to have the ability to show record as urgent if the Allocated Date is greater than 4 week from the system date but less than 5 week. This will ensure Urgent is displayed next to all records that are exactly within 4 weeks

Urgent: IIf([Allocated Date]>DateAdd("w",4,Date())And<DateAdd("w",5,Date()),"Yes","No")

 

[CJ_London]

You seem to be changing the requirement each time - before urgent was when the allocateddate was within the next 4 weeks, now you are saying greater than 4 weeks and less than 5 weeks.

You really need to just try things rather than spend hours wondering about which way is better - try it and then decide

with regards your latest requirement

Urgent: IIf([Allocated Date]>DateAdd("w",4,Date()) And [Allocated Date]<DateAdd("w",5,Date()),"Yes","No")

 

[ChrisSedgwick]

before urgent was when the allocateddate was within the next 4 weeks, now you are saying greater than 4 weeks and less than 5 weeks.

This is because I only want to view records where they are exactly within 4 week, I'm not interested in records that are in the 5th week onwards (until they fall into the 4th week).

As mentioned above, the formula worked but brought all records that we're within 4 weeks onwards.

I know I'm experimenting as I'm going along, but until I see how they run, I'm not 100% certain it's correct. I hope you can bare with me.

Thanks,
Chris,

 

[CJ_London]

OK - but my point is, rather than saying 'does this look right', try it, if you get an error try to fix it and if you still can't fix it, come back with what you've tried and what error you get - or if it works but provides the wrong result, the result you get and the result you want.

Generally, I can tell you whether the code is syntactically correct, but not whether it is what you want - particularly as you keep moving the gateposts.

For example your last post now says

This is because I only want to view records where they are exactly within 4 week....

Exactly means you should be using >= and not > which means - and within means < but I understand now what you really mean is between 4 and 5 weeks (I think!)

if the Allocated Date is greater than 4 week

per your earlier post

and prior to that

If Production week no = blank or 0 and Allocated Date is within 4 weeks of Job Inputted Date then show as Urgent.

would use <

 

[ChrisSedgwick]

I apologise for the confusion and the vague description throughout. I now believe its the 'exact' expression I'm looking for.

I've used the formula from your previous post:

Urgent: IIf([Allocated Date]>DateAdd("w",4,Date()) And [Allocated Date]<DateAdd("w",5,Date()),"Yes","No")

However, it's now showing all as 'No' in the Urgent field, even though the dates are correct and should show as Urgent.

I've attached the displayed results and the expression I'm using, which is the one above.

Thanks,

 

[CJ_London]

looking at your data, none of these are urgent. Today is 2nd March so you are looking for dates between 30th March and 6th April - the latest date you have is 9th March

 

[ChrisSedgwick]

I could only fit so much data in, every record shows as 'No' - I've attached another screen shot in the dates that should show as 'Yes'

 

[CJ_London]

teach me to double check - you need to use "ww", not "w"

 

[ChrisSedgwick]

Same issue - still showing all records as 'No'

This is the expression I'm now using

Urgent: IIf([Allocated Date]>DateAdd("ww",4,Date()) And [Allocated Date]<DateAdd("ww",5,Date()),"Yes","No")

 

[ChrisSedgwick]

Apologies, I was missing the =

This is now the desired result, once again sorry for the vague replies!

I appreciate your patience and support.

Thank you.

Chris.