Automatically Generated Fields

  • Thread starter Thread starter RichardY
  • Start date Start date
R

RichardY

In my database I have a 'Date Out' field and a 'Quater' field (whic
must correspond with the 'Date Out' field). The problem is ou
company's fiscal year is April to March, so there is often confusio
when it comes to entries for Jan., Feb., and March.

For example, a person will enter Jan. 4, 2004 into the 'Date Out' fiel
but will enter Q4-2004 into the 'Quarter' field when the correct entr
should have entered Q4-2003.

How do I setup the 'Quarter' field so that it will automaticall
generate a value based on the 'Date Out' field so that I can preven
this error from occuring?

Any help will be greatly appreciated
 
Richard
the easiest way is to create a separate table in your database named "Financial Quarters" with fields named "Date Out" and "Quarter".You can then enter the values for lets say the next 5 years.Create a combo box in the original table that refers to the "Financial Quarters" Table.The will then only be able to choose the pre-defined quarter
----- RichardY wrote: ----


In my database I have a 'Date Out' field and a 'Quater' field (whic
must correspond with the 'Date Out' field). The problem is ou
company's fiscal year is April to March, so there is often confusio
when it comes to entries for Jan., Feb., and March.

For example, a person will enter Jan. 4, 2004 into the 'Date Out' fiel
but will enter Q4-2004 into the 'Quarter' field when the correct entr
should have entered Q4-2003.

How do I setup the 'Quarter' field so that it will automaticall
generate a value based on the 'Date Out' field so that I can preven
this error from occuring

Any help will be greatly appreciated
 
In my database I have a 'Date Out' field and a 'Quater' field (which
must correspond with the 'Date Out' field). The problem is our
company's fiscal year is April to March, so there is often confusion
when it comes to entries for Jan., Feb., and March.

For example, a person will enter Jan. 4, 2004 into the 'Date Out' field
but will enter Q4-2004 into the 'Quarter' field when the correct entry
should have entered Q4-2003.

How do I setup the 'Quarter' field so that it will automatically
generate a value based on the 'Date Out' field so that I can prevent
this error from occuring?
Click to expand...

By not including it in your table AT ALL. It's redundant and can be
calculated on demand.

Instead, calculate it on the fly:

Format(DateAdd("m", -3, [Date Out]), "\QQ-yyyy")

This will subtract three months to the DateOut so that January-March
2004 are mapped to October-December 2003; the Format expression will
display this as Q4-2003.
 
Back
Top