Using LookUp tables in sql query,need help

  • Thread starter Thread starter Julia
  • Start date Start date
J

Julia

Hi,

I need help with the following query,
I have two LookUp tables:

Cities

Id Name
1 USA
2 UK

Courtiers

Id Name
1 Boston
2 London


assuming user input City and Country NAME,I need to get the ID for the names
if possible in one query(I know of course how to do it in several queries)
if the query cannot find a name it should return -1

for example

User Input:

City:LANDON(note for the 'A')
Country:USA

excepted output:
-1,1


Thanks in advance.
 
Hi,




SELECT Nz(DLookup("id" , "Courtiers", "Name=City" ), -1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP
 
Thanks.

Michel Walsh said:
Hi,




SELECT Nz(DLookup("id" , "Courtiers", "Name=City" ), -1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Would this same method work within a Query?

Expr1: DLookUp("[County_Name]","Q02 County","[County_Code]
=County")

County is one of the fields I choose in the query, but I
get the error "Can't find the name County you used in the
expression". If I hard code a value for County, it does a
static lookup, but I want to base it on the County value
selected in the query.
 
Hi,


Should try:

Expr1: DLookUp("[County_Name]","Q02 County", "[County_Code]=" & County )


The third argument has to be coputed before being send to DLookup, so, the
actual value hold in County is then "imprinted" by the concatenation, and
DLookup sees something like ""[County_Code]=1023" .



Hoping it may help,
Vanderghast, Access MVP


JG316 said:
Would this same method work within a Query?

Expr1: DLookUp("[County_Name]","Q02 County","[County_Code]
=County")

County is one of the fields I choose in the query, but I
get the error "Can't find the name County you used in the
expression". If I hard code a value for County, it does a
static lookup, but I want to base it on the County value
selected in the query.
-----Original Message-----
Hi,




SELECT Nz(DLookup("id" , "Courtiers", "Name=City" ), -1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP





.
Click to expand...
Click to expand...
 
Tried that ... getting the error 'Data type mismatch in
criteria expression'. County_Code and County are text
fields. I tried hardcoding a value in for County, but get
the same error.

-----Original Message-----
Hi,


Should try:

Expr1: DLookUp("[County_Name]","Q02
Click to expand...
County", "[County_Code]=" & County )
The third argument has to be coputed before being send to DLookup, so, the
actual value hold in County is then "imprinted" by the concatenation, and
DLookup sees something like ""[County_Code]=1023" .



Hoping it may help,
Vanderghast, Access MVP


Would this same method work within a Query?

Expr1: DLookUp("[County_Name]","Q02 County","[County_Code]
=County")

County is one of the fields I choose in the query, but I
get the error "Can't find the name County you used in the
expression". If I hard code a value for County, it does a
static lookup, but I want to base it on the County value
selected in the query.
-----Original Message-----
Hi,




SELECT Nz(DLookup("id" , "Courtiers", "Name=City" ), - 1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP


Hi,

I need help with the following query,
I have two LookUp tables:

Cities

Id Name
1 USA
2 UK

Courtiers

Id Name
1 Boston
2 London


assuming user input City and Country NAME,I need to
Click to expand...
get
the ID for the
names
if possible in one query(I know of course how to do it in several queries)
if the query cannot find a name it should return -1

for example

User Input:

City:LANDON(note for the 'A')
Country:USA

excepted output:
-1,1


Thanks in advance.





.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,

If they are text, then:


Expr1: DLookUp("[County_Name]","Q02 County", "[County_Code]=""" & County &
"""" )



Hoping it may help,
Vanderghast, Access MVP


JG316 said:
Tried that ... getting the error 'Data type mismatch in
criteria expression'. County_Code and County are text
fields. I tried hardcoding a value in for County, but get
the same error.

-----Original Message-----
Hi,


Should try:

Expr1: DLookUp("[County_Name]","Q02
Click to expand...
County", "[County_Code]=" & County )
The third argument has to be coputed before being send to DLookup, so, the
actual value hold in County is then "imprinted" by the concatenation, and
DLookup sees something like ""[County_Code]=1023" .



Hoping it may help,
Vanderghast, Access MVP


Would this same method work within a Query?

Expr1: DLookUp("[County_Name]","Q02 County","[County_Code]
=County")

County is one of the fields I choose in the query, but I
get the error "Can't find the name County you used in the
expression". If I hard code a value for County, it does a
static lookup, but I want to base it on the County value
selected in the query.

-----Original Message-----
Hi,




SELECT Nz(DLookup("id" , "Courtiers", "Name=City" ), - 1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP


Hi,

I need help with the following query,
I have two LookUp tables:

Cities

Id Name
1 USA
2 UK

Courtiers

Id Name
1 Boston
2 London


assuming user input City and Country NAME,I need to get
the ID for the
names
if possible in one query(I know of course how to do it
in several queries)
if the query cannot find a name it should return -1

for example

User Input:

City:LANDON(note for the 'A')
Country:USA

excepted output:
-1,1


Thanks in advance.





.
Click to expand...


.
Click to expand...
Click to expand...
 
Most helpful, thanks
-----Original Message-----
Hi,

If they are text, then:


Expr1: DLookUp("[County_Name]","Q02
Click to expand...
County", "[County_Code]=""" & County &
"""" )



Hoping it may help,
Vanderghast, Access MVP


JG316 said:
Tried that ... getting the error 'Data type mismatch in
criteria expression'. County_Code and County are text
fields. I tried hardcoding a value in for County, but get
the same error.

-----Original Message-----
Hi,


Should try:

Expr1: DLookUp("[County_Name]","Q02
Click to expand...
County", "[County_Code]=" & County )
The third argument has to be coputed before being send
Click to expand...
to
DLookup, so, the
actual value hold in County is then "imprinted" by the concatenation, and
DLookup sees something like ""[County_Code]=1023" .



Hoping it may help,
Vanderghast, Access MVP


Would this same method work within a Query?

Expr1: DLookUp("[County_Name]","Q02 County","[County_Code]
=County")

County is one of the fields I choose in the query, but I
get the error "Can't find the name County you used in the
expression". If I hard code a value for County, it does a
static lookup, but I want to base it on the County value
selected in the query.

-----Original Message-----
Hi,




SELECT Nz(DLookup
Click to expand...
("id" , "Courtiers", "Name=City" ), -
1),
Nz(DLookup("id", "Cities", "Name=Country" ), - 1)





no FROM clause.




Hoping it may help,
Vanderghast, Access MVP


Hi,

I need help with the following query,
I have two LookUp tables:

Cities

Id Name
1 USA
2 UK

Courtiers

Id Name
1 Boston
2 London


assuming user input City and Country NAME,I need to get
the ID for the
names
if possible in one query(I know of course how to do it
in several queries)
if the query cannot find a name it should return -1

for example

User Input:

City:LANDON(note for the 'A')
Country:USA

excepted output:
-1,1


Thanks in advance.





.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top