duedate function

[Guest]

dearfriends,
i want an function to find out the duedate of a student basing on his
gradation.
public function duedate(grade as double , lastdate as date)
'but the gradation will be given in alphabets like "a',"b","c"
dim myvar as string
if myvar = "A" THEN grade = 3 'monitering date will be after 3 months from
lastmonitered darte
if myvar = "B" then grade = 2
if myvar = "c" then grade = 1
duedate = dateadd("m" , grade,lastdate)
endif
end function
My problom is
(1) this function is returning false values like duedate(3,01/01/2000) is
giving
30:03:1900 why ?
(2) i would like to include an string value insted of (grade as double ) in
my function b'couse users will enter grade in strings
how to setright it please

 

[Rob Parker]

Hi Balu,

First thing: the code you posted DOES NOT RUN - it generates an "end if
without block if' error. So I don't know why it returns a false value,
because I don't know what your code really is.

Assuming that your code does not contain the endif statement (which itself
is wrong - you can type it into the VBA editor, where it will be immediately
corrected to End If) which is the source of this error, then the reason you
get 30/03/1900 is because, if you call your function as you show, you are
actually giving it a datetime value of 1 divided by 1 divided by 2000. This
number (0.0005) represents 12:00:43 AM on 30 December 1899; adding 3 months
to it gives (quite correctly) 30/03/1900 12:00:43 AM.

If you want to pass a date string to the function (rather than a variable
which has a DateTime datatype, you must delimit it with # characters. If
you do so, then:
duedate (3, #01/01/2000#) will return 01/04/2000 (in my system default
date format of dd/mm/yyyy) or 04/01/2000 (if your system default date format
if mm/dd/yyyy).

You can easily pass a string to your function (and that seems to be what
your internal If statements are designed to convert). Try the following:

Public Function DueDate(myvar As String, lastdate As Date) As Date
Dim grade As Double

If myvar = "A" Then grade = 3
If myvar = "B" Then grade = 2
If myvar = "C" Then grade = 1
DueDate = DateAdd("m", grade, lastdate)
End Function

This will return DueDate equal to lastdate if the string variable is not A,
B or C.

You can test this in immediate mode by entering something like:
DueDate ("A", #22/04/2007#), which will return 22/07/2007

You should really add some error trapping to any routine you write. This
will fail if either of the inputs are the wrong datatype.

HTH,

Rob

 

[Jamie Collins]

duedate (3, #01/01/2000#) will return 01/04/2000 (in my system default date format of dd/mm/yyyy) or 04/01/2000 (if your system default date format
if mm/dd/yyyy).

I don't think "your system default date format" makes any difference
for DATETIME values delimited by # characters in either VBA or SQL
e.g.

SELECT DATEPART('D', #04/01/2000#)

will always return 1.

The best approach IMO is to use a representation as close to ISO 4217
format as Access/Jet allows (this is after all an international group)
and always include an explicit time value (because Access/Jet has but
one temporal data type named DATETIME) e.g. can there be any
reasonable any doubt as to what this will return:

SELECT DATEPART('D', #2000-04-01 00:00:00#)

Note that saving a query in *Access* may change the format to default
U. S. of A. format, as does VBA, again regardless of "system default
date format".

Jamie.

--

 

[Jamie Collins]

public function duedate(grade as double , lastdate asdate)
'but the gradation will be given in alphabets like "a',"b","c"
dim myvar as string
if myvar = "A" THEN grade = 3 'moniteringdatewill be after 3 months from
lastmonitered darte
if myvar = "B" then grade = 2
if myvar = "c" then grade = 1
duedate = dateadd("m" , grade,lastdate)
endif
end function

Suggestion: a data driven approach: create a lookup table
(grade_letter, interval_months) then use the table in a query e.g.
(aircode)

SELECT S1.student_number, DateAdd('m', T1.interval_months,
S1.lastdate) AS due_date
FROM Students AS S1
INNER JOIN GradeIntervals AS T1
ON S1.grade = T1.grade;

This makes maintenance easier IMO e.g. amend intervals, add grades,
not reliant on Access nor your front end etc.

Jamie.

--

 

[Rob Parker]

Hi Jamie,

For the function in question (which is essentially just a dateadd function
in a (not very efficient) wrapper), it does make a difference. Try it. In
the immediate window, enter:
? dateadd("m",3,#1/1/2007#)

If your system setting is US dates (MM/dd/yyyy or similar), this will return
04/01/2007; if your system setting is non-US (dd/MM/yyyy or similar), this
will return 01/04/2007. Do it once, then change the setting via Control
Panel - Regional Settings, and do it again. Then set it back to your normal
default, before you get very confused ;-)

Another interesting point from this is that, if you are viewing a datetime
field in a datasheet (table or query), or in a bound control, where the
field is formatted with a different setting than the system default, the
date will appear to swap day and month when a datasheet cell or bound
control gains focus. Can be very disconcerting.

I'm certainly not implying that the underlying data value is different, or
that the value returned by a datepart function will differ; I'm merely
stating that what you see is different - and may cause confusion.

And another thing: When I enter the expression you posted
SELECT DATEPART('D', #04/01/2000#)
in the SQL view of a new query, it returns 1 (as you say); if I then switch
to design view in the query (on my system, with default date format of
dd-MM-yyyy), it shows (cut and paste direct from query grid):
Expr1: DatePart('d',#01-04-2000#)
Here, in *Access*, the expression is changed to the system default, NOT to
the US default.

Rob

 

[Guest]

For the function in question (which is essentially just a dateadd function
in a (not very efficient) wrapper), it does make a difference. Try it.

I can take you word for it <g>. Sound like we talking cross purposes, sorry
for the distraction.

Jamie.

--