format day of week NOT from a date

[Guest]

Hi,
I need to create a field that formats day of week rather than text. We will
enter the day (currently combo box), but later we will need a list BY day of
week. The problem is that without a proper format, "Monday" comes after
"Friday" (alphabetic sorting).

There is no date to tie this to, and I am trying to find a solution that
avoids the unnecessary trouble of creating a whole table where day of week is
tied to a number then having to use this silly number and table in every
single query! I'm hoping that since Excel has no trouble with this, Access
won't either.

Thank you!

 

[Pieter Wijnen]

Use the WeekDay Function on the Date Optionally with the Dayofweek set to
vbMonday (2 in Query)
Weekday(now) Yields 2 for today
Weekday(now,2) Yields 1 For today

HTH

Pieter

 

[Pieter Wijnen]

Use the WeekDay Function on the Date Optionally with the Dayofweek set to
vbMonday (2 in Query)
Weekday(now) Yields 2 for today
Weekday(now,2) Yields 1 For today

HTH

Pieter

 

[Guest]

I think you have a combo box with the days of the week typed in as text ??
If so (and you are wanting to record the date that you update the record ??)

You could create a new field in your table called [DateField] and on your
form use the AfterUpdate of the combo ([DateCombo]). Just an idea but may
have missunderstood what you're trying to do.


Private Sub DateCombo_AfterUpdate()
On Error GoTo DateCombo_Click_Err

Forms!Form1!DateField = Date


DateCombo_AfterUpdate_Exit:
Exit Sub

DateCombo_AfterUpdate_Err:
MsgBox Error$
Resume DateCombo_AfterUpdate_Exit

End Sub

 

[Guest]

I think you have a combo box with the days of the week typed in as text ??
If so (and you are wanting to record the date that you update the record ??)

You could create a new field in your table called [DateField] and on your
form use the AfterUpdate of the combo ([DateCombo]). Just an idea but may
have missunderstood what you're trying to do.


Private Sub DateCombo_AfterUpdate()
On Error GoTo DateCombo_Click_Err

Forms!Form1!DateField = Date


DateCombo_AfterUpdate_Exit:
Exit Sub

DateCombo_AfterUpdate_Err:
MsgBox Error$
Resume DateCombo_AfterUpdate_Exit

End Sub

 

[Guest]

Hi again,
I really appreciate both responses!

Unfortuantely the first response is WAY over my head. If that is the best
way to do this, I would just need a little help using vb.

All I am trying to do is to get Access to figure out that when I sort by
DayOfWeek, the order is Monday, Tuesday, Wednesday, Thursday, Friday. I have
DayOfWeek in my table and a single column combo box in my form to avoid entry
errors.

The second answer seems to rely on me having an actual DATE to which the day
of week would be tied. We do not. The day of week we are capturing is not
tied to any date field. I just need day of week.

Thank you again!!!

 

[Guest]

Hi again,
I really appreciate both responses!

Unfortuantely the first response is WAY over my head. If that is the best
way to do this, I would just need a little help using vb.

All I am trying to do is to get Access to figure out that when I sort by
DayOfWeek, the order is Monday, Tuesday, Wednesday, Thursday, Friday. I have
DayOfWeek in my table and a single column combo box in my form to avoid entry
errors.

The second answer seems to rely on me having an actual DATE to which the day
of week would be tied. We do not. The day of week we are capturing is not
tied to any date field. I just need day of week.

Thank you again!!!

 

[Guest]

There is a simple way to to allocate numerical vaules (AfterUpdate) to any
text value but if you didnt understand Pieter's answer then you may not be
able to run this simply.

So, try this.
Create a Combo Box
Make sure the Combo Box has 2 columns
In the first colum write 1, 2, 3 etc up to 7.
In the second column put Monday, Tuesday, Wednesday etc
On the properties box click on the Data tab and select Bound Coloum
Set the Bounnd column to 2
Go to Format on the properties box and in the area next to
Column Count = 2
Column Heads = No
Column widths = 0;2
Save the form

Sort you details on the number column

Hope this helps

If you would like to have a go at some VERY simply code let me know

 

[Guest]

There is a simple way to to allocate numerical vaules (AfterUpdate) to any
text value but if you didnt understand Pieter's answer then you may not be
able to run this simply.

So, try this.
Create a Combo Box
Make sure the Combo Box has 2 columns
In the first colum write 1, 2, 3 etc up to 7.
In the second column put Monday, Tuesday, Wednesday etc
On the properties box click on the Data tab and select Bound Coloum
Set the Bounnd column to 2
Go to Format on the properties box and in the area next to
Column Count = 2
Column Heads = No
Column widths = 0;2
Save the form

Sort you details on the number column

Hope this helps

If you would like to have a go at some VERY simply code let me know

 

[Jamie Collins]

Unfortuantely the first response is WAY over my head. If that is the best
way to do this, I would just need a little help using vb.

No need for VB:

CREATE TABLE Test4 (
my_day VARCHAR(9) NOT NULL,
CHECK (my_day IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday'))
)
;
INSERT INTO Test4 VALUES ('Tuesday')
;
INSERT INTO Test4 VALUES ('Monday')
;
INSERT INTO Test4 VALUES ('Thursday')
;
INSERT INTO Test4 VALUES ('Wednesday')
;
INSERT INTO Test4 VALUES ('Sunday')
;
INSERT INTO Test4 VALUES ('Saturday')
;
INSERT INTO Test4 VALUES ('Friday')
;
SELECT my_day
FROM Test4
ORDER BY (INSTR(1, 'montuewedthufrisatsun', LCASE$(LEFT$(my_day, 3))) -
1) / 3
;

 

[Jamie Collins]

Unfortuantely the first response is WAY over my head. If that is the best
way to do this, I would just need a little help using vb.

No need for VB:

CREATE TABLE Test4 (
my_day VARCHAR(9) NOT NULL,
CHECK (my_day IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday'))
)
;
INSERT INTO Test4 VALUES ('Tuesday')
;
INSERT INTO Test4 VALUES ('Monday')
;
INSERT INTO Test4 VALUES ('Thursday')
;
INSERT INTO Test4 VALUES ('Wednesday')
;
INSERT INTO Test4 VALUES ('Sunday')
;
INSERT INTO Test4 VALUES ('Saturday')
;
INSERT INTO Test4 VALUES ('Friday')
;
SELECT my_day
FROM Test4
ORDER BY (INSTR(1, 'montuewedthufrisatsun', LCASE$(LEFT$(my_day, 3))) -
1) / 3
;