UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
5 Pages V « < 3 4 5  (Go to first unread post)
   Reply to this topicStart new topic
> Using Weekday Function To Detect Sat Or Sunday, Access 2013    
 
   
Doug Galayda
post Jun 30 2017, 07:56 PM
Post#81



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


Joe,
I don't know if I should post this or try to contact you or create a completely new post, but here goes.

I need to make another report similar to the first Due report, except that it could handle
A month, the Due report handles a week.
If I set the date to the next Monday, it works fine for that week,
But is it possible to do that for a whole month?

--The next report is on the Action date, it is the first lead column
I told the manager I could try to extend the range of the code.
But it is designed to work with a week.

I want to use the query I have, qryDue, and make it somehow work a whole month or 30 days in advance, the same way it does for a week, but for 30 days.

The rptDue you helped me with is done, works great.
You put on the date, a Monday, and it evaluates for the week, Mondy through Sunday

The rptDue takes waiver date and due dates, finds the greatest of those two,
Then if those two land on a week end sat or Sun, then update the action date
(a created placeholder) to Friday.
Else take the greatest of the waiver and the Due and make that the created Action date.

The manager said they need to project into the future when someone takes a vacation
Make it similar to the Due report but with a date range or a way to do the same thing but for the
Next month.
At first I thought how can I get a date range on a date range
I am entering a start date, the Monday of the week, and it evaluates for next 7 days.

I want to do it for “Action List” report for the next 30 days.
I think it is possible but don’t know how to increase it.
Thanks!
Go to the top of the page
 
RJD
post Jul 1 2017, 09:58 AM
Post#82


UtterAccess VIP
Posts: 7,648
Joined: 25-October 10
From: Gulf South USA


Hi again Doug: This is really very straight-forward, and you should learn to make these changes so that when new time-period requirements are requested you can do this yourself quickly. This is very basic Access query activity. You need only change the +4 in the query (that gets the date range of Monday through Friday) to +29, which will get 30 days inclusive. Then you can change the words in the parameter and in the query, and also in the report, to reflect the new range.

You could even, in the future, create a request form in Access to enter any date range or starting date and range length for the query and report to operate from, rather than prompting the date and hard-coding the range length.

And you are not clear in your requirement. You mentioned both 30 days and 1 month. These are two different things, and are handled differently in a query. I would also add 4-weeks to your possible requirement options as well. Again, another calculation from the base date. I mention this because you are adjusting dates to M-F, and 30 days or 1 month will pretty much always fall across partial weeks. Your (and you management's) choice, of course, but something to consider.

I made the minor adjustments to your last db, using 30 days, so take a look at the new query and report.

But really, again, these are very basic Access skills you should develop.

HTH
Joe
Attached File(s)
Attached File  Test06192017_Rev2.zip ( 1.86MB )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Doug Galayda
post Jul 3 2017, 09:49 AM
Post#83



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


1)Sorry for the late response, thank god you replied, my oldest son is moving back in with us after seven years on his own for three weeks until he gets another apartment,
and it has been chaotic, I want to thank you for your patience and assistance.

2)
Friday I did replace the 4 with 30 on Friday, but only on the first section, and I did not change the other and it came out short and I did not check the data, and that’s when I reached out to you.
I thought was only on the first column. I do understand the set up is a flexible date range now.

3)
the manager said it is a big picture, long term planning kind of thing, and he mentioned for the next few weeks, so figured the next 30 days.
I would like to learn how to operate it with weeks also, like you said that might be a better option.

3)
Before the prior form that drove the Due report, and I can use 30 day range you have in the query for that and the action report.
Before your response I was planning to make different queries to feed the same reports for different purposes. But like you said it would be best to feed the report queries fields off a form driver screen.
That is how the Due and Action report operated before in the old .Adp

I am going to make a screen that will supply the fields for the reports
and then have a run report button beside the input field(Site_Id), also a
date range(s) with a date picker.
I think the current 29 days will work for the Action report.
and since the 30 days will fall across partial weeks.
I would like to know how to work it for four weeks also.
I will research using weeks.
The manager of that bureau is not back until 705/2017)

I know I will have to list the form.fields names in the queries to get their values passed into the query(s)
it would work like this
user enters date range
user hits run report button beside the date range.
the report runs, running the query with the values off of the form report driver screen.
From date – end Dates
order by Reviewer
order by GAU

To get the reports up and going, to buy time and until I figure out how to work the form drivers correctly.
I may go the route of making two different ways to run the report by having two copies of the same report
and having two queries running the same report, the report having two different names but it being the same report.
I would have a list of tables in a table and run a case statement to run the report choices off the screen.
That is all I know how to do right now.

I did not see any Saturdays in the report to prove that the Saturdays were being set to Friday, so I will update some of the data.
I am grateful for all your help.
This post has been edited by Doug Galayda: Jul 3 2017, 09:51 AM
Go to the top of the page
 
RJD
post Jul 3 2017, 10:41 AM
Post#84


UtterAccess VIP
Posts: 7,648
Joined: 25-October 10
From: Gulf South USA


Hi Doug: Take a look at my mods to the last db. It adds the proper way to use a form to select a start date and the number of weeks to report. Again, pretty straight-forward. And the report range is variable by weeks.

HTH
Joe
Attached File(s)
Attached File  Test06192017_Rev3.zip ( 1.88MB )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Doug Galayda
post Jul 3 2017, 10:48 AM
Post#85



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


Thank you for the working report form driver example geared to number of weeks
including a date picker! I appreciate all your guidance
and will use your examples as building blocks
I am working on developing my skills as I go
and I appreciate your advice and you are very helpful.
This post has been edited by Doug Galayda: Jul 3 2017, 11:20 AM
Go to the top of the page
 
RJD
post Jul 3 2017, 11:09 AM
Post#86


UtterAccess VIP
Posts: 7,648
Joined: 25-October 10
From: Gulf South USA


You are very welcome.
Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
5 Pages V « < 3 4 5


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 06:15 AM