Pick a specific number?

David_Skov · Aug 30, 2004

I have my database (ofcourse) and then I made a query which I have choosen to be sorted by year and then by week. I do a small calculation but in that calculation I need to get the info of the weeknumber in the final/last row of the database-query.

So if I have a structure that looks like this:

Year - week - cost
2003 - 2 - 5000
2003 - 3 - 7000
... - ... - ...
2004 - 20 - 5543
2004 - 20 - 8325
2004 - 21 - 2000

The "-" indicates a new cell. I need a formula or a way to be able to know what the week in the last row is (in the example above its 21). But since I would like it to run automatically I would really like to avoid doing any manual work

I have an ID in the first coloum btw. Just ascending numbers from 1,2,3...etc.

fly_champ · Aug 30, 2004

Are you trying to get a week number from the current date like:

Code:

    'set date and week number
        intweeknumber = DatePart("ww", Now())
    'correct for actual week number
        intweeknumber = intweeknumber - 1
    'load weeknumber into string phrase
        strdate = " Week # " & intweeknumber

or are you trying to get the date from the week number like:

Code:

Public Function myWeekRange(WeekNum As Integer, Optional fmt As Integer = 0, Optional sdy As Integer = 0) As String
' fmt = Format options: Range = 0 to 8
'       0 = Week 17: 4/18/04 - 4/24/04 (default)
'       1 = Week 17: 4/18/2004 - 4/24/2004
'       2 = Week 17: April 18, 2004 - April 24, 2004
'       3 = Week 17: April 18 - 24, 2004
'       4 = 4/18/04 - 4/24/04
'       5 = 4/18/2004 - 4/24/2004
'       6 = 4/18 - 24/2004
'       7 = April 18, 2004 - April 24, 2004
'       8 = April 18 - 24, 2004
'
' sdy = Starting Day of Week options: Range = 0 to 6
'       0 = Sunday (default)
'       1 = Monday
'       2 = Tuesday (and so forth)
'
' USAGE:
' In the debug window, ?myWeekRange(17) will yield "Week 17: 4/18/04 - 4/24/04"
' In the debug window, ?myWeekRange(17,1) will yield "Week 17: 4/18/2004 - 4/24/2004"
' In the debug window, ?myWeekRange(17,1,1) will yield "Week 17: 4/19/2004 - 4/25/2004"
' In the debug window, ?myWeekRange(17,5,2) will yield "4/20/2004 - 4/26/2004"
' and so forth. 
'
' You can also use it in queries: DtRng: myWeekRange(Format([Date],'ww'),1,1)
'
' Season to taste.
    
Dim StartDate As Date
Dim StartRange As Date
Dim EndRange As Date
Dim numDays As Long
Dim maxWk As Long
Dim fwDays As Long            ' stores the number of days in the first week of the year
Dim dtTmpDate As Date
Dim i As Integer, w As String
Dim sdFmt As String           ' stores the format string for the week start date
Dim edFmt As String           ' stores the format string for the week end date
    
' Validate format option value and msg user if out of range
    If fmt < 0 Or fmt > 8 Then
        MsgBox "That is not a valid format option." & vbCrLf & vbCrLf & _
               "Format options are:" & vbCrLf & _
               "    0 = 'Week 17: 4/18/04 - 4/24/04' (default)" & vbCrLf & _
               "    1 = 'Week 17: 4/18/2004 - 4/24/2004'" & vbCrLf & _
               "    2 = 'Week 17: April 18, 2004 - April 24, 2004'" & vbCrLf & _
               "    3 = 'Week 17: April 18 - 24, 2004'" & vbCrLf & _
               "    4 = '4/18/04 - 4/24/04'" & vbCrLf & _
               "    5 = '4/18/2004 - 4/24/2004'" & vbCrLf & _
               "    6 = '4/18 - 24/2004'" & vbCrLf & _
               "    7 = 'April 18, 2004 - April 24, 2004'" & vbCrLf & _
               "    8 = 'April 18 - 24, 2004'", vbExclamation, "Invalid Week"
        Exit Function
    End If
    
' Validate first day of week (sdy) option value and msg user if out of range
    If sdy < 0 Or sdy > 6 Then
        MsgBox "That is not a valid First Day of Week option." & vbCrLf & vbCrLf & _
               "Format options are:" & vbCrLf & _
               "    0 = Sunday (default)" & vbCrLf & _
               "    1 = Monday" & vbCrLf & _
               "    2 = Tuesday" & vbCrLf & _
               "    3 = Wednesday" & vbCrLf & _
               "    4 = Thursday" & vbCrLf & _
               "    5 = Friday" & vbCrLf & _
               "    6 = Saturday", vbExclamation, "Invalid First Day of Week"
        Exit Function
    End If
    
' Determine the maximum week number of the current year
    maxWk = Format(CDate(Format("12/31/" & Year(Now()), "m/d/yyyy")), "ww")
    If WeekNum > maxWk Or WeekNum < 1 Then
        MsgBox "That is not a valid week number.", vbExclamation, "Invalid Week"
        Exit Function
    End If
    
