Deriving & reformating a date field from another date field in the same table

[mtrainier70]

A novice question. I've inherited a database into which the user (not me) is entering essentially the same Date data twice: field 1 contains 6/19/2014 and for field 2 they enter June-14 (the month and year from field 1).



I suspect there should be a way to derive and then reformat the display of field 2 based on the contents of field 1, such that the user only has to enter the first. Is this true? If so, how?

(I know there really is no need for the 2nd field, but existing reports and queries are built around it -- so for right now, I'm just trying to eliminate the duplicate data entry)

Thank you

[RuralGuy]

Look into the Format() function.

[rpeare]

http://www.techonthenet.com/excel/fo.../monthname.php

This link might be helpful too.

So in the 'on exit' of your date field you could have something like

UselessField = Monthname(datepart("m",[DateField]), false) & "-" & datepart("d", [Datefield])

[lfpm062010]

Minor change. It should be the last 2 digit of year and not day. Also check for null.

UselessField = IIF(ISNULL([DateField]), NULL, Monthname(datepart("m",[DateField]), false) & "-" & RIGHT(Year([Datefield]), 2))

[rpeare]

oop you're right I didn't even look I thought it was the month/day not the month/year

[RuralGuy]

I think using Format() would have been:
UselessField = IIF(ISNULL([DateField]), NULL, Format([DateField],"mmm-yy")

[rpeare]

yep, the only reason I suggested the monthname function was he actually typed out the full month name and that was the only way I knew of to get a non-truncated month name.

[RuralGuy]

Oops, I should have user "mmmm-yy"