Web Development

General discussion

Gravatar
Locked

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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

gravatar
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.

gravatar
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.

gravatar
Collapse -

by MRA2 In reply to

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

Alan

gravatar
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

gravatar
Collapse -

by MRA2 In reply to Access 97 Date Comparison

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Start or search

Related Discussions

Related Forums