variables and the weekday() function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

warnett - 17 Dec 2005 20:18 GMT

hey there all.
Been using access for my project at school and I need the system to work out
the day of the week for me, given a date stored in a variable. Found
something with access though:

WeekDay("07/11/2005",2) returns 1 (monday which is correct)

but using a variable of either data type date or string with this value
stored returns an incorrect value.

for example a variable datedate of type variant

WeekDay(datedate,2) returns 6 (saturday which is wrong).

I have tried both a string and a date for the data type of the variable but
it still wishes to produce the wrong answer. I believe it is to do with
quotation marks around the date. Any ideas.

Thanks Jason

Reply to this Message

Douglas J. Steele - 17 Dec 2005 21:31 GMT

How have you assigned a value to datedate?

I wrote the following sub:

Sub TestDate()
Dim datedate As Date

datedate = #7/11/2005#
Debug.Print WeekDay(datedate, 2)

End Sub

then ran it in the Immediate Window:

TestDate
1

I suspect you probably didn't delimit the dates with the # characters,
instead having datedate = 7/11/2005. Access would have done the arithmetic
on that, getting .0003173, which to Access is 00:00:27 on 30 Dec, 1899, and
30 Dec, 1899 was, in fact a Saturday.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> hey there all.
> Been using access for my project at school and I need the system to work
[quoted text clipped - 17 lines]
>
> Thanks Jason

Reply to this Message

Duane Hookom - 17 Dec 2005 21:37 GMT

The WeekDay() function expects a date data type as the first argument. It
always works correctly.

I expect you are feeding a date like 7/11/2005 which can be interpreted as 7
divided by 11 divided by 2005 which is 0.0003192 (much less than 1 day).
Since MS dates begin 12/30/1899, then your value will be a time early on
that date. Dec 30, 1899 was a Saturday.

Signature

Duane Hookom
MS Access MVP

> hey there all.
> Been using access for my project at school and I need the system to work
[quoted text clipped - 17 lines]
>
> Thanks Jason

Reply to this Message

warnett - 18 Dec 2005 20:23 GMT

yes, you are correct. i know i must delimit the dates but the language will
not allow me in the context i am using it in. i may not have made the
situation clear so here is the code. all the dates are in american format and
DLLI refers to a variable with a stored date in date format. the value of
dlli is dependent upon what has been entered onto the form

createdate = dateadd("d", 10, DLLI)
dow = weekday(createdate,2)

i know the correct date is being produced by createdate. im sure that if i
can find a way to delimit this date then the weekday function will work as i
am expecting it to. i have tried using CDate and CStr around createdate but
still to no avail.

i have been with this problem for a few weeks now... begining to get on my
nerves. thanks for all posts! jason

Reply to this Message

Duane Hookom - 19 Dec 2005 00:26 GMT

What you are describing should work. You may need to set a break point in
your code and step through it watching the values.

Signature

Duane Hookom
MS Access MVP

> yes, you are correct. i know i must delimit the dates but the language
> will
[quoted text clipped - 16 lines]
> i have been with this problem for a few weeks now... begining to get on my
> nerves. thanks for all posts! jason

Reply to this Message