dlookup Criteria

  • Thread starter Thread starter Jason Frazer
  • Start date Start date
J

Jason Frazer

I have a macro that uses the dlookup command to look up
tax rate and juristiction code. Can i use more that one
Criteria in the dlookuo command. You can see the 3
criteria I have set below.

Thanks for your help

Jason Frazer

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click
Dim strCriteria As String
Dim strTaxField As String
Dim strJurField As String
Dim JurCode As String
Dim Cnty As String
Dim curTaxRate As Currency
Dim cosTaxRate As Currency
Dim chkInsideCityLimits As Variant
Dim strShipCityField As String
Dim strCountyField As String

Rem DoCmd.RunCommand acCmdSaveRecord
cosTaxRate = [Customer Sales Tax Rate]
strShipCityField = [Ship City]
strCountyField = [County]
chkInsideCityLimits = [InsideCityLimits]
If chkInsideCityLimits Then
strTaxField = "InsideCityLimitsTaxRate"
strJurField = "InsideCityLimitsJurisdictionCode"
Else
strTaxField = "OutsideCityLimitsTaxRate"
strJurField = "OutsideCityLimitsJurisdictionCode"
End If

strCriteria1 = "ZipCode='" & [PostalCode] & " " & "'"
strCriteria2 = "City='" & [Ship City] & " " & "'"
strCriteria3 = "County='" & [County] & " " & "'"

curTaxRate = DLookup(strTaxField, "Table_Tax_Rate",
strCriteria)
JurCode = DLookup(strJurField, "Table_Tax_Rate",
strCriteria)
Cnty = DLookup("County", "Table_Tax_Rate", strCriteria)

If cosTaxRate <> curTaxRate Then
MsgBox "Customer Tax does not equal State assigned tax
CLARIFICATION NEEDED!"
End If

[Sales Tax Rate] = curTaxRate
[JurisdictionCode] = JurCode
[County] = Cnty

Me.Refresh
MsgBox "Tax Information was updated."

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command69_Click

End Sub
 
Jason said:
I have a macro that uses the dlookup command to look up
tax rate and juristiction code. Can i use more that one
Criteria in the dlookuo command. You can see the 3
criteria I have set below.

Thanks for your help

Jason Frazer

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click
Dim strCriteria As String
Dim strTaxField As String
Dim strJurField As String
Dim JurCode As String
Dim Cnty As String
Dim curTaxRate As Currency
Dim cosTaxRate As Currency
Dim chkInsideCityLimits As Variant
Dim strShipCityField As String
Dim strCountyField As String

Rem DoCmd.RunCommand acCmdSaveRecord
cosTaxRate = [Customer Sales Tax Rate]
strShipCityField = [Ship City]
strCountyField = [County]
chkInsideCityLimits = [InsideCityLimits]
If chkInsideCityLimits Then
strTaxField = "InsideCityLimitsTaxRate"
strJurField = "InsideCityLimitsJurisdictionCode"
Else
strTaxField = "OutsideCityLimitsTaxRate"
strJurField = "OutsideCityLimitsJurisdictionCode"
End If

strCriteria1 = "ZipCode='" & [PostalCode] & " " & "'"
strCriteria2 = "City='" & [Ship City] & " " & "'"
strCriteria3 = "County='" & [County] & " " & "'"

curTaxRate = DLookup(strTaxField, "Table_Tax_Rate",
strCriteria)
JurCode = DLookup(strJurField, "Table_Tax_Rate",
strCriteria)
Cnty = DLookup("County", "Table_Tax_Rate", strCriteria)
Click to expand...

You never declared strCriteria1, 2 or 3 and you never
assigned the combined criteria to strCriteria. When you
combine multiple criteria in a where clause, you have to
indicate if they're combined with And or Or operators. I
think this is what you want:

Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String

strCriteria = strCriteria1 & " And " & strCriteria2 & " And
" & strCriteria3
 
