WeekDayName function error

The weekday and weekdayname functions should be available with the standard settings in Access 2000.  Go to any module (or simply create one using the New button in Modules) then press F2.  This gives you the object browser.  Type in Weekday in the combobox next to the binoculars and click the binoculars.  You will see that weekday and weekdayname are members of the VBA library (dll).  The weekday function is in the date/time class while the weekdayname function is in the strings class.  

Yes thats all correct what you said bknouse, but there must be some restriction about me using those functions in the Append Query in Access even though its in the VBA library and thats where i need this extra DLL or something?!?! As this query work fine on that one colleagues PC and on no others (i tried 3 others). His has this elusive DLL which neither him or i dont know whats called or where it is. Maybe an add on or something?!?!

As a test...
Create a new query using a table that has a date/time field.
Bring the date/time field to the output grid.
Next to this create an expression as follows:
WDN:Weekdayname(Weekday([FieldName]))
Note: FieldName will be the name of the field.
Run the query.

I tried what ya said and yes it still says "Undefined Function WeekdayName in expression" again. Thats also basically what i am doing in the original query, except the test i done for u was a select query and mines an append query. So there is some restriction on using them functions in a query.

Please check if there are any missing libraries.
Open or create a module.
Select Tools from the menu bar.
Select References.
Check if there are any libraries SELECTED that say MISSING.

No none say missing.

Thanks for checking.
When you used the object browser, did the Weekday and Weekdayname functions appear?
Try bringing up the Immediate Window (Cntl-G) and type in the following tests:
? weekday(#1/1/2000#)
This should return: 7

? weekdayname(weekday(#1/1/2000#))
This should return: Saturday

BTW: You should have the following libraries checked in references:
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Ole Automation

As another test, try creating a new database, add a table with a date/time field and enter 1 record.  Then try the query I mentioned before.

FYI: I don't use these functions.  I tried them on my machine without any problems (and no special dll).

YES they appeared in the object browser.
YES both tests work in the immediate window.
YES I have all those libraries you said checked and a few more.

I created a brand new database with jus one date/time field and entered a date manually. Made a query by bringing down the date field and then in the next colomn in field row put WDN:Weekdayname(Weekday([date])) and still says "Undefined Function Weekdayname in expression" when i open it again.

I know there something i missing but what i dunno! hehe!

Make sure that you have this file in that directory or even better update your VB6 runtime libraries using this link:

FILE IS:  C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

VB6 Runtime: http://www.softwarepatch.com/windows/vbrun6.html

If it's undefined you can check if it exists:

Open up a module
Press F2 that will bring up you Object Browser
Search for weekday or weekdayname

If nothing comes up then you have to install that file or install runtime library.

Hi lucas911,
Yes that file does exist in that directory.
bknouse also suggested about the Object Browser and yes both function are found when searched for in the Object Browser.
I'm 80% sure i have some kind of DLL or file missing for what i trying to do as i say my colleagues comp has it and it works fine on his! Just dont know what this file is or how to get it.....

Lucas had it right.  The file you need is VBE6.DLL, installed by default as "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL"

You can test the operation of the function by starting a blank user module.  Paste this code and run it:

Sub Test()
Dim x

For x = 1 to 7
  Debug.Print WeekdayName(x)
Next

End Sub

Also, I'm not sure what version of Access you are running, but if you have blocked unsafe expressions, there are many built-in functions and methods that will not work without Jet 4.0 SP 8.  If you still can't get it to work, paste this sub into a new module, and use it instead of WeekDayName():

Public Function User_WeekdayName(User_DateValue As Integer, _
        Optional User_StartOfWeek As Integer = 1, _
        Optional User_Abbreviate As Boolean = False) As String

Dim y As Integer
Dim x As String

y = User_DateValue + User_StartOfWeek - 1
Do While ((y < 1) Or (y > 7))
  If y < 1 Then y = y + 7
  If y > 7 Then y = y - 7
Loop

Select Case y
    Case 1: x = "Sunday"
    Case 2: x = "Monday"
    Case 3: x = "Tuesday"
    Case 4: x = "Wednesday"
    Case 5: x = "Thursday"
    Case 6: x = "Friday"
    Case 7: x = "Saturday"
    Case Else: x = ""
End Select

If User_Abbreviate Then
    User_WeekdayName = Left(x, 3)
Else
    User_WeekdayName = x
End If

End Function

Yes this is the problem routinet,

I already have that file and the module code u gave above works perfectly in the immediate window when i run it and it prints out all of the days of the week!!

I am using Access 2000, I dont know what u mean about blocking unsafe expressions. But my computer is fully updated by windoes update so dont think my jet program is out of date. But i know what i'm doing cant be done by default on Access 2000 because i tried it on 3 other computers and a clean installed, so its not jus mine! My colleague and my clients have whatever dll or file i need coz runs on there's perfectly. Just cant run the weekdayname inside the query on my computer or anyone elses. They doesn't know how or where got it coz it was a long time ago!! hehe!

Yer i can see the code u gave me could get round it but id like to try and find the straight forward way ie find this extra dll file.

extra details about the query:

Its an append query
Field: Expr1: Weekday([Date])
Append To: Expr1

Field: Expr2: WeekdayName([Expr1],False,1)
Append To: Expr2

Other fields normal.....

ERROR: "Runtime error 3085 Undefined function WeekDayName in expression"

Its the function being in the query it doesn't like coz even when i make new one and use weekdayname it doesn't work, same error

btw this is NOT my code it works perfectly for the clients as there comp has dll and dont really wanna change too much

Have a good weekend!

Just a long shot, but have you tried an explicit reference to the function?  Change your query field to look like this:

Field: Expr2: Strings.WeekdayName([Expr1],False,1)
Append To: Expr2


Also, you can right-click on the field and use the "Build..." command to build the expression through the dialog.  Chances are, those options are not going to work.  Have you tried resetting all your references, or reinstalling office?

OK, found your resolution.

http://support.microsoft.com/default.aspx?scid=kb;en-us;225956

It basically tells you to do the same thing I said a couple posts ago.  You have to create your own function and call it that way.  Entering it directly as an expression in a field will never work (or so they say!).  Here's code you can use to do this:

Public Function MyWeekDayName(User_DateValue As Long, _
        Optional User_Abbreviate As Boolean = False, _
        Optional User_StartOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek) As String

MyWeekDayName = WeekDayName(User_DateValue,User_Abbreviate,User_StartOfWeek)

End Function

Personally, I like the one I posted earlier a bit better.  The built-in will return an error if you pass a value <1 or >7.  My custom accounts for that.

Ok mate i done that and yes it works fine. I came round to ur way of thinkin becuase if u think about it, if this is not standard in Access as i think its not then when / if they restall the server the database will not work so its good for it to be local to the database.

I doin a full system check now and jus realised in another place this guy has used MonthName in a report like this (i haven't touched this part of database):

=MonthName(Month(Date()),True)

It comes up with dialog box wanting input for MonthName i guess this is the same problem as Weeldayname only with reports for MonthName. Code this in module aswell? how do that?

I jus conformed that is is the same problem by putting it into a new query and running it the error is exactly the same as was for weekdayname!!

"Undefined function MonthName in expression"

routinet u are a genius! As far as i can see the whole system works fine now. Now jus hope my clients like the changed and it run how they want!! hehe! Or there may be more q's on here later!! So look out!! LOL

U well deserve these points, u single handly helped me with all my problem on this project!! LOL
Thanks a lot mate
Take care for the future