Web Development

General discussion


Access 97 Date Comparison

By MRA2 ·
I have an Access 97 Application which posts Monthly dues. I want to stop accidental ADVANCE (Greater than current Month & Year) posting of dues.
There is a List box where the year is selected and a button for each month. So the date comes out as [yearbox] + month. For example 200312 or 200302. (I hardcoded 01, 02 through 12.)
Now to compare the posting month/year with the present Month/Year to stop advance posting by accident. I took datepart("yyyy,date) & datepart("m",date)
But it does not work correctly because I get for Dec 200412 and for January 20041. With the zero missing in months 1-9 the less than comparison get the WRONG ANSWER.

Any ideas???????????????

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by techrep In reply to Access 97 Date Comparison

What your doing isn't going to work because you're really trying to do a comparison by sort order. You need to use true date variables so you can use the built-in date arithmetic functions. For example:

Determine the current month and year and calculate the beginning of the next month:

Dim FirstOfNextMonth as Variant 'date type
Dim Today as Variant 'date type
Dim TheMonth as integer, TheYear as Integer

Today = Date() 'store current date as a date
TheMonth = Month(Now)
TheYear = Year(Now)

If TheMonth = 12 Then 'adjust if month is Dec
TheMonth = 1
TheYear = TheYear + 1
End If

'the following line will store the data as a date:
FirstOfNextMonth = TheMonth & "/1/" & TheYear

If Today >= FirstOfNextMonth Then
Msgbox "Greater than current Month & Year"
End If

There may be some other date functions that can be used to achieve this result. I'm trying to show you in code what the logic would be.

Collapse -

by techrep In reply to

Sorry, I left out the CVDate function:

Change this line:
FirstOfNextMonth = TheMonth & "/1/" & TheYear

to this:
FirstOfNextMonth = CVDate(TheMonth & "/1/" & TheYear)

Then it will work.

Collapse -

by MRA2 In reply to

Thanks for your help!!!!!!!!!!!!!!!!!!!!!!!


Collapse -

by MRA2 In reply to Access 97 Date Comparison

This code gives the wrong answer:

Private Sub Command8_Click()
Dim firstofnextmonth As Variant 'date type
Dim today As Variant 'date type
Dim TheMonth As Integer, theyear As Integer

today = Date
TheMonth = 1
theyear = 2000
firstofnextmonth = TheMonth & "/1/" & theyear
If firstofnextmonth >= today Then
MsgBox " Greater than current Month & Year"
End If

End Sub

Collapse -

by MRA2 In reply to Access 97 Date Comparison

This question was closed by the author

Related Discussions

Related Forums