Weekday Calculation Problem?!?
Oct 13, 2005
Hi,
I have been trying to calculate date difference not including weekends. I found this code to use in another thread, but am unsure of where to put it.
I am quite new to using access, so a detailed description would be very useful!!!
Here's the code posted:
intDaysInPeriod = DateDiff("d", StartDate, EndDate)
intSaturdays = DateDiff("ww", StartDate, EndDate, vbSaturday)
intSundays = DateDiff("ww", StartDate, EndDate, vbSunday)
intWeekDays=intDaysInPeriod - intSaturdays - intSundays
Thanks in advance!!
Rosx
View Replies
ADVERTISEMENT
Aug 16, 2007
I am currently building a database incorporating critical events for a manufacturing process. The start date of the process triggers the expected dates for each critical event therefore I will simply add days to the start date to get the critical event date. How do I skip weekends. If the start date was on Wednesday the 5th and the first critical event was to conclude on the 3rd day after Wednesday the 5th I would like to have the critical event skip the weekend and move to Monday the 10th. I would like to do this within a query.
View 1 Replies
View Related
Oct 29, 2007
hello to everyone...i am a new member here.... and also a begginer in vba ....
in a few words i need to know how can i display all the monday's off a particullar month and generally another days of a month
thanx in advance
View 1 Replies
View Related
Oct 30, 2007
I have a table embedded in a form that data is entered on. The data to be entered is a project number, date, and a numerical value. I want the date to be entered only be on a Sunday. Is there a data validation rule to apply or do I need to use VBA? I do not know VBA!
View 3 Replies
View Related
Oct 22, 2005
I used the format text box in my query design to generate weekdays as text from my date field. It works great. However, I was hoping to produce some graphs where I could use those weekdays as 7 categories to plot against some other data. Now I am finding that to be a problem as the "real" format is a date and when I try to change the format in excel it creates this random number. Any suggestions on how to get around this problem. Thanks a bunch.
View 6 Replies
View Related
Aug 20, 2013
solve the following without using VBA or WeekName function.
Table 1: readers name, readers birthdate
Table 2: index, day (1-Sunday, 2-Monday etc.)
Query 1: readers name, readers birthdate, weekday of birthday (by WEEKDAY([birthdate])
Now what I need is somehow to link this query with Table 2 to give me the name of the day of birth for every reader.Actually I want to use table 2 as VLOOKUP table in Excel)
View 6 Replies
View Related
Dec 18, 2007
I have a table with 8 columns titled - Destination, Mon, Tues, Wed …to.. Sun. This is shown as "OriginalTable" worksheet in the attached example workbook. I want to create a new table from this original table that looks like "NewlTable" worksheet in the attached example. So I will have a new table that has 3 columns titled - Destination, Day, Weight. Weekdays will become rows so each destination will have 7 rows, one for each day of the week. The measure will be listed against each day of the week. Can someone please look at the attached example for me and help me with this.
Many thanks in advance.
View 2 Replies
View Related
Mar 12, 2008
Hi all.
I can complete this in excel no problem with monday through sunday being 1 -7, but is the same possible in access.
i.e. 12/03/08 = Wednesday.
Many Thanks Dean:confused:
Update:
DoW: Weekday([Calldate],0)
View 2 Replies
View Related
Dec 9, 2005
Hi,
A text box on my form automatically capitalizes the fist letter of a "weekday-word".
Like when I put in "sunday" this text box converts it automatically to "Sunday".
How can I avoid this?
View 3 Replies
View Related
Dec 27, 2013
I have a column in my table for day of the week (Monday, Tuesday, etc.). What can I enter in the "Order By" table-property to order the records by their chronological weekdays? Additionally, is there a similar type of action I can perform within a related form?
View 3 Replies
View Related
Mar 20, 2006
I've looked and looked and am afraid that I didn't find this because it's not possible...
The forms in my db are not the clearest, but one way of simplifying it for the users is to have them click on the calendar icon and then have a few fields automatically populate with the selected date's month, another with the year, and another with the day of the week.
Is this possible? I figured out how to have a field populated with the date, but with all of the specific queries needed, I have to do it this way...
Any thoughts? Also, is it at all possible (this is totally separate) to have an icon on the desktop that brings up a login sheet for which the entry users only see the front end, but the db admins log in and see the back end?
Thanks in advance!!
E
View 5 Replies
View Related
Apr 5, 2006
I am currently using the =Weekday([Date]) formula to show which day of the week is it, but is it possible to show the actual day not a number?
Eg instead of "1" it shows "Sunday"
Cheers
View 1 Replies
View Related
Mar 16, 2005
hi,
i want to dispaly the weekday or the day of the month
i am using the formula =format([date],"d") for day or "ddd" for weekday in the text box
but it is not returning the value that i want. returns #name//
whats wrong with the formula
many thanks
View 8 Replies
View Related
Mar 21, 2014
I am looking for Access VBA code to count number of Day Name in given range
E.g. March 01, 2014 to March 31, 2014
Mon = 5
Tue = 4
Wed = 4
Thu = 4
Fri = 4
Sat = 5
Sun = 5
View 11 Replies
View Related
Jun 27, 2015
Can I display the weekday in a label on a report? If so, how? The record source for the report has a variable "StartDate" and I want to show the day of the week (Monday, Tuesday, etc) of this date in a label.
View 3 Replies
View Related
Sep 18, 2013
Here is the basic info:
Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones
In the header of the form I have a calculated control box with the following control:
=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)
Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked. I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.
I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)
but it still sums up the whole week as the expression holds true.I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.
View 4 Replies
View Related
Jul 7, 2015
I have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
View 7 Replies
View Related
May 4, 2015
I have a query, that contains the field, weeknumber and weekday value, and year.
now i want to convert this values to proper/standard date format.
Example:
Weeknumber: 19
Weekday: 3 (Tuesday)
Year: 2015
Expected Result: May 5, 2015
View 5 Replies
View Related
Oct 11, 2005
Trying to calculate a few fields. 1st field is [delays] and 2nd field is [Planned]
The Plan scheduled for 60 and the Delays are an entered value. In the on update command button on the form I want code that will take the value above 60 and add then to the value in the [Delays] So if delays is 10 and the planned is 70 then planned will change to 60 and delays will = 20
any help
Jon
View 2 Replies
View Related
Oct 24, 2005
Hi All.
I have 3 tables in Access:
tblCurrency (Fields: IDCurrency, CDate, CRate)
tblOrder (Fields: IDOrder, OName, ODate)
tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder)
IDOrder is related to COrder with "One to Many" connection.
I have Form frmOrder, based on tblOrder. For Example Form has 1 record:
IDCal OName ODate
1 XXXX 2005.10.24
For this record related table has 2 records:
IDCal Rate1 Rate2 Rate3 COrder
1 10 3.5 15 1
2 6 4.2 12 1
tblCurrency for Exapmle have one record too:
IDCurrency CDate CRate
1 2005.10.24 0.2
In frmOrder I have textBox txtRate. Here I need to be calculated:
=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder).
By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1
Please help with expression.
Thank You in advance.
View 3 Replies
View Related
Jun 9, 2007
hey i hope some one can help me...
I am making a database for products and suppliers. Many suppliers can supply each product.
The product details are in a table called products, the suppliers details are stored in a table called suppliers and the suppliers offer for each product is stored in a table called prices.
the structure is as follows:
Table: Products
Product Name
Trade Price
Table: Suppliers
Supplier Name
Table: Prices
Product Name
Supplier Name
Supplier Offer
I have form that shows the product details and a subtable linked to the table:prices.
The problem
I need to add a field in the subtable called "Percent off trade" which is the trade price - supplier price / trade price * 100
i created a query showing the same records as the prices table plus the field "trade off price" and set the Field to:
Percent Off Trade: ([Forms]![Products]![Trade Price]-[Prices]![Supplier Price])/[Products]![Trade Price]*100
But this doesnt give me the correct percent :confused:
I hope i have explained this query and would really appreciate anyone who takes the time to suggest anything that could help
thanks in advance!
View 5 Replies
View Related
Nov 21, 2007
How can I multiplicate two numbers from two columns in a subform, show their result at the end of the row and then calculate sum of those results on the main form. This is something what I know how to do but the problem is following:
The total sum on the main form should be multiplicated wit 0,5 % and then reduced for a value of a specific row sum. It should be something like this: (Sum of rows sum * 0,5 %) - Sum of the specific row = Total sum on the main form. And all of this should be on a report too.
View 1 Replies
View Related
Jan 2, 2006
What would I use for a formuls to find all product that are in stock.
like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock?
In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal.
How would I query just for paper and not metal?
I am also going to need a query for the lowest price and the highest price.
Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do.
Any and all help would be great
Thank You
WackoWolf
View 10 Replies
View Related
Jan 20, 2008
hi,
i'm working in a database and i face some problems in some calculation
this is my table which contain these fields:
NOC number
Company Name
Warning Letter
Note: each company has many NOC
Here what i need from the query
1- create a field which contain a total of warning for each month
2- create a field which contain a total of warning for each NOC
3- create a field which contain the total of warning for the company
View 3 Replies
View Related
Feb 26, 2008
This is probably an easy calcualtion I am overthinking...
I am trying to make a query where I am calculating the "Total Value" of each customer's order of each item for the current week. The item "my company" is selling is fish, sizes small, medium and large all at different prices. I'm not exactly sure what they mean by "Total value", but I assume I take the cost times the quantity...but then what is the difference between Total Value and total ordered (in dollar amount)? Any suggestions?
View 2 Replies
View Related
May 25, 2005
I wonder if someone can point me in the right direction with a calculation problem. I have never done calculations in access before but want to make a balance sheet for a club account that I am designing. Very much in its trial stages, I have a form with three currency text boxes in it, set up as continuous. MoneyIn and MoneyOut are in the main form whilst Total is in the footer. All I want is to be able to put in a value in MoneyIn or MoneyOut and for the Total to update. Problem! I dont have a clue where to start! :rolleyes:
View 2 Replies
View Related
