Access Formatting Date Field Display based on condition

[blees10]

Hi Everyone,

I have been trying for a while to get this working and it is driving me crazy.

I have a subform on a report. On this form, there are 2 fields, tax_id and tax_date. The date is stored as text and is displayed as yyyy-dd-mm. I would like the tax_date to display only the year when tax_id = 100.

I've tried using the left[tax_date],4), but I can't get it to display ONLY when tax_id = 100

Thanks for your help!

[Jim Doherty]

Hi Everyone,

I have been trying for a while to get this working and it is driving me crazy.

I have a subform on a report. On this form, there are 2 fields, tax_id and tax_date. The date is stored as text and is displayed as yyyy-dd-mm. I would like the tax_date to display only the year when tax_id = 100.

I've tried using the left[tax_date],4), but I can't get it to display ONLY when tax_id = 100

Thanks for your help!

If your subform on your report is not based on a saved query or SQL Statement then make it so and in the query grid if your tax_id field is text datatype place this in the field section of the grid

taxdate: IIf([tax_id]="100",[tax_date],Null)

If your tax_id field is a number datatype then use this

taxdate: IIf([tax_id]=100,[tax_date],Null)

You will then have a column of data called taxdate that you can then use in the report. You cannot call this new calculated field tax_date in itself because it would cause a circular reference error but otherwise it will present you with what you require

Regards

Jim :)

[blees10]

Thanks!

I had to format the code because if it wasn't tax_id wasn't = "100", I wanted to display the full year.

So I had to create an additional field in my table which only displays the 4 digit year and then modified the code as below:

taxdate: IIf([tax_id="100",[taxdateyear],[tax_date])

Its works great.

Once again thanks for your help.

-b