Dlookup With Dates Not Working

[MCTampa]

I have a text box called Current that populates todays date on load.
I want the next text two text boxes (Prior and Y2) to populate the same day prior year and the same day two year's ago by looking up those dates off of a table.
I don't want to do date()-365 and date()-728 because I want the user to be able to select a different date in the Current box.

On button click I want Prior and Y2 to update.

I'm using the following sub (trying to solve for Prior for the moment).

Private Sub Command7_Click()
Prior = DLookup("[Year1]", "Dates", "#" & Format(Forms!Form1![Current], "mm\/dd\/yyyy\") & "#")
End Sub

My "Dates" table runs from 1/1/2021 through 12/31/2021 and has two columns Year1 and Year2 which run from 1/3/2020 and 1/4/2019.

Today, the current box populates with 5/25/2021 and when I press my button, I only get 1/3/2020 for my Prior box.

Not sure what I'm doing wrong with the formatting, but evidently something is amiss.

Thanks,
Mike

 

[JonXL]

Instead of a DLookup() this might be easier...

Prior = DateSerial(Year(Forms!Form1![Current]) - 1, Month(Forms!Form1![Current]), Day(Forms!Form1![Current]))

 

[Micron]

Or DateAdd function?
Me.Prior = DateAdd("yyyy", -1, Me.Current) ? Not sure I see the need for a table of dates unless you want to avoid dates that are weekends or holidays. That is a different matter.

Current is not a name I would use for anything:

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

www.allenbrowne.com