Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Unanswered: Access2000 functions won't work in expressions

    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.

    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.:

    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 hope someone can help.
    Thanks, billywt

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Why not just return just the date field in the SQL statement (without using WeekDay or any other date functions) and then in the rest of your code after you've opened the recordset, set a variable to =Weekday(rs!MyField)? Or set the variable with the expression before the SQL statement and then use that variable in the SQL Statement.

    ex:
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    SQL = "Select * from MyTable"
    rs.open SQL, currentProject.connection,adopendynamic, adopenoptimistic
    dim SomeVariable as variant
    dim SWeekDay as variant
    SomeVariable = weekday(rs!DateFieldX)
    SWeekDay = Format(rs!DateFieldX,"ddd")
    ....
    ....
    rs.close
    set rs = nothing

    Also look at the format command (for dates). For example, to get the weekdayname (ie. Friday) of a date, you put in Format([MyDateField],"ddd") Not sure why you need to use the DateSerial function to do this.
    Last edited by pkstormy; 11-01-08 at 03:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •