what is the access equivalent to excel's vlookup function

I have not been able to make Dlookup work as I understand that it returns the value from a specific "named" field. such as:
=DLookUp ("CurrentValue","myTable","[LookupField]='1 FOUR AP1')
so I thought there must be something else.  

How does Dim VarX As Variant work?  I want to return the value this is 1 column to the right of the lookup field (this is a date so it changes) or the second and so on.

I am looking to populate based on date most recent, most recent less 1, etc.  

Here is a picture of my query.

I want the 1st lookup to be 4/2, the 2nd to be 4/1, the 3rd to be 3/31.  The query is date > tmax date -7 so the field names will change daily.

I hope this helps
Untitled.jpg

=DLookUp ("CurrentValue","myTable","[LookupField]='1 FOUR AP1' ")  missing " char on the end of the  string

How does Dim VarX As Variant work?  - This variable can be any type of data

Thanks, I miss typed.  Do I need to post a related question to get back to my original concern?  How do I tell it to look to the field 1 position to the right or 3 positions to the right of my lookup field?  since the field name (in this case  4/2, 4/1, 3/31, 3/30) changes each day. or perhaps I cannot...

Thanks. It seems I will need to post a related question as it looks as though I will continually have to define the fields as my dates will change daily, but the positions do not.