Help!! DLookup Problem

[Clarkie001]

Hi,

I have a dlookup which should return integer based on a specific code.
Here is my SQL

SELECT Rein_Hist.Date, Rein_Hist.FMS_ID, Count(Rein_Hist.CardHolder_No) AS Sales, [Rein_Hist]![Campaign_Code] & [Rein_Hist]![Source_Code] AS Campaign_Source, Int(DLookUp("[APPLIC_ID]","REIN_CODES","[Campaign_Source]='CM33RW3M'")) AS APPLIC_ID INTO Rein_Sales_mtbl
FROM (Rein_Hist INNER JOIN CURR_REPOR ON Rein_Hist.Date = CURR_REPOR.REPOR_DATE) INNER JOIN AGENT_DETAILS ON Rein_Hist.FMS_ID = AGENT_DETAILS.FMS_ID
GROUP BY Rein_Hist.Date, Rein_Hist.FMS_ID, [Rein_Hist]![Campaign_Code] & [Rein_Hist]![Source_Code], Int(DLookUp("[APPLIC_ID]","REIN_CODES","[Campaign_Source]='CM33RW3M'"))
HAVING ((([Rein_Hist]![Campaign_Code] & [Rein_Hist]![Source_Code]) In (select Campaign_Source from REIN_CODES where Disabled=False)));

The above code works fine, as you can see in the APPLIC_ID column I have actually typed in a specific code and it returns the correct ID.

The issue is I do not wish to have a constant there, as there are more than one possible code type which change constantly, therefore I want to add in the lookup column. When I do this it returns the wrong number, it returns the ID which shows up most, or first, or something like that.

I want to change this constant to [REIN_CODES].[Campaign_Source].

Does anyone know how to get around this? Any help will be much appreciated.

Thanks

Clarkie

 

[tbclbd]

Try something like this

"[Campaign_Source] = '" & [REIN_CODES].[Campaign_Source]& "'"


Double quote at beginning, single/double after the equal sign, then double, single, double after the second &

 

[Clarkie001]

Hi tbclbd

I get a message saying it cant find REIN_CODES.Campaign_Cource, when I have it like that....any other ideas?

Clarkie

 

[rkasnick]

It looks like you have declared a local variable "Campaign_Source", as in **[Rein_Hist]![Campaign_Code] & [Rein_Hist]![Source_Code] AS Campaign_Source** so try taking the [Rein_Hist]! out of the dlookup parameter, so it will look like "[Campaign_Source] = '" & Campaign_Source & "'"


Regards
Rod