="[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
Can You explain This Condition
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can You explain This Condition
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") & "#"
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Can You explain This Condition
Also see the tutorial in this thread for more...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.