DLookup Problems continued...

[Patrick Goodyear]

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]

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]

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]

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.