Access VB code - Dlookup with multiple criteria

Question

I have a form with a text box that I want populated based on the selections made in two other cbo boxes on that form.

Note - these two fields already use a waterfall type query that if you select a specific type - it will only show the dates associated with that type.

So the objective is to put the MeetDateType that is associated with the specific Meeting Type and Meeting Date. 

There is a table (tblMtgDates) that contain these same fields.

This is the Dlookup I created - but it's not working. 

Also, not sure if I should be it on the last cbo field I populate with the AfterUpdate option, or if I should make the dlookup the Control Source of the text box.  But if they change the date, I want the textbox to update as well.

Me.Text24 = DLookup("[MeetDateType]", "tblMtgDates", "[MeetingDates] =" & [Forms]![frmAttendance]![cboMtgDate] And "[MeetType] = " & [Forms]![frmAttendance]![cboMtgType])

I'm sure it's something of a 'DOH' I'm just overlooking.  Thank you for your help.

Answer 1

You haven't provided the data types of the columns/fields which is very critical to Domain Aggregate functions. Assuming MeetingDates is a date/time and MeetType is numeric try:

Me.Text24 = DLookup("[MeetDateType]", "tblMtgDates", "[MeetingDates] =#" & [Forms]![frmAttendance]![cboMtgDate] & "# And [MeetType] = " & [Forms]![frmAttendance]![cboMtgType])

If MeetType is text:

Me.Text24 = DLookup("[MeetDateType]", "tblMtgDates", "[MeetingDates] =#" & [Forms]![frmAttendance]![cboMtgDate] & "# And [MeetType] = """ & [Forms]![frmAttendance]![cboMtgType] & """")

Answer 2

sandra142,

Interesting link with some good content particularly the ConcatenateFieldValues() function that looks like it might have been "lifted" and modified slightly from my Tek-Tips FAQ.

Answer 3

As the discussion is about Access VB code so, I thought I can also contribute some information regarding this. As I too don’t have much knowledge about access VBA CODE but after reading this post…. something new and informative I came to know. So, I am feeling glad to share with u all.

Learn All About Microsoft Access VBA Code – VBA Functions, Shortcuts And Much More…!

If you are having such informative guide regarding VBA CODE then do share, i an curiously waiting for your response.

Answer 4

See, I told you I thought it was a DOH moment.  Sometimes you look at things so long your eyes get blurry.

And I did put it on the AfterUpdate of the cboMtgDate field.  Works great!  Thank you so very much!!!