Using Dlookup in VBA

[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")

 

[Douglas J. Steele]

Your syntax is incorrect.

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

 

[mcescher]

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")

Try this (untested)

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

Hope it helps,
Chris M.

 

[Daniel Pineault]

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.

 

[David C. Holley]

Why aren't you just joining the tables and using the SELECT statement to
pull the value?