Formating of a module

  • Thread starter Thread starter Sondra
  • Start date Start date
S

Sondra

Module written for BeforeUpdate:

Want the end result to be

001-2005
002-2005
003-2005

Currently the results are:

200-5001
200-5002
200-5003

Where did I go wrong in writing this? I'm a newbie to
Access modules and can't determine where the format is
defined.

Please help.


Dim mTable As String, mField As String, mYearPart As Long,
mNextNumber As Long
'number is returned as 7 characters: ###YYYY
'where YYYY is the year
'### is the next number for that year
'format code to display this number should be "000-000"
mTable = "RMEntry"
mField = "RMNumber"
mYearPart = Right(CStr(Year(Me.RMYear)), 4) * 1000
mNextNumber = Nz(DMax(mField, mTable, mField & ">=" &
mYearPart), 0)

If mNextNumber = 0 Then
mNextNumber = mYearPart
End If

mNextNumber = mNextNumber + 1
Me.RMNumber = mNextNumber
 
I need more info about your code before i can help.
1) what is the data you are entering
2) what is the data in the table

i guessed that Me.RMYear = a date. if so then you don't need the right or
CStr. this will give you the same answer:

mYearPart = Year("1/1/5") * 1000 ' you get 2005000 either way

vb allows you to assign numbers to strings and strings to numbers as long as
the string to number has no characters in it. so you don't need CStr.
The year function will return the year of any date.
I am not sure why you are * 1000 but in doing so you are taking 2005 * 1000
and getting 2005000. if thats what you want the then you are ok.

For the rest i need to know what data you have in the table because that is
what you are adding + 1 too.

good luck!
 
Back
Top