Default Field Value

[iseman22]

Trying to build a field default value using data from a field in a different table. The deafult value contains multiple elements text ("ASFL"); then two-digit, fiscal year (12); then three digit julian date (123). I tried to use varying themes on the following to no avail:

= Display Text: IIf(Month(Now())>9,"ASFL" & Right(Year(Now()),2),"ASFL") & (Right(Year(Now()),2)+1))

I want to pull today's julian date from the tblJDate table; but, I also have to account for the government's fiscal year (why I tried to use an IIF statement).

Unfortunately, I keep getting an invalid syntax error.

Thanks in Advance for Your Help.

Tryin' to avoid newb behavior,

Paul.

 

[spikepl]

When you have syntax errors in a complex nested expression, then the way to deal with that is to start at the bottom of the pile, make that element work by itself, then the next bit etc, so if you do get an error you immediately know what caused it.

The opposite way, often used by people here, is to concoct a complex statement and then yell "Syntax error"

Sure, you can get a cure here, but that lasts only until the next complex expression that keels over.

 

[Sketchin]

I have a number in the format FiscalYear-GLXXXX XXX being autonumber.
I calculate the fiscal year using a default value of:
=IIf(Month(Date())<4,Year(Date()),Year(Date())+1) - which will work for fiscal years ending march 31. I store this in its own field, and then combine my fiscal year field with the rest of the number and store that in an InvoiceNumber field.

The final product looks like this for this fiscal year:

2013-GL0001

Hope that makes sense.

 

[fkegley]

You may want to try converting the Year pieces to a string using the CStr function, like this:

= Display Text: IIf(Month(Now())>9,"ASFL" & Right(CStr(Year(Now()),2)),"ASFL") & (Right(CStr(Year(Now()),2)+1)))