Using LookUp tables in sql query,need help

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


"Julia" wrote in message
...
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.

Thanks.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in 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


"Julia" wrote in message
...
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.

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


"Julia" wrote in message
...
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,


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" wrote in message
...
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


"Julia" wrote in message
...
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.





.

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
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" wrote in
message
...
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


"Julia" wrote in message
...
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,

If they are text, then:


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



Hoping it may help,
Vanderghast, Access MVP


"JG316" wrote in message
...
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
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" wrote in
message
...
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


"Julia" wrote in message
.. .
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.





.



.

Most helpful, thanks

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

If they are text, then:


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



Hoping it may help,
Vanderghast, Access MVP


"JG316" wrote in message
...
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
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" wrote in
message
.. .
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


"Julia" wrote in message
. ..
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.





.



.



.