Forumla For Calculating Date???

Apr 25, 2008

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

View Replies


ADVERTISEMENT

Calculating A Date Six Months In Advance Of A Date

Jan 20, 2005

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!

View 1 Replies View Related

Calculating Working Date Between Orderdate And Due Date

Jan 11, 2007

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

View 2 Replies View Related

Calculating Date

Jul 17, 2006

I'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 Related

Calculating A Date Lag

Feb 23, 2007

HI 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

View 2 Replies View Related

Calculating A Date

Oct 20, 2005

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.

View 1 Replies View Related

Calculating Age At A Particular Date

Aug 19, 2007

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

View 3 Replies View Related

Calculating First Date Of The Month

Dec 16, 2005

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!!

View 1 Replies View Related

Calculating Quarter Out Of Date

Feb 22, 2013

I have a dataset in which i have a variable Bill date like this

Bill Date

1/16/2012

11/16/2012
11/16/2012
11/16/2012
11/16/2012

1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012

Now I want to create another variable 'Quarter' and calculate this on the basis of Date and want the output as follow

Quarter

Q1-2012

Q4-2012
Q4-2012
Q4-2012
Q4-2012

Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012

I was thinking of doing this with the combination of Datepart and if-elseif combinations. But as per my knowledge access donot support multiple Ifs statement and I dont want to write VBA code for the same...

View 5 Replies View Related

Time Date Formatting For Calculating

May 15, 2006

Apologies if this has been asked before but I have a problem with being able to format a field to accept an hours and minutes value where the hours is likely to be greater than 23.

I have a form that is related to the number of hours that people spend in respite care. Entry and exit dates are provided in a standard way - date and time - and I have a calculating field that calculates the number of hours and minutes that a person spends at the respite centre based on the entries made in the entry and exit fields.

Because the calculating field is based on a module and I need to have the calculated amount included in the table, I have set up a VBA code to copy the result of the calculation into a controlled field elsewhere in the form.

However, I also need the form to do a simple calculation whereby hours spent away from the centre are subtracted from the total hours calculated by the module, so I need the copied value to be in a format where the calcualtion is able to be performed.

In a nutshell I guess I am looking for a time format for the field that will allow the recipt of a value greater than 23 hours.

Of course perhaps I need a more sophisticated VBA script to perform the calculation behind the scenes but I admit to not being particularly at ease with VBA scripting. If anybody has any suggestions about either a format or a suggestion for a VBA script, I would be very appreciative.

Thanks in advance,

Chris Montgomery

View 4 Replies View Related

Calculating Dates Between To Date Ranges

Dec 27, 2007

Calculating dates between TWO date ranges:

I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.

Example:

test table:

Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith


Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?

(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)


Don

View 3 Replies View Related

Queries :: Calculating Different Date Formats

Jul 18, 2014

I have a query based on two tables that calculates days elapsed between two dates. Problem is one date field is date/time and the other is short date, so the result is always infinite decimal places. I don't want to change the date format for the date/time on the one table. I tried the format function in the query, but still ended up with decimal places.I just want to make that one long date to act like a short date for calculating "days", not hours, etc.

View 1 Replies View Related

Queries :: Calculating Most Current Date

Nov 10, 2014

I have a problem with my query to calculate a most current date. I have try everything to explain all current data at each TubeNumber, but there are no calculated answers at TubeNumber 7, IC2, IC3 and IC4. Why not these TubeNumbers? See below my formula to find a current date:

Code:
FindACurrentDate: IIf([Time]=DMax("Time";"qlkp_FindACurrentDate";"[Date]=#" & DMax("Date";"qlkp_FindACurrentDate";"[TubeNumber]='" & [TubeNumber] & "'") & "# And [TubeNumber]='" & [TubeNumber] & "'");"This is most current date!")

I have send my database as an attachment with my question.

View 3 Replies View Related

Calculating A Date From Another Table Record?

Sep 20, 2013

Code:

DateAdd(Interval, Number, Date)

function. More specifically, I am using a bound form with a table that I need the date for. The date will be an integer from a specific record (based on a search) from a separate table, and then added to the current day's date (in days).

View 3 Replies View Related

Calculating Date Difference In Query Or Sql

Jul 29, 2014

I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is

equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14

What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..

View 3 Replies View Related

Calculating Age In Years And Months From Date Of Birth

Jan 19, 2006

I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.

Thanks

View 14 Replies View Related

