date value expression and syntax

[Azad, DH, BD]

I have a table where I have variuos itmes & there closing balance of some
previous months. Now I'd like to DlookUp the previous month balance of
different items (by matching the item name) as the opening balance for the
current / present/Entered month. I've the code bellow for querries:

Op Balance: DLookUp("[Cl Balance]","Append","[Last Date] = #"&
Format(DateSerial(Year([Forms]![frmWhatDates1]![frmEndDate]
),Month([Forms]![frmWhatDates1]![frmEndDate]),0)) &â€#†& "And [Item name]=
""" & DLookUp("[Item name]","Append","[Last Date] = #"&
Format(DateSerial(Year([Forms]![frmWhatDates1]![frmEndDate]),Month([Forms]![frmWhatDates1]![frmEndDate]),0)) &â€#â€)&"""")

but is showing error msg: invalid date value and you have entered an operand
without an operator.



Can anybody help me out? Any help will be highly appreciated & I'll remain
grateful forever.

AZAD

 

[Douglas J. Steele]

I don't understand the point of the Format function calls around your
DateSerial calls. If you're going to use the Format function, make sure
you're using an appropriate Format for a date.

Try:

Op Balance: DLookUp("[Cl Balance]","Append","[Last Date] = "&
Format(DateSerial(Year([Forms]![frmWhatDates1]![frmEndDate]),
Month([Forms]![frmWhatDates1]![frmEndDate]),0), "\#yyyy\-mm\-dd\#") &
"And [Item name]= """ & DLookUp("[Item name]","Append","[Last Date] = "&
Format(DateSerial(Year([Forms]![frmWhatDates1]![frmEndDate]),
Month([Forms]![frmWhatDates1]![frmEndDate]),0), "\#yyyy\-mm\-dd\#"))&"""")

Of course, you appear to be doing this within a query, and using DLookup is
seldom desirable in a query. Instead, you should be able to join your
existing query to the Append table.