Clarkie001
IS-IT--Management
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
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