WeekdayName equivalent in 2000

[sunnybrook]

Have Access 2000 which doesn't appear to support WeekdayName function (think it is in 2007).



I have a table with some columns named Monday thru Sunday.

Would like to reference the appropriate column in a WHERE statement based on a date.

Is there a simple way of dynamically generating the column name from the date?

[maximus]

Well if WeekdayName doesn't work in Access 2000 then you can use this Function written below:

Syntax:NameofDay(DateField)

Option Compare Database
Dim strDayName(7) As String
Dim intDayNumber As String
Dim MyDate As Date
Function NameofDay(MyDate) As String


If IsNull(MyDate) Then
Exit Function
Else
strDayName(1) = "Sunday" '/Array containing the names of the Days/
strDayName(2) = "Monday"
strDayName(3) = "TuesDay"
strDayName(4) = "Wednesday"
strDayName(5) = "Thursday"
strDayName(6) = "Friday"
strDayName(7) = "Saturday"

intDayNumber = Weekday(MyDate)
NameofDay = strDayName(intDayNumber)
End If
End Function

mark the problem solved if this solves your problem.

[sunnybrook]

Thanks for that, it works great (with the addition of "Friday", lol). I really appreciate it.


Now, I just have to get my WHERE statement working.....

I cannot believe how a few years away from ACCESS has fried my brain in that regard.

With columns Monday thru Sunday SELECTed, I have .... WHERE NameofDay(Weekday(Format("14/07/10","dd/mm/yy"),1))='x';

Does this check column Wednesday for 'x' or do I have to do something to the expression so that SQL knows that the result 'Wednesday' refers to the column of that name?

Can I use the same expression in SELECT to retrieve only the column I am interested in e.g.

SELECT X1.aa, X1.bb, NameofDay(Weekday(Format("14/07/10","dd/mm/yy"),1)) AS Fred
FROM X1
WHERE Fred = 'x';

so I have columns aa, bb and Wednesday (AS Fred) or do I have to do something to the expression for the SELECT clause to recognise the result as a column.

[maximus]

use NameofDay(Format("14/07/10","dd/mm/yy"))='X' instead of NameofDay(Weekday(Format("14/07/10","dd/mm/yy"),1))

The date formated to dd/mm/yy and subsequently the name of the day is given by the Function.

[sunnybrook]

Yes, thanks. However, how do I use the result as a column name?

e.g. in WHERE NameofDay((Format("14/07/10","dd/mm/yy"))='x'

to check column Wednesday or in

SELECT NameofDay((Format("14/07/10","dd/mm/yy")) AS Fred

to generate column Fred with contents of column Wednesday.

[maximus]

I have attached this code on to the OnClick Event of a Command on a form. I have a Table Called Table 1. Its has a Field Sunday. To Select this Field and to create a Query I have used this Code: 07/18/2010 is a Sunday and Thus the field name is derived using the function.


Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim MyString as String
Dim strSQL as Object

MyString = "Select " & NameofDay(#7/18/2010#) & " From Table1"
Set strSQL = CurrentDb.CreateQueryDef("MyQuery", myString)



Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


This way you can create dynamic queries using Date from an form control as reference.

if this solves your problem mark this thread solved.

[sunnybrook]

Assuming that your code is equivalent to....

SELECT NameofDay(#7/18/2010#)
FROM Table1;

...in the SQL View of a query in Access, using my database, the result is a dataset consisting of one column (called Expr1000) containing x records (where x is the number of records in the original dataset) each with content "Sunday".

Perhaps I have made an invalid assumption. as to the appropriate format of the SQL View query.

What I want is a dataset consisting of a column containing the values from the column known as Sunday.

[sunnybrook]

I hate having to change my approach, once I have started. However, despite the horrendous additional work that I now have to do in capturing data, I have merged the Monday thru Sunday columns into one containing (a concatenation of) the digits 1-7 and now do a instr() function to see if the day of the week is valid.

One big mistake I have made is assuming that I can use SQL 2000 Server functions whereas I am restricted to Access 2000 functions. That may explain why I can't use the indirect addressing that I am/was trying to use.

[maximus]

Look into this example. Here I have used the same function to reference to a field. I have a table table which has a Field Sunday. Now I have used this function to create a query To select only this column the code as below.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
strdropquery = "Drop Table MyQuery"
On Error Resume Next
CurrentDb.Execute strdropquery, dbFailOnError

myString = "Select " & NameofDay(#7/18/2010#) & " From Table1"
Set strSQL = CurrentDb.CreateQueryDef("MyQuery", myString)
DoCmd.OpenQuery "MyQuery"


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


I have used the function to reference to the Field.

Refer to the sample.

A start up form will open click create query button.

Please refer to the sample

Note:
Changing the date like the example below the Select query created will select a Field Monday

myString = "Select " & NameofDay(#7/19/2010#) & " From Table1"

Sometimes you need creative thinking rather than hasty changes to the work procedure. Try to think a solution to a problem not a easy way out. This will in the long run improve your skills immensely