UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform Asking For Parameter, Access 2016    
 
   
whitechair
post Dec 5 2017, 12:19 PM
Post#1



Posts: 419
Joined: 26-June 08



I have the following SQL in the recordsource of a subform:
CODE
SELECT tblNavigatorLeads.LegalName, Abs(DateDiff("d",Now(),[ContHisDate])) AS HisDate, tblContactHistory.DateAdded AS ContHisDate
FROM (tblNavigatorLeads LEFT JOIN qryMostRecentContact ON tblNavigatorLeads.DotNo = qryMostRecentContact.DOT) LEFT JOIN tblContactHistory ON qryMostRecentContact.MaxOfContactHistoryID = tblContactHistory.ContactHistoryID
ORDER BY tblContactHistory.DateAdded;

It works fine, but when I wanted to add a filter to the HisDate (> 5) it asks me for a parameter for ContHisDate.

Why does it need a parameter for the ContHisDate column only when I have a filter in place?
CODE
SELECT tblNavigatorLeads.LegalName, Abs(DateDiff("d",Now(),[ContHisDate])) AS HisDate, tblContactHistory.DateAdded AS ContHisDate
FROM (tblNavigatorLeads LEFT JOIN qryMostRecentContact ON tblNavigatorLeads.DotNo = qryMostRecentContact.DOT) LEFT JOIN tblContactHistory ON qryMostRecentContact.MaxOfContactHistoryID = tblContactHistory.ContactHistoryID
WHERE (((Abs(DateDiff("d",Now(),[ContHisDate])))>5))
ORDER BY tblContactHistory.DateAdded;

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
theDBguy
post Dec 5 2017, 12:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Jeff,

What happens if you change your WHERE clause to use the alias?

WHERE Abs(DateDiff("d",Now(),[HisDate]))>5

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
whitechair
post Dec 5 2017, 02:40 PM
Post#3



Posts: 419
Joined: 26-June 08



It just asks for the parameter for HisDate. Is there an issue with putting a criteria in for a calculated field?

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
theDBguy
post Dec 5 2017, 02:46 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Jeff,

I don't know of any issue with using calculations as criteria. How about adding the table's name to the field's or alias name?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
BuzyG
post Dec 6 2017, 05:31 AM
Post#5



Posts: 341
Joined: 20-September 12
From: Cornwall UK


Have you tried evaluating it.

Abs(DateDiff("d",Now(),Eval("[ContHisDate]")))

WHERE (((Abs(DateDiff("d",Now(),Eval("[ContHisDate]"))))>5))

Just a thought.
This post has been edited by BuzyG: Dec 6 2017, 05:35 AM

--------------------
Live to Surf
Go to the top of the page
 
BruceM
post Dec 6 2017, 08:20 AM
Post#6


UtterAccess VIP
Posts: 7,023
Joined: 24-May 10
From: Downeast Maine


Since DateAdded is the underlying value for the ContHisDate alias, why not use DateAdded in the DateDiff expression, both in the SELECT and the criteria:

Abs(DateDiff("d",Now(),tblContactHistory.DateAdded)) AS HisDate

Using an alias field in a calculation for another alias field is going deeper into the weeds than seems necessary.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 07:43 AM