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: 451
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;
Go to the top of the page
 
theDBguy
post Dec 5 2017, 12:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,500
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
Go to the top of the page
 
whitechair
post Dec 5 2017, 02:40 PM
Post#3



Posts: 451
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?
Go to the top of the page
 
theDBguy
post Dec 5 2017, 02:46 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,500
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?
Go to the top of the page
 
BuzyG
post Dec 6 2017, 05:31 AM
Post#5



Posts: 386
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
Go to the top of the page
 
BruceM
post Dec 6 2017, 08:20 AM
Post#6


UtterAccess VIP
Posts: 7,683
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    20th October 2018 - 12:06 PM