Solved

DLookup with multiple Criterea-- Error

Posted on 2011-03-12
18
271 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
  • 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 84
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 50

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
Independent Software Vendors: 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!

 
LVL 84
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 50

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 50

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 50

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 50

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 84
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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