So I have a database of contacts, and it has their birthdays listed as well. What I need to do is calculate the number of Fridays from their birthday up until today's date. How do I do this? Thanks
View RepliesIn my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
Any help will be appreciated, thanks
I am calculating number of weeks between start and finish date. Some of the users have questioned the validity of calculation as they are not too sure if this is being calculated as Monday to Friday week or does it work by calculating 7 days. If the latter is true, then I should see a decimal value. I am using date diff formula.
View 12 Replies View RelatedHow do I write a Access 2010 Web database expression to give me the number of days between a particular field eg [sold date] and todays date?
Being a Web database I know I am restricted to a smaller list of available expressions - normally part of my expression would include eg date().
Hi everyone
I'm a very very new access user so many apologies in advance for when I have no idea what I"m talking about.
I'm working on creating a report that will display multiple expiration dates. Currently I have an employee database, not created by me, that has all of our employees professional licenses listed.
Prof license, auto license, liability, etc.
I want to create a report that will tell me what has already expired or will expire in the next 30 days.
I did use this
Between DateAdd("d",-30,Date()) and Date() and it is bringing back info up to 2009. which isn't what I need. I'm sure I'm doing something wrong here.
I want anything that has expired regardless of the date from today, before today, and 30 days from today but I don't care about anything more than 30 days from today's date.
I noticed that when I looked at the existing database that the fields are set as text fields. I tried to change them to dates and it gave me a "deleting 106" records error message. Yikes!
Any help would be appreciated. I don't really understand expressions and I don't understand if I'm supposed to put the actual date in parentheses or what. Please pardon my ignorance.
Many many thanks.
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I'm trying to create an expression to calculate percentage.
The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.
I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.
I have a table that I imported from excel. It has start date, start time, end date end date. I did the datediff function to get the number of hours took, for exampel I wanted a report to show if the start and end date took more than 24 hours, but the problem is that I have some dates that started on friday therefore moset of them too like 72 hours since that saturday and friday shouldn't be counted. Is there a way I could filer for firdays and put a different criteria for them. I want my qry to do the followings:
Give me all items that too more than 24 hours. but if it's friday give me the ones that too 72 hours. Thanks guys.
i am trying to find the expression to count the number of days excluding fridays from a given txtStartDate and txtEndDate.
i've got the expression to count the number of days but i need omit the fridays from it.
I have a library system and i need to work out the number of days from the date the book was due and the current date. Could anyone help me.
View 2 Replies View RelatedI am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.
I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.
I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]
But get #Func! when I run the query.
Not sure if this is actually done with a query (hell, im not sure if it can be done) as my Access knowledge is limited but I've got a list of Patient Appointment Dates and I need to calculate a list of 'Next Appointment' dates that are six months on from the appointment dates not including saturdays and sundays (as the health center is closed those days)
BTW this is for coursework, I don't really work for a health center :S
edit: I seem to have put this in the tables forum, not the queries forum. Apologies for that, I'm not an Idiot, Honest!
I have a query in which I have a long list of orders and their entry date and due dates. I would like to calculate the number of working days between the both in order to see if the supplier keeps his theoretical delivery term.
Can some one give me a way to achieve this in my query:
DUE DATE.........ORDER DATE.........DELIVERY
03-03-2007 -/- 24-10-2006 = working days
many thanks in advance
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
I have an Access database query with a month (with full names of months in them) column. I need to build an expression that will take
DECEMEBER and make it 12
November and make it 11
October and make it 10 and so on.
I tried this and it worked for 1 month at a time ****** Expr1: IIf([month]="January","1") ****** but how would I format it to make all the months corespond with the right number?
The table value is not a month field it is a text field. so I don't think any of the date variables will work. I hope this is simple one for you access guys out there. I really need some help on this one.
I've created several expressions in a query to test for values in a field (one column for each value for use in a report).
The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.
I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.
I am trying to pull a number from a text box. I listed the expression for the box below. [Current Record] is a number and Count(*) is a number. I couldn't figure out what field gave me the total records so I did a count instead. If I want to pull the value from just the count portion, what expression would do that?
Code:
=[CurrentRecord] & " of " & Count(*)
Note the spaces on either side of "of"
Correct syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell
View 3 Replies View RelatedI'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies View RelatedHI there,
I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.
I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.
Hoping someone can assist.
Many thanks
Hi all
I have a text box in a form which is displaying the date with a default value : =date()
Now what i want is another text box which will display for example 30 days after of the todays date.
Lats say today is 20/10/2005 the second text box will calculate 30 days after and will show 20/11/2005
Thank you.
Please help me calculate the age of a person at a particular date without having to go into the expression every year and change the cutoff date year.
A basketball player in our league must be a certain age as of Sept. 30 each year to play in leagues divided by age. I am using the following to calculate age:
age: Int(DateDiff("d",[bdate],#9/30/2007#)/365)
I want to get rid of the 2007 and replace it with an expression that will automatically change every year.
Thank you for your time and consideration
I have the following expression as part of the recordsource for a report:
Is it possible to put a mask on the output of [phone1]? Ideally something like: (716) 555-5555 x1234
Code : phone1: [ContactNo1] & " " & [ContactNo1_ext]
hi
i need a function to work out the first date of the month if i give it a date ...
e.g.
23/07/05
returns 01/07/05
im sure this is simple let me know!! thanks!!
Heres one for you all...maybe you can help me...
I have a combo box that triggers a calendar, which the user selects and it then populates the combobox with the date.
Great everything is working....BUT...what I want to do next is populate another combobox with another date in regards to the one just choosen...
So say on the OnChange Event or AfterUpdate Event on the first date:
I want 2 Weeks plus a day and the next tuesday.
So basically I want to look out 2 weeks plus a day and then grab the next available tuesdays date...
Anyone have any idea how to accomplish this...
I am completly lost here...
Thanks