Weekday Calculation

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 Replies

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 2 Replies View Related

Weekday Function

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

Data Validation - Certain Weekday

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

Need To Convert Weekday To Text

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

Queries :: Name Of Weekday In A Query

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

Transpose Weekday Columns To Rows

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

Date Conversion To Weekday And Weekends

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

Text Box Capitalizes First Weekday Character

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

Order By Weekday In Table And Form

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

Pull Month/Year/WeekDay From Calendar?

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

=Weekday([Date]) Show Actual Day Not Number

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

Display The Dates Day Or The Weekday Eg Moday In A Text Box

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

Forms :: Count Weekday In Given Date Range

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

Reports :: Can Display Weekday In A Label On A Report

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

Forms :: Calculated Controls Based On Weekday In Form

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

Queries :: Display Date / Time Based On Weekday

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

Queries :: Convert Weeknumber And Weekday To Proper Date Format

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

IF Then Calculation

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

Q:Calculation

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

Need Help With Calculation

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

Calculation

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

What Calculation To Use?

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

Need Help In Calculation

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

Help With A Calculation!

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

Calculation

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