(') in DLookup Function

[Guest]

Item 1
I've got a DLookup function in a report that references the [Name] field in my report. It works great until some joker with the name O'Neil is called. I couldn't figure it out until I tried deleting the (') from his name to see if it would make a difference. Sure enough it did. How can I maintain the integrity of his name and have his data display correctly.

Item 2
I've got a % field in this same report. One poor soul has zero in his denominator so I get an error. I'd like to force this situation to return "NA". Is there and Iserror function like in Excel?

 

[Duane Hookom]

Item 1: Show us your control source property

Item 2: Show us your control source property

--
Duane Hookom
Microsoft Access MVP

Item 1:
I've got a DLookup function in a report that references the [Name] field

in my report. It works great until some joker with the name O'Neil is
called. I couldn't figure it out until I tried deleting the (') from his
name to see if it would make a difference. Sure enough it did. How can I
maintain the integrity of his name and have his data display correctly.

Item 2:
I've got a % field in this same report. One poor soul has zero in his

denominator so I get an error. I'd like to force this situation to return
"NA". Is there and Iserror function like in Excel?

 

[Guest]

Item 1: =DLookUp("sum([points])","[qtrnamesum]","[name] = '" & [name] & "'")

Item 2: =(Nz([points])-Nz([Text31]))/Nz([points])

 

[fredg]

Please include the pertinent part of any previous message.

You have a field named "Name" in your table.
"Name", among others words, is an Access/VBA reserved word and should
not be used as the name of a field.

Read the relevant Microsoft KnowledgeBase article:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

To return names that include a ' in them, use:
=DLookUp("[points]","[qtrnamesum]","[NameField] = " & chr(34) &
[NameField] & chr(34))

I don't believe you can use "Sum([Points])" in a DLookUp, unless it is
the actual name of a field, in which case you need to place the open
bracket at the beginning of the field name, not within the field name.

Perhaps you should use:
=DSUM("[Points]","[qtrnamesum]","[NameField] = " & chr(34) &
[NameField] & chr(34))
to get the sum of [Points] for each name.

In any event change the field named "Name" to something else, and use
chr(34) as indicated above.

I don't remember what you were trying to do with the second Item.

 

[Guest]

Acutally sum([field]) works just fine inside DLookup in 2003. Thanks for the chr(34) tip. Mister O'Neil will be thrilled. And a lesson to the kids our there...for literal strings chr(34) is the preferred method. The field really isn't [Name]...I just thought it would make it easier to understand because I'm sloppy and don't use good naming techniques.

As for the other item. I'm trying to prevent an error message from displaying when I divide by zero. I'm trying to show percentage breakdown of points and one poor schlep got zero points. Instead of #Num or whatever Access displays when you divide by zero, I would prefer "NA". I tried an IIF but it didn't work (syntax was probably wrong, or it could be totally inappropriate to use here)...again here's the current control source. Thanks if you can help. Long live this site!

=(Nz([points])-Nz([Text31]))/Nz([points])

Bob

 

[Duane Hookom]

I suggest that you always include the second argument in Nz(). Also, I would
rename Text31 to something that makes sense.
=IIf(Nz([Points],0)=0,Null,(Nz([points],0)-Nz([Text31],0))/Nz([points],0))

--
Duane Hookom
MS Access MVP

Acutally sum([field]) works just fine inside DLookup in 2003. Thanks for

the chr(34) tip. Mister O'Neil will be thrilled. And a lesson to the kids
our there...for literal strings chr(34) is the preferred method. The field
really isn't [Name]...I just thought it would make it easier to understand
because I'm sloppy and don't use good naming techniques.

As for the other item. I'm trying to prevent an error message from

displaying when I divide by zero. I'm trying to show percentage breakdown
of points and one poor schlep got zero points. Instead of #Num or whatever
Access displays when you divide by zero, I would prefer "NA". I tried an
IIF but it didn't work (syntax was probably wrong, or it could be totally
inappropriate to use here)...again here's the current control source.
Thanks if you can help. Long live this site!

=(Nz([points])-Nz([Text31]))/Nz([points])

Bob

 

[fredg]

Acutally sum([field]) works just fine inside DLookup in 2003.
Thanks for the chr(34) tip. Mister O'Neil will be thrilled. And a
lesson to the kids our there...for literal strings chr(34) is the
preferred method. The field really isn't [Name]...I just thought
it would make it easier to understand because I'm sloppy and don't
use good naming techniques.

As for the other item. I'm trying to prevent an error message from
displaying when I divide by zero. I'm trying to show percentage
breakdown of points and one poor schlep got zero points. Instead
of #Num or whatever Access displays when you divide by zero, I
would prefer "NA". I tried an IIF but it didn't work (syntax was
probably wrong, or it could be totally inappropriate to use
here)...again here's the current control source. Thanks if you can
help. Long live this site!

=(Nz([points])-Nz([Text31]))/Nz([points])

Bob

<<

Bob,

There are hundreds of people reading this message who have no idea
what the first paragraph refers to.
This is a newsgroup set up to help everyone who reads it, not just
the ones engaging in a personal correspondence.

Please use the "Reply to Group" (or whatever the tool button on your
newsreader is called to reply to the newsgroup).
This way your original message, as well as the reply, can be read and
understood by everyone who reads the post. A bit of creative snipping
can remove most of the un-needed previous text, without leaving new
readers in the dark.

Does this help?
=IIf(Nz([Points],0) = 0,"NA",
(Nz([points])-Nz([Text31]))/Nz([points]))