?
Solved

DLookup with multiple Criterea-- Error

Posted on 2011-03-12
18
Medium Priority
?
281 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:ml-sd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 75
ID: 35115050
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
 
LVL 85
ID: 35115618
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35115700
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 85
ID: 35115794
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
 

Author Comment

by:ml-sd
ID: 35115796
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35115944
> I tried both solutions and recieve the same error ...

If so, listen to Scott.

/gustav
0
 

Author Comment

by:ml-sd
ID: 35116163
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35116176
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
 

Author Comment

by:ml-sd
ID: 35116791
The name of the main form is: ContactMainF
subform:  PolicyF
control:  PolicyTypeCombo
Does this change the strWhere statement at all ??
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35116842
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
 

Author Comment

by:ml-sd
ID: 35116959
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35116996
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
 
LVL 75
ID: 35117559
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
 
LVL 75
ID: 35117661
@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
 

Accepted Solution

by:
ml-sd earned 0 total points
ID: 35118097
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
 
LVL 85
ID: 35223240
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
 

Author Closing Comment

by:ml-sd
ID: 35252732
I found the solution by searching on the internet
0
 
LVL 75
ID: 35260770
"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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question