Multiple Criteria in Where Clause

[Chip]

I'm trying to open a form based on two criteria. I get the missing operator
error.
Thr code I am using is:
Dim PolicyType As String, PolicyYear As Integer
Dim stDocName As String
Dim stLinkCriteria As String
PolicyType = InputBox("What type: Dental, Medical, Life, LTD or STD?")
PolicyYear = InputBox("What 4 digit Year?")
stLinkCriteria = "[CustomerNumber]=" & Me![CustomerNumber] _
& " And [EffectiveDate] Like */*/" & PolicyYear
Select Case PolicyType
Case Is = "Dental"
stDocName = "sfmCustomerDentalPolicies"
Case Is = "Life"
stDocName = "sfmCustomerLifePolicies"
Case Is = "LTD"
stDocName = "sfmCustomerLTDPolicies"
Case Is = "Medical"
stDocName = "sfmCustomerMedPolicies"
Case Is = "STD"
stDocName = "sfmCustomerSTDPolicies"
End Select
DoCmd.OpenForm stDocName, , , stLinkCriteria

Customer Number is an autonumber.
It works fine with just customer number.

 

[Jeff Boyce]

Chip

Revisit how you are looking for [EffectiveDate]. Even though a date/time
field in Access LOOKS like it holds "x/y/z", that's just formatting/display.

I believe you want to check to see if the Year([EffectiveDate]) =
[PolicyYear].

(that assumes compatible data type in [PolicyYear])

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Chip]

Jeff,
Thanks. I had tried using DatePart function but that would also hiccup. This
works perfectly.
chip

Chip

Revisit how you are looking for [EffectiveDate]. Even though a date/time
field in Access LOOKS like it holds "x/y/z", that's just formatting/display.

I believe you want to check to see if the Year([EffectiveDate]) =
[PolicyYear].

(that assumes compatible data type in [PolicyYear])

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I'm trying to open a form based on two criteria. I get the missing operator
error.
Thr code I am using is:
Dim PolicyType As String, PolicyYear As Integer
Dim stDocName As String
Dim stLinkCriteria As String
PolicyType = InputBox("What type: Dental, Medical, Life, LTD or STD?")
PolicyYear = InputBox("What 4 digit Year?")
stLinkCriteria = "[CustomerNumber]=" & Me![CustomerNumber] _
& " And [EffectiveDate] Like */*/" & PolicyYear
Select Case PolicyType
Case Is = "Dental"
stDocName = "sfmCustomerDentalPolicies"
Case Is = "Life"
stDocName = "sfmCustomerLifePolicies"
Case Is = "LTD"
stDocName = "sfmCustomerLTDPolicies"
Case Is = "Medical"
stDocName = "sfmCustomerMedPolicies"
Case Is = "STD"
stDocName = "sfmCustomerSTDPolicies"
End Select
DoCmd.OpenForm stDocName, , , stLinkCriteria

Customer Number is an autonumber.
It works fine with just customer number.