Remove "Enter Parameter Values"

[talkiechick]

I have a query with 4 date fields, and with two expressions that use datediff with those fields. Then I take an IIF statement and create an expression of datediff 1 - datediff 2. the problem is some of my date fields will be null.

Here is my SQL:

SELECT [Customer List].[Company Name], [Loan Info].[Loan Type], [Customer List].[Borrower's Name], [Loan Info].[Date Opened], [Loan Info].[Date Declined/Closed], [Loan Info].[Month Disbursed], [Loan Info].[Sales Stage], [Loan Info].[Loan Status], [Loan Info].[Loan Amount], Trim([Last Name] & "," & " " & [First Name]) AS BDO, DateDiff("d",[Date Opened],Now()) AS [Total Days], [Loan Info].Underwriter, ([Loan Amount]*(1-([Guaranty %]/100))) AS [Bank Exposure], Sum([Loan Info].[Loan Amount]) AS [SumOfLoan Amount], [Loan Info].[Guaranty %], [Loan Info].Industry, [Loan Info].[BDO ID], DateDiff("d",(IIf([Inactive Date] Is Null,Null,[Inactive Date])),(IIf([Reactive Date] Is Null,Null,[Reactive Date]))) AS [Inactive Days], IIf([Inactive Days] Is Null,[Total Days],([Total Days]-[Inactive Days])) AS [# of Days]
FROM [BDO List] INNER JOIN ([Customer List] INNER JOIN [Loan Info] ON [Customer List].[Company ID] = [Loan Info].[Company ID]) ON ([BDO List].[BDO ID] = [Loan Info].[BDO ID]) AND ([BDO List].[BDO ID] = [Loan Info].[BDO ID])
GROUP BY [Customer List].[Company Name], [Loan Info].[Loan Type], [Customer List].[Borrower's Name], [Loan Info].[Date Opened], [Loan Info].[Date Declined/Closed], [Loan Info].[Month Disbursed], [Loan Info].[Sales Stage], [Loan Info].[Loan Status], [Loan Info].[Loan Amount], Trim([Last Name] & "," & " " & [First Name]), [Loan Info].Underwriter, ([Loan Amount]*(1-([Guaranty %]/100))), [Loan Info].[Guaranty %], [Loan Info].Industry, [Loan Info].[BDO ID], [BDO List].[Last Name], Trim([Last Name] & "," & " " & [First Name]), DateDiff("d",(IIf([Inactive Date] Is Null,Null,[Inactive Date])),(IIf([Reactive Date] Is Null,Null,[Reactive Date]))), IIf([Inactive Days] Is Null,[Total Days],([Total Days]-[Inactive Days]))
HAVING ((([Loan Info].[Loan Status])="Active" Or ([Loan Info].[Loan Status])="In Construction" Or ([Loan Info].[Loan Status])="Not Submitted"))
ORDER BY [BDO List].[Last Name], Trim([Last Name] & "," & " " & [First Name]);


The query runs fine with entering through the parameters, but I would like the parameters not to show up at all. Can someone help explain where i messed up?

Thanks

 

[MarkK]

The query engine assumes a parameter when it can't find a field with the specified name in any of the referenced tables. Consider a table tRealTable that does not contain a field called NoSuchField. Then the query ...

SELECT NoSuchField FROM tRealTable;

... will prompt you to supply a value for NoSuchField.
This is the cause of the prompts you receive. What are the names of the values you are prompted to supply?
Also, as an aside, a tidy way to do this ...

LastName & ", " & FirstName

... is ...

LastName + ", " & FirstName

Note the plus sign. If LastName returns Null then the Null value is propagated by the plus sign and the ", " is Null'ed also. This is handy where some lastnames are not known and would normally display as ...

, John

Cheers,

 

[talkiechick]

The actual issue isn't with the names. It is with the expression, "Total Days", "Inactive Days", & "# of Days". the query is prompting a parameter value for Total days and inactive days.