Please help with syntax

[mshaynerush]

I would like to use DLookUp to pull the month from the month field in a query. The query formats the month as full month, January, February, etc...



Here is my attempt. Can you please give me the proper syntax:

=DLookUp("[Total Verifications]","[qryTotalInspectionsByMonthYTD]","Format([Month],"mmm")=" &[Reports]![rptMonthlyVTeam]![hidDate] & ")

The report textbox hidDate has code in it that will format the current month as "mmm" : this is what drives hidDate.
=Format(Date(),"mmm")

the query i'm pulling from counts the amount of inspections in a given month and I need to pull that number into a text box within my report. For nothing else, i'd like to learn the syntax.

[June7]

If the field is text, use apostrophe delimiters on the criteria:

=DLookUp("[Total Verifications]","[qryTotalInspectionsByMonthYTD]","Format([Month],"mmm")='" & [Reports]![rptMonthlyVTeam]![hidDate] & "'")

If a date, use #, nothing for number.

Where are you using this expression? Why does it reference a control on report for input?

[mshaynerush]

I am creating a monthly report that will query the database using a 30 day range, so the number generated from this DLookUp will total the amount of inspections in a 30 day range. The domain of the DLookUp counts the inspections and groups by months for the year, so I'm attempting to grab only the month field from that query that matches the current month so that I don't have to use a drop down selection to run a report, i can just open the report from the main console of my database and have it ready to go. basically.

My problem, now that I look closer, is that the query returns the month already formatted as the whole month, "October" so I need to make the field = the same thing as my hidDate field, whose value is =Format(Date(),"mmmm") to update the month as the month changes.

Sorry for the confusion.

[mshaynerush]

Why can't I just make the control source the query itself? that doesn't seem to work.

[mshaynerush]

I'm sorry to bother you. Your answer did help quite a bit, actually, but this is what i used that works:

=DLookUp("[Total Verifications]","[qryTotalInspectionsByMonthYTD]","[Month]='" & [Reports]![rptMonthlyVTeam]![hidDate] & "'")

[Dal Jeanis]

Actually, we very much appreciate when the original poster posts the answer that worked. Thanks!