Date Ranges

[navi95]

Hello All,

Ive made a booking system using access and I wanted to create a cleaning schedule.
Essentially I want this query to look at the date of the arrival and then allocate that client to either Cleaning Schedule 1 or Cleaning schedule 2.

Clients arriving during 1st - 7th or 15th-22nd will be schedule 1
Clients arriving during 8th-14th or 23rd-31st will be schedule 2.

My idea was to create a nested IIF statement but Ive tried several times with no luck.

Much appreciated,

Navi

 

[CJ_London]

no need for nested iifs. assuming this is in a query, try

Schedule:iif(day(arrivaldate) between 1 and 7 or day(arrivaldate) between 15 and 22,"schedule1","schedule2")

 

[navi95]

Thanks for the swift response. Right of course, since there is only two answers a normal if statement is enough, makes sense!

I tried that, it didn't work (syntax error) and then I also changed the () around Date of Arrival to [] like this:

Schedule:iif(day[Date Of Arrival] between 1 and 7 or day[Date of Arrival] between 15 and 22,"schedule1","schedule2")

But im still getting a syntax error :banghead:

 

[CJ_London]

you need the square brackets because you have spaces in your field name (always a bad idea, more typing, greater chance of difficult to spot errors). You have removed the round brackets so need to put them back.

For future reference 'syntax error' can mean pretty much anything, so also st the actual error description

 

[arnelgp]

iif(day([Date Of Arrival])<8 or (day([Date Of Arrival])>14 and day([Date Of Arrival])<23),"schedule1","schedule2")

 

[navi95]

iif(day([Date Of Arrival])<8 or (day([Date Of Arrival])>14 and day([Date Of Arrival])<23),"schedule1","schedule2")

That did the trick! much appreciated!

 

[navi95]

you need the square brackets because you have spaces in your field name (always a bad idea, more typing, greater chance of difficult to spot errors). You have removed the round brackets so need to put them back.

For future reference 'syntax error' can mean pretty much anything, so also st the actual error description

I understood what you meant after arnelgp's post
The reason I have spaces in the Field Name is for the report I created which prints out the Bill, but I will keep a mental note to keep things simple where I can. Thanks

 

[Minty]

I understood what you meant after arnelgp's post
The reason I have spaces in the Field Name is for the report I created which prints out the Bill, but I will keep a mental note to keep things simple where I can. Thanks

You can set a caption with nice spaces and things without having to have them in your field names.

 

[navi95]

This all working well, I would like to another step if possible. Now its set up to show which apartment is to be cleaned when, can I set another filter on top so it will only show which apartments will have to be cleaned this week?
So schedule 1 is Week 1 and Week 3 of the month and Schedule 2 is for Week 2 and Week 4.

 

[arnelgp]

Schedule:iif(day([Date Of Arrival])<8 or (day([Date Of Arrival])>14 and day([Date Of Arrival])<23),"schedule1","schedule2"),
WeekSched: switch(day([Date Of Arrival])<8, "Week 1", day([Date Of Arrival])<15, "Week 2", day([Date Of Arrival])<22, "Week 3", day([Date Of Arrival])<32, "Week 4")

 

[navi95]

Schedule:iif(day([Date Of Arrival])<8 or (day([Date Of Arrival])>14 and day([Date Of Arrival])<23),"schedule1","schedule2"),
WeekSched: switch(day([Date Of Arrival])<8, "Week 1", day([Date Of Arrival])<15, "Week 2", day([Date Of Arrival])<22, "Week 3", day([Date Of Arrival])<32, "Week 4")

Thats not quite what I am after, that essentially shows me in which week number the client arrived.
So, if I open the query in Week 1 or 3 of the month it will only show the apartments which are in Schedule 1. I hope I explained that properly

 

[arnelgp]

do you need to create new query to filter the one we made?

 

[navi95]

I thought perhaps something could be added in the Criteria field of the query in order to filter it out. If a second query is needed, that is fine as well.

So essentially this query shows which apartment belongs to which schedule and the second seperate query will show which apartment will need cleaning depending on which week I open it, correct?
What would be the best way of achieving this?

 

[arnelgp]

will the criteria also depends on [Date of arrival] or on another field? which field?

if still on date of arrival field, you can add another calculated field in our orig query:

Week 1: iif(day([Date Of Arrival])<8, [apartment], null),
Week 2: iif(day([Date Of Arrival])>7 and day([Date Of Arrival])<15, [apartment], null),
Week 3: iif(day([Date Of Arrival])>15 and day([Date Of Arrival])<22, [apartment], null),
Week 4: iif(day([Date Of Arrival])>21 and day([Date Of Arrival])<32, [apartment], null)


then create another query that will filter which week to return:

if you only want to show week 1:
select * from ourQuery where not [week 1] is null;

for 1 and 3 weeks:
select * from ourQuery where (not ([week 1] is null)) or (not ([week3] is null));

 

[CJ_London]

The reason I have spaces in the Field Name is for the report I created

a common answer - be aware that what is in the caption property of a field is what is used to populate label captions in forms and reports - and if it is left blank it used the field name. So by not completing the caption you are actually creating more work for yourself.

 

[navi95]

will the criteria also depends on [Date of arrival] or on another field? which field?

if still on date of arrival field, you can add another calculated field in our orig query:

Week 1: iif(day([Date Of Arrival])<8, [apartment], null),
Week 2: iif(day([Date Of Arrival])>7 and day([Date Of Arrival])<15, [apartment], null),
Week 3: iif(day([Date Of Arrival])>15 and day([Date Of Arrival])<22, [apartment], null),
Week 4: iif(day([Date Of Arrival])>21 and day([Date Of Arrival])<32, [apartment], null)


then create another query that will filter which week to return:

if you only want to show week 1:
select * from ourQuery where not [week 1] is null;

for 1 and 3 weeks:
select * from ourQuery where (not ([week 1] is null)) or (not ([week3] is null));

I dont think this is it, the filter needs to be worked around which week I open the query. So if I open the query on say the 17th June, which is week 2, then the results will list all the apartments which were assigned Schedule2.
Do you see where im coming from?

 

[Minty]

The current "week" you are in is easily worked out, using the same criteria used to generate the schedule in the first place substituting Date() for your stored date eg.

iif(day(Date())<8 or (day(date())>14 and day(Date())<23),"schedule1","schedule2")

So there is your criteria.

 

[navi95]

Absolutely brilliant Thank you guys so much! Its working like a charm!

Thanks everyone for your input