' Set the date of the first day of the current year
    StartDate = CDate(Format("01/01/" & Year(Now()), "m/d/yyyy"))
    
    dtTmpDate = DateSerial(Year(StartDate), Month(StartDate), 1)
    
' Determine the number of days in the first week of the current year
    For i = 1 To 7
      If WeekDay(dtTmpDate) = vbSunday Then
        fwDays = Format(dtTmpDate, "d") - sdy
        Exit For 
      End If
      dtTmpDate = dtTmpDate + 1
    Next i
    
' Establish the Date Ranges of the week
    numDays = (7 * (WeekNum - 1)) - fwDays
    StartRange = DateAdd("d", numDays, StartDate)
    EndRange = DateAdd("d", 6, StartRange)
    
    If fmt < 4 Then
        w = "Week " & WeekNum & ": "
      Else
        w = ""
    End If
    
' Render the Date Range output format
    Select Case fmt
        Case 0, 4
            sdFmt = "m/d/yy"
            edFmt = "m/d/yy"
        Case 1, 5
            sdFmt = "m/d/yyyy"
            edFmt = "m/d/yyyy"
        Case 2, 7
            sdFmt = "mmmm d, yyyy"
            edFmt = "mmmm d, yyyy"
        Case 3
            sdFmt = "mmmm/d"
            edFmt = "d/yyyy"
        Case 6
            sdFmt = "m/d"
            edFmt = "d/yyyy"
        Case 8
            sdFmt = "mmmm d"
            edFmt = "d, yyyy"
        Case Else
            myWeekRange = "something broke"
            Exit Function
    End Select
    
' Render the Date Range output
    myWeekRange = w & Format(StartRange, sdFmt) & " - " & Format(EndRange, edFmt)
    
End Function

hope this helps

Jon

David_Skov · Aug 30, 2004

I'm trying to get the weeknumber from the current data. The data already in my database

I'm going to use the retrieved weeknumber in a formula:

IIf([WeekNum]<=week,"YTD","-")

Where 'week' is the retrieved week in my database.

See the problem is that I have this database and I have a lot of data in that database. I made a query database and sorted by year and week. I then use the week as a tool to get YTD (Year To Date).

So whenever I add new data to my database I need to be able to retrieve the latest week. Meaning, if I enter data for august this year, my new YTD will be week 30 instead of week 26 which is for July.

I add the data once every month and the data includes weeknumber so I only need to get the latest weeknumber.

mdmilner · Aug 30, 2004

Here's a simple approach.

Add this to your query:

DatePart("ww",[Date]) AS Wk

Where [Date] is the name of your Date field, NOT the results of the Date() function.

Order it by the above field in descending order and specify TOP 1
A code snippet example from elsewhere. This may not meet your needs exactly, but it should give you an idea or two.

Code:

SELECT TOP 1  tblData.Initial, tblData.Number, tblData.City, tblData.St, tblData.Location, tblData.File, tblData.Date, DatePart("ww",[Date]) AS Wk
FROM tblData
ORDER BY DatePart("ww",[Date]) DESC;

Mike

David_Skov · Aug 30, 2004

If I can sort my data in the query, I have the latest week in the row in the bottom. I suppose that a query is a way to retrieve data from the database? Why can't I retrieve data from a cell then?

I don't need to calculate a weeknumber, I have the week number as part of the database. The database sort of determine the weeknumber. The reason is that every time I add new data to my database, it includes the latest weeks, but not nescessarily the weeknumber according to today (so to speak).

I can understand that the database doesn't sort at all, but the query do

