Dlookup with multiple criteria that may change

[Angel69]

Hi,

I have a table named z_ResltsSampleCountMonthly. In that table I have fields SampleSize, Month and LOB.

I have a report that I need to use a Dlookup in order to pull the SampleSize by LOB into my report. However, my report has three columns that change based on the start and end dates for the report. It shows current month plus the two prior months to show a trend.

So I have each column heading update automatically based on the dates the user enters into the start and end date on the main menu form.

If they select 12/1/14 to 12/31/14 the three columns heading would update to October 2014, November 2014, and December 2014. In the table I have the data for all three months so when I use this formula it works but it's putting September 2014 data under October 2014 so I need to have multiple criteria; one being the LOB and the other being the column heading which is equal to the field Month in the table.

=DLookUp("[SampleSize]","z_ResultsSampleCountMonthly","[LOB] = ""CMES""")

This is what I tried to do to add the second criteria to pull based on text59 being equal to the Month field in the table but it's not working:

[SIZE=3][FONT=Calibri]=DLookUp("[SampleSize]","z_ResultsSampleCountMonthly","[LOB] ="CMES" And [Month] = ' " & [Text59] & " ' ")[/FONT][/SIZE]

Any help would be greatly appreciated!!

Thanks!

 

[Angel69]

Some additional information I just realized. Text59 is based on formula

=DateAdd("m",-2,[Forms]![Main Menu]![EndDate])

. When I deleted my Dlookup formula and replace it with =[Text59] to see what the value in that field is I get 10/31/2014 although the Month field value is October 2014 in the table.

 

[smig]

You must use US date format
mm/dd/yyyy

Format([myDate], #mm\/dd\/yyyy#)