problems with dates in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey there,

For the past few weeks I've been programming a lot of things with VBA. There
only is one thing that I haven't got a solution for. I've made a script in
VBA which has to add a certain field to a table based upon a date. This date
changes every month.

In the current situation I manually have to adjust this date every month but
I was wondering if there is a way to do this automatically.

Example:

if rec_source![Date] > 20070600 Then
rec_destination[InProcess] = "1"
Else rec_destination[InProcess] = "0"

Because everything that is less than a month old is In Process the date
changes every month. When it's July the date is 20070600. When it's August
the date changes to 20070700.

Does anybody have an idea how I can solve this problem?

Thanks a Lot
 
if rec_source![Date] > CLng(Format(Date, "yyyymm") & "00") Then

BTW, you should rename the field from Date: Date's a reserved word, and
using it for your own purposes can lead to problems. For a good discussion
on names to avoid in Access, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
DJRave said:
Because everything that is less than a month old is In Process the date
changes every month. When it's July the date is 20070600. When it's August
the date changes to 20070700.
Click to expand...

I think you mean when it's July the date is 20070700 and when it's August the
date changes to 20070800.

I assume your "dates" in the database are held as integers of this format.

if rec_source![Date] > CLng(Format(Date(),"yyyymm") & "00") Then

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Back
Top