Plz help me out here...do you have any suggestions to handle this problem then?. It seems to me as stupid if I need to reset the database every month to make sure that I get the YTD correct. :.(

mdmilner · Aug 30, 2004

David,

The answer is within the query I posted above.

Code:

SELECT TOP 1  field1, field2, field3, yourWKfield FROM tblData 
ORDER BY yourWKfield DESC;

What this does is sort your table in reverse order. Largest value to lowest. The qualifier TOP 1 tells the query to only return the single highest value present (the highest week number). And, because there are multiple entries for that one field, it returns all of them.

As a note, TOP 2 would not return the last two weeks. It would return the first two values, and any values that match them. So if you had 10 entries for the latest week, it would return 10 entries also. TOP 11 would end up returning the top two weeks. Assuming you had 10 entries for week 20 and 10 entries for week 19 -- it would return 20 entries total.

This is not the only way to accomplish this. Generally, the others are more complex and only needed if you have different requirements.

Here is the reason why I and others showed you how to calculate the week value. Under normal circumstances, it is bad database form to include fields that are based on each other. Never, ever, put a date field, and then a field representing the week number of that date field, into a table.

The reason is, it's redundant and takes up extra space. Why add something in that you can calculate on the fly as you need it, only when you need it. The reason so many do it is because most look at an Access table just like they look at an Excel spreadsheet...in reality, they are very different.

Also, most do not realize that nearly everything that you think you must have a table to do, you can do with a query.

Try this sometime. Build a table, then build a query based on that table. And then, build yet another query, based on the query and think about what kind of capabilities that gives you. You can export that query directly to excel, or use that query as the recordsource for a form or report.

Mike

David_Skov · Aug 31, 2004

First, were to put this code in the query? (Yes, I'm new to Access)

Secondly, the week number is very important and can't be generated automatically since it's part of the data. The data is advertisements on several product categories in denmark. So I have 100-150 rows with each week. The weeknumber tells when the commercial was send out to the consumers. So it makes no sense to generate the weeknumber.

But the weeknumber tells me to what week I have data. At my work we use 5-4-4 as a quarter. So january is 5 weeks, february 4 etc. So it's important for me to retrieve the latest weeknumber in order to know the YTD (Year To Date). Not the real YTD but YTD based on the data I entered.

I have this in my query allready:

IIf([Uge]<=30,"YTD","-")

But I need the '30' which is the latest week from which I have data, to be dynamic. So the next time I add more data to my database, it also contains new weeknumbers, and the highest weeknumber among those should replace the '30' in my query

The formula above work, but it would be so great if I can just append more data to the database and don't need to think about the YTD

And a last thing, thanks very much for your patience with a n00b as me

mdmilner said:
David,

The answer is within the query I posted above.

Code:

SELECT TOP 1 field1, field2, field3, yourWKfield FROM tblData ORDER BY yourWKfield DESC;

What this does is sort your table in reverse order. Largest value to lowest. The qualifier TOP 1 tells the query to only return the single highest value present (the highest week number). And, because there are multiple entries for that one field, it returns all of them.

As a note, TOP 2 would not return the last two weeks. It would return the first two values, and any values that match them. So if you had 10 entries for the latest week, it would return 10 entries also. TOP 11 would end up returning the top two weeks. Assuming you had 10 entries for week 20 and 10 entries for week 19 -- it would return 20 entries total.

This is not the only way to accomplish this. Generally, the others are more complex and only needed if you have different requirements.

Here is the reason why I and others showed you how to calculate the week value. Under normal circumstances, it is bad database form to include fields that are based on each other. Never, ever, put a date field, and then a field representing the week number of that date field, into a table.

The reason is, it's redundant and takes up extra space. Why add something in that you can calculate on the fly as you need it, only when you need it. The reason so many do it is because most look at an Access table just like they look at an Excel spreadsheet...in reality, they are very different.

Also, most do not realize that nearly everything that you think you must have a table to do, you can do with a query.

Try this sometime. Build a table, then build a query based on that table. And then, build yet another query, based on the query and think about what kind of capabilities that gives you. You can export that query directly to excel, or use that query as the recordsource for a form or report.

Mike

bat17 · Aug 31, 2004

David

Another way to try, is to use the Dmax() function.
Create your query but don't worry about creating a new column using IIf([Uge]<=30,"YTD","-") to filter by. Instead in the criteria for the week number add the following function

<=DMax("[intWeek]","[MyData]","[intyear] = " & DMax("[intYear]","[MyData]"))

Where:-
MyData = the name of your table
intWeek = is the name of your week number field
intYear = the name of your year field

This should then return the highest week number in the highest year and use that as the criteria.

HTH

Peter

David_Skov · Aug 31, 2004

Wohooo, it seems to be it!

Just what I needed

ray: Tx

bat17 said:
David

Another way to try, is to use the Dmax() function.
Create your query but don't worry about creating a new column using IIf([Uge]<=30,"YTD","-") to filter by. Instead in the criteria for the week number add the following function

<=DMax("[intWeek]","[MyData]","[intyear] = " & DMax("[intYear]","[MyData]"))

Where:-
MyData = the name of your table
intWeek = is the name of your week number field
intYear = the name of your year field

This should then return the highest week number in the highest year and use that as the criteria.

HTH

Peter

mdmilner · Aug 31, 2004

Um, David. Nothing wrong with Bat's fix. In fact, I like it better.
But, in answer to "where does my example go" - the answer is the exact same place you're typing, just in a different form.

What you must be using is the QBE (Query by Example) Wizard interface. That's the one that gives you a pull down menu to select fields, or you can drag and drop them into the grid. Bat17, for example, was talking about dropping info into the criteria field to get your information.

Try something for me. Take that saved query that you just picked up and with it still open, go up to the View Menu in the Access window and select SQL view.

And then a trick question. By any chance does my example above appear in a similar syntax to what you're now looking at?

It will not be exact, because it's a slightly different method.

Mike

Pick a specific number?

David_Skov

Active Member

Excel Facts

fly_champ

New Member

David_Skov

Active Member

mdmilner

Well-known Member

David_Skov

Active Member

mdmilner

Well-known Member

David_Skov

Active Member

bat17

Well-known Member

David_Skov

Active Member

mdmilner

Well-known Member

Similar threads

Forum statistics

Share this page

Pick a specific number?

Active Member

Excel Facts

New Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock