Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

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.
0 new messages