Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #14074 > unrolled thread

syntax of iif function

Back to article view | Back to comp.databases.ms-access

Contents

  syntax of iif function internet.shopping@foobox.com - 2020-04-18 04:43 -0700
    Re: syntax of iif function Peter Doering <nospam@doering.org> - 2020-04-19 14:30 +0200
      Re: syntax of iif function internet.shopping@foobox.com - 2020-04-19 07:08 -0700

#14074 — syntax of iif function

I recently came across and odd syntax problem.

I usually create/modify/edit an application on my computer and then transfer/copy it to my client’s server.  This involves compressing the .accdb file, transferring it, unzipping the file and then linking to the client’s data file.

If I wish to make very minor change and don’t want to bother this whole procedure,  I will modify both my copy and the client’s copy, ensuring that they remain the same.  I did this recently when I needed to change 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 on the client’s copy but this came up with syntax errors.  

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.  

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 separator!  I had not realised that the syntax was dependent in this way.  The Microsoft page defining the syntax of an iif function does not mention this:
https://support.office.com/en-gb/article/iif-function-32436ecf-c629-48a3-9900-647539c764e3


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




This adventure led me to another Microsoft oddity.  When clarifying the above 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 seems to have disappeared.  There are ‘regional settings’ in Settings but the list separator is not there.   Where on earth is the Control Panel?


Microsoft really does frustrate me sometimes.


Jim

just musing in these locked down times!

[toc] | [next] | [standalone]

#14075

internet.shopping@foobox.com wrote:

> However i cannot find the Control Panel on my version of Windows 10.  It
> seems to have disappeared.  There are ‘regional settings’ in Settings
> but the list separator is not there.   Where on earth is the Control
> Panel?

You can find it in:

- Settings
-> Time & Language 
-> Region
-> Additional date, time, & regional settings (top right under Related
settings).

This will show you the "old" dialog where you can do these changes.

As hint, not to do with Access: 
- Press Windows-button.
- Start typing, e.g. "Regional" which gives you "Regional format" = the
page you want to see. ;-)

Peter

[toc] | [prev] | [next] | [standalone]

#14076

Thank you Peter.

I have also found, from a web search, another way:
Start meu
in the alphabetic list click Windows System and the Control Panel is listed.

They certainly kept it hidden (a bit!)  I wonder why

Jim


On Sunday, 19 April 2020 13:30:39 UTC+1, Peter Doering  wrote:
> internet.shopping@foobox.com wrote:
> 
> > However i cannot find the Control Panel on my version of Windows 10.  It
> > seems to have disappeared.  There are ‘regional settings’ in Settings
> > but the list separator is not there.   Where on earth is the Control
> > Panel?
> 
> You can find it in:
> 
> - Settings
> -> Time & Language 
> -> Region
> -> Additional date, time, & regional settings (top right under Related
> settings).
> 
> This will show you the "old" dialog where you can do these changes.
> 
> As hint, not to do with Access: 
> - Press Windows-button.
> - Start typing, e.g. "Regional" which gives you "Regional format" = the
> page you want to see. ;-)
> 
> Peter

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web