Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Chicago, IL

    Unanswered: Convert number field to day of week!


    I addressed this in the past but wasn't really able to work this out... Now it's back!

    For this exercise, there are two fields that I would like to work with:

    [UPP_CHECK_DATE] - Format: Number - This value contained in this field is actually a date, formatted as YYYYMMDD, but it was imported as a NUMBER field.

    [TYPE] - Format: Text - blank field

    Here is what I would like to do:

    I would like to create a query or VB code that will populate the TYPE field with one of three values, based on the day of week represented by the "Date" contained in the UPP_CHECK_DATE field.

    More specifically,
    I want the TYPE field to be populated with "CASH" if the day of week happens to be a Monday.
    I want the TYPE field to be populated with "ADJ" if the day of week happens to be a Friday.
    Otherwise, I want the TYPE field to be populated with "UPP".

    I have seen examples of where DateAsText is used, but this is a number field and I can't convert it for practical reasons.

    I appreciate any and all help offered!



  2. #2
    Join Date
    Jan 2004
    Myanmar (Burma)

    Lightbulb Here with simple code for idea.

    Dim UPP_CHECK_DATE As Long
    Dim Type_ As String
    Dim myWeekDay As Integer

    UPP_CHECK_DATE = 38027 ' 38027is numeric value of 10-Feb-2004, Tuesday.
    myWeekDay = Weekday(UPP_CHECK_DATE)

    Select Case myWeekDay
    Case 1 ' for Sunday
    Type_ = "UPP"
    Case 2 ' for Monday
    Type_ = "CASH"
    Case 3 ' for Tuesday
    Type_ = "UPP"
    Case 4 ' for Wednesday
    Type_ = "UPP"
    Case 5 ' for Thursday
    Type_ = "UPP"
    Case 6 ' for Friday
    Type_ = "ADJ"
    Case 7 ' for Saturday
    Type_ = "UPP"
    End Select
    Cyber : The easiest place to make huge mistake.

  3. #3
    Join Date
    Jan 2004
    The Netherlands
    For the first rule in normalisation states: Do not store calculatable values!

    To convert your YYYYMMDD number to a "real date" use:

    Then dunk that into a function like the one mmlatt posted.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts