I am working on 2010 ACCESS form and would like to see if it is possible to gene

Question

I am working on 2010 ACCESS form and would like to see if it is possible to generate the data based on 2 fields.

I have a table with the following fields:

COUNTDATE (DATE OF CASH COUNT)

OFFDATE (DATE OF OFFERING)

CLASS (M=Morning session, A=Afternoon session)

OFFER_ID

When enter data in:

COUNTDATE= AUG 07, 2014

OFFDATE = AUG 07, 2014

CLASS = M

The OFFER_ID will generate the following data:

YYYYMMDD&CLASS

Based on the above, it will be "20140807M".

I tried the following way:

=PlainText(Year([COUNT_DATE]) & (IIf(Month([COUNT_DATE])<10,"0" & Month([COUNT_DATE]),Month([COUNT_DATE]))) & (IIf(Day([COUNT_DATE])<10,"0" & Day([COUNT_DATE]),Day([COUNT_DATE]))),9) & [CLASS]

But it did not work. Please advise how I can accomplish this.

Thanks

=PlainText(Year([COUNT_DATE]) & (IIf(Month([COUNT_DATE])<10,"0" & Month([COUNT_DATE]),Month([COUNT_DATE]))) & (IIf(Day([COUNT_DATE])<10,"0" & Day([COUNT_DATE]),Day([COUNT_DATE]))),9) & [CLASS]

But it did not work. Please advise how I can accomplish this.

Thanks

Explanation / Answer

the best way to accomplish this is that you don't.

Storing the same data in two different fields is redundant and bad design.

Storing multiple pieces of information in one field violates the principle that fields should be "atomic" - storing only one "chunk" of information.

Your Offer_ID should be simply a meaningless unique ID, such as an Autonumber. You can, if you wish, generate this composite text string in a Query just for display purposes, but it should not be stored anywhere in any table.

A simpler expression would be

=Format([COUNT_DATE], "yyyymmdd") & [CLASS]

You can create a unique Index on the combination of [COUNT_DATE] and [CLASS] if you want to prevent entering the same class twice.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.