Sort Day of Week field Sun-Sat
Diff between two dates (sat/sun = 1 day)
I have a pickup and delivered date of a package. I need to calculate the difference of the dates with sat/sun only counted as one day. Does anyone have scripts to do this?
Form to select week and display weekday totals
I have a table with the following fields:
I would like to create a form that allows a user to select a week of the year (Sun-Sat) by date and return the number trips by day (How many on Monday, Tuesday), separated by Trip Type (Long Haul, Short Haul, Repo) and also a day total and week total.
IIf Date Query
This is in Access 2010 and I am adding days to a date based on a certain day of the week. Sun, Mon, Tue, Wed would add 9 days and that portion works fine. When I add the next part forThu, Fri and Sat it gives me a -1 result for all days.
Sun, Mon, Tue, Wed needs to calculate or add 9 days to Date
Thu, Fri need to calculate or add 11 days to Date
Sat need to calculate or add 10 days to date
This is a database to calculate the expiration day after 7 working days from a specific date.
how to create a week planning?
I need to create an access file that show a "week planning" based on a table of this type:
09/01/2012 dinner with Kate
I want to see in a form something like this:
mon tue wed thu fri sat sun
can I do this using a crossed field query?
some day have no event, other have more than one.
I have to filter by week (e.g. "this week, this week
Date to day of week
I have a field ldate (lesson date). Next to that, I have a field ldow (lesson day of week). I am trying to return the day of week in that field based on the ldate then save both to the table.
ex. I input 01/14/2012 in the ldate field I want it to return Sat in the ldow field.
I did search but couldn't / didn't find anything specific to converting this way
One of my customers made a funky request. When calculating date ranges on their report they want to calculate calendar days but count Saturday and Sunday as 1 day.
Example: My start date is Wed, 1 Jun and my end date is Tue, 7 Jun. Wed counts as day 0, Thu counts as day 1, Fri day 2, Sat & Sun as day 3, Mon as day 4, Tue as day 5.
I thought about using the WEEKDAY function to determine the day of the week the start and end dates occur and calculating the workdays and adding 1 if a weekend fell between the dates, but it is possible that some of my date ranges could cover multiple weeks.
How would I calculate this with a formula/VBA
Day of Week Totals
I have a table that has Event, Start day and End day as some of the fields. I would like to have a query that totals the number events for each day of the week. The tricky part is that the start day and end day may be different and I need to count the totals on both days.
Sample Data in table:
Event/Start Day/End Day
I would like the query to produce the following:
Filling in data in query
I have a table with data for only one day a week (Thursdays). This data is used for that week until we get a new set the following Thursday.
The table with data is called VFF and has columns Date, SeriesName, Value. This is updated every Thursday.
I want to create a new table with all calendar dates where I populate the dates using the latest available data in that week.
Thu Nov 10 data from Nov 10
Fri Nov 11 data from Nov 10
Sat Nov 12 data from Nov 10
Sun Nov 13 data from Nov 10
Mon Nov 14 data from Nov 10
Tue Nov 15 data from Nov 10
Wed Nov 16 data from Nov 10
Thu Nov 17 data from Nov 17
Fri Nov 18 data from Nov 17
I have tried to think of a few different solutions but I find they are not elegant nor very efficient.
If anyone has a good idea of how I should go about this I would be most grateful
I'm trying to design a database that can record the responses of a survey. This survey has 24 questions in total. This is the first time I'm doing this so I have zero experience.
Which two TV channels do you watch the most during the day? At what times do you watch these channels?
a) First Channel (can only pick one)
b) Day (can enter multiple combinations, e.g. [Mon-Wed], [Mon, Wed, Sat], [Thurs, Sun], etc.)
c) Time (can enter multiple combinations, e.g. 8-9am, 8-11am, 10-4pm, etc.) d) Second Channel (can only pick one)
e) Day (can enter multiple combinations, e.g. [Mon-Wed], [Mon, Wed, Sat], [Thurs, Sun], etc.)
f) Time (can enter multiple combinations, e.g. 8-9am, 8-11am, 10-4pm, etc.)
The survey has 24 questions in total, I know I need to use the "memo" datatype but have no idea where to store it so it's clean and neat.
They can combine "Mon", "Tues"."Sun" in any possible combination. But I do not know how to create a field in a table that can accept multiple choices. Any articles or demo videos on how to do this?
3) Same goes for "Time". I thought to break it up to 6-7am, 7-8am, etc. in discrete 1 hour increments, but I don't know how to allow the user to select multiple timeframes. For e.g. he can just select 7-8pm, or 7-10pm by selecting 7-8pm, 8-9pm and 9-10pm.
Do not append if date is weekend?
I have an automated daily append query 7 days a week. There is one query where I append the daily target.
I would like to put in the daily calculated target field in the query if the date fall into weekend (Sat & Sun), return all value to 0 (zero).
conditional tick box
I'm trying to make a booking overview, I have a field for each of the days of the week which is set to Yes/No data type. I also have a field for the day of arrival and day of departure.
For the booking overview I'm planning on using a cross tab query with the days of the week in columns and the Room Number as rows, tick boxes can then be used to show whether the room has been booked out on that day. Does anyone know how I could link the days of the week fields to the day of arrival and day of departure fields, so that when a record has been added for a room on them dates it shows up in the fields for the days of the week?
Group Result by weeks
I have order for different day and I would like to create a query that would look like this
Date_Ordered(a day field in my tables but I would like it to be grouped by week) and a sold price where it sums for a certain week so my query would have 2 column
Date Ordered(with the year and grouped by week)
Sold_Sums (sums of the sold_price for the grouped week)
and I don't really know how to do this I tried in a report but it gave me all the money we made for grouped week but it didn't wrote the week so I had a bunch of prices with no date. so
day of week input mask
I am trying to format a field to the Data Type that would the Day of the week like Monday and want the default to be the current day but cant figure how how at add that format. Also I want to confirm that the correct function for the Default Value is WeekdayName?
How to Calculate days of the week
I have a Table which contains dates & daysof the week. Instead of typing in the day can/how I just type in the date & automatically have the "day" appearin the day field
Filter last day of the week
I have two fields in a table that contains date and week#. Currently, I filtered manually the date using "between" the start date and end of the current week date.
start date: 1/1/2010
Last day of the week would be 9/25/2011 since 10/1/2011 and 10/2/2011 have not arrive.
Criteria: between 1/1/2010 and 9/25/2011.
This is not very practical since I have to change it very week. The reason I do this is I don't want the data shows on the week that have not yet past. In this example if I didn't specify the stop date (9/25/2011) then I will show the week 39 data(from 9/26 to 10/2/2011) which is incomplete due to two more day have not come.I want it to check it automatically in which if the current week have not past then stop at the last day of previous week.
I've searched the net but didn't find one that allow me to do what I need most just shows the current week such as DatePart, DateAdd
Query will not sort
Field: Area, Sort Ascending
Field: OrderNumber, Sort Ascending
Field: Status, Criteria Like "*Y*"
Field: Week 1, Criteria Is Null
It will do everything except sort on the order number.
Sorting by month and day - without year
I have a birthdate field which I want to sort by in a report, but I only want it to sort by the month and day - not the year. Is there a way to do this? Do I have to create a query which splits the field somehow? If it's not possible, is there a way to automatically split that field into two or three separate fields, either as one for month and day and the other for year or a separate field for each piece of the date?
week commencting date as a vaildation rule
I have a table that multiple colleagues are updating which requires the population of a week commencing date. At my place of work we consider Sunday to be the first day of the week. Any ideas how I can restrict this field to only allow week commencing dates?
here we go peeps, need help, this is a long winded one!
In a form I am calculating the sum total of a field, in this case time given for a specific task, so the calculation is simply =Sum([Time Given]) however I need to separate specific days.
The Fields in this Table are as follows:
Day - Name - Task - Time Given
I have added a Text box with the calculation above but this will only give me a total based on the on field - Time Given - I need to separate Monday - Friday and Saturday & Sunday, so if the Day Field = Monday, Tuesday, Wednesday, Thursday or Friday the time given for these days are added and the Sat & Sun are ignored, then an additional Text box set up to Calculate the Time Given for Saturday and Sunday only.
As usual I am grateful for all help, and please remember I'm not the sharpest knife in the drawer so make the help simple if you can, lol
Code to delete records older than a week.
We have a very simple, one table, access database attached to the backend of a peice of scanning equipment.
We make thousands and thousands of scans every day and the database fill ups very fast and gets slow.
We transfer this data into another program each day for reporting and record keeping so there is no need to keep this raw scanner data any longer than about a week.
Is there anyway to add some simple code to AUTOMATICALLY delete all records that are over a week old on a sort of revolving basis? - maybe at midnight when there is no one there? or maybe just once a week to delete records older than 7 days?
The computer, database and the scanner are ALWAYS turned on, we hardly ever turn it off so its not something that can be programmed at startup or shutdown.
Could anyone let me know if this is possible or easy to do? and how to do it