Dlookup Criteria

[Ascentient]

I am have problems with the (((tblBMtk.[MATERIAL-CODE-1])=[forms]![frmReOrderLevels]![cboMaterialCode])) AND ((tblBMtk.[ORDER-DATE]) Between DateAdd(""yyyy"",-1,Now()) And Now())))
of the code. Can anyone help me sort this mess out?

I need the contents of a combo box to equal the field [MATERIAL-CODE-1], but it the product has to have been sold in the last year.

DSum("[QTY-SHIP-1]", "tblBMtk", (((tblBMtk.[MATERIAL-CODE-1])=[forms]![frmReOrderLevels]![cboMaterialCode])) AND ((tblBMtk.[ORDER-DATE]) Between DateAdd(""yyyy"",-1,Now()) And Now())))

Thanks in advance.
Ascent

 

[jebry]

Hi!

Try this:

DSum("[QTY-SHIP-1]", "tblBMtk", "[MATERIAL-CODE-1]=" & forms!frmReOrderLevels!cboMaterialCode & " AND [ORDER-DATE] Between " & DateAdd("yyyy",-1,Now()) & " And " & Now())

I am not sure you use the between keyword with the aggregate functions. If not you can use a >= with the <= in any way that supports your logic. You may need to add #'s inside the " so that they will surround the dates. Also, this code assumes the material-code-1 is numeric.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Zion7]

yes, you need hash marks surrounding dates,
when using aggregate functions.

 

[PHV]

Keeping your way:
DSum("[QTY-SHIP-1]", "tblBMtk", "[MATERIAL-CODE-1]=[Forms]![frmReOrderLevels]![cboMaterialCode] AND ([ORDER-DATE] Between DateAdd(""yyyy"",-1,Now()) And Now())")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ascentient]

Thanks for the reply people. I will test these out today. I have been out of town and unable to spend time testing your suggestions.

Ascent

 

[Ascentient]

I ended up using PHV's code. Thanks! With a couple of tweaks to fit a for next statement, it gives me exactly what I need.

Ascent

 

[esaias]

I am having a problem of calculating moving average cost in report(inventory system).
#ofunit costperunit totalcost MAC
biginning 200 5000 $25.00
purchase1 100 $26.00 2600 balance 300 7600 $25.33 sale1 (100) $25.33 (2533) balance 200 5067 purchase2 600 $27 16200 balance 800 21,267 $26.58 sale2 (300) $26.58 (7,975)
balnce 500 13,292
....it goes on like that...
so how can i design this in report.only the quantity and unit price of the purchases are held in table and the beginning balance while for the sale only quantity is held in the table....so if you ever come accoss this problem please help!
esaias

 

[Ascentient]

Esaias,
Your post should be in a new thread as it does not relate to the current topic. Please repost and provide some additional detail with a better layout of your sample data. (It's hard to read and follow.)

Ascent