DLookup confusion

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a form in which data is viewed only coming in from
a table to a textbox. Below is the dlookup that I did but
no matter what I do, it continues to tell me there is a
operator that's out of whack or missing. Below is the way
it pasted in but it is a straight line on the app. There
are 3 criteria (subprocess, component, clientcode) that
need to match to bring in the correct date
(ActualCompletionDate) from the table. I've tried quotes,
double quotes, etc. There will be several more dlookups
to use on the form as well that will be similar to this
one.

usrfrmPRAProjectTimeTracking.txtInternalMeetingActualComple
tionDate = DLookup("[ActualCompletionDate]",
"usrtblPRAProjectRiskAssessmentDetail",
"[SubProcess] = 'Implementation Meeting'",
& "[Component]= 'Internal Meeting'",
& "[ClientCode]= Forms![usrfrmPRAProjectTimeTracking]!
[ClientCode]")

Can any see what the error of my ways are in this?
Thanks in advance for any assistace.
*** John
 
Hi John,

The conditional part of the dlookup must all fit into one string - it is the
3rd parameter. You had yours listed as the 3rd, 4th and 5th parameters
(which don't exist). As a simple example the function call should look like
this:

x=dlookup("MyField","MyQuery","fld1='a' AND fld2='b' AND fld3='c'")

Where the 3rd parameter is the string:
"fld1='a' AND fld2='b' AND fld3='c'"

Notice that each part of the condition must be connected with a boolean
operator - AND in this case if you want all of the conditions to be met. So,
your function call would become the following (note that I used line
continuations to break the call into a somewhat more readable format - the
newsreader probably still mangle it):

usrfrmPRAProjectTimeTracking.txtInternalMeetingActualCompletionDate =
DLookup("[ActualCompletionDate]", _
"usrtblPRAProjectRiskAssessmentDetail", _
"[SubProcess] = 'Implementation Meeting'" & _
" AND [Component]= 'Internal Meeting'" & _
" AND [ClientCode]= " &
Forms![usrfrmPRAProjectTimeTracking]![ClientCode])

If the third field, ClientCode, is text then this should be changed to embed
additional quotes that remain in the string after it is processed by VBA to
concatenate in the value from the referenced form control:

usrfrmPRAProjectTimeTracking.txtInternalMeetingActualCompletionDate =
DLookup("[ActualCompletionDate]", _
"usrtblPRAProjectRiskAssessmentDetail", _
"[SubProcess] = 'Implementation Meeting'" & _
" AND [Component]= 'Internal Meeting'" & _
" AND [ClientCode]= """ &
Forms![usrfrmPRAProjectTimeTracking]![ClientCode] & """")
 
Sandra, thank you for the clarification.
*** John

-----Original Message-----
Hi John,

The conditional part of the dlookup must all fit into one string - it is the
3rd parameter. You had yours listed as the 3rd, 4th and 5th parameters
(which don't exist). As a simple example the function call should look like
this:

x=dlookup("MyField","MyQuery","fld1='a' AND fld2='b' AND fld3='c'")

Where the 3rd parameter is the string:
"fld1='a' AND fld2='b' AND fld3='c'"

Notice that each part of the condition must be connected with a boolean
operator - AND in this case if you want all of the conditions to be met. So,
your function call would become the following (note that I used line
continuations to break the call into a somewhat more readable format - the
newsreader probably still mangle it):

usrfrmPRAProjectTimeTracking.txtInternalMeetingActualComp letionDate =
DLookup("[ActualCompletionDate]", _
"usrtblPRAProjectRiskAssessmentDetail", _
"[SubProcess] = 'Implementation Meeting'" & _
" AND [Component]= 'Internal Meeting'" & _
" AND [ClientCode]= " &
Forms![usrfrmPRAProjectTimeTracking]![ClientCode])

If the third field, ClientCode, is text then this should be changed to embed
additional quotes that remain in the string after it is processed by VBA to
concatenate in the value from the referenced form control:

usrfrmPRAProjectTimeTracking.txtInternalMeetingActualComp letionDate =
DLookup("[ActualCompletionDate]", _
"usrtblPRAProjectRiskAssessmentDetail", _
"[SubProcess] = 'Implementation Meeting'" & _
" AND [Component]= 'Internal Meeting'" & _
" AND [ClientCode]= """ &
Forms![usrfrmPRAProjectTimeTracking]![ClientCode] & """")


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a form in which data is viewed only coming in from
a table to a textbox. Below is the dlookup that I did but
no matter what I do, it continues to tell me there is a
operator that's out of whack or missing. Below is the way
it pasted in but it is a straight line on the app. There
are 3 criteria (subprocess, component, clientcode) that
need to match to bring in the correct date
(ActualCompletionDate) from the table. I've tried quotes,
double quotes, etc. There will be several more dlookups
to use on the form as well that will be similar to this
one.
Click to expand...
usrfrmPRAProjectTimeTracking.txtInternalMeetingActualCompl
e
tionDate = DLookup("[ActualCompletionDate]",
"usrtblPRAProjectRiskAssessmentDetail",
"[SubProcess] = 'Implementation Meeting'",
& "[Component]= 'Internal Meeting'",
& "[ClientCode]= Forms![usrfrmPRAProjectTimeTracking]!
[ClientCode]")

Can any see what the error of my ways are in this?
Thanks in advance for any assistace.
*** John
Click to expand...

.
Click to expand...
 
Back
Top