Query based on multiple day/date criteria

[stevekos07]

Hello, my name is Steve. I am currently working on a project to develop an access database to help manage a roster of calls to clients on a daily basis based on two general criteria:


1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)


2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)


I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")


However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.



I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need some help with constructing the relationships and the query here.

 

[AlexN]

Hello, my name is Steve. I am currently working on a project to develop an access database to help manage a roster of calls to clients on a daily basis based on two general criteria:


1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)


2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)


I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")


However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.



I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need some help with constructing the relationships and the query here.

How about using WeekDay or WeekDayName functions?

 

[CJ_London]

first - see this link about multivalue fields so you can understand how they are referenced and how they actually work

https://support.office.com/en-us/ar...d-fields-7C2FD644-3771-48E4-B6DC-6DE9BEBBEC31

Also see this link abut the weekday function

https://msdn.microsoft.com/en-us/library/82yfs2zh(v=vs.90).aspx

Now see if your multivalue field is designed to return a number relating to the day of the week (my guess is no).

If no, you need to modify your multivalue field to

rowsource type - ValueList
rowsource 1;Sun;2;Mon;3;Tue....
BoundColumn=1
ColumnCount=2
ColumnWidths=0

then your requirement becomes

CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]) AND weekday(Date())=[CallOn].Value,"No","Yes")

I've assumed CallOn is the name of your multivalue field

Note this only works for CallOn - if you instead wanted to exclude days not to call on you would need a separate table of days 1-7

 

[stevekos07]

Hello. Yes, I worked it out. This is the expression I used:

CallToday: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]) Or [NoCallDaysMulti].[Value]=Weekday(Date(),2),"No","Yes")

I was using an AND operator for the second part and getting the wrong result. Obviously when thinking about it in plain english, it was an OR - "If such and such, OR if such and such, don't call, otherwise call"

The other hitch was setting the first day of the week to a 2, which worked for some reason where leaving it as the default did not. It might have something to do with my system time, so may need to be corrected on our system at work. (This is a funny problem that sometimes persists regardless of what you set as the user settings in Windows.)

I actually feel very proud of myself for working it out. Such is the payoff for working half a weekend for no pay .

 

[stevekos07]

first - see this link about multivalue fields so you can understand how they are referenced and how they actually work

(link deleted)

Also see this link abut the weekday function

(link deleted)

Now see if your multivalue field is designed to return a number relating to the day of the week (my guess is no).

If no, you need to modify your multivalue field to

rowsource type - ValueList
rowsource 1;Sun;2;Mon;3;Tue....
BoundColumn=1
ColumnCount=2
ColumnWidths=0

then your requirement becomes

CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]) AND weekday(Date())=[CallOn].Value,"No","Yes")

I've assumed CallOn is the name of your multivalue field

Note this only works for CallOn - if you instead wanted to exclude days not to call on you would need a separate table of days 1-7

I didn't have to assign the values in the multivalue field. Access correctly worked out the days based on the ID assigned to the days, which are the same as the default values. I did that on purpose just in case the text values didn't work. (The bound column is 1 which is the ID values).

Still, it may be wise to enforce this just in case as you suggest.

Anyway, I am stoked that I got over that hurdle. That has been the real head-banger of the whole build up till now.

 

[CJ_London]

glad you got is sorted

 

[stevekos07]

Ok, not quite done yet with this issue.

Now that I have the query working, I need to filter the results to show only one result for each client. At the moment I have a result for each value in the multivalue field. I can't post links or photos yet but picture several results for each client with more than one value selected in the NoCallDays field.

E.g.:
John Brown - NoCallFrom (blank) NoCallTo (blank) - Wed - Yes
John Brown - NoCallFrom (20/7/15) NoCallTo (29/7/15) - Fri - No
and so forth.....

How do I filter the results based on a day of the week value that corresponds to today's date to get a result for each client for today?

 

[CJ_London]

better if you can post the entire sql to your query since it may depend on its structure

 

[stevekos07]

Ok, here it is:

SELECT tblClients.FirstName, tblClients.LastName, tblNoCallDays.NoCallFrom, tblNoCallDays.NoCallTo, tblClients.NoCallDaysMulti.Value, IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]) Or [NoCallDaysMulti].[Value]=Weekday(Date(),1),"No","Yes") AS CallToday
FROM tblClients LEFT JOIN tblNoCallDays ON tblClients.ClientID = tblNoCallDays.ClientIDFK
ORDER BY tblClients.LastName;

The query yields a result for each client for every .value in the multivalue field for that client. It is obvious though that if there is at least one "No" result for the client, in the list of results for that client, then it fails the test and the result is no call today. I just need to know how to filter out the irrelevant results and arrive at just one result for each client. My skills at constructing the correct expression in the query, or in the SQL statement are not at this level yet I'm afraid.