Using Week, Month, Year and Day of Week to Determine Date

[sgurusha]

Folks:

Is there a way you can use the week, month(possibly), year and Day of the Week to determine the date?

For example Week 2 of Sunday, January, 2000 would be 9/1/2000.

Not sure what VBA functions in MS Access 97 can help in determining this.

Thanks in Advance.


Sunil W. G.

 

[WHM]

Check help in msdn help for date functions in SQL statements, the formats you are trying to achieve should be listed along with their syntax. (I have copied a partial listing of the date format functions below)

Returns a Variant (String) containing anexpression formatted according to instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. Aconstant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the year.


Settings

The firstdayofweekargument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday


The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the year.
vbFirstFullWeek 3 Start with the first full week of the year.


Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric formats.
Dates and times Use predefined named date/time formats or create user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.


If you try to format a number without specifying format, Format provides functionality similar to the Str function, although it is internationally aware. However, positive numbers formatted as strings using Format don’t include a leading space reserved for the sign of the value; those converted using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date formatting, the supplied expression must be Gregorian. If the Visual Basic Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is unchanged. If the calendar is Hijri, all date format symbols (for example, dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar. Format symbols remain in English; symbols that result in text display (for example, AM and PM) display the string (English or Arabic) associated with that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666

 

[sgurusha]

Hi WHM

Thanks for the info, but I think Format does not apply here. Unless you see something I don't. Can you provide a starting point using Format?

Thanks.

SWG.

 

[GeekGirlau]

You can use the DateAdd function to start from a specific date. For example, DateAdd("ww", 2, #1/1/00#) will result in 15-Jan-2000 - 2 weeks from 1 Jan.

 

[sgurusha]

Thanks GeekGirlau:

That is a starting point. But is there a way I can use the knowledge that I know the day of the week?

Thanks.

SWG

 

[WHM]

Sunil,
I was not more specific because I do not understand:

Week 2 of Sunday, January, 2000 would be 9/1/2000

This does not make any sense to me. If you are looking for the second Sunday in September then you can use delimiters like:
Select ......
WHERE ((Format(DateField, &quot;dddd&quot = 'Sunday') AND (Format(DateField, &quot;d&quot > 7) AND (Format(DateField, &quot;d&quot < 14) AND (Format(DateField, &quot;yyyy&quot = 2000) AND (Format(DateField, &quot;m&quot; = 9))

Likewise you can use any number of logic operators to find any particular date reference that you need, as long as you can think of an arithmetic method to gurantee a single correct result. Use the Query Building tool built into Access to help you work out the results that you want, and then you can change the fixed equivalents to passed variables, or you can create SQL statements programatically from within code to satisfy a wider range of variables.

If you could be more clear on what you are trying to achieve then I can give you more precise syntax, and, hopefully, more compact SQL.

Hunter

 

[MichaelRed]

Ugly -but then the spec isn't exactly the 'prettiest' I've seen lately. You WILL want to review the arguments and provide the correct value types. The Day and Month names may be abreviated or spelled out, while the number of weeks and the year both need to be integers. I am more-or-less clueless as to WHY anyone would actually do it this way, but:

Public Function basDateParts2date(WkNum As Integer, _
                                  DayName As String, _
                                  MonthName As String, _
                                  YearNum As Integer) As Date

    Dim DayNum As Integer
    Dim MnthNum As Integer
    Dim FirstOfMnth As Date

    Select Case UCase(DayName)

        Case Is = "SUN", "SUNDAY"
            DayNum = 1

        Case Is = "MON", "MONDAY"
            DayNum = 2

        Case Is = "TUE", "TUESDY"
            DayNum = 3

        Case Is = "WED", "WEDNESDAY"
            DayNum = 4

        Case Is = "THU", "THURSDAY"
            DayNum = 5

        Case Is = "FRI", "FRIDAY"
            DayNum = 6

        Case Is = "SAT", "SATURDAY"
            DayNum = 7
    End Select

    Select Case UCase(MonthName)

        Case Is = "JAN", "JANURARY"
            MnthNum = 1

        Case Is = "FEB", "FEBURARY"
            MnthNum = 2

        Case Is = "MAR", "MARCH"
            MnthNum = 3

        Case Is = "APR", "APRIL"
            MnthNum = 4

        Case Is = "MAY"
            MnthNum = 5

        Case Is = "JUN", "JUNE"
            MnthNum = 6

        Case Is = "JUL", "JULY"
            MnthNum = 7

        Case Is = "AUG", "AUGUST"
            MnthNum = 8

        Case Is = "SEP", "SEPTEMBER"
            MnthNum = 9

        Case Is = "OCT", "OCTOBER"
            MnthNum = 10

        Case Is = "NOV", "NOVEMBER"
            MnthNum = 11

        Case Is = "DEC", "DECEMBER"
            MnthNum = 12

    End Select

    FirstOfMnth = DateSerial(YearNum, MnthNum, 1)       'First of the Month Date
    WeekAdd = DateAdd("ww", WkNum, FirstOfMnth)         'Offset the Number of Weeks
    basDateParts2date = DateAdd("d", DayNum - 7, WeekAdd)

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over