Requesting Dlookup help

G

Guest

Using Access 2002 XP Pro
I want to do something like the following in the CountrolSource of a report
field:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
[ClientSource])
AdSourceT has pk of a long int autonumber
ClientSource is a text field that stores the AdSourceID number from a
dataentry form combo box.
I want the report to print the AdSourceDescription, not the numeric code for
the description.
The above returns #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = '" &
[ClientSource] & "' ")
I also get #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = 1") ' force
return of walk-in
I get expected results (Walk-in which is the AdSourceDescription for
AdSourceID = 1

I can't get the 3rd (criteria) parameter of the Dlookup formed correctly to
take the numeric value from the text field (ClientSource) to match the long
int AdSourceID.

Thanks in advance, Allen.
 
D

Duane Hookom

I am not sure why "ClientSource is a text field" when it should clearly by a
numeric Long Integer to relate to AdSourceID.
Normally, I would include the AdSourceT table in the record source of the
report. If you can't do this, try use:

=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
Val([ClientSource]))
 
G

Guest

Thanks for the reply Duane. I understand your suggestion that ClientSource
be made a long int. It started out life holding the text description of the
client source but I wanted to change it to hold the code instead.

BTW, when I tried the val() function I still get an error. I tried
=val([ClientSource]) after trying your full statement and it still displays
#Error. I guess I will go change the table definition and see how much data
I can mess up.

Cheers, Allen.

Duane Hookom said:
I am not sure why "ClientSource is a text field" when it should clearly by a
numeric Long Integer to relate to AdSourceID.
Normally, I would include the AdSourceT table in the record source of the
report. If you can't do this, try use:

=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
Val([ClientSource]))

--
Duane Hookom
MS Access MVP


Allen said:
Using Access 2002 XP Pro
I want to do something like the following in the CountrolSource of a
report
field:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
[ClientSource])
AdSourceT has pk of a long int autonumber
ClientSource is a text field that stores the AdSourceID number from a
dataentry form combo box.
I want the report to print the AdSourceDescription, not the numeric code
for
the description.
The above returns #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = '" &
[ClientSource] & "' ")
I also get #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = 1") ' force
return of walk-in
I get expected results (Walk-in which is the AdSourceDescription for
AdSourceID = 1

I can't get the 3rd (criteria) parameter of the Dlookup formed correctly
to
take the numeric value from the text field (ClientSource) to match the
long
int AdSourceID.

Thanks in advance, Allen.
Click to expand...
Click to expand...
 
G

Guest

The source for the report is:
SELECT Clients.*,
StoreInfoT.*,
[StoreCity] & ", " & [StoreState] & " " & [StoreZip] AS StoreAdr,
[ClientFirstName] & " " & [ClientMiddleName] & " " & [ClientLastName] AS
CName, [SpouseFirstName] & " " & [SpouseMiddleName] & " " & [SpouseLastName]
AS Sname,
[ClientCity] & ", " & [ClientState] & " " & [ClientZIP] AS Cadr,
ReturnTypeT.ReturnTypeDescription
FROM StoreInfoT,
Clients INNER JOIN ReturnTypeT ON
Clients.ClientRetType=ReturnTypeT.ReturnTypeID
WHERE (((Clients.SSN_ITIN)=forms!EditClientF!SSN_ITIN));

When I tried to add AdSourceT.AdSourceDescription...
Clients INNERJOIN AdSourceT ON Clients.ClientSource=AdSourceT.AdSourceID
I got error about multiple JOINs giving ambigous results (or something to
that effect) and suggesting that I setup 2 queries (one feeding other). I
wanted to do the Dlookup instead of that.

Where in the Access help are the functions such as Val() documented. If I
could find a list of them I could figure out which ones to use.

Thanks, Allen.
Duane Hookom said:
I am not sure why "ClientSource is a text field" when it should clearly by a
numeric Long Integer to relate to AdSourceID.
Normally, I would include the AdSourceT table in the record source of the
report. If you can't do this, try use:

=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
Val([ClientSource]))

--
Duane Hookom
MS Access MVP


