Second go at this.

[stevekos07]

Hi all, I asked about this problem in a previous post, and after not receiving any definite solution I got thinking about it further.

Previous post link http://www.access-programmers.co.uk/forums/showthread.php?t=279542
added by JDraw

My aim is to produce a report daily which shows which clients require a call based on:

- Selected days of the week where they do not want a call (e.g. no call on a Monday, Wednesday and Sunday).
- Ad-hoc periods when they do not want a call (e.g. No call from xxx to xxx).
- Call any day where either above does not apply.

This kind of situation is simillar to say what would apply to managing a newspaper delivery round or milk run etc. In our case we run a service calling elderly frail people at home every day to check on their welfare.

I started out with trying a multi-value field to select the days not to call, but dealing with multi-value fields in queries seems to be quite a challenge. So I have gone down a different track. I have set up in my clients table a field for each day of the week as a Yes/No field, and then have 7 separate queries which can be run applicable to each day of the week (our service operates 365 days/year).

This seems to work well. I can run each query to produce a report based on the day of the week.

My next challenge is to produce a form command button to run the query applicable to today based on the day of the week (ie. if today is a monday it will run the qryMondayCalls query etc.).

Can someone help with how to set up such a button in a form?

 

[Ranman256]

I would think you store them in a tDNC (do not call table)

[clientID] [DOW] [TimeStart] [TimeEnd] [Note]
123 ,Mon,, dow
123 , 3pm, 4pm, EveryDay
123 , 1/1/201,5 3/1/2015, Dates
123 Wed ,,, dow

dow for days of week not to call
dates for specific dates
times for specific times everyday
(sorry the formatting wont say)

 

[stevekos07]

Thanks for that Ranman256. I thought of that initially, but the issue is that each client can select any day or days from the week not to receive a call. This means that there either must be a separate field for each day with a Yes/No as a record, or a table with a separate record for each weekday with a corresponding Yes/No checkbox for each day. I don't know which way is better.

My thinking was that there isn't much difference seeing as it is highly unlikely that a new day of the week will appear any time soon, so the list is limited to 7 either way.

I am not an expert at Access so I'm all ears!

 

[Ranman256]

no, you dont need a separate field for each day, thats what DATE field is for.
However, this is a more advanced format, but your way will work too.
[clientID], m,t,w,th,f,sa,su

 

[stevekos07]

Thanks Ranman2656. I guess because I am more of an Excel person I find it hard to think outside multiple fields for things that other table formats will achieve . I'm getting there though.

Can you assist me with the command button question?

 

[jdraw]

Steve,

Here is a link to a sticky (tutorial) that may be useful. It uses an Exclude table similar in concept to ranman's, I think.

Direct link to tutorial

Good luck.

 

[stevekos07]

That is a great alternative to having a checkbox against a record too. It means that you could import an Excel table with a large number of exclusions and be able to deal with them in one action. Nice. .

 

[stevekos07]

Ok, I'm still needing help on this project. I wish I had someone who I could just call and ask for help!

So far I have developed separate queries which will give me a calls list based on today's date that will show calls for any given day of the week. (Actually I have designed 2 queries for Monday and Tuesday, but it is only a matter of designing the others for the other days of the week). This means that I could put 7 command buttons on a form called 'Monday Calls', 'Tuesday Calls' etc.

