DLookup Question

[Bunky]

I have looked at this until I am cross-eyed. Hopefully, someone will help!

I have a table [StateAbbv] that has State names and the postal abbreviation
[Abbrev]. I have another table that I want to take the State identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF statement
like this
Forign: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) - It
gives me an error message that Microsoft cannot find '[Statey]'. Any help
would be greatly appreciated.

 

[Roger Carlson]

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table, ie
StateAbbv. It's saying it can't find Statey in the table because it's not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Bunky]

Roger,

I changed it like you said and now I am getting 'ERROR' in the column.

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)
???

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table, ie
StateAbbv. It's saying it can't find Statey in the table because it's not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any help
would be greatly appreciated.

 

[Roger Carlson]

That's because I was only looking at the DLookUp and not the whole
expression. Your DLookUp by itself is not an expression. The IIF needs an
expression to evaluate. I'm assuming here that if there is a match, you
want a 0 to appear in the Forgin column. If there is not a match, you want
a 1 to appear, correct?

If so, try this:
Forgin: IIf(Not IsNull(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" &
[Statey])),0,1)

Reason? If there is not a match, DLookUp will return a Null, so we test for
that with the IsNull function.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,

I changed it like you said and now I am getting 'ERROR' in the column.

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)
???

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table,
ie
StateAbbv. It's saying it can't find Statey in the table because it's
not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will
help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State
identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any
help
would be greatly appreciated.

 

[Douglas J. Steele]

Roger forgot that the state abbreviation is likely a string, not a number.

Try


Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]='" & [Statey] &
"'"),0,1)

Exagerated for clarity, that's

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]= ' " & [Statey] & " '
" ),0,1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Roger,

I changed it like you said and now I am getting 'ERROR' in the column.

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)
???

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table,
ie
StateAbbv. It's saying it can't find Statey in the table because it's
not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will
help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State
identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any
help
would be greatly appreciated.

 

[Bunky]

Statey is not in the StateAbbv table; Abbrev is the abbreviation I wish to
check against. Statey is in the query being used as I/P to this query.

Question: Why the '&' in the line? what is it doing for you?

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table, ie
StateAbbv. It's saying it can't find Statey in the table because it's not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any help
would be greatly appreciated.

 

[Roger Carlson]

Take a look at this link: http://support.microsoft.com/kb/208786/EN-US/
and look specifically for this heading (halfway down the page): Specifying
Textual Criteria That Comes from a Field on a Form.

Short answer is that it inserts the *value* of the field on that actual row
of your query into the DLookUp. This means, of course, that the DLookUp
executed once for *each* row, which is why it can be such a resource hog.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Statey is not in the StateAbbv table; Abbrev is the abbreviation I wish to
check against. Statey is in the query being used as I/P to this query.

Question: Why the '&' in the line? what is it doing for you?

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table,
ie
StateAbbv. It's saying it can't find Statey in the table because it's
not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will
help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State
identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any
help
would be greatly appreciated.

 

[Bunky]

Thank You BOTH!

It works just like I want it to.
Is there somewhere I could look to find a breakdown of this function? MS
Help is not helping me.

Thanks again!!!!

Roger forgot that the state abbreviation is likely a string, not a number.

Try


Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]='" & [Statey] &
"'"),0,1)

Exagerated for clarity, that's

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]= ' " & [Statey] & " '
" ),0,1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Roger,

I changed it like you said and now I am getting 'ERROR' in the column.

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)
???

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table,
ie
StateAbbv. It's saying it can't find Statey in the table because it's
not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will
help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State
identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any
help
would be greatly appreciated.

 

[Roger Carlson]

Quite true. Thanks.

That being the case, perhaps the Not IsNull() is unnecessary. Still, it's
more explicit and it shouldn't hurt.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger forgot that the state abbreviation is likely a string, not a number.

Try


Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]='" & [Statey] &
"'"),0,1)

Exagerated for clarity, that's

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]= ' " & [Statey] & "
' " ),0,1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Roger,

I changed it like you said and now I am getting 'ERROR' in the column.

Forgin: IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)
???

If I'm reading this correctly, Statey is not in the StateAbbv table,
correct? The "Where" argument must have a field from the domain table,
ie
StateAbbv. It's saying it can't find Statey in the table because it's
not
there.

Try switching the fields: Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Abbrev]=" & [Statey]),0,1)

Notice too, that I took [Statey] out of the quotes.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have looked at this until I am cross-eyed. Hopefully, someone will
help!

I have a table [StateAbbv] that has State names and the postal
abbreviation
[Abbrev]. I have another table that I want to take the State
identifier
[Statey] and verify it is correct. So I wrote a DLookup in an IIF
statement
like this
Forign:
IIf(DLookUp("[Abbrev]","[StateAbbv]","[Statey]=[Abbrev]"),0,1) -
It
gives me an error message that Microsoft cannot find '[Statey]'. Any
help
would be greatly appreciated.