|Subject:||syntax of iif function|
|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",=
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:
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=
Microsoft really does frustrate me sometimes.
just musing in these locked down times!