minus number in DMax

[rick roberts]

Consider the following -- a table with StartBalance, Payment and EndBalance. for the next record i want the end balance of the previous record to become the start balance of the next -- i use
DMax("PrevousBalance","tblFloat") this works fine until i put a minus number in the payment box -- why is this? can i get round it without creating a seperate column for minus numbers? thanks in anticipation

 

[RuralGuy]

So the EndBalance field is a calculated field in your table? What is the calculation?

 

[Atomic Shrimp]

It sounds to me like your code is finding the record with the largest value, not necessarily the last one in the table.

 

[gemma-the-husky]

It sounds to me like your code is finding the record with the largest value, not necessarily the last one in the table.

that sounds right - if all you are storing is amounts, you will struggle - surely you need to store the payment date/time to determine the last allocation


--------
out of interest is the minus number a real item (say a refund or something) , or just a marker for something else.

 

[rick roberts]

ok ill try and answer all questions together -- the calculation is EndBalance=StartBalance+Payment - -so that if the payment was actually a deduction i would just put it in as a minus figure. Atomic does mention something that rings true since this is just a simplified version of the whole problem - it does sound like im not finding the last record - the original does have a date -- the one i gave as an instance has an autonumber but i didnt think it was crucial to include that in the DMax code
if it helps i can include the actual part im having trouble with as a stand alone program -just the table and form

 

[RuralGuy]

Posting the mini-db would help.

 

[rick roberts]

here is the actual part ive been having problems with -- not the same names as i mentioned in my first post but it shows the problem i have - only entries to CashBank seems to work --and then only when it is a poistive number

 

[Scooterbug]

Well, after a quick look, I noticed that your dmax was looking for the max amount of all records. Using this code:

Private Sub Form_Current()
Dim curFloat As Currency
Dim lngLastEntry As Integer
 
lngLastEntry = DMax("[floatID]", "tblfloat")
curFloat = Dlookup("[total]", "tblFloat", "[floatid]=" & lngLastEntry)

If Me.NewRecord Then
OpenFloat.Value = curFloat
End If
End Sub

It finds the last record, based on the autonumber (You can use the date.). Then using that, finds the total from that record. I had no problem using negative numbers with this.

You also want to change the field name Date in your table. Date is a reserved word and will cause you heartache if you continue to use it.

 

[rick roberts]

thanks scooter that works perfectly - so just to recap - i was originally going to use DLookup but after reading post on here regarding that function it seemed that everyone said that DMax was a better option - would it have been simpler to use DLast? my assumption was that on a new record the code would look for the total in the last record and use it in the new one thanks again all for the resolution

 

[gemma-the-husky]

there are lots of domain functions, but dlast isnt one of them

access doesnt understand ordering of records - thats where the problem comes from - so you have to be very specific in instructing access - now because there is no first or last - but there is dmax and a dmin - you can say

eg

find the record with the largest date, relating to a given customer
then use that. (ie use dmax)

the thing here though is that dmax will return different records depending on the search criteria (here we are searching for the largest date, for a given customer) - hence, there is no concept of an absolute dlast.

even this example CAN be suspect, because if you have two cash entries on the same data, you cant predict which one will be retrieved - so you actually need a design that enables you to specify a UNIQUE record.

this is why normalisation (table, and key design) in access is vitally important, to ensure that you can reliable relate records together, and retrieve information you need.

 

[Atomic Shrimp]

...It finds the last record, based on the autonumber (You can use the date.).

Date would probably be safer - there are some exotic circumstances in which an autonumber may skip a whole block of numbers and (or at least I think so) circumstances in which the numbers in the missing block might be revisited later on.

 

[rick roberts]

damn - id already anticipated gemma's point about duplicate dates and decided to go the autonumber route - now you tell me thats not safe either lol - well at least i got an answer and i learnt something thanks for your help

 

[gemma-the-husky]

damn - id already anticipated gemma's point about duplicate dates and decided to go the autonumber route - now you tell me thats not safe either lol - well at least i got an answer and i learnt something thanks for your help

while an autonumber is obviously unique, it doesn't (or may not) represent any real world attribute to identify a row

note that you can use now() rather than date() to store the date and TIME a record was entered. (using times sometimes makes things a bit more complex, though) alternatively you could store a sequential record number counter, which would give you an easy way of sorting records. I would tend to add a sequential record number

 

[rick roberts]

whats the difference between a sequential number and an autonumber - arent autonumbers unherently sequential by nature -- tell me if im being a pain at this stage lol

 

[Scooterbug]

whats the difference between a sequential number and an autonumber - arent autonumbers unherently sequential by nature -- tell me if im being a pain at this stage lol

Yes, autonumbers start out sequential...but if you delete a record, then that number assigned is gone. When this happens, you have gaps. So if out of my first 5 records, I delete the fifth one, the next record will have a 6 in the autonumber, not 5.

If you use a sequential number and record 5 is deleted, using dmax() + 1 to get the sequential number for the next record, it will actually be 5.

Make sense? (if not, I apoligize...my teaching skills can be sub par at times )

 

[rick roberts]

but if you deleted the first number out of 5 -- wont that still leave a gap cos the sequence will start at 2?

 

[gemma-the-husky]

an autonumber is just a unique key

an autonumber will follow the sequence in which you inserted records, and therefore MAY be ok to use.

But it may not necessarily be the correct logical order. Say you record payments for a week at a time, and happen to put Friday's in first etc. Then Monday's payments will have a HIGHER autonum than Friday's.


So i mean a sequential number that you control yourself, rather than just assuming you can use the autonumber.

The point is, that if the order of entry of items is important, then you need a reliable way of determining what that order was/is

 

[rick roberts]

but if you delete any other than the last record you will still have a gap? if you delete the first record then your next sequence for a new record will still be six?

 

[Scooterbug]

Yes...the only way to have a true sequencial range is to not delete any of the records.

What Gemma was getting at is there are two ways to have sequencial numbering...either by use of the program (autonumber) or by creating it yourself (by using dmax() + 1)

 

[gemma-the-husky]

what i was REALLY trying to say is that its not necessarily sequential numbering thats the issue - the issue is that rick needs to be able to retrieve the last record - so we need to defrine what is meant by last - this may be the highest sequential number, but not necessarily - this will only apply if the data is entered in that order.

You can solve this by adopting any approriate enterprise rule - its just formulating the rule.

eg - rick is trying to monitor a balance on an account by storing on each transaction the opening balance before the transaction and closing balance after the transaction. Hence he needs to retrieve the opening balance - hence the requirement to find the last item.

Another way of looking it at is that you dont realy need to store this at all.

To find the outstanding balance all you need to do is to add up all the tranascation values relating to that item - which is what a database is good at!