Access2000 functions won't work in expressions

[billywt]

I am trying to use WeekdayName in a cross tab query. It causes an error. I am looking for a work-a-round for this.

The following statement is from the MS Access2000 Knowledge base:
"When you use one of the new Visual Basic for Applications functions as an expression, you may receive the #Name? error value. For example, if you set the ControlSource property of a text box to the expression =WeekdayName(6), the text box returns #Name? instead of the value Friday.

CAUSE
The FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent(), InStrRev(), MonthName(), Replace(), Round(), StrReverse() and WeekdayName() functions are not supported as expressions."

The following is the SQL code I am trying to use in a CrossTab Query.:

SELECT T_Days.DayNum, IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),[DayNum],Null) AS Dnm, IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),WeekdayName(Weekday(DateSerial(Fn_GetNu m("CboYr"),Fn_GetNum("CboMn"),[DayNum])),True),Null) AS WkDay, IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),Weekday(DateSerial(Fn_GetNum("CboYr"),F n_GetNum("CboMn"),[DayNum])),Null) AS WkD, IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),DatePart("ww",DateSerial(Fn_GetNum("Cbo Yr"),Fn_GetNum("CboMn"),[DayNum]))-DatePart("ww",DateSerial(Fn_GetNum("CboYr"),Fn_Get Num("CboMn"),1))+1,Null) AS WkNum, IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),DatePart("ww",DateSerial(Fn_GetNum("Cbo Yr"),Fn_GetNum("CboMn"),[DayNum]))-DatePart("ww",DateSerial(Fn_GetNum("CboYr"),Fn_Get Num("CboMn"),1))+1,Null) & IIf([DayNum]<=Day(DateSerial(Fn_GetNum("CboYr"),Fn_GetNum("Cbo Mn")+1,0)),Weekday(DateSerial(Fn_GetNum("CboYr"),F n_GetNum("CboMn"),[DayNum])),Null) AS WkNumWKD
FROM T_Days;

I get the following error message: "Undefined function 'WeekdayName" in expression."

I hope someone can help.
Thanks, billywt

 

[dhookom]

Does your MDB compile? I'm not sure when WeekdayName() was introduced in VBA but I don't even see it in the SQL above.

Press Ctrl+G to open the debug window and try enter:

? WeekdayName(3)

Do you get an error?
If WeekDayName() isn't found, you could right your own function with the same function name.

You can replace WeekDayName() with:
Choose([YourExpression], "Sunday", "Monday", "Tuesday", ..., "Saturday")


Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Thanks Duane,
WeekdayName is in the third line of the SQL.
I tried your suggestion, in the intermediate window, and it worked OK.
I will try re-writing the SQL and see how it works.
Thanks,
Bill

 

[dhookom]

I pasted the SQL into notepad and searched for the function but it didn't find it.

Maybe you could describe what you are attempting to do with the expressions in your query.

Are there typos of "Cbo Mn" and "CboMn" and "F n_GetNum" and "Fn_GetNum" and "CboYr" and "Cbo Yr"?


Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Duane,
I am sending an attachment, by email, of the Access program that I am trying to use. When I run the program it deletes the T_TempTaskPlan table. I made a copy of this table and named it T_TempTaskPlanOrg.
If it runs OK for you, I may have a problem with my Access2000 installation.

Thanks,
Bill

 

[dhookom]

Don't send files to people without a request. I am deleting your email and attachment. I don't have Access 2000 installed anywhere.

I have asked you questions. If you want my help (or others), please provide the information in this forum.

Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Sorry Duane, I did not know about sending files.

CboMn and cboYr are combo boxes.

The SQL listed above is used in other querys.

Here is a short query that also does not work:
Q_LookUpMonth
SELECT T_Month.MonthNum AS Mn, MonthName([MonthNum],True) AS MName
FROM T_Month
ORDER BY T_Month.MonthNum;

This query is the row source for combo box "CboMn".

This may be easier to find out why it fails.
I get the following error message: "Undefined function 'MonthName" in expression."

Thanks,
Bill

 

[dhookom]

Did the first SQL you posted have spelling errors as I asked earlier?

Did you try to debug your code?

What is the code of Fn_Get Num() and why are you calling it over and over?

CboMn and cboYr are strings in your SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Duane,

(Duane) Did the first SQL you posted have spelling errors as I asked earlier?
(Bill) No it did not have any spelling errors.

(Duane) Did you try to debug your code?
(Bill) I tryed to run it in the intermediat window but nothing happened. When I compile the code it checked out ok.

(Duane) What is the code of Fn_Get Num() and why are you calling it over and over?
(Bill) I do not know what this code is trying to do.
I downloaded this program from Rodgers Access Library site. The program "Crosstab_CalendarStyleTaskPlanner" writen by A.D. Tpjal.
I emailed him about the problem I am having and he said it worked fine on his Access2000.
I am trying to learn how the program works so I can write a semilar one for a different process.
Thanks, Bill

 

[dhookom]

If there aren't spelling errors then you are stating these variations are all spelled correctly "Cbo Mn" and "CboMn" and "F n_GetNum" and "Fn_GetNum" and "CboYr" and "Cbo Yr"?

I downloaded A.D.'s sample and it works for me (Access 2007). Where is the crosstab query that you posted in your original post?

If you are looking for a simple calendar style report, check out the no-code solution at

http://www.access.hookom.net/Samples.htm.

Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Duane,
The name of the query, in my original post,is Q_A.

Thanks,
Bill

 

[dhookom]

Did you make any changes in A.D.'s sample?
If you download it again and open it, do you get an error?

Duane
Hook'D on Access
MS Access MVP

 

[billywt]

Duane,
I just downloaded a fresh copy of the program and when I ran it, I had the same errors.
I must have a problem with my Access2000 installation.
Thanks for all of your help.
Bill