Parameter prompt when Order By has calculated field?

[LNBruno]

I'm going crazy trying to figure this one out...

I've got a form that allows the user to change the sort order of the data in a list box (dynamically changes SQL 'Order By'). There's a Print button on the form with the following code:

Private Sub cmdPrint_Click()
    Dim sSQL As String
    Dim db As Database
    Dim qd As QueryDef
    
'--since user can change sort on the fly,
'--get the current version of the SQL statement:
    sSQL = lstSales.RowSource

'--crack open the database:
    Set db = CurrentDb()

'--assign new SQL value to the query:
    Set qd = db.QueryDefs("qryRptFlash")
    qd.Sql = sSQL

'--clean up:
    qd.Close
    Set qd = Nothing
    db.Close
    Set db = Nothing

'--NOTE:  the report uses these fields (bound):
    lblSortedBy.Caption = "Sorted by:  " & msSortedBy
    lblDateRange.Caption = msDateRange

'--run the report:
    DoCmd.OpenReport "rptFlash", acViewPreview, "qryRptFlash"

End Sub

So the report runs from the query. I can change the sort order in the query and run both the query and report separately with no problem. When I click on the Print button, the report runs with no problem EXCEPT when the Order By includes a calculated aliased field:

SoldDate-ContactDate

which results in a "Enter parameter value" dialog. If I click OK, the report runs with no error.

I'm afraid I'm going to see this again with the other calculated fields when the users get around to entering some data for them!

I looked at other "parameter prompt" posts, but couldn't find anything that seemed to address this.

Any ideas?

Oh, yeah; here's the contents of qryRptFlash:

SELECT ContactDate, SoldDate, SoldDate-ContactDate AS [Diff in Days], 
ProposalAmount, BudgetEstimator AS [Estimated Amount], 
ProposalAmount-BudgetEstimator AS [Diff in $], 
(ProposalAmount-BudgetEstimator)/ProposalAmount AS [Diff in %], 
Channel, Employs, Region, Opportunity, Product, PM, Segment, SubSegment
FROM tblMaster
WHERE SoldDate BETWEEN #02/01/2004# AND #02/19/2004#
ORDER BY SoldDate-ContactDate;

< M!ke >

 

[CosmoKramer]

Mike,

The OrderBy clause can also refer to the field's position in the SELECT statement. Try:

SELECT ContactDate, SoldDate, SoldDate-ContactDate AS [Diff in Days], 
ProposalAmount, BudgetEstimator AS [Estimated Amount], 
ProposalAmount-BudgetEstimator AS [Diff in $], 
(ProposalAmount-BudgetEstimator)/ProposalAmount AS [Diff in %], 
Channel, Employs, Region, Opportunity, Product, PM, Segment, SubSegment
FROM tblMaster
WHERE SoldDate BETWEEN #02/01/2004# AND #02/19/2004#
ORDER BY 3;

Hoc nomen meum verum non est.

 

[LNBruno]

Now it prompts for '3'...

Pardon me while I take a moment for a sip of coffee and a loud, long primal scream!

&quot;If stupidity got us into this mess, then why can't it get us out?&quot; -Will Rogers


< M!ke >

 

[CosmoKramer]

I dunno, it works for me....Can you post your current select statement??

Hoc nomen meum verum non est.

 

[LNBruno]

sure:

SELECT ContactDate, SoldDate, SoldDate-ContactDate AS [Diff in Days], ProposalAmount, BudgetEstimator AS [Estimated Amount], ProposalAmount-BudgetEstimator AS [Diff in $], (ProposalAmount-BudgetEstimator)/ProposalAmount AS [Diff in %], Channel, Employs, Region, Opportunity, Product, PM, Segment, SubSegment
FROM tblMaster
WHERE SoldDate BETWEEN #02/01/2004# AND #02/20/2004#
ORDER BY 3;

< M!ke >

 

[LNBruno]

Isn't it nice when you take a weekend off, come back to work, and the answer to your question magically comes to you?

By dropping the query name in the DoCmd and letting the report's own RecordSource property handle it, the report runs WITHOUT the freaking prompt!

&quot;In order for you to go crazy, you'd have to back up about 15 years...&quot; - the wife


< M!ke >