Returning the day and not full date...

[agehoops]

I was wondering if there was a function in access which allows the user to type in a date, and instead of showing just the date, it shows the actual day of the week as well, in a seperate field. Is this possible?

Thanks
Adrian

 

[neileg]

Lookat the Format() function in Access help. It will allow you to display the date pretty well any way you can think of.

 

[Rod D]

Hi Adrian

I am running Access 2002 and have the WeekdayName function available which will do the trick for you.

The syntax is ...
=WeekdayName(Weekday([<DateField>]),<Abbreviate>,<FirstDay>)

Where:
WeekDay[...] function returns the day index number
<DateField> = the field name of the date that you key
<Abbreviate> = (True/False) Should the result be abbreviated? ("Mon" as opposed to "Monday")-Optional
<Firstday> = First day of week (0=Default, 1=Sunday, 2=Monday etc.) This will affect the result - Optional

Have a look in Help for more info.

Hope this helps
Rgds
Rod

 

[agehoops]

Ok, i'm able to get it to display the day in the field, but i cant seem to be able to type the date into one text box and have it stay as a date, but at the same time, display the day of that date, in another text box

 

[agehoops]

brilliant! Forget my last post, it's working fine now. Thank you so much.

 

[Rod D]

I bow to Neil's expertise - I seem to have found the proverbial sledge-hammer!

In both of our solutions, just create another field with it's ControlSource property set to the date input field.

Rgds
Rod

 

[agehoops]

I managed to get your way working perfectly except that if i do not have a date in the first field, the field which will lookup the day of week, has #Error in it. Is there any way around this?
Thanks

 

[neileg]

You need to use some VBA code so that your second text box is only populated after something is entered in the first. This means using the After Update event. The code might be as follows (untested):

Me.txtDayOfWeek=Format([txtMyDate],"dddd")

 

[agehoops]

Still get the #Error with it until i type in a date, and if i remove the Control Source from the Day Of Week field, it just throws back another error. Is there a way of using an If statement to say if it is equal to #Error then make it blank, or if the Date for Booking is empty then make day of week empty??

 

[Rod D]

In answer to your problem, I am assuming that you are trying the "WeekdayName" function solution.

We need to check to see if there is anything in the date field before attempting the calculation. To do this, change the controlsource formula to:

=IIf(IsNull([<datefield>]),"",WeekdayName(Weekday([<Datefield>]),False,1))

This will return a Null if the <datefield> is null.

Another thought though.
If you take SV's idea and change the format of the field to something like...
dddd dd/mm/yyyy
and the input mask to something like
00/00/0000 (Change the "0"'s to "9"'s if you want)
You can then enter the date as normal and Access will display the day automatically - meaning you will not need a second field...

Rgds
Rod

 

[Rod D]

Sorry SV & Neil, gave you the wrong credits there...must be getting old...

 

[agehoops]

Working!

Thanks Rod. Working perfectly now. I was able to use the other solutions, however i want there to be 2 seperate fields. Now that i've got 2 seperate fields, i want to be able to creat IF statements using the Day of the week field, but i want the Date itself, to be stored in the table as well.

Thanks very much for all of your help guys. Been brilliant!

 

[neileg]

'S funny, it works for me. See the attached.

 

[neileg]

Thanks Rod. Working perfectly now. I was able to use the other solutions, however i want there to be 2 seperate fields. Now that i've got 2 seperate fields, i want to be able to creat IF statements using the Day of the week field, but i want the Date itself, to be stored in the table as well.

Well, no you don't, actually. You shouldn't be storing the Day of the week in yoiur table at all.

You can use Format([MyDAte],'dddd') in your IIF function to give you the weekday any time you want

 

[Bat17]

Looks like over-kill either way
dump the date field onto the form twice, format the first to what ever flavour of Date you need, goto the second, in properties, set its format to DDDD, Locked to Yes, Tab Stop to No.

HTH

Peter

 

[agehoops]

Alright, well i'll leave it as it is for now, and if i come up to any problems with the way it is setup, i will start to change it with the other solutions. Thanks for the help

 

[agehoops]

Ok, it is working and returning the day of the week in the field i want, however it is not actually putting this into the table. I know i don't NEED it to, but it is just a preference i would like it to do.

The control source for the day of week field is

=IIf(IsNull([Date for Booking]),"",WeekdayName(Weekday([Date for Booking]),False,1))

Thanks

 

[Bat17]

You cant add the value from a calculated field directly to a table, you will need to bind the field to the table and use code on the update event on the date to write the weekday to you day field as per Neil's earlier post.

You will also need to make sure that the date can only be edited via the forms. If it is altered directly in the table or a query then the day will no longer match the date, which is why this sort of thing is best avoided.

Peter

 

[Lightwave]

Good call people - I'd been looking for this.