Web Development

Ask something
Discussion Locked

Access 97 Date Comparison

0 helpful
5 replies

by mra2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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???????????????
Alan
Abramowitza@coned.com

5 replies

by techrep

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.

by techrep

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.

by mra2

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

This discussion is closed to new replies.