Using Dlookup in VBA

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

Hello. I originally posted this as a Report question but was suggested that
I use VBA instead. Either way I can't seem to get my report field
(txtAsterick) in my Detail Section to show the results from my code.

Dim strFullName As String
strFullName = "tblMasterProviderDataHistory_Data.[Last Name]" & ", " &
"tblMasterProviderDataHistory_Data.[First Name]"

Me.txtAsterick1 = DLookup("PROV_UHC_CAT_NUM",
"tblMasterProviderDataHistory_Data", "FiscalYearInd=2010" And "strFullName =
me.ProviderName")
 
Your syntax is incorrect.

Me.txtAsterick1 = DLookup("PROV_UHC_CAT_NUM",
"tblMasterProviderDataHistory_Data", "FiscalYearInd=2010 And strFullName =
""" & me.ProviderName & """")
 
Hello.  I originally posted this as a Report question but was suggestedthat
I use VBA instead.  Either way I can't seem to get my report field
(txtAsterick) in my Detail Section to show the results from my code.

Dim strFullName As String
strFullName = "tblMasterProviderDataHistory_Data.[Last Name]" & ", " &
"tblMasterProviderDataHistory_Data.[First Name]"

Me.txtAsterick1 = DLookup("PROV_UHC_CAT_NUM",
"tblMasterProviderDataHistory_Data", "FiscalYearInd=2010" And "strFullName =
me.ProviderName")
Click to expand...

Try this (untested)

Me.txtAsterick1 = DLookup("[PROV_UHC_CAT_NUM]", _
"tblMasterProviderDataHistory_Data", _
"FiscalYearInd=2010 AND " & strFullName & " = '" &
Me.ProviderName & "' ")

Hope it helps,
Chris M.
 
Your DLookup criteria is wrong, incoherant.

Me.txtAsterick1 = DLookup("PROV_UHC_CAT_NUM",
"tblMasterProviderDataHistory_Data", "FiscalYearInd=2010" And "strFullName =
me.ProviderName")

The Criteria is to be written as it would in a WHERE SQL Clause, so if we
look at yours
"FiscalYearInd=2010" And "strFullName = me.ProviderName"

We first have to fix some basic synthax

"FiscalYearInd=2010 And strFullName ='" & me.ProviderName & "'"

***Note you have to enclose string values in ''

Lastly, if I understand your code properly you are trying to match up a
single control's value with a concatenation of several table fields?! Things
get a little more complex. You would need to split your form's control value
into their relating parts so you could do a comparison. So your code would
become something more like:

Dim strFullName As String
strFirstName = left(me.ProviderName,instr(me.ProviderName," ")-1)
strLastName = Mid(me.ProviderName,instr(me.ProviderName," ")+1);

Me.txtAsterick1 = DLookup("PROV_UHC_CAT_NUM",
"tblMasterProviderDataHistory_Data", "[FiscalYearInd]=2010 And [First Name]
='" & strFirstName & "' AND [Last Name] ='" & strLastName & "'")

but of course, this is based on my understanding your code correctly.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Why aren't you just joining the tables and using the SELECT statement to
pull the value?
 
Back
Top