Okay, this is partially working... the dlookup runs
throught the if statement as though there is a record,
but it isn't finding the data in the "In Bin" field.
What am I doing wrong?! THANKS!

Dim strMindsetCrit As String
Dim strCustCrit As String
Dim strBin As String
Dim strGround As String
Dim strNext As String
Dim strCriteria As String
Dim strMindID As String
Dim strCustID As String

strMindID = Forms![frmMarketing Mindset Individual]!
[mindsetid]
strCustID = Me![txtCustomerID]

If (CurrentProject.AllForms("frmMarketing Mindset
Individual").IsLoaded = True) Then
strMindsetCrit = ("mindsetid= " & strMindID)
strCustCrit = ("companyid= " & strCustID)
strCriteria = strMindsetCrit & " And " & strCustCrit
Else
strMindsetCrit = ("mindsetid= '" & Forms!
[frmMarketing Mindset]![frmMarketing Mindset subform]!
[mindsetid])
strCustCrit = ("companyid= '" & Forms![frmMarketing
Mindset]![frmMarketing Mindset subform]![CustomerID])
strCriteria = strMindsetCrit & "And" & strCustCrit
End If

If Not IsNull(strBin = DLookup("[tblMindset Memo].[In
Bin]", "tblMindset Memo", strCriteria)) Then
Me![MemoBin].Visible = True
Me![MemoBin].Value = Me![In Bin] & strBin
Me![In Bin].Visible = False
Me![lblBin].Visible = True
Else
-----Original Message-----
Jason said:
I have a macro that uses the dlookup command to look up
tax rate and juristiction code. Can i use more that one
Criteria in the dlookuo command. You can see the 3
criteria I have set below.

Thanks for your help

Jason Frazer

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click
Dim strCriteria As String
Dim strTaxField As String
Dim strJurField As String
Dim JurCode As String
Dim Cnty As String
Dim curTaxRate As Currency
Dim cosTaxRate As Currency
Dim chkInsideCityLimits As Variant
Dim strShipCityField As String
Dim strCountyField As String

Rem DoCmd.RunCommand acCmdSaveRecord
cosTaxRate = [Customer Sales Tax Rate]
strShipCityField = [Ship City]
strCountyField = [County]
chkInsideCityLimits = [InsideCityLimits]
If chkInsideCityLimits Then
strTaxField = "InsideCityLimitsTaxRate"
strJurField = "InsideCityLimitsJurisdictionCode"
Else
strTaxField = "OutsideCityLimitsTaxRate"
strJurField = "OutsideCityLimitsJurisdictionCode"
End If

strCriteria1 = "ZipCode='" & [PostalCode] & " " & "'"
strCriteria2 = "City='" & [Ship City] & " " & "'"
strCriteria3 = "County='" & [County] & " " & "'"

curTaxRate = DLookup(strTaxField, "Table_Tax_Rate",
strCriteria)
JurCode = DLookup(strJurField, "Table_Tax_Rate",
strCriteria)
Cnty = DLookup("County", "Table_Tax_Rate", strCriteria)
Click to expand...

You never declared strCriteria1, 2 or 3 and you never
assigned the combined criteria to strCriteria. When you
combine multiple criteria in a where clause, you have to
indicate if they're combined with And or Or operators. I
think this is what you want:

Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String

strCriteria = strCriteria1 & " And " & strCriteria2 & " And
" & strCriteria3
Click to expand...
 
TM said:
Okay, this is partially working... the dlookup runs
throught the if statement as though there is a record,
but it isn't finding the data in the "In Bin" field.
What am I doing wrong?! THANKS!

Dim strMindsetCrit As String
Dim strCustCrit As String
Dim strBin As String
Click to expand...
Since DLookup returns Null if not found, strBin must be
declared As Variant

Dim strGround As String
Dim strNext As String
Dim strCriteria As String
Dim strMindID As String
Dim strCustID As String

strMindID = Forms![frmMarketing Mindset Individual]!
[mindsetid]
strCustID = Me![txtCustomerID]

If (CurrentProject.AllForms("frmMarketing Mindset
Individual").IsLoaded = True) Then
strMindsetCrit = ("mindsetid= " & strMindID)
strCustCrit = ("companyid= " & strCustID)
strCriteria = strMindsetCrit & " And " & strCustCrit
Else
strMindsetCrit = ("mindsetid= '" & Forms!
[frmMarketing Mindset]![frmMarketing Mindset subform]!
[mindsetid])
strCustCrit = ("companyid= '" & Forms![frmMarketing
Click to expand...
Extra quote above

Mindset]![frmMarketing Mindset subform]![CustomerID])
strCriteria = strMindsetCrit & "And" & strCustCrit
Click to expand...
Missing spaces before and after And

End If

If Not IsNull(strBin = DLookup("[tblMindset Memo].[In
Bin]", "tblMindset Memo", strCriteria)) Then
Click to expand...
VBA does not permit an assignment in the middle of an
expression. Write it as two statements:
strBin = DLookup("[tblMindset Memo].[In Bin]",
"tblMindset Memo", strCriteria)
If Not IsNull(strBin) Then

Me![MemoBin].Visible = True
Me![MemoBin].Value = Me![In Bin] & strBin
Me![In Bin].Visible = False
Me![lblBin].Visible = True
Click to expand...
 
Thank you.. but I'm still having the same problem. I am
understanding the function correctly - right?? It should
return the contents of the field, correct?? That's what
I need! I changed the type to "variant" and now the
contents of "[In Bin]" appears to be "empty" but when I
look at the table with that matching criteria, there is
data (text) in that field. So, it is recognizing the
criteria and that the criteria is being met... but not
the field. Other suggestions??

THANKS! I'm going to try taking the spaces out and see
if that helps.
-----Original Message-----
TM said:
Okay, this is partially working... the dlookup runs
throught the if statement as though there is a record,
but it isn't finding the data in the "In Bin" field.
What am I doing wrong?! THANKS!

Dim strMindsetCrit As String
Dim strCustCrit As String
Dim strBin As String
Click to expand...
Since DLookup returns Null if not found, strBin must be
declared As Variant

Dim strGround As String
Dim strNext As String
Dim strCriteria As String
Dim strMindID As String
Dim strCustID As String

strMindID = Forms![frmMarketing Mindset Individual]!
[mindsetid]
strCustID = Me![txtCustomerID]

If (CurrentProject.AllForms("frmMarketing Mindset
Individual").IsLoaded = True) Then
strMindsetCrit = ("mindsetid= " & strMindID)
strCustCrit = ("companyid= " & strCustID)
strCriteria = strMindsetCrit & " And " & strCustCrit
Else
strMindsetCrit = ("mindsetid= '" & Forms!
[frmMarketing Mindset]![frmMarketing Mindset subform]!
[mindsetid])
strCustCrit = ("companyid= '" & Forms![frmMarketing
Click to expand...
Extra quote above

Mindset]![frmMarketing Mindset subform]![CustomerID])
strCriteria = strMindsetCrit & "And" & strCustCrit
Click to expand...
Missing spaces before and after And
End If

If Not IsNull(strBin = DLookup("[tblMindset Memo].[In
Bin]", "tblMindset Memo", strCriteria)) Then
Click to expand...
VBA does not permit an assignment in the middle of an
expression. Write it as two statements:
strBin = DLookup("[tblMindset Memo].[In Bin]",
"tblMindset Memo", strCriteria)
If Not IsNull(strBin) Then

Me![MemoBin].Visible = True
Me![MemoBin].Value = Me![In Bin] & strBin
Me![In Bin].Visible = False
Me![lblBin].Visible = True
Click to expand...
Click to expand...
 
Back
Top