Filter date

G

Guest

Hi All!

I have a simple table

Prod_ID
Cost
Date

My Prod_ID and Date are filtered throught the form's criteria. I'd like to
be able to get the max date from a table if the given criteria doesn't exist
in the table.

I know how to get it using vb, but I thought that I could escape from
writing a code.

So far I came up with this in my query for the date:

IIf(([tblNewItemCost]![Date_YMD]<>[Forms]![frmMain]![Text0] And
[tblNewItemCost]![Date_YMD]<>[Forms]![frmMain]![Text2])
,Max([NewItemCostTbl]![Date_YMD]),([tblNewItemCost]![Date_YMD]>=[Forms]![frmMain]![Text0] And [tblNewItemCost]![Date_YMD]<=[Forms]![frmMain]![Text2]))

I am getting aggregate function error.

Any reply will be greatly appreciated.
 
M

[MVP] S.Clark

You 'escaped' from VBA such that you could be imprisoned by this?

I don't understand where you are trying to use this Max() function?

If in a query, then you would need to have a GROUP BY statement.
If in a controlsource, then the answer is you can't do that.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 
G

Guest

thank you for your reply!
I was thinking to use this as a function in a query field.
The two fields in query are PeodId and a cost, which are grouped. the
function from my previous statement was supposely filtering my date in my
third filed in a query. Is this possible?

[MVP] S.Clark said:
You 'escaped' from VBA such that you could be imprisoned by this?

I don't understand where you are trying to use this Max() function?

If in a query, then you would need to have a GROUP BY statement.
If in a controlsource, then the answer is you can't do that.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Malvina said:
Hi All!

I have a simple table

Prod_ID
Cost
Date

My Prod_ID and Date are filtered throught the form's criteria. I'd like
to
be able to get the max date from a table if the given criteria doesn't
exist
in the table.

I know how to get it using vb, but I thought that I could escape from
writing a code.

So far I came up with this in my query for the date:

IIf(([tblNewItemCost]![Date_YMD]<>[Forms]![frmMain]![Text0] And
[tblNewItemCost]![Date_YMD]<>[Forms]![frmMain]![Text2])
,Max([NewItemCostTbl]![Date_YMD]),([tblNewItemCost]![Date_YMD]>=[Forms]![frmMain]![Text0]
And [tblNewItemCost]![Date_YMD]<=[Forms]![frmMain]![Text2]))

I am getting aggregate function error.

Any reply will be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top