WeekDay problem in office 2003

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

Dim MyDate, MyWeekDay
MyDate = #February 12, 1969# ' Assign a date.
MyWeekDay = Weekday(MyDate) ' MyWeekDay contains 4 because
' MyDate represents a Wednesday.

I did a help search for WeekDay and got the above example.

Why does the weekday say type mismatch when I point to it?
It looks like the weekday function is not working in office
2003?
 
First, it is a poor example in the Help file. The first line should read

Dim MyDate As Date, MyWeekDay As Integer

Next, I just ran it with no errors. Weekday does work. If it doesn't for
you, then there is probably a References problem. I suspect, that if you
played with a few more functions, that you would find more that don't work.
For information of fixing References, try this link.

http://www.allenbrowne.com/ser-38.html
 
PS,

You say the problem occurs when you "point" to it. How are you pointing to
it? What data type is the field that you are using this in? Because of the
way the variable is Dimmed, it is a Variant.
 
Wayne said:
PS,

You say the problem occurs when you "point" to it. How are you pointing to
it? What data type is the field that you are using this in? Because of the
way the variable is Dimmed, it is a Variant.


Here is how I am doing this.



Dim MyDate As Date
Dim MyWeekDay As Integer
MyDate = #February 12, 1969#    ' Assign a date.
MyWeekDay = Weekday(MyDate)    ' MyWeekDay contains 4 because 
    ' MyDate represents a Wednesday.



1 I made an unbound txt box on a form.
2 I selected click in properties and selected code.
3 I pasted in the help example for the WeekDay function.
4 I got red text on the line

MyDate = #February 12, 1969#    ' Assign a date.

5 I changed #February 12, 1969# to (Date).
6 I clicked on the unbound txt box and it debugged with a
type mismatch for line

MyWeekDay = Weekday(MyDate)

7 I placed my cursor over the MyDate and it gives me the
current date.
8 I moved my cursor over Weekday and it says type mismatch.
9 I moved my cursor over MyWeekDay and it says 0

Am I using the weekday function in the right place?
Can it be used in VBA Code in my form?
Does it only work in a Query and not in VBA Code?
I have the webpage you gave me and will work on it later.
 
Ok, the main reason for the PS was because I realized that this was in the
Queries newsgroup and though it might have to do with the data type of the
field in a query.

I copied and pasted your code into a Public Sub and ran it. It worked
without errors. One thing I did notice though, Access wouldn't leave
#February 12, 1969# as you have it. Instead it changed it to #2/12/1969# (US
Date format). However, to make the change, it had to recognize it, so that
really isn't a problem.

You could place this code in a function, passing a field value to the
function, and have it return the Weekday for it. However, it would be easier
just to use the Weekday function and get the Weekday of the field value.

Example:
WeekdayOfField:Weekday([FieldName])

This would create a calculated field in the query. There is no need to
reinvent the wheel by creating your own weekday function when there already
is one.

To attach this code to a textbox's click event, set the On Click in the
textbox's Properties sheet to [Event Procedure], click the ... button to the
right of it and the code editor will open. Place the code between the Sub
....... and End Sub statements. If just simply pasting this code into the
editor gives you red text on the line you indicate, you really need to check
the References item mention in my first post.
 
Wayne said:
Ok, the main reason for the PS was because I realized that this was in the
Queries newsgroup and though it might have to do with the data type of the
field in a query.

I copied and pasted your code into a Public Sub and ran it. It worked
without errors. One thing I did notice though, Access wouldn't leave
#February 12, 1969# as you have it. Instead it changed it to #2/12/1969#
#(US
Date format). However, to make the change, it had to recognize it, so that
really isn't a problem.

You could place this code in a function, passing a field value to the
function, and have it return the Weekday for it. However, it would be
easier just to use the Weekday function and get the Weekday of the field
value.

Example:
WeekdayOfField:Weekday([FieldName])

This would create a calculated field in the query. There is no need to
reinvent the wheel by creating your own weekday function when there
already is one.

To attach this code to a textbox's click event, set the On Click in the
textbox's Properties sheet to [Event Procedure], click the ... button to
the right of it and the code editor will open. Place the code between the
Sub ...... and End Sub statements. If just simply pasting this code into
the editor gives you red text on the line you indicate, you really need to
check the References item mention in my first post.





Could I get a snapshot of your Reference? Maybe I could identify the
missing reference. I looked in mine and I don't know which one to pick.
There are many to choose.
I will be looking more into it.

Just to let you know, I had the same problem with office 2003 at home and
office 20002 at work. The old type missmatch problem.
 
Fred said:
Could I get a snapshot of your Reference? Maybe I could identify the
missing reference. I looked in mine and I don't know which one to pick.
There are many to choose.
I will be looking more into it.

In a situation like this (where the same database works on one machine but
not on another), Reference problems are generally caused by one of the
selected references being marked as "MISSING:", as opposed to having
forgotten to select a reference in the first place.
 
As Doug mentioned, it will probably be listed as MISSING. Sometimes
unchecking a reference, clicking Ok, then reopening the dialog and
rechecking the reference will fix things. Sometimes it doesn't matter which
reference you do this to, you just have to remember which one it was,
because after you uncheck it, you'll have to scroll down the list to find it
again. It won't be up at the top any longer.

To see what the default references are for a database, create a new, blank
database and look at the references it has. Any beyond that should only be
checked it you need them for what you're doing.

--
Wayne Morgan
MS Access MVP


Fred said:
Wayne said:
Ok, the main reason for the PS was because I realized that this was in
the
Queries newsgroup and though it might have to do with the data type of
the
field in a query.

I copied and pasted your code into a Public Sub and ran it. It worked
without errors. One thing I did notice though, Access wouldn't leave
#February 12, 1969# as you have it. Instead it changed it to #2/12/1969#
#(US
Date format). However, to make the change, it had to recognize it, so
that
really isn't a problem.

You could place this code in a function, passing a field value to the
function, and have it return the Weekday for it. However, it would be
easier just to use the Weekday function and get the Weekday of the field
value.

Example:
WeekdayOfField:Weekday([FieldName])

This would create a calculated field in the query. There is no need to
reinvent the wheel by creating your own weekday function when there
already is one.

To attach this code to a textbox's click event, set the On Click in the
textbox's Properties sheet to [Event Procedure], click the ... button to
the right of it and the code editor will open. Place the code between the
Sub ...... and End Sub statements. If just simply pasting this code into
the editor gives you red text on the line you indicate, you really need
to
check the References item mention in my first post.





Could I get a snapshot of your Reference? Maybe I could identify the
missing reference. I looked in mine and I don't know which one to pick.
There are many to choose.
I will be looking more into it.

Just to let you know, I had the same problem with office 2003 at home and
office 20002 at work. The old type missmatch problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top