Struggling with DLookup

[fitshase]

Hi all, I'm hoping you can help before a hole appears in my wall....:banghead:

I am trying to use the DLookup function in a form. I have a few unbound boxes which have calculations in them to show dates based on another field. Now I need to have a DLookup to pull a value associated with that date into another field.

For example:-
Freqency = "3" (years)
Month 1 = "01/01/2016"
Month 2 = (unbound with calculation): =DateAdd("yyyy",[Frequency],[Month1])
Month 3 = (unbound with calculation): =DateAdd("yyyy",[Frequency],[Month2])

Now, I have a separate table (tblSalesTotals) with a list of dates and a value associated with that date (i.e., 2 columns, "SalesMonthDate", "SalesMonthTotalValue"). The date is the last date of the month and the value is the total quantity for that month. None of the fields are linked to the main database tables.

I want to be able to have:-

MonthTotal = (look up "Month1" date in tblSalesTotals against "SalesMonthDate" and return the corresponding value from the "SalesMonthTotalValue" column).

This would let me add a date in "Month1" and then autopopulate the other fields to show the same sales totals in that month in the previous x years (depending on the value of "Frequency").

I cannot seem to get it to work and cannot understand the DLookup function at all! I can do it quite easily in Excel using VLookup but can't in Access.

Is anyone able to help?

 

[Ranman256]

Dlookup([field2return],[table/query], [where clause])

X= Dlookup("[salesmonthTotalValue]","tblSalesTotals","[salesMonthDate]=#" & me.month1 & "#")

 

[plog]

I cannot seem to get it to work and cannot understand the DLookup function at all!

That's really the only part of your post I was able to follow. So, here's the documentation on DLookup: http://www.techonthenet.com/access/functions/domain/dlookup.php

If you would like to post your Dlookup I could help debug it as well. Be sure to include the code and any error messages you are getting with it.

 

[fitshase]

Best way I can describe it is visually (see below/attached)

Ranman256 - thanks for the formula but it brings up a blank field (no error). I've changed the headers and table name to make them a bit shorter (i.e., easier for me to remember!).

 

[Minty]

Try removing the me. reference if you are doing this in the controls ControlSource.

 

[fitshase]

Hi Minty,

Thanks - just tried that but the same thing happens - no value comes into the box.

=DLookUp("[SalesTotal]","tblSalesData","[SalesDate]=#" & [SalesDate1] & "#")

 

[fitshase]

I seem to have done it with a bit of fiddling around with the formula.

I removed the # symbols and it works a treat. Thanks all for the help.

=DLookUp("[SalesTotal]","tblSalesData","[SalesDate]=[SalesDate1]")