Solved

WeekDayName function error

Posted on 2004-04-28
19
1,584 Views
Last Modified: 2011-04-14
I'm a newbee so be gentle!! hehe!
I am making changes to a Access database for a client. This was developed before by another person. On the system it uses the WeekDay and WeekDayName function in a query. This works fine on their PC but not mine. It says "Runtime error 3085 Undefined function WeekDayName in expression" I have been told i need a extra DLL file to use this function. I know this because on my coleagues computer he has the file i need and using the same database the query works no problems!! I looked in the office directory but as we have different office elements installed,i cant distinguish whats for office and whats the DLL i need to get these functions to work. We are both running office 2000 though. I have also done many google searches but cant seem to find it!! What is the DLL called and where can i get it???
0
Comment
Question by:How_Di
  • 9
  • 4
  • 4
  • +1
19 Comments
 
LVL 6

Expert Comment

by:bknouse
Comment Utility
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.  
0
 

Author Comment

by:How_Di
Comment Utility
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?!?!
0
 
LVL 6

Expert Comment

by:bknouse
Comment Utility
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.
0
 

Author Comment

by:How_Di
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:bknouse
Comment Utility
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.

0
 

Author Comment

by:How_Di
Comment Utility
No none say missing.
0
 
LVL 6

Expert Comment

by:bknouse
Comment Utility
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).

0
 

Author Comment

by:How_Di
Comment Utility
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!
0
 
LVL 13

Expert Comment

by:lucas911
Comment Utility
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

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 13

Expert Comment

by:lucas911
Comment Utility
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.
0
 

Author Comment

by:How_Di
Comment Utility
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.....
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
0
 

Author Comment

by:How_Di
Comment Utility
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!
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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?
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.
0
 

Author Comment

by:How_Di
Comment Utility
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?
0
 

Author Comment

by:How_Di
Comment Utility
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"
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 95 total points
Comment Utility
Here's one for MonthName:

Public Function User_MonthName(User_DateValue As Integer, Optional User_Abbreviate As Boolean = False) As String

Dim y As Integer
Dim x As String

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

Select Case y
    Case 1: x = "January"
    Case 2: x = "February"
    Case 3: x = "March"
    Case 4: x = "April"
    Case 5: x = "May"
    Case 6: x = "June"
    Case 7: x = "July"
    Case 8: x = "August"
    Case 9: x = "September"
    Case 10: x = "October"
    Case 11: x = "November"
    Case 12: x = "December"
    Case Else: x = ""
End Select

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

End Function
0
 

Author Comment

by:How_Di
Comment Utility
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now