Newbie : Working with dates

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

Hi.

Using Access 2000, how is it possible to work with dates in a table, and at
what point is this set up? I assume I can set up calculations in the table
design view.

I will be entering a date, then the next field needs to be that date+ 90
days. I'm sure this cannot be difficult.

Gurus please help!!!

Thanks in Advance,
Robin
 
Robin-

You cannot do it in the table, but you *can* do it in the form you design to
edit the table. In the AfterUpdate event of the first date field, set the
second date to [FirstDate] + 90 -- either in VB or with a SetValue action in
a macro.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi.

Using Access 2000, how is it possible to work with dates in a table, and at
what point is this set up? I assume I can set up calculations in the table
design view.

I will be entering a date, then the next field needs to be that date+ 90
days. I'm sure this cannot be difficult.

Gurus please help!!!

Thanks in Advance,
Robin
Click to expand...

It's never a good idea to assume!!!
Tables do NOT do calculations.
A Table should be used to just store the relevant data to be able to
perform the calculations elsewhere.... In a Report, in a Query, or on
a Form.

For example, all you need do is store the date in the [DateField]..
Then, whenever you needed that date+90 days, in a Form or in a Report,
you would use an unbound control and set it's control source to:

=[DateField] + 90
or...
= DateAdd("d",90,[DateField])

The result of this calculation need not be stored.
Just run it again if needed again.
 
OK, I see.

I have set up an AutoForm linked to the table. I have entered Design View,
now where can I perform the calculation? I select the field I want, but
where do I enter the code to do it?

Thanks again,
Robin

fredg said:
Hi.

Using Access 2000, how is it possible to work with dates in a table, and at
what point is this set up? I assume I can set up calculations in the table
design view.

I will be entering a date, then the next field needs to be that date+ 90
days. I'm sure this cannot be difficult.

Gurus please help!!!

Thanks in Advance,
Robin
Click to expand...

It's never a good idea to assume!!!
Tables do NOT do calculations.
A Table should be used to just store the relevant data to be able to
perform the calculations elsewhere.... In a Report, in a Query, or on
a Form.

For example, all you need do is store the date in the [DateField]..
Then, whenever you needed that date+90 days, in a Form or in a Report,
you would use an unbound control and set it's control source to:

=[DateField] + 90
or...
= DateAdd("d",90,[DateField])

The result of this calculation need not be stored.
Just run it again if needed again.
Click to expand...
 
OK, I see.

I have set up an AutoForm linked to the table. I have entered Design View,
now where can I perform the calculation? I select the field I want, but
where do I enter the code to do it?

Thanks again,
Robin

fredg said:
Hi.

Using Access 2000, how is it possible to work with dates in a table, and at
what point is this set up? I assume I can set up calculations in the table
design view.

I will be entering a date, then the next field needs to be that date+ 90
days. I'm sure this cannot be difficult.

Gurus please help!!!

Thanks in Advance,
Robin
Click to expand...

It's never a good idea to assume!!!
Tables do NOT do calculations.
A Table should be used to just store the relevant data to be able to
perform the calculations elsewhere.... In a Report, in a Query, or on
a Form.

For example, all you need do is store the date in the [DateField]..
Then, whenever you needed that date+90 days, in a Form or in a Report,
you would use an unbound control and set it's control source to:

=[DateField] + 90
or...
= DateAdd("d",90,[DateField])

The result of this calculation need not be stored.
Just run it again if needed again.
Click to expand...
Click to expand...

You're in Form Design View.
Click on the ToolBox Tool button (it has a hammer and wrench icon).
Click on the icon and place a Text control on your form (the Text
control icon is Ab|).
Display the control's property sheet.
Click on the Data tab.
The first line is the Control Source property.
On that line, write:
=[DateField] + 90

