Iif Formula Syntax Error

[Butterfly_Collector]

Hi,

I am trying to run formula field a query to produce a particular date:

[Date]: IIf([Document_Number]=1,"03 Jan 07")

When I try to develop the formula further:

[Date]: IIf([Document_Number]=1,"03 Jan 07"),(Document_Number]=2,"23 Aug 07")) etc...

A syntax error formula appears.

Can anyone point me in the right direction please?

Thanks

 

[MStef]

I suggest to use a FUNCTION (in Modules), and Select Case statement,
for this problem.

 

[Butterfly_Collector]

Thanks for your reply. I am however a relative novice when it comes to Access.

 

[MStef]

Send a short example of your "mdb", (access 2000, or 2002-2003),
and say what do you want.

 

[namliam]

First of you shouldnt use [Date] as an Alias, date is a reserved word.

The proper syntax would be:
DocDate: IIf([Document_Number]=1,"03 Jan 07",IIF(Document_Number]=2,"23 Aug 07"), "SomeThingElse") )

The idea of MStef would be to use a function, which is valid depening on the number of options.
Another valid option would be to make a related table which contains this data.

As a general note, your using dates in a text "way" this is generaly not good.
To change the dates to 'real' dates, use # instead of "
DocDate: IIf([Document_Number]=1,#01/03/07#,IIF(Document_Number]=2,#08/23/07#, NULL) )
Note the use of the US date format !