syntax of iif function

Giganews Newsgroups
Subject: syntax of iif function
Posted by:  internet.shoppi…@foobox.com
Date: Sat, 18 Apr 2020

I recently came across and odd syntax problem.

I usually create/modify/edit an application on my computer and then transfe=
r/copy it to my client=E2=80=99s server.  This involves compressing the .ac=
cdb file, transferring it, unzipping the file and then linking to the clien=
t=E2=80=99s data file.

If I wish to make very minor change and don=E2=80=99t want to bother this w=
hole procedure,  I will modify both my copy and the client=E2=80=99s copy, =
ensuring that they remain the same.  I did this recently when I needed to c=
hange a calculated field in the query to a Form.

PaymentDue: IIf(Not IsNull([PurchaseOrders].[ConsignmentDate]),DateAdd("d",=
Nz([CreditPeriod],120),[PurchaseOrders].[ConsignmentDate]),DateAdd("d",Nz([=
CreditPeriod],120),[LetterDate]))

I simply copied and pasted this into the corresponding field in the query o=
n the client=E2=80=99s copy but this came up with syntax errors. =20

It worked on my computer but not on the other one.  The window on the query=
is not large and trying to sort it out was tedious. =20

I then realised that on the other computer I needed to use a ; instead of a=
, as the separator.  The query was using the locally defined list separato=
r!  I had not realised that the syntax was dependent in this way.  The Micr=
osoft page defining the syntax of an iif function does not mention this:
https://support.office.com/en-gb/article/iif-function-32436ecf-c629-48a3-99=
00-647539c764e3

Interestingly, if I transfer the whole .accdb file the commas are automatic=
ally changed to semi-colons.

This adventure led me to another Microsoft oddity.  When clarifying the abo=
ve I needed to confirm what the list separate was on each machine.  This is=
set in the Control Panel.

However i cannot find the Control Panel on my version of Windows 10.  It se=
ems to have disappeared.  There are =E2=80=98regional settings=E2=80=99 in =
Settings but the list separator is not there.  Where on earth is the Contr=
ol Panel?

Microsoft really does frustrate me sometimes.

Jim

just musing in these locked down times!

Replies