Comparing Text Fields in table

[DaveA]

Our incident numbering system is based on the following YEAR 05 MONTH
06 NMBR 1234

looks like 05-06-1234

the counter number is manually reset each month and I would like to
make it automatic. I am having trouble with the comparing the old month
stored as two digit number as text with the new two digit month found
using format(date()," mm")

I am using a seperate table with two fields txtMonthOld and
txtMonthCurrent . My idea was to write the new two digit month in
txtMonthCurrent and then compare the two fields. After the comparison
was made, the two digit month in txtMonthCurrent was then written in
txtMonthOld. If there was a difference, a seperate code that I use to
reset the counter number takes effect. If there is no difference,
nothing happens.

I cannot figure out how to compare the two fields? Any help would be
appreceated

Dave

[Johnny Meredith]

Perhaps this (air):

Private Const cTableWithField sToCompare As String = "MyTable"
Private Const cFirstField As String = "txtMonthOl d"
Private Const cSecondField As String = "txtMonthCurren t"

Function CompareMe() as Boolean
Dim db As DAO.Database
Dim rec as DAO.Recordset
Dim return as Boolean
'Set Default Return Value to False
return = False
Set db = CurrentDb()
With db
'Open a dynaset (so findfirst method is available)
Set rec = .OpenRecordSet( cTableWithField sToCompare, dbOpenDynaset)
With rec
'Populate recordset
.MoveLast
.MoveFirst
'Insert Code here to find the record??
'Example:
'.FindFirst("ID = " & somevariable)
'Here's the logic to compare the fields
If .Fields(cFirstF ield) = .Fields(cSecond Field) Then
return = True
End If
.Close
End With
.Close
End With
'Clean up
Set rec = Nothing
Set db = Nothing
'Set return value
CompareMe = return
End Function

This function returns true if the fields match, false if they don't.
Note that I've only stubbed out code to find a particular record. I
would envision a parameter to the function (i.e. see somevariable
above) that provides the key to the record you need.

HTH,
Johnny
(feel free to email me for clarification at j{deleteme]meredith AT g
MAil DOT com)