Multiple Criteria in Where Clause


G

Guest

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.
 
Ad

Advertisements

J

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/
 
Ad

Advertisements

G

Guest

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

Jeff Boyce said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top