Date value as a weekday

[nick5196]

Hi,
I have a field which calculates a date in the form dd/mm/yyyy.
I would like a msgbox to appear if the weekday e.g. monday is equal to a preselected weekday within a combo.

The first hurdle is understanding how to define a date as a weekday, if possible at all.

Thanks a lot for any help
Nick

 

[davper]

Weekday(Date()) returns an integer from 1(Sunday) to 7(Saturday)

iif(Weekday(Date()) between 2 and 6, "Weekday", "Weekend")

 

[nick5196]

How would i incorporate this within my code
The combo box has a value list "monday; tuesday.......".

Would i need to specify these as an integer or can I display the date as a weekday and show the msgbox if the two are =.

thanks

 

[nick5196]

Actually that is okay, I have the code working if i "manually" input an integer. All i need to know now is how to identify an unbound textbox's value and state that the masgbox will fire if the Weekday(Me.Text295) = the unbound textbox's value.

i have tried me.textboxname.value but this does not work.

Any idea, thanks

 

[davper]

When I build the combobox, I would have 2 values: the integer for the day of the week and this is your bound column and is hidden from view, The other value is the name of the weekday.

If rewriting the combosbox is not possible, you can use a Select Case

select case combobox.value
case "Monday"
variable = 2
case "Tuesday"
variable = 3
...etc.
end select

 

[nick5196]

How do i specify these "cases" within the code.
i am trying this to no avail

If (weekday(Me.Text295)) = Me.collegeday.selectcase then...

thanks, almost there!

 

[nick5196]

If not i did have a similar system which resulted in 2 unbound textboxes. One with the number corresponding to the weekday of the date and the other to the combobox value as a number.

I just need the code to fire when the two are eaqual. Im trying this but am unsure as to why it wont work but will when i click the unboude textboxes and put the numbers in a few times!?!

If Me.Text427.Value = Me.Text429.Value Then
MsgBox...

 

[vbaInet]

What davper means is, the unbound Combo box should have two columns - the first column will be the number equivalent of the weekday and the second will be the weekday name. So for the Row Source:

1;Sunday;2;Monday;3;Tuesday;[COLOR=Red]... etc[/COLOR]

Set the properties of the combo box as follows:

Column Count - 2
Bound Column - 1
Column Width - 0cm; 3cm

where 3cm can be anything you want it to be. The most likely setting would be the Width of your combo box.

What you will end up with is your combo box's values looking like this:

Sunday
Monday
Tuesday
[COLOR=Red]. 
.
. [/COLOR]
[COLOR=Red]and so on ...[/COLOR]

You will notice that the numeric value is hidden because you set the first column's width to zero.

In the After Update event of the Combo box that is bound to your date field, the code will look something like this:

If Not IsNull(Me.[COLOR=Red]Bound[/COLOR]Combox) And Not IsNull([COLOR=Blue]Unbound[/COLOR]Combobox) Then
    If Weekday(Me.[COLOR=Red]Bound[/COLOR]Combox) = Me.[COLOR=Blue]Unbound[/COLOR]Combobox Then
        Msgbox "Weekday the same"
    End If
End If

 

[gemma-the-husky]

.... but note that you can get at the weekday directly

you have the weekday function, and various constants ...

select case weekday(somedate)
case vbmonday : ....
case vbsunday : ....
end select