Search for 2 different field criterea in VB command

[tcheck]

I have a simple question. I would like to open a sub form using 2 different fields within the open form. I have the following command with an on click;



Private Sub Label27_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.OpenForm "frm_View_ActivityID", , , "[Mgr ID] = '" & Me![Mgr ID] & "'"
End Sub

This works fine but I would also like to use a date field added to the command but I can't seem to get it correct. I want to open it with Me!MGR ID and Me!AcctDate

I tried
Private Sub Label27_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.OpenForm "frm_View_ActivityID", , , "[Mgr ID] = '" & Me![Mgr ID] & "'" & "[ActDate] = '" & Me![ActDate] & "'"
End Sub

With no luck. Thanks for your help in advance.

[pbaldy]

You need the word " And " between the 2 criteria, and date values need to be surrounded by # rather than '.

[tcheck]

I tried
DoCmd.OpenForm "frm_View_ActivityID", , , "[Mgr ID] = '" & Me![Mgr ID] & "'" AND "#[Actdate]# =" & Me!"#[ActDate]#"

AndI am still getting errors.

[pbaldy]

The # would be positioned like the ' are around the text value, and like the text value, only around the value, not the field name. You are also closing off the string before the word AND, which leaves it out in the middle of nowhere.

[tcheck]

Can you give me an example?

[pbaldy]

Check out the multi-criteria example at the bottom:

http://www.mvps.org/access/general/gen0018.htm

The syntax of the wherecondition would be similar.

[tcheck]

Each one of the wher commands work independently fom one another, but when I I add them together such as;

DoCmd.OpenForm "frm_View_Activity", , , "[Mgr ID] = '" & Me![Mgr ID] & "'" And "[ActDate] = #" & Me![Act_date] & "#"

I get a VB error
Run-Time error '12':

Type mismatch

[pbaldy]

You're still closing the string off improperly. Try

DoCmd.OpenForm "frm_View_Activity", , , "[Mgr ID] = '" & Me![Mgr ID] & "' And [ActDate] = #" & Me![Act_date] & "#"

[tcheck]

That was it. Thank you very much. Have a great weekend.