Max Parameter Value

[Kavy]

Good day all!

The below SQL pulls a value called 'datee' from a open form. If 'datee' is later then June 1, 2011, I would like to replace it with the value 'June 1, 2011'. Is there a way of doing this?

Thank you

Kavy

<code>

SELECT ([Datee]-[Start])*[$perDay] AS [BTD Salary], [Amount]/([end]-[start]) AS [$perDay], BAccounts.Amount, BAccounts.Open, BVendors.ID, [Forms]![Budgets]![txtEDate] AS Datee
FROM BVendors INNER JOIN BAccounts ON BVendors.ID = BAccounts.BVendor
WHERE (((BAccounts.Open)=True) AND ((BVendors.ID)=1));
</code>

 

[james_lankford]

just guessing, but try

SELECT 
    iif 
    ( 
      [Datee] > #06/01/2011#, 
      #06/01/2011# - [Start], 
      [Datee] - [Start] 
    ) * [$perDay] AS [BTD Salary],

 

[Kavy]

just guessing, but try

SELECT 
    iif 
    ( 
      [Datee] > #06/01/2011#, 
      #06/01/2011# - [Start], 
      [Datee] - [Start] 
    ) * [$perDay] AS [BTD Salary],

Thank you!