dates (1 Viewer)

[Wesley]

question:

how do i take a date that i have stored in a table (inputted by a form), then add a period of time (e.g. 1 week, 12 months, 365 days etc.) to it and have that value pop back up on my original form (in a seperate field)?

 

[bradcccs]

Use the DateAdd function. Access help has reference to this function, but depending on your version, the help may not be too helpful.

I now use Access XP, but often refer back to Acc97 help files for descriptions that "make sense". I have attached the help file extract from Acc97 below for you reference.

If you have probs, repost and someone will set you straight.

Cheers

Brad.

Help file extract:

Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

DateAdd(interval, number, date)

The DateAdd function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to add.
number Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date Required. Variant (Date) or literal representing date to which the interval is added.
Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Remarks

You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.
To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
The DateAdd function won't return an invalid date. The following example adds one month to January 31:

DateAdd("m", 1, "31-Jan-95")

In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.

If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.

If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.

End Extract.

 

[Wesley]

okies,

now if the date i want to use as a starting point (i.e. DateAdd ("yyyy",1,"[field in table_01]")

when i have tried this, i get a #name? response.

 

[bradcccs]

Go for:

=DateAdd ("yyyy",1,[DateBox])

Where DateBox is the name of the text box on your form that is displaying the date you wish to add to.

Cheers

Brad.

 

[Wesley]

okies
somehow i must have been making a typo

guess what i REALLY want to do is have a combo box with the following:

biweekly
monthly
quarterly
semiannually
annually

i would like perhaps to have those fields in my table with a number in them.

BUT.

here is the kicker... i would like to correlate a value selected via my combo box that will do something like this:

DateAdd ("designation given via combo box selection", "a number found in a table which again corresponds to the choice selected from the combo", "Next Due Date - again from a table"

I have figured out how to use the basic function... what i'm interested in is how can i force that formula to make adjustments given that different equipment has different timing requirements.

 

[Wesley]

is this something where i would want to create a relationship that would tie the text of "Monthly" to a Character "M" and then to a Number? So that this effect would happen

DateAdd ("character selected by selecting monthly, using relationships to tie it to an "M", "Digit selected (in this case "1") by selecting montly then tying it to a "1", "Date Selected (by referencing "last performed on current form, or by selecting a field on the table where that date would be stored"

sorry that sounded like alot.

 

[Wesley]

also

for trying to tie values that would be used in an expression to fields in a combo box is there a good keyword to look up? (in access help)?

 

[bradcccs]

See if you can make sense of this.

Note the use of extra columns in the combo box and the dateadd expression.

You could use a similar approach for report basis etc.

Let me know how you go.

Cheers

Brad.

(Acc2000 - If not suitable let me know.)

 

[Wesley]

yes
thank you!!!! thank you so much!

 

[Wesley]

hmm
i tried to duplicate what you did... so that i could have the option of having more than one maintenance type per piece of equipment and i seem to be having some problems.

how could i attach a zip'd file to this message so that perhaps somebody could explain what i screwed up and how to look for solutions to problems like this in the future. I apologize for my ignorance, i really have just started working with access.

regards,

 

[rhett7660]

Just wanted to say thank you to bradcccs and added to his reputation for taking the time list out the date functions ie m=month etc. Thanks again.





Keywords: Dates, Date, Minutes, Months, Seconds