DMax, DLast, Carryforward? Increment last Check Number

[kicslinks]

Hi,

I have an Access 2010 bank reconciliation database in which I have a sub-form frmCheckBatch based on table (tblCkBat) where checks can be entered in batches, i.e, all checks from the same date can be entered and tied back to a single record in the master form / table (frmMstr /tblMstrTable).

I need to be able to carryforward the last check number and increment it by one each time. There are 2 series of check numbers; 109257 and 41484.

I have tried DMax in VBA and:
1. it always finds the 41484 series
2. it increments the original check number every time and not the last check number just entered.

Thanks in advance for any help

 

[MarkK]

A few things:
1) if DMax() finds 41000 and not 109000, then the field is probably a string, since in a numeric field, 109 > 41, but in a string field "41" > "109". If the field is a string, it's a bit of a hazard, since "109" > " 41", but "109" < "41", and that space is easy to miss, but it will throw off your DMax() result.

2) DMax() takes three parameters, the last of which is criteria, so you can do . . .

DMax("Number", "tblTransaction", "Number < '41000'")

. . . and return the Maximum number that is less than some other number, again, subject to the hazards that you are using strings, but this fact will give you the capability to find the DMax() of a subset of your records, which is what you are posting about.

3)

all checks from the same date can be entered and tied back to a single record

You don't need to tie them to a record somewhere if they are all entered on the same date. Just group them by date. What data does the parent record contain if all the child records already contain the same date?

Hope this helps,

 

[pbaldy]

I'm not sure I understand, but it sounds like the field may be text rather than numeric. That would cause an alphabetic sort, and that would make 41484 larger than 109257.

 

[kicslinks]

Hi, Thanks for your quick responses.

This database was built from an Excel workbook containing 5 years of tranactions for four bank accounts.

There are ACH transfers which occur between four different bank accounts in this database. There are ACH transactions that are recorded like checsk and deposits as well as checks. Each account has a master table which holds the date and other fields which are applicable to the different types of transactions in that account but are not contained in one of the transaction tables. e.g., Batch Checks, Transfers, AHCs and Deposits.

I changed the CheckNumber field type in the table from text to number, I will see if that helps. Do you know how I can find either the numbers that start with "1" or the those that start with "4" depending on which series the last check was in using the same query or do I need two queries?

Thanks again for all your assistance.

 

[MarkK]

If you changed the field's data type to a number, then you don't want to concern yourself with finding the first "1" or "4". That is what you would do if the field was a string.

Did you read the part about DMax() having three parameters, and that you can find the Max number within a subset of your records? Check post #2, item number 2), and consider the following code . . .

Global Const g_MinUpper As Long = 100000 [COLOR="Green"]'the start number of your upper range[/COLOR]

Function GetMaxLower() As Long
   GetMaxLower = DMax("Number", "tblTransaction", [B][COLOR="DarkRed"]"Number < " & g_MinUpper[/COLOR][/B])
End Function 

Function GetMaxUpper() As Long
   GetMaxUpper = DMax("Number", "tblTransaction")
End Function

 

[kicslinks]

Thanks Mark, I will re-read post #2 and try the code you provided. I will let you know how it works. Thanks again for all your help!

 

[MarkK]

You bet, good luck,