Using a parameter in a join statement. 1

[davidjimes]

Hi Guys...

I am trying to use a parameter in an inner join, which as you probably know is not possible (or at least it seems to me to be impossible).

For example, let's say I have access prompt the user for a date [EndDate], and generate a make table query based upon this input. I would also like to take this EndDate and, within the same statement, execute an inner join to return an appropriate foreign exchange rate as at that date (for example).

Can anyone suggest how I might go about doing this?

Many thanks in advance!

David

 

[PHV]

use a parameter in an inner join, which as you probably know is not possible
Really ????

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[davidjimes]

My apologies if my statement about parameters not being allowed in queries was incorrect, but when I try to do it..I get an error "Join expression not supported."

My join expression (excerpt) is as follows:

INNER JOIN WeeklyRates ON ([NewEndDate] = WeeklyRates.EndDate)

Where [NewEndDate] is my user inputted parameter (i.e. it pops up in Access for me to input). The query works perfectly when I type a value in directly in the SQL statement for [NewEndDate] but when I try to refer to it as above, it gives me the error.

Any help would be very much appreciated.

David

 

[PHV]

Can you please post the whole SQL code ?
Syntax of join:
FROM Table1 INNER JOIN Table2 ON [highlight]Table1.Field1[/highlight] = Table2.Field2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[davidjimes]

Here you go...Sorry it took me so long, it was extremely long, so I stripped it down to a bare bones version (which prior to changing the INNER JOIN part) works. Recall that [NewEndDate] is user inputted.

Many thanks,

David

SELECT DISTINCTROW CLng(Deals!NotionalMil) AS NotionalMil, ((Deals!PVBeforeEquityCurr)-(Deals!EquityAmtinCurr)+(Deals!EquityRecapinCurr)-[OtherFees])*[SpotRate] AS ProfitabilityCdn, [NewBeginDate] AS ProfitStartDate, [NewEndDate] AS ProfitEndDate
FROM Deals INNER JOIN NPVWeekly ON ([NewEndDate] = NPVWeekly.ValDate) AND (Deals.TraderBook = NPVWeekly.BookID) AND (Deals.DealCurrency = NPVWeekly.CurID)
WHERE ((([NewBeginDate])=[NewBeginDate]) AND (([NewEndDate])=[NewEndDate]));

 

[PHV]

Something like this ?
FROM Deals INNER JOIN NPVWeekly ON (Deals.TraderBook = NPVWeekly.BookID) AND (Deals.DealCurrency = NPVWeekly.CurID)
WHERE NPVWeekly.ValDate Between [NewBeginDate] AND [NewEndDate]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[davidjimes]

Genius! Thanks very much...I never even thought about doing it like that.