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: 456
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]?
Go to the top of the page
post Jan 12 2018, 11:48 AM

Access Wiki and Forums Moderator
Posts: 73,966
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    19th December 2018 - 06:31 AM