Query asking for parameter value... 3

[iuianj07]

Hello guys,

I have a query, although it is working correctly and pulling the right data... that whenever I run the query, a pop up box with "Enter Parameter Value" for MAXDate is showing up? Normally, I just leave it blank and hit OK and the query pulls up the correct results... does anyone know what causes the asking for a parameter value to show up? And how to fix it? The query is below:

SELECT TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]) AS TotalTATTime, dateAddNoWeekends(Maximum([TotalScope_MaxDate].[DateAssigned],[TotalScope_MaxDat
e].[RushReqDate],[TotalScope_MaxDate].[ResolvedIssueDate]),[TotalTATTime]) AS DueDate
FROM (TotalScope_MaxDate LEFT JOIN TATDays ON (TotalScope_MaxDate.MAXDateYear = TATDays.Year) AND (TotalScope_MaxDate.MAXDateMonth = TATDays.Month)) LEFT JOIN TOtalScope_TATRush ON (TotalScope_MaxDate.ReportingPeriod = TOtalScope_TATRush.ReportingPeriod) AND (TotalScope_MaxDate.LoanNumber = TOtalScope_TATRush.LoanNumber)
GROUP BY TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]), TOtalScope_TATRush.TATRush, TATDays.TATDays;

MaxDate exists in TotalScope_MaxDate query, below is the query:

SELECT Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.DateAssigned, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.Analyst, Job_Tracking.QC, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]) AS MaxDate, Format([MAXDate],"mmm") AS MAXDateMonth, Format([MAXDate],"yyyy") AS MAXDateYear
FROM Job_Tracking LEFT JOIN JobTracking_PropertyCount ON (Job_Tracking.LoanNumber = JobTracking_PropertyCount.LoanNumber) AND (Job_Tracking.ReportingPeriod = JobTracking_PropertyCount.ReportingPeriod)
WHERE (((Job_Tracking.Withdraw)=0))
GROUP BY Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.DateAssigned, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.Analyst, Job_Tracking.QC, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]), Format([MAXDate],"mmm"), Format([MAXDate],"yyyy")
HAVING (((Job_Tracking.ReportingPeriod)<"2020*" Or (Job_Tracking.ReportingPeriod)<"2020*"))
ORDER BY Job_Tracking.Analyst, Job_Tracking.QC;

as you will see, MaxDate is a function that compares 3 dates and returns the max date between the 3... below is the code for MaxDate:

Public Function Maximum(ParamArray MyArray()) As Date

   Dim intLoop As Long
  
   For intLoop = LBound(MyArray) To UBound(MyArray)

       If IsNull(MyArray(intLoop)) Then
           'do nothing
       ElseIf IsNull(Maximum) Then
           Maximum = MyArray(intLoop)
       ElseIf MyArray(intLoop) > Maximum Then
           Maximum = MyArray(intLoop)
       End If

   Next

End Function

Is there any way that we could not let that parameter show up?

Any help is greatly appreciated... Thank you

 

[dhookom]

The issue is from

SELECT Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.DateAssigned, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.Analyst, Job_Tracking.QC, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]) AS MaxDate, Format([b][red][MAXDate][/red][/b],"mmm") AS MAXDateMonth, Format([b][red][MAXDate][/red][/b],"yyyy") AS MAXDateYear

I try to avoid using a derived column in another expression in the same query. I would replace MaxDate with Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]).

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

If you replace

Format([MAXDate],"mmm"),  etc.

with

Format(Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]),"mmm"),  etc.

that will likely stop your prompts. There are issues with resolving an alias later in the SQL statement.


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[lameid]

It is also good to avoid using aliases in other columns because SQL Server does not support this and Access applications of finding their way to SQL. Of all the things that have ever bit me moving to SQL Server, this one hurt the most (not that nested IIF statements are fun to convert either).

 

[iuianj07]

Thanks dhookom, traingamer and lameid... replacing the alias MaxDate with the corresponding expression helped eliminate the parameter pop up...

thanks