Question How to retrieve data based on condition?

[shieriel]

I have a database for product ordering. What i want to do is a code to check if a specific product have order history for the last 3 months.
I dont know how to translate it into code.
Something like, when i input product name on the order form, a code will check that in the database table and show a message that the product has transaction record for the last 3 previous months.

 

[John Big Booty]

You could do this with a DLookup() with citreira to match your ProductID AND Greater than DateAdd("m", -3, Date()).

 

[Galaxiom]

Try something like this:

DLookup("[OrderDateField]", "[OrderTable]", "ProductID=Forms!formname.controlname AND DateDiff("m",[OrderDateField], Date()) < 3")

If it returns a value then there has been an order.

 

[shieriel]

Thanks! I will try this on my database....

 

[shieriel]

When i tried to put the formula, I got the following error:

Compile error:
Expected: list separator or )


and then the letter "m" on the formula is highlighted. What is meant by that error?

 

[jdraw]

Please show us the code involved.

 

[shieriel]

I put this code on the "after update" event, and when error occurs I tried to create new textbox and put this formula also but with the same error result:

DLookup("[OrderDate]", "[OrderTable]", "ProductID=Forms!NewOrder_Form.ProductID AND DateDiff("m",[OrderDate], Date()) < 3")

 

[Galaxiom]

Sorry. My mistake. The m should have single quotes around it due to the double quotes in the DLookup.

 

[shieriel]

I see. Ok will try it again later. Many thanks...

 

[shieriel]

Sir, I am confusing with the formula. Please correct me if i am wrong: I put the formula on the textbox but nothing happens, it always shows error or "Name?". What is wrong here?

 

[shieriel]

up` for help...

 

[John Big Booty]

Try;

DLookup("[OrderDateField]", "[OrderTable]", "[ProductID] = " & Forms!formname.controlname & " AND [OrderDateField] > " & DateAdd('m',-3,Date()))

 

[Galaxiom]

The expression needs an equals sign at the beginning when it is used as a control source.