Open Form Macro with 3 WHERE Conditions

[BPERELLA]

I am trying to open a Form using a command button on an exiting form. One condition is based on a date field, one on a text field and the other on a text field saved from an option group. Not sure whether to use the looked-up text or the numeric value for the option group field value. Using the wizard, gives me the first condition:

="[pp_end_date]=" & "#" & [pp_end_date] & "#"

text field =[Employee ID]

option group field = [Pay Type] 1="State", 2="Osha"

Don't know the syntax for 1 never mind 3 conditions. Any help is appreciated.

Thanks

 

[June7]

I don't use macros, only VBA.

Strongly advise not to use spaces in naming convention.

What value is actually saved into [Pay Type] - number?

="[pp_end_date]=#" & [pp_end_date] & "# AND [Employee ID] = " & [Employee ID] & " AND [Pay Type]=" & [Pay Type]

This requires input into all 3 criteria.

 

[BPERELLA]

The table shows text, but to get a conditional format to work I had to use the number, 1 or 2 for State or OSHA.

 

[June7]

If field is saving 1 or 2 why is it a text field?

What is name of Option Group control? Use that in the criteria.

Recommend not building lookups in table.

 

[oleronesoftwares]

The table shows text, but to get a conditional format to work I had to use the number, 1 or 2 for State or OSHA.

Option group in Access uses numbers as values.

Look at the link below, it contains the official documentation from Microsoft

https://support.microsoft.com/en-us/office/optionvalue-property-d54830a1-ece1-4367-ac9f-eb7a0752482e

 

[BPERELLA]

When I entered the syntax offered by June7, I got the error "Data Type mismatch in criteria expression"

="[pp_end_date]=#" & [pp_end_date] & "# AND [Employee ID] = " & [Employee ID] & " AND [Pay Type]=" & [Pay Type]


Looking at the Macro error window, the argument is:

Comments 21, Form, , [pp_end_date]=#5/15/2021# AND [EMPLOYEE_NUMBER] = 10000 AND [Pay Type]=1, , Normal

These are the correct values. {EMPLOYEE_NUMBER] is a text field. I think the mismatch must be on [Pay Type]. It's reading the Option Group number of 1.

The underlying query dhows it as 1. What do you think is causing the mismatch error?

 

[June7]

If it is a text field, need apostrophe delimiters. Similar to the # delimiter for dates. Sorry, didn't notice that in OP. Is the field Employee ID or Employee_Number?

="[pp_end_date]=#" & [pp_end_date] & "# AND [Employee ID] = '" & [Employee ID] & "' AND [Pay Type]=" & [Pay Type]

 

[BPERELLA]

="[pp_end_date]=#" & [pp_end_date] & "# AND [Employee ID] = '" & [Employee ID] & "' AND [Pay Type]=" & [Pay Type]

My mistake it is Employee_Number. Same mismatch error after adding single quotes as you suggested.

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[BPERELLA]

Thanks for your offer June7. Just too much confidential data to clean out. If [Pay_type] were text how would that change the expression?

="[pp_end_date]=#" & [pp_end_date] & "# AND [Employee ID] = '" & [Employee ID] & "' AND [Pay Type]=" & [Pay Type]

 

[June7]

Apostrophe delimiters, same as with [Employee ID].
='" & [Pay Type] & "'"

 

[BPERELLA]

That did it! Seems like because there is a lookup to convert the Option Group number, It seems to be saving the text. Thanks June7!!