DateSerial-Calculating a date 35 days from today

[RLN60]

RE: Access 2007/WinXP-SP3

I am trying to calculate a date 35 days from the date in my recordset. The
recordset value was retrieved from a column in my table with a format of
Date, and the format of the field in the table was "Short Date".
By the time the application gets to these two lines of code,
rst.Fields(4).Value contains a valid date "8/31/2009" (minus the quotes).
Here are my two lines of code:

<begin code>
Dim intDaysRemaining as integer
intDaysRemaining = DateSerial(Year(rst.Fields(4).Value),
Month(rst.Fields(4).Value), Day(rst.Fields(4).Value) + 35)
<end code>

I am getting an "Overflow6" error when trying to use the DateSerial function.
I was anticipating getting a date back like "10/15/2009", or whatever 35
days on the calendar would be from the date provided in rst.Fields(4).value.
I got the Date SErail function from the Access portion of the MS Office
website, but apparently I still am not doing something correctly , but I
don't see it.

Any assistance would be appreciated.

Thank you.

 

[vanderghast]

I doubt that DateSerial generates the error. Can you try to dim the variable
as a date, instead?


Dim intDaysRemaining as Date



(and removing that ... ugly... prefix, int )


Note that you could have used DateAdd, too, instead of DateSerial and Year,
Month and Day.


Vanderghast, Access MVP

 

[John Spencer]

Why not use the DateAdd function?

First problem is that integer is too small. The largest value that integer can
handle is 32K. The value you are trying to assign to it is the equivalent of
40101.

Dim intDaysRemaining as Long


With DateAdd your assignment statement would be shorter
IntDaysRemaining = DateAdd("D",35,rst.Fields(4))

And if you want a DATE why not use
Dim dteDaysRemaining as Date
dteDaysRemaining = DateAdd("D",35,rst.Fields(4))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[RLN60]

This worked:
Dim dteNextChangeDate as Date
dteNextChangeDate = DateAdd("D", 60, rst.Fields(4).Value)
MsgBox "Date of last change was:" & rst.Fields(4).Value & vbCrLf & _
"pw will expire in " & DateDiff("d", rst.Fields(4).Value,
rst.Fields(4).Value + 60) & " days and need to be changed again by " &
dteNextChangeDate & "."

And produces this message:
"Date of last change was 8/31/2009
"pw will expire in 60 days and need to changed by 10/30/2009."

Worked great!

Thank you for the assistance guys, I appreciate it.

 

[CraigH]

Inline

I doubt that DateSerial generates the error. Can you try to dim the variable
as a date, instead?


Dim intDaysRemaining as Date

Should be dateFuture or dteFuture (Like John has)- or something more
descriptive
intDaysRemaining would be more appropriate if you subtracted two dates.

(and removing that ... ugly... prefix, int )

Why ugly - From Access Web:

3. Thou shalt choose a naming convention and abide by its wisdom and never
allow spaces in thy names.

I like it because Autocomplete I know what to start with to find my variable
name.

 

[vanderghast]

Prefixing, which takes its origine from Hungary convention in C, not C++
but plain C, it is useful in C, because the C compiler is oriented toward
performance, not toward beinf fool-proof (so many thanks to that C compiler
for the various bugs about buffer overflow and similar), but in VBA, as well
as in C#, it has close to none useful reliable "documentation" utility.


Indeed, first, for useful documentation, you can always get the EXACT dim
declaration of a variable through the contextual item menu "Definition...",
which is always up to date (not something we can be sure with hand made
prefix). That is more an environment (VBE, VisualStudio) than a VBA/C#
feature, though.

Second, if you get the wrong data type when calling you own procedure, you
are very likely to get COMPILE TIME ERROR if the data type is wrong.

Third, using the wrong date type for supplied procedure, you are very likely
to get a safe implicit cast occuring ANYHOW if you have the wrong data type.
As example,

? DateAdd("d", 5, "8/31/2009")

will work, even if the third argument is a string, rather than a date. The
string is simple casted as a date, behind the scene, for you, and no
pernitious buffer overflow will occur. At worse, the implicit cast may
produce a misleading run time error if it fails, or compile time error if no
implicit cast exist to get the expected data type from the supplied one.

Four, it is totally misleading when coupled with a default method. Say you
use a prefix for CONTROL, say ctrl for illustration (can be txt for a text
edit control, btn for button, and so on, but I stay generic and illustrate
with ctrl). But if you send it to a subrouting expecting, say, a double
value, would you use dbl_ctrl_controlName.Value? No, sure, you CANNOT. You
will still use the prefix ctrl EVEN if what you really pass is a DOUBLE
(again just for illustration). So, the prefix ctrl is misleading and
un-informative (is it a double, or is it a string, well, in Access, it is
none of these, it is a variant, anyhow... you use the prefix for a variant a
lot? ).

Five, hand maintained convention is prone to error, can you relay on it when
you use someone ELSE code? No need if you usually VBE feature, though (see
point 1).

Six, it decreases readability by making names interminable (unless you train
your mind to skip over the prefix, but then, what is it use if you
instinctively skip it? )


Finally, as for about 'keeping" the data together, it is preferable to use a
typedef (or a class) for that purpose. And you will benefit of intellisence,
in addition to the compiler checkings.


Sure, if you feel comfortable with your prefixing notation, I don't expect
to convince you to abandon it, after all, YOU work with YOUR code, so you
organize it as you prefer, not as "I" said, and that is quite fine with me.
But prefixing, in VBA and C# is FAR from being essential because the
compiler is 'mature', the documentation is better automatically maintained
by the environment, and because there are cases where it is by its own
nature misleading anyhow. The only good advantage I see to it is that it
allows to speak about it, and most of the time, in a very passionate way.
After all, such prefixing convention is easier than, say, using class, isn't
it? but is it as useful? no. While C has no class, VBA has. In fact, people
using class often drop their habit of using prefix.


Vanderghast, Access MVP