Allen said:
Using Access 2002 XP Pro
I want to do something like the following in the CountrolSource of a
report
field:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
[ClientSource])
AdSourceT has pk of a long int autonumber
ClientSource is a text field that stores the AdSourceID number from a
dataentry form combo box.
I want the report to print the AdSourceDescription, not the numeric code
for
the description.
The above returns #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = '" &
[ClientSource] & "' ")
I also get #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = 1") ' force
return of walk-in
I get expected results (Walk-in which is the AdSourceDescription for
AdSourceID = 1

I can't get the 3rd (criteria) parameter of the Dlookup formed correctly
to
take the numeric value from the text field (ClientSource) to match the
long
int AdSourceID.

Thanks in advance, Allen.
Click to expand...
Click to expand...
 
D

Duane Hookom

If all you joins are inner joins then you shouldn't get ambiguous joins
messages. You might have some ambiguous field errors is you have one or more
fields with the same name.

You can find function Help by pressing F1 when in a module window.

--
Duane Hookom
MS Access MVP


Allen said:
The source for the report is:
SELECT Clients.*,
StoreInfoT.*,
[StoreCity] & ", " & [StoreState] & " " & [StoreZip] AS StoreAdr,
[ClientFirstName] & " " & [ClientMiddleName] & " " & [ClientLastName] AS
CName, [SpouseFirstName] & " " & [SpouseMiddleName] & " " &
[SpouseLastName]
AS Sname,
[ClientCity] & ", " & [ClientState] & " " & [ClientZIP] AS Cadr,
ReturnTypeT.ReturnTypeDescription
FROM StoreInfoT,
Clients INNER JOIN ReturnTypeT ON
Clients.ClientRetType=ReturnTypeT.ReturnTypeID
WHERE (((Clients.SSN_ITIN)=forms!EditClientF!SSN_ITIN));

When I tried to add AdSourceT.AdSourceDescription...
Clients INNERJOIN AdSourceT ON Clients.ClientSource=AdSourceT.AdSourceID
I got error about multiple JOINs giving ambigous results (or something to
that effect) and suggesting that I setup 2 queries (one feeding other). I
wanted to do the Dlookup instead of that.

Where in the Access help are the functions such as Val() documented. If I
could find a list of them I could figure out which ones to use.

Thanks, Allen.
Duane Hookom said:
I am not sure why "ClientSource is a text field" when it should clearly
by a
numeric Long Integer to relate to AdSourceID.
Normally, I would include the AdSourceT table in the record source of the
report. If you can't do this, try use:

=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
Val([ClientSource]))

--
Duane Hookom
MS Access MVP


Allen said:
Using Access 2002 XP Pro
I want to do something like the following in the CountrolSource of a
report
field:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
[ClientSource])
AdSourceT has pk of a long int autonumber
ClientSource is a text field that stores the AdSourceID number from a
dataentry form combo box.
I want the report to print the AdSourceDescription, not the numeric
code
for
the description.
The above returns #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = '" &
[ClientSource] & "' ")
I also get #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = 1") '
force
return of walk-in
I get expected results (Walk-in which is the AdSourceDescription for
AdSourceID = 1

I can't get the 3rd (criteria) parameter of the Dlookup formed
correctly
to
take the numeric value from the text field (ClientSource) to match the
long
int AdSourceID.

Thanks in advance, Allen.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Duane, I reconnected the AdSourceT to the query and it now works.
Don't know what I had done wrong but it is now working.

Duane Hookom said:
If all you joins are inner joins then you shouldn't get ambiguous joins
messages. You might have some ambiguous field errors is you have one or more
fields with the same name.

You can find function Help by pressing F1 when in a module window.

--
Duane Hookom
MS Access MVP


Allen said:
The source for the report is:
SELECT Clients.*,
StoreInfoT.*,
[StoreCity] & ", " & [StoreState] & " " & [StoreZip] AS StoreAdr,
[ClientFirstName] & " " & [ClientMiddleName] & " " & [ClientLastName] AS
CName, [SpouseFirstName] & " " & [SpouseMiddleName] & " " &
[SpouseLastName]
AS Sname,
[ClientCity] & ", " & [ClientState] & " " & [ClientZIP] AS Cadr,
ReturnTypeT.ReturnTypeDescription
FROM StoreInfoT,
Clients INNER JOIN ReturnTypeT ON
Clients.ClientRetType=ReturnTypeT.ReturnTypeID
WHERE (((Clients.SSN_ITIN)=forms!EditClientF!SSN_ITIN));

When I tried to add AdSourceT.AdSourceDescription...
Clients INNERJOIN AdSourceT ON Clients.ClientSource=AdSourceT.AdSourceID
I got error about multiple JOINs giving ambigous results (or something to
that effect) and suggesting that I setup 2 queries (one feeding other). I
wanted to do the Dlookup instead of that.

Where in the Access help are the functions such as Val() documented. If I
could find a list of them I could figure out which ones to use.

Thanks, Allen.
Duane Hookom said:
I am not sure why "ClientSource is a text field" when it should clearly
by a
numeric Long Integer to relate to AdSourceID.
Normally, I would include the AdSourceT table in the record source of the
report. If you can't do this, try use:

=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
Val([ClientSource]))

--
Duane Hookom
MS Access MVP


Using Access 2002 XP Pro
I want to do something like the following in the CountrolSource of a
report
field:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = " &
[ClientSource])
AdSourceT has pk of a long int autonumber
ClientSource is a text field that stores the AdSourceID number from a
dataentry form combo box.
I want the report to print the AdSourceDescription, not the numeric
code
for
the description.
The above returns #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = '" &
[ClientSource] & "' ")
I also get #Error

If I put:
=DLookUp("[AdSourceDescription]","[AdSourceT]","[AdSourceID] = 1") '
force
return of walk-in
I get expected results (Walk-in which is the AdSourceDescription for
AdSourceID = 1

I can't get the 3rd (criteria) parameter of the Dlookup formed
correctly
to
take the numeric value from the text field (ClientSource) to match the
long
int AdSourceID.

Thanks in advance, Allen.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dlookup 1
Controlsource won't let Dlookup to enter 12
Syntax problem with a DLookup function call 2
DLookup can't find a value 13
Dlookup VBA for multiple dates 0
dlookup documentation? 1
DLookUp is driving me crazy. 2
Using DLookup to populate a text box 7

Top