Help with an Access GUI formula for Update Query

[hellfire104]

Hi there!


I have an update query that is updating the values in a field based on an inner join. The output of which looks something like this:

Table Name.Field = VBF Source.Date
(THE DATE FIELD IS A TEXT FORMATTED FIELD)

DATE

2/1/2015

5/1/2015

8/1/2015

11/1/2015

2/1/2016 Based on this updated field I need a formula for another update query, preferrably the same update query that does the following: Table Name.Field = VBF Source.CurrentPeriod IF VBF Source.date = MAX value (ie most recent date) then "T" ELSE "F" END IF Table Name.Field = VBF Source.PreviousPeriod If the value is exactly one year before the maxvalue then "T" else "F" END IF Final Result is below. Date CurrentPeriod PreviousPeriod 2/1/2015 F T 5/1/2015 F F 8/1/2015 F F 11/1/2015 F F 2/1/2016 T F

Thank you very kindly!

[CJ_London]

you should avoid spaces and non alpha numeric characters in table and field names and Date is a reserved word. So trying to understand this

Table Name.Field = VBF Source.Date

Is table called VBF and field Source.Date? or table is "VBF Source" and field called Date?

to find the max date, use a dmax function or a subquery

iif(mydate=dmax("myDate","myTable"),"T","F")

or

iif(mydate=(SELECT max myDate from myTable),"T","F")

and combine with datediff to find the year old date

iif(mydate=datediff("yyyy",-1,dmax("myDate","myTable")),"T","F")

or

iif(mydate=datediff("yyyy",-1,(SELECT max myDate from myTable)),"T","F")