select MAX date but with rules

Andy B

Registered User.
Local time
Today, 17:49
Joined
Oct 14, 2004
Messages
31
Producing a report that shows stock in and out, i have set up subtotals to record the actual amount of stock, and i want to show the last date of the stock in.

Now i have used MAX date but that shows the last transaction ( which could be an out not an in).

As the outs are minus and the ins are additions i tried to sety up a WHERE in the expression builder to say WHERE stock is greater than zero, but it doesnt seem to recognise the WHERE, how else can i write this to actually show the last date in.

THanks
 
sorry to be a bit thick whats my SQL ?
 
sorry to be a bit thick whats my SQL ?

I think the totals are being done in the usual Access Report way - a text box with control source:
= MAX(dtTransaction)
, which is very limited.

You would need to use domain aggregate max (DMAX) instead:

= DMAX("dtTransaction", "tblTransactions","intStock > 0")

And in answer to your question, SQL is the script behind queries:
SELECT Field FROM Table WHERE Condition
You can have MAX and SUM and COUNT and other functions in SQL statements
 
Ok the reason i was confused about the SQL is because for this operation i was using the exprssion builder and putting the formual directly into the property sheet.

As a subtotal in a report picking the last date was OK, the MAX function gave me this, but i want the alst date stock went in.

So i wanted to do MAX [shipdate] WHERE [stock]>0

It seemed so simple but it doesnt seem to work.
 
thanks for advising it it would work in a query but i am trying to set it up in a Subtotal section on a report, ideally as an expresssion.

I am running SQL on the subtotal section to hide the section all together if the stock level is zero as follows:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If AccessTotalsSumOfStockA = 0 Then
Me.GroupFooter0.Visible = False
Me.GroupFooter0.Height = 0
Else
Me.GroupFooter0.Visible = True
Me.GroupFooter0.Height = 30
End If
End Sub

I did try adding some code in the ELSE section but i couldnt get anything to work (syntax errors)
 
I think this thread would be much better placed in the Reports forum

And be aware that the code you showed is VBA not SQL code.

I would try to help more but it's been a long time since I bothered to use Access reports (reports I do are always populating word or excel templates these days because I can't stand Access reports for exactly these sorts of limitations). I have a feeling the answer is - you can't. But an expert on Access Reports might know of a way and you'd be more likely to find one of them in the Reports forum section ;)
 
Guys,

Got it sorted after messing about and i came up with

=Max(IIf([sumofstocka]<0,Null,[ShipDate]))

and it WORKS !!!

Might not be the most eligant solution but got there in the end.

Thanks for all of your help.

Andy B
 

Users who are viewing this thread

Back
Top Bottom