Solved

DLookup with multiple Criterea-- Error

Posted on 2011-03-12
18
258 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

758 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

18 Experts available now in Live!

Get 1:1 Help Now