Dlookup for finf stock Position

[sbaud2003]

I fell into your original syntax errors. Try this one:

=DLookup("current_stock", "tblStock", "Stock_Id = 'P'" & Format([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID], "00000") & "'")

If that doesn't work try these and let us know which works:

=DLookup("current_stock", "tblStock", "[Stock_Id] = 'P00001'")
=DLookup("current_stock", "tblStock", "[Stock_Id] = 1")

=DLookup("current_stock", "tblStock", "[Stock_Id] = 1") CODES working fine in the main form with stock ID =1
others are producing error
The value of ITEMID is not being taken from subform (datasheet view)

 

[arnelgp]

=DLookup("current_stock", "tblStock", "Stock_Id = & CLng("0" & Replace([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID] , "P", ""))

 

[sbaud2003]

=DLookup("current_stock", "tblStock", "[Stock_Id] = 1") it works for Stock-ID=1 in the main form

=DLookup("current_stock", "tblStock", "Stock_Id = & CLng("0" & Replace([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID] , "P", ""))

getting compie error in "0"

 

[Pat Hartman]

=DLookup("current_stock", "tblStock", "[Stock_Id] = 1")

Refer back to my original post. Autonumbers are NUMERIC. Formatting is NOT saved. Therefore the "p" that you "see" is a figment of your imagination. It is not really there. That means that you need to know whether the form field is showing a "p" or not. if the "p" is showing in the form field, you MUST remove it since it is not actually stored.

I would NEVER, EVER format an Autonumber and I suggest that you don't either. We are 24 posts into this question and the reason is obvious. Refer back to my original post and create a separate user friendly identifier if you insist on having a "P" prefix. Post back if you need help.