(Change [DateField] to whatever the actual name of the field is that
contains the date to which you wish to add 90 days.

Save the changes and open the form.

This calculated date will not be saved to any table.
 
Thanks Fred. Is there also a way to save it to the table? I also need to
make it a bit more complicated.

I have 4 input fields for date, and 1 calculated field, relating to the
"servicing" of cows.
Date1, Date2, Date3, Date4 and Calving Date.

Calving Date = Date1+Xdays unless Date2 has been entered. In this case,
Calving Date=Date2+Xdays, unless Date3 has been entered. You get the
picture? How can I make Access perform the calculation on the correct
field?

Many thanks again,
Robin


fredg said:
OK, I see.

I have set up an AutoForm linked to the table. I have entered Design View,
now where can I perform the calculation? I select the field I want, but
where do I enter the code to do it?

Thanks again,
Robin

fredg said:
On Mon, 12 Apr 2004 21:48:59 +0100, Robin wrote:

Hi.

Using Access 2000, how is it possible to work with dates in a table,
Click to expand...
and
at
what point is this set up? I assume I can set up calculations in the table
design view.

I will be entering a date, then the next field needs to be that date+ 90
days. I'm sure this cannot be difficult.

Gurus please help!!!

Thanks in Advance,
Robin

It's never a good idea to assume!!!
Tables do NOT do calculations.
A Table should be used to just store the relevant data to be able to
perform the calculations elsewhere.... In a Report, in a Query, or on
a Form.

For example, all you need do is store the date in the [DateField]..
Then, whenever you needed that date+90 days, in a Form or in a Report,
you would use an unbound control and set it's control source to:

=[DateField] + 90
or...
= DateAdd("d",90,[DateField])

The result of this calculation need not be stored.
Just run it again if needed again.
Click to expand...
Click to expand...

You're in Form Design View.
Click on the ToolBox Tool button (it has a hammer and wrench icon).
Click on the icon and place a Text control on your form (the Text
control icon is Ab|).
Display the control's property sheet.
Click on the Data tab.
The first line is the Control Source property.
On that line, write:
=[DateField] + 90

(Change [DateField] to whatever the actual name of the field is that
contains the date to which you wish to add 90 days.

Save the changes and open the form.

This calculated date will not be saved to any table.
Click to expand...
 
Thanks Fred. Is there also a way to save it to the table? I also need to
make it a bit more complicated.

I have 4 input fields for date, and 1 calculated field, relating to the
"servicing" of cows.
Date1, Date2, Date3, Date4 and Calving Date.

Calving Date = Date1+Xdays unless Date2 has been entered. In this case,
Calving Date=Date2+Xdays, unless Date3 has been entered. You get the
picture? How can I make Access perform the calculation on the correct
field?

Many thanks again,
Robin
Click to expand...

1) regarding: > Is there also a way to save it to the table? <
Access is not a spreadsheet. Use it properly.
In Access, calculated data (data derived by using already stored data)
should not be stored, unless there is compelling reason to do so. (A
compelling reason might be a product price that is changeable and you
wish to store a customer's total sales price as of a certain date.)

As long as you already have stored a date in one of the 4 fields
mentioned above, you simply run the calculation and arrive at the
correct calving date whenever that information is needed.

2) So only one of the 4 date fields will have data at any one time?
And [Date1] is used only if none of the others has a date?

=IIf(Not IsNull([Date4]),[Date4]+90,IIf(Not IsNull([Date3]),[Date3] +
90,IIf(Not IsNull([Date2]),[Date2] + 90, [Date1] + 90)))
 
Thanks again Fred.

It is a little more complex than I realised. I thought Access would easily
store the calculated data. Thanks for setting me straight! The only thing
that could be a problem is when running reports, but I guess that can be
done by basing the report on forms and queries.

Your calculation code worked great! Cheers. It gives me something to work
with now to build other calculations etc.

Do you know of a good site for learning the language you use to program the
calculations? Is it VB or SQL?

Cheers,
Robin
 
Back
Top