Calculating Date/time Remaining In A Table

Jan 12, 2007

Hi, I have never had to do any maths with access before, so I don’t know where to start!

What I need to do is calculate remaining warranty information for products.

In the table I have 3 columns. the 1st column is "Purchase date (dd/mm/yy)" 2nd column is "Total warranty period (years)" and the 3rd column is "Remaining warranty"

The 3rd column is the one that will display how long is left to run! How do I achieve this?

Thanks.

View 6 Replies View Related

Calculating New Date And Returning Dates Within A Range

May 26, 2005

I am tring to add a number of years to a dob. I'm doing this by adding my
date+years*365.26 I get a string of numbers. I then convert the number in
the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.

An example would be:

DOB age date I need to follow up
5/15/1935 + 75 = 5/15/2005

Then I request dates between 4/1/05 and 5/30/05 and this date is returned.


I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.

I've tried: Adding a column to my query to convert the number string back to
date format (criteria failed to return a result). Adding a second column
that ='s my converted number to date column (criteria failed to return a
result)
And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data.
Is ther an easier way to: Add a number of years to a date and have it return
a date and not the number string?
If not is there a better way to convert the string to a date?
Is there a way to get my criteria to recognize the date and return date
between my begin and end dates?

Thanks

View 14 Replies View Related

Query Calculating No Of Days Between Start And End Date

Mar 20, 2007

i am trying to run a query from a form which will bring up the no of days difference between the start and end date also on the same form.
The query doesn't bring back any results can someone please guide in what i am doing wrong.
Here is the query
SELECT DateDiff('d',[start date],[end date]) AS [no of days]
FROM [booked property]
WHERE ((([booked property]![start date])=[forms]![booking]![booked property]![start date]) AND (([booked property]![end date])=[forms]![booking]![booked property]![end date]));

Thanks

View 2 Replies View Related

Expression Help: Calculating Number Of Fridays Since A Certain Date

Feb 7, 2005

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

Modules & VBA :: Calculating Week Of A Month For Given Date

Mar 3, 2014

I have found multiple ways of calculating the week of a month for a given date. Now, I want to reverse it, i.e. given a month and week and day of week calculate the date.

Note that in week 1 and last week, there will often be days with no value.

View 2 Replies View Related

Calculating Days Between Start Date And Today?

Jul 2, 2014

I have form which automatically takes a start value for today the fieldname is Date.

Now what I want to do is calculate the difference between Date and Today's date in days with 2 criteria's

Ist criteria will be choosing the field which has a boolean field named Was Issue Resolved and has value as Null or False and
2nd criteria will be to show only days with greater than 21 days

This is what I m trying to do

Expr3: DateDiff("d",[Date],[date()])

but gives me error on date() as it can't find this parameter

I tried this also: >=DateAdd("d",-21,Date())

But its not population difference between the Date and Today Date ...

View 1 Replies View Related

Calculating Number Of Work Dates From 2 Date Fields

May 29, 2005

In 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

View 14 Replies View Related

Calculating Date Difference Excluding Weekends In Access

Aug 8, 2006

I have a query that is looking to calculate staff absence.

Absence Start date & Return to work date

Looking to calculate number of days (not a problem) but to exclude weekends ie saturday and sunday ????

Not sure if this can be done --- any assistance would be greatly received.

thanks and regards

ian watson
Yarm Cleveland UK

View 10 Replies View Related

Forms :: Calculating A Future Business / Work Date

Sep 14, 2013

I am trying to find a simple way to determine the work date based on knowing the Start date and how many days ahead I want the date for. These means I need to exclude weekends and holidays.

I tried Pat Hartmans solution located at

[URL]

I discovered that the function in there that is supposed to do this does not account for weekends, only holidays.

View 7 Replies View Related

Modules & VBA :: Calculating A Date While Taking Into Account Holidays

Mar 17, 2015

I have been looking for quite some time for some vba code that would return a date based on values in 2 fields.

In other words, I already have a field that returns transit days based on the customer chosen. End users will then enter a DueDate for the order. I want a field that returns a "ScheduledShipDate" based on [DueDate]-[TransitDays]. The part that makes this more difficult, in my opinion, is the fact that I also need to take into account some specific holidays.

I have already constructed a table with a list of the 6 company holidays and their corresponding dates. [tblHolidays.HolidayDate]

Unfortunately, my vba knowhow might as well be limited to copy and pasting as I do not need to use it all that often.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved