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    
post Jan 12 2018, 11:38 AM

Posts: 436
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.

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]?

Jeff Moseler
Access 2007
Go to the top of the page
post Jan 12 2018, 11:48 AM

Access Wiki and Forums Moderator
Posts: 71,734
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...

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

Custom Search
RSSSearch   Top   Lo-Fi    19th March 2018 - 05:46 AM