Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

format day of week NOT from a date

Discussion in 'Information Technology' started by ash, Jul 28, 2006.

  1. ash

    ash
    Expand Collapse
    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!
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "ash" <ash@discussions.microsoft.com> wrote in message
    news:8ED3323B-C039-4AB3-AB9A-5706E35A9C51@microsoft.com...
    > 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!
     
  4. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    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


    --
    Wayne




    "Pieter Wijnen" wrote:

    > 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
    >
    > "ash" <ash@discussions.microsoft.com> wrote in message
    > news:8ED3323B-C039-4AB3-AB9A-5706E35A9C51@microsoft.com...
    > > 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!

    >
    >
    >
     
  5. ash

    ash
    Expand Collapse
    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!!!
     
  6. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    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


    --
    Wayne




    "ash" wrote:

    > 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!!!
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    ash wrote:
    > 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
    ;
     

Share This Page