DLookup

[sayeed]

Dear Friends,
I have joined the forum new and need help.

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=Forms![Production Finished Bricks]![Date]-1" & [Product Name]=[Forms]![Production Finished Bricks]![Product Name]=[Product Name])

There is 7 product in a day I am trying to bring the Balance Qty to next date opening stock.

Help me

 

[arnelgp]

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & [Product Name]=[Forms]![Production Finished Bricks]![Product Name] & "'")

it is advisable to have a Product code instead of product name, as users might misspell the the product name while inputting.

 

[sayeed]

Dear,

As per ur advice I have used the code :
=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & [Product Name]=[Forms]![Production Finished Bricks]![Product Name] & "'")

but result return #Name?

I am using product name from drop down menu.

 

[sayeed]

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & [Product Name]=[Forms]![Production Finished Bricks]![Product Name] & "'")

it is advisable to have a Product code instead of product name, as users might misspell the the product name while inputting.

As per ur advice I have used the code :
but result return #Name?

I am using product name from drop down menu.

 

[JHB]

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & [Product Name]=[Forms]![Production Finished Bricks]![Product Name] & "'")

It seems to be some fault here!

 

[arnelgp]

use the column number of your combobox, assumming product name is in column 2, the following code will do it. remember the default Option Base is 0, which means your list column actually starts with 0.

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & Nz([Forms]![Production Finished Bricks]![Product Name].Column(1),"") & "'")

 

[sayeed]

use the column number of your combobox, assumming product name is in column 2, the following code will do it. remember the default Option Base is 0, which means your list column actually starts with 0.

=DLookUp("[Balance FB Quantity]","Production Finished Bricks","[Date]=#" & Format(Forms![Production Finished Bricks]![Date]-1,"mm/dd/yyyy") & "# And [Product Name] = '" & Nz([Forms]![Production Finished Bricks]![Product Name].Column(1),"") & "'")

But Dear It is showing same result #Name?.

 

[arnelgp]

then what is the name of your combobox?

 

[sayeed]

then what is the name of your combobox?

Is [Product Name]

I have Add the [Product Code] and
Now I have trying by IIF AND But It is showing the current date balance only.

=IIf(([Product Code]=[Product Code]) And ([Date]<Now()),[Balance FB Quantity],0)

Appreciate your cooperation.