DLookup with multiple Criterea-- Error

I am trying to do a DLookup with Multiple criterea. when I run the code I get :  Run Time Error '13':    Type Mismatch  Attached is the coding that I used.  All the criterea are Text.  the lookup is numeric.  Thanks
Dim strWhere As String
     strWhere = ("PolType='" & Forms!ContactMainF!PolicyF.Form!PolicyTypeCombo & "'") And ("CarrierCode='" & Forms!ContactMainF!PolicyF.Form!BillingIDCombo & "'") And ("SubCompanyCode = '" & Forms!ContactMainF!PolicyF!Form.CarrierCombo & "'")
     Percent = DLookup("Comm", "SubCompanyDataT", strWhere)
End Sub

Open in new window

ml-sdAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ml-sdConnect With a Mentor Author Commented:
After alot of searching, I found the correct syntax:

strWhere = "[PolType]= '" & Forms!ContactMainF!PolicyF!PolicyTypeCombo & "' And [CarrierCode] = '" & Forms!ContactMainF!PolicyF!BillingIDCombo & "' And [SubCompanyCode] = '" & Forms!ContactMainF!PolicyF!CarrierCombo & "'"

The above syntax allowed  me to use 3 criterea for my dlookup    

Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:

     Dim strWhere As String
     strWhere = ("[PolType]=" & Chr(34) & Forms!ContactMainF!PolicyF.Form!PolicyTypeCombo & Chr(34) & " And [CarrierCode]=" & Chr(34) & Forms!ContactMainF!PolicyF.Form!BillingIDCombo & Chr(34) & " And [SubCompanyCode] = " & Chr(34) & Forms!ContactMainF!PolicyF!Form.CarrierCombo & Chr(34))
     Percent = DLookup("Comm", "SubCompanyDataT", strWhere)

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are [PolType], [CarrierCode] and [SubCompanyCode] all Text datatypes Fields in your tables? You mention this, but double check to be sure? If they're not, they don't need to be surrounded with quotes (single or double).

If they are, and if mx's suggestion doesn't fix things, then is your Percent variable declared as a Numeric datatype?

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Gustav BrockCIOCommented:
Try with:

Dim strWhere As String
     strWhere = Chr(34) & "PolType='" & Forms!ContactMainF!PolicyF.Form!PolicyTypeCombo & "' And CarrierCode='" & Forms!ContactMainF!PolicyF.Form!BillingIDCombo & "' And  SubCompanyCode = '" & Forms!ContactMainF!PolicyF!Form.CarrierCombo & "'" & Chr(34)
     Percent = DLookup("Comm", "SubCompanyDataT", strWhere)
End Sub

Open in new window


/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, I didn't notice at first that you are apparently dealing with Subforms:

Forms!ContactMainF!PolicyF.Form!PolicyTypeCombo

If so, make sure that you're correctly referring to the Subform CONTROL on the ContactMainF Form. This is the CONTROL that hosts your Subform, and may or may not be named the same as the Form you're using as a Subform.

To determine the name, open the ContactMainF in Design view. Click ONCE on the Subform, then examine the Properties dialog for this item. If you see a property named "SourceObject", and if there are ONLY 2 events for this control (Enter and Exit events), then you've got the correct control, and the value in the Name property for the control should be used when referring to the Subform.
0
 
ml-sdAuthor Commented:
I tried both solutions and recieve the same error:

Run Time Error "2465": Contact cant find the field "Form" referred to in your expression  ??
0
 
Gustav BrockCIOCommented:
> I tried both solutions and recieve the same error ...

If so, listen to Scott.

/gustav
0
 
ml-sdAuthor Commented:
I checked Scott's answer, the subform is linked to the master. If it wasn't wouldn't I get a different error? Like something not found. This is type mismatch?
0
 
Gustav BrockCIOCommented:
That's not what Scott wrote about. He mentions that it looks like PolicyF is the name of your subform and not the name of the control on the main form holding the subform - and how to check that.

/gustav
0
 
ml-sdAuthor Commented:
The name of the main form is: ContactMainF
subform:  PolicyF
control:  PolicyTypeCombo
Does this change the strWhere statement at all ??
0
 
Gustav BrockCIOCommented:
PolicyTypeCombo is the name of the combobox.

If PolicyF is the name of the subform, you need to replace that with the name of the subformcontrol on the main form holding the subform:

Forms!ContactMainF!<replace with name of the subformcontrol on main form>.Form!PolicyTypeCombo

/gustav
0
 
ml-sdAuthor Commented:
I did what Scott had suggested. for the subform control.  It is  PolicyF..  I tried removing 2 of the criterea and I got a match.  As soon as I added the 2nd criterea back with AND I got the error.  Could that be the problem?

btw..  Thank you so much for the help so far.
Mike
0
 
Gustav BrockCIOCommented:
OK, then I guess your CarrierCode is numeric, thus remove the single quotes:
Dim strWhere As String
     strWhere = Chr(34) & "PolType='" & Forms!ContactMainF!PolicyF.Form!PolicyTypeCombo & "' And CarrierCode=" & Forms!ContactMainF!PolicyF.Form!BillingIDCombo & " And  SubCompanyCode = '" & Forms!ContactMainF!PolicyF!Form.CarrierCombo & "'" & Chr(34)
     Percent = DLookup("Comm", "SubCompanyDataT", strWhere)
End Sub

Open in new window


Perhaps you need the same procedure for SubCompanyCode?

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
RE: "and the value in the Name property for the control should be used when referring to the Subform."
"If PolicyF is the name of the subform, you need to replace that with the name of the subformcontrol on the main form holding the subform:"

Of course, by Default ... if you use the Wizard to create a subform, the Name property ends up being the same as the Source Object, unless you ... the developer explicitly changes that.  So, when the Subform Control Name is the same as the Source Object, there is no specific issue in this case.  You can easily test this.  Now ... IF, the Name property is different - for whatever reason, then of course, you *must* refer to the Name property in expressions similar to the one above.
I *always* give the Name property a different values than the Source Object value ... something like fsub1 or fsubSomeNameSimilarToTheSourceObject.  This way for me, there is no confusion.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
@ml-sd:

Relative to the screen shot below, please indicate the values of the Name and SourceObject in your case.

Also, what are the Data Types (Text, Numeric, Date/Time,etc) ... in your table ... of the following:
PolType
CarrierCode
SubCompanyCode

mx
Capture1.gif
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your syntax is not correct. It might be working now, but it WILL break at some point.

You MUST refer to the Form object of your Subform control:

 Forms!ContactMainF!PolicyF.Form!BillingIDCombo

0
 
ml-sdAuthor Commented:
I found the solution by searching on the internet
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"You MUST refer to the Form object of your Subform control:"
Well, actually ...as Leigh Purvis (I think) pointed out some time ago ... if you use the bang notation, Form is not required:

Forms!ContactMainF!PolicyF!BillingIDCombo
                                             ^

However, if you use the Dot notation, the Form is required because it will not compile otherwise

Forms!ContactMainF!PolicyF.BillingIDCombo   ' will not compile
                                            ^
Forms!ContactMainF!PolicyF.Form.BillingIDCombo  ' Will compile and will always work
                                                     ^
Forms!ContactMainF!PolicyF.Form!BillingIDCombo  ' Will compile and will always work
                                                     ^                        
Consequent, that is why the OP's code does work.

I prefer to use the Form object just to be more explicit.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.