Can You explain This Condition

User avatar
sistemaPR
2StarLounger
Posts: 119
Joined: 01 Jul 2013, 14:08

Can You explain This Condition

Post by sistemaPR »

="[NPI ID]=" & "'" & [NPI ID] & "'"

the ampersand symbol, double cuotes, and also single cuotes , is unbelivable.

Are there are any rules ? That I can learn to do this

User avatar
HansV
Administrator
Posts: 78487
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can You explain This Condition

Post by HansV »

In this expression, the first [NPI ID] is enclosed in quotes, so it is a literal string. NPI ID the name of a field, and since the name contains a space, we have to put it between square brackets: [NPI ID].
The second [NPI ID] is outside the quotes; it refers to the value of the NPI ID field or control on your form.
Apparently, NPI ID is a text field.
Let's look at an example: suppose the value of the NPI ID field or control on your form is John. Without the occurrence of "'" &, the expression would evaluate to

[NPI ID]=John

When Access evaluates this expression, it will assume that John is the name of a field, and most likely there won't be such a field, so you'll be prompted to enter the value of John. That was not what you intended.
By adding "'" & twice, the expression evaluates to

[NPI ID]='John'

The single quotes around John tell Access that it is a string value, so the field NPI ID will be compared to the text string John. The returns either True (if [NPI ID] equals 'John') or False (otherwise).

See (for example) this post for other ways to do this.

If NPI ID had been a number field, it wouldn't have been necessary to place single quotes around the value:

="[NPI ID]=" & [NPI ID]

And if it had been a date field, you would have had to enclose the value in # characters and make sure that it is either in US date format mm/dd/yyyy or international date format yyyy-mm-dd:

="[NPI ID]=" & "#" & Format([NPI ID], "yyyy-mm-dd") & "#"
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Can You explain This Condition

Post by Rudi »

Regards,
Rudi

If your absence does not affect them, your presence didn't matter.