HELP with crazy receipt numbering

[jlm722]

ok folks, I'm gonna try to make as much sense out of this as i can. I had a db written for me so i could cash checks in my small business and keep up with the records of such. However, the person who designed the db has vanished and will no longer support it. I know just enough about access to be dangerous ) So, here's my dillemma. I had the db configured to where my receipt numbering worked like this; on today, September 22, 2009, the first receipt number generated is 90922001: (the first 9 being the year, the 09 being the month, the 22 being the day, and 001 being the first check cashed today) (actually the format is yymmdd, but for some reason the 0 doesn't show on the year 09). Here's where my problem begins. One year ago i started using this db. on september 21, 2008 i cashed two checks. (receipt #'s 80921001 & 80921002). yesterday, on september 21, 2009 i cashed a check and it picked up from where the 2008 receipts left off. ie: it numbered the receipt 80921003. I'm gonna post the portion of code that does this. Can someone please look and tell me what is screwed up in here to cause this?

'**If all is OK, now assign sequential number
maxdate = Me.txtcashdate
char2 = Format(Me.txtcashdate, "mmdd")
'===new per jody
Dim xan As String


Dim howsql
Dim rshow
howsql = "Select * from a_datescheck"
Set rshow = CurrentDb.OpenRecordset(howsql)
'Dim dayx As Long
'dayx = DateDiff("d", rshow.lastdate, Now())
If rshow.RecordCount > 0 Then
xan = rshow.nextnumber + 1
Else
xan = Format(Me.txtcashdate, "yymmdd") & "001"
End If

p.s. i noticed that 3 lines down, it shows char2 = Format(Me.txtcashdate, "mmdd"). i tried changing to yymmdd, but it appears that it caused the code to jump to line 15 (xan = Format(Me.txtcashdate, "yymmdd") & "001") because it tries to number everyreceipt as 90921001. which in this case attempts to duplicate a primary key (receipt number) and causes an error. PLEEEEASE HELP!!! much thanks in advance!

[RuralGuy]

How about posting the field specifications for the a_datescheck table?

[jlm722]

RuralGuy? i appreciate the reply, but there is NO a_datescheck table. I've looked everywhere and do not know what that refers to.

[RuralGuy]

Is it a query? If it works at all then it must be a table or a query.

[jlm722]

yes yes yes you're right, it is a query! i think you are on to something. the query looks like this: the first section : field: NextNumber: ProcNum, Table: tblLoanDetail, Total: Max. then the second section says : Field: xn:Format([cashdate],"mmdd"), Total: Group By, Criteria: getchar2()

[RuralGuy]

Switch to SQL view and post that and also look up the getchar2() function in a standard module and post that code as well.

[jlm722]

ok here it is in sql view
SELECT Max(tblLoanDetail.ProcNum) AS NextNumber, Format([cashdate],"mmdd") AS xn
FROM tblLoanDetail
GROUP BY Format([cashdate],"mmdd")
HAVING (((Format([cashdate],"mmdd"))=getchar2()));

[RuralGuy]

Can you post the getchar2() function? It is probably in a standard module.

[jlm722]

The funtuon says getchar2() = Char2 I'm believing from reading, that in this case, it means in the format mmdd it's returning the second character from the month number. Makes no sense to me. I am sure this is where the problem is. It would explain why my receipt numbers went back to 2008 and looked at the month of September and ignored the year. The problem is, I can't figure out how to modify this query to make it look at the year.

[RuralGuy]

The function starts with Public Function...and ends with End Function. Would you post all of that please.

[jlm722]

Public Function getchar2()
getchar2 = char2
End Function

[RuralGuy]

Ahhh...so it is returning a Public variable. Any chance you can post your db? It would save some time.

[jlm722]

Sir, I'd love to post it, but it exceeds the size to post on here. is there an email i can send it to? I promise i'm safe, and i reeeeally appreciate your help!

[RuralGuy]

If you Compact and Repair your db and then zip it, this site will accept up to 2MB Zip files. If that doesn't work let me know and I'll give you my email addy.

[jlm722]

i tried, it says my size is 2.55mb