convert a number to weekday name

[jamaarneen]

Hi

It might be a very simple function, what I don't know yet.

I have a field which contains values from 1 to 7, which represents weekdays.

what I want is, that when i am using this field value in a form/report, it should be converted to weekday-name string. (example: the value 1 should appear in the textbox as Sunday)

Thanks in advanced, Ja

 

[dhookom]

March of this year began on a Sunday

  =Format(DateSerial(2009,3,[YourUnnamedField]),"dddd")

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

If you're just wanting to convert 1 - 7 to weekday names, just add a table with 2 columns: DayNbr, DayNam. Use as a lookup.

But as Duane pointed out, you can return weekday names from actual dates, using the Format function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jamaarneen]

Thank you both for your response

Duane: your suggestion is great, but I'll try to explain my problem better.
the thing is, I have a table containing data over volunteers. a volunteer can be available weekly, semi-monthly, monthly... but everyone has one week day that he prefers to help. So that value - preferred day - is stored in a field. but it's not stored as "sunday", it's stored as a numeric value 1. [blue]the reason i did so, because I want to be able to match the value of this field, to the weekday value of a date, so i can get all volunteers how are relevant for this day/blue].

all i want is, that the user should not see in the textbox the number 1, he should see "Sunday"

(it is not a 'date' that I want to get his weekday-name, so 'DateSerial' will not help me.)

Skip: I have thought myself of the option working with a table, but I am thinking that there should be a function for that - to convert the weekday-number to a weekday name.

Thanks again, Ja

 

[PHV]

should be a function for that - to convert the weekday-number to a weekday name
This is precisely what Duane's suggestion does ...

 

[jamaarneen]

Hi to all of you

Right, I got already what Duane's suggestion does.. but that' a trick... (well, i must admit, some times thinking tricky, is the most efficient...)

and, the[blue] good news [/blue]is: there is a simple function named weekday(), and it works perfectly. the code would be like this:

WeekdayName([vol_PreferenceWeekday], , vbSunday)

but, the [red]bad news [/red]is, i want to use in a row source for a control box. it will work, but if i want to add the 3th argument, it will give me the annoying: #name? (reminding my other thread now here in 'Access forms forum')
and the 3th argument is important, because my systems first-day-of-week is Monday, as for my form it should be Sunday.

hoping for your help (or, should i take the tricky way???)
Ja

 

[PHV]

Replace vbSunday with 1 (ie its value: one)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamaarneen]

I tried already, and here is what i got:

=WeekdayName([vol_PreferenceWeekday[red][b]][/b][/red],0,1)

this won't let me leave the property box, by giving a message: "the expression you entered contains invalid text"

=WeekdayName([vol_PreferenceWeekday,0,1[red][b]][/b][/red])

this one will let me go through, but in form view it will appear: #name?

Thanks, Ja

 

[dhookom]

Do you have a user-defined function with the same name as the VBA function?

What is the name of your text box?

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

thanks Duane

As you probably noticed in my other thread, the problem is solved:
; in place of ,