Dlookup With Dates Not Working

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
73
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).

VBA Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
482
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Instead of a DLookup() this might be easier...

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,233
Office Version
  1. 365
Platform
  1. Windows
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:
 

Forum statistics

Threads
1,141,069
Messages
5,704,105
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top