Solved

DLookup with multiple Criterea-- Error

Posted on 2011-03-12
18
261 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 49

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

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 49

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now