DLookup Problems continued...

17 views
Skip to first unread message

Patrick Goodyear

unread,
Sep 8, 2014, 8:51:19 PM9/8/14
to
Bob,

Thanks for the reply. UniquePath is a text field, however it is really
the conversion and concatenation of two numeric fields.

What could be wrong with this statement if all fields are numeric fields?

=DLookUp("NewTx_Frequency","tbl_Full_Paths", _
"RX_MW_SiteID = " & [Forms]![sfrm_Paths]![MW_Site_ID] _
And "MW_Site_ID = " & [Forms![sfrm_Paths]![RX_MW_Site_ID])

Kind Regards,

Patrick

John W. Vinson

unread,
Sep 9, 2014, 1:04:32 AM9/9/14
to
The third argument to DLookUp must be a valid query criterion. You need to put
the AND - part of the syntax of the SQL string WHERE clause - inside the quots
so the criterion becomes

RX_MW_SiteID = 123 And MW_Site_ID = 321

after it's expanded. Try

=DLookUp("NewTx_Frequency","tbl_Full_Paths", _
"RX_MW_SiteID = " & [Forms]![sfrm_Paths]![MW_Site_ID] _
" And MW_Site_ID = " & [Forms![sfrm_Paths]![RX_MW_Site_ID])

Note the blank after the quote and before the AND. It's important; otherwise
the concatenation would bive you

RX_MW_SiteID = 123And MW_Site_ID = 321

Access won't know what to make of the 123And.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com

Patrick Goodyear

unread,
Sep 9, 2014, 2:56:17 PM9/9/14
to
On 9/8/2014 9:04 PM, John W. Vinson wrote:
> =DLookUp("NewTx_Frequency","tbl_Full_Paths", _
> "RX_MW_SiteID = " & [Forms]![sfrm_Paths]![MW_Site_ID] _
> " And MW_Site_ID = " & [Forms![sfrm_Paths]![RX_MW_Site_ID])
John,

Thank you for your help. After I added the "]" after "[Forms! on line
three; I get an error message: "The expression you entered contains
invalid syntax. You may entered an operand without an operator."

This is what I have entered:

=DLookUp("NewTx_Frequency","tbl_Full_Paths","RX_MW_SiteID = " &
[Forms]![sfrm_Paths]![MW_Site_ID] " And MW_Site_ID = " &

Patrick Goodyear

unread,
Sep 9, 2014, 3:49:48 PM9/9/14
to
On 9/8/2014 9:04 PM, John W. Vinson wrote:
> On Mon, 08 Sep 2014 16:51:19 -0800, Patrick Goodyear
> <patric....@gmail.com> wrote:
>
>> Bob,
>>
>> Thanks for the reply. UniquePath is a text field, however it is really
>> the conversion and concatenation of two numeric fields.
>>
>> What could be wrong with this statement if all fields are numeric fields?
>>
>> =DLookUp("NewTx_Frequency","tbl_Full_Paths", _
>> "RX_MW_SiteID = " & [Forms]![sfrm_Paths]![MW_Site_ID] _
>> And "MW_Site_ID = " & [Forms![sfrm_Paths]![RX_MW_Site_ID])
>>
>> Kind Regards,
>>
>> Patrick
>
> The third argument to DLookUp must be a valid query criterion. You need to put
> the AND - part of the syntax of the SQL string WHERE clause - inside the quots
> so the criterion becomes
>
> RX_MW_SiteID = 123 And MW_Site_ID = 321
>
> after it's expanded. Try
>
> =DLookUp("NewTx_Frequency","tbl_Full_Paths", _
> "RX_MW_SiteID = " & [Forms]![sfrm_Paths]![MW_Site_ID] _
> " And MW_Site_ID = " & [Forms![sfrm_Paths]![RX_MW_Site_ID])
>
> Note the blank after the quote and before the AND. It's important; otherwise
> the concatenation would bive you
>
> RX_MW_SiteID = 123And MW_Site_ID = 321
>
> Access won't know what to make of the 123And.
>
Also, when I break it into function parts as a VBA function, it gets to
the DLookUp, and the error is that it can't find the referenced form
[sfrm_Paths] which is the current form.
Reply all
Reply to author
Forward
0 new messages