Hi guys! I just joined dbforums. I am only a novice Access user. I hope you can help me out with this.
I have a form with two date fields. The [set_date?] field is set to medium date with Date() as the default value. The [date_released] field has the following default value expression: =Switch(Month(Date())=1,"01",Month(Date())=2,"02", Month(Date())=3,"03",Month(Date())=4,"04",Month(Da te())=5,"05",Month(Date())=6,"06",Month(Date())=7, "07",Month(Date())=8,"08",Month(Date())=9,"09",Mon th(Date())=10,"10",Month(Date())=11,"11",Month(Dat e())=12,"12")+Format([set_date],"ddyy")
I used this expression because I want the [date_released] field to return a numbered value. For example: if the current date is 21-Jan-04, [date_released] should return the value 012104. I use these values as reference numbers of reports that I send to others. (I already have archived reports that also use this reference number scheme. I used to do these reports in Excel. That is why I am trying to use the same scheme in access.)
My problem is that when I change the date of [set_date?], [date_released] does not update. So I tried assigning a macro to [set_date?] that will set the value of [date_released]. However, Access says that the expression for the item in macro design view is too long to be edited. I tried using the expression: Format([set_date?],"mmddyy") instead. But when [date_released] updates, the 0 is omitted from the reference number. That's why I think using Switch is better. But how?