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
> Criteria On A Calculated Field, Access 2016    
 
   
whitechair
post Jan 12 2018, 11:38 AM
Post#1



Posts: 451
Joined: 26-June 08



I have the following query in the Control Source of a list box. I would like to limit the list box to dates from the current month.

CODE
SELECT tblNavigatorLeads.DotNo, tblNavigatorLeads.LegalName, qryCurrentMonthActivityCount.CountOfDOT, tblNavigatorLeads.TotNumTrucks, tblProspectStatus.ProspectStatus, Nz([Confirmed Renewal],[InsEffDate]) AS AdjEffDate, tblAttributes.[Confirmed Renewal], tblNavigatorLeads.InsEffDate, Month([AdjEffDate]) AS MonthEffDate
FROM ((tblNavigatorLeads LEFT JOIN qryCurrentMonthActivityCount ON tblNavigatorLeads.DotNo = qryCurrentMonthActivityCount.DOT) LEFT JOIN tblAttributes ON tblNavigatorLeads.DotNo = tblAttributes.DOT) INNER JOIN tblProspectStatus ON tblNavigatorLeads.DotNo = tblProspectStatus.DOT
WHERE (((tblProspectStatus.ProspectStatus)=5) AND ((Month([AdjEffDate]))=Month(Now())));


AdjEffDate is a calculated field. It checks if [Confirmed Renewal] is empty. If it is, it uses [InsEffDate], otherwise it uses [Confirmed Renewal]
[MonthEffDate] is a calculated field based on [AdjEffDate]. It displays the month only.

When I add "Month(Now())" to the criteria for [MonthEffDate], it brings up an input box asking for [AdjEffDate]. If I remove "Month(Now())" it runs just fine. Why does adding a criteria to a calculated field in a query cause it to not recognize [AdjEffDate]?
Go to the top of the page
 
theDBguy
post Jan 12 2018, 11:48 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi Jeff,

I'm not sure about the "why," but I think the workaround is to define the same expression in the criteria. For example, if I had something like this:

SELECT [FirstName] & [LastName] As FullName
FROM tblEmployees
WHERE FullName = "JeffMoseler"

I might try converting it to this:

SELECT [FirstName] & [LastName] As FullName
FROM tblEmployees
WHERE [FirstName] & [LastName] = "JeffMoseler"

Hope it helps...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 08:31 PM