While I am pretty happy with myself that I have come this far, seeing that I am fairly green in Access still, it is still not what I ultimately want. What I want is to place a date picker and a command button to run a 'Calls' report based on the date selected (with the default of today's date obviously, which is standard anyway).

So I need to be able to write an expression in the query to produce the calls report on any selected date, and link this to the date selected in the date picker.

I would really appreciate someone helping me to work through the design of how to do this .http://www.access-programmers.co.uk//www.pinterest.com/pin/create/extension/

 

[jdraw]

Steve,

Show us your table(s) design. Getting your tables designed to meet your needs is the first major step.

Seems you have Customers, CallSchedule and a DoNoTCall list.

 

[CJ_London]

I wish I had someone who I could just call and ask for help!

There is if you want to pay for it

 

[stevekos07]

There is if you want to pay for it

Yes, and it may come to that for some problems. For now I would prefer not to. Although this project is for work, I am doing it mostly in my own time and at my expense.

I work for a not-for-profit with limited funding and there isn't much money available for IT projects outside of routine or scheduled ones. This project is one I have offered to undertake on a primarily voluntary basis in order to make life easier for both myself and my colleagues to get some better time efficiency with routine work tasks etc.

Any assistance I receive here is for the benefit of our clients and our work in general. I will refrain from naming the organisation I work for here as it might surprise you that we don't have the resources we should have to devote to these kinds of projects.

http://www.access-programmers.co.uk//www.pinterest.com/pin/create/extension/

 

[stevekos07]

Here are some images of my tables and query. (Don't laugh if you think I have gone about this totally wrong. That is why I am here!)

(I don't know why these images are so huge, I have resized them in my file storage??)

My tblClients:



tblDaysOfWeek:



tblClientsCallDays (actually now renamed tblNoCallDays):



My qryCallMonday (I was thinking of one for each day, but I am sure there's a better way):



I would appreciate knowing how I could develop just one query which is based on a selected date that could be called from a date picker and command button on a form.

Thanks in advance.http://www.access-programmers.co.uk//www.pinterest.com/pin/create/extension/

 

[stevekos07]

Re: Second go at this. - SOLVED!

I did it!!

It took about 2 hours tonight to work out the syntax, but I finally got it to work.

This is the expression I used to test an input date against 2 sets of From-To conditions and also the seven Weekday fields checked either Yes or No:

CallToday: IIf([Date]>=[NoCallFrom] And [Date]<=[NoCallTo] Or [Date]>=[NoCallFrom2] And [Date]<=[NoCallTo2] Or Weekday([Date],1)=1 And [Sun]=True Or Weekday([Date],1)=2 And [Mon]=True Or Weekday([Date],1)=3 And [Tue]=True Or Weekday([Date],1)=4 And [Wed]=True Or Weekday([Date],1)=5 And [Thu]=True Or Weekday([Date],1)=6 And [Fri]=True Or Weekday([Date],1)=7 And [Sat]=True,"No","Yes")

I tested it with my Clients table and it worked correctly for every date I entered.

This means that I can now call up a 'Calls Today' report for any day in the future or past based on the selected values of those fields.

I am surprised that I got it right.

Now the rest of the database is pretty straightforward.

Sorry I didn't have the patience to wait for your answers, but I wanted to get on with this project and so I dissolved myself into trying to learn the expression syntax.

 

[namliam]

1) holy unreadable IIF

CallToday: IIf([Date]>=[NoCallFrom]  And [Date]<=[NoCallTo] 
            Or [Date]>=[NoCallFrom2] And [Date]<=[NoCallTo2] 
            Or Weekday([Date],1)=1 And [Sun]=True 
            Or Weekday([Date],1)=2 And [Mon]=True 
            Or Weekday([Date],1)=3 And [Tue]=True 
            Or Weekday([Date],1)=4 And [Wed]=True 
            Or Weekday([Date],1)=5 And [Thu]=True 
            Or Weekday([Date],1)=6 And [Fri]=True 
            Or Weekday([Date],1)=7 And [Sat]=True,"No","Yes")

2) a column named DATE? Date is a reserved word and will cause you headaches...

3) Mixing AND and OR without the use of brackets is asking for trouble

Sorry I didn't have the patience to wait for your answers, but I wanted to get on with this project and so I dissolved myself into trying to learn the expression syntax.

If you want fast help, start paying by the hour

 

[stevekos07]

1) holy unreadable IIF

CallToday: IIf([Date]>=[NoCallFrom]  And [Date]<=[NoCallTo] 
            Or [Date]>=[NoCallFrom2] And [Date]<=[NoCallTo2] 
            Or Weekday([Date],1)=1 And [Sun]=True 
            Or Weekday([Date],1)=2 And [Mon]=True 
            Or Weekday([Date],1)=3 And [Tue]=True 
            Or Weekday([Date],1)=4 And [Wed]=True 
            Or Weekday([Date],1)=5 And [Thu]=True 
            Or Weekday([Date],1)=6 And [Fri]=True 
            Or Weekday([Date],1)=7 And [Sat]=True,"No","Yes")

2) a column named DATE? Date is a reserved word and will cause you headaches...

3) Mixing AND and OR without the use of brackets is asking for trouble


If you want fast help, start paying by the hour

Namliam, I didn't say it was the only way to achieve my result, and certainly not the best I'm sure. I am still studying Access and I am sure to come across a better way to structure this expression. This was just a first solution to my problem. The key accomplishment here was to prove to myself that I could do what I wanted to do. I am sure to come up with better ways to do this kind of thing in the future.

I can see that you have re-formatted the expression to be more readable. A good idea to build it that way and would have saved me a lot of confusion.

I don't actually have a [Date] field. That a function to prompt a date parameter input. Ultimately I will not leave this in, it will be substituted with a command button on a form to run the query and this part of the code will reference that.

Re the brackets, I am still a bit confused about where and what brackets to use in long expressions. I would be open to suggestions as to how to improve my structure here. I may even end up totally re-structuring the tables and query, but for now I am very pleased that I achieved this with my limited knowledge to date.

Yes, I admit I am guilty of being a bit impatient.

 

[jdraw]

Re Brackets etc.