Week Day Date then Week End Date

[KellyR]

Hello,
I have a query that has the date the record was recorded. I also have a non linked table that has all the Friday's (End of week) in it for the next few years.
What I'm trying to do is this;
If the date would be 12/23/2008 then add the record 12/26/2008 in the query to the "new" column based off my tbldates.

When I do this the query grows in size, duplicating all the current records. I'm not sure why it's doing this?

Thank you,

 

[Uncle Gizmo]

I'm not sure I can grasp the crux of the matter please could you supply more information.

 

[KellyR]

Hello,
I currently have (3) Fields in my query

ID CalculatedHours EndTime

The result when ran, shows 1710 records which is correct.

When I add a non related table that has a list of Fridays for the next few years. I'm trying to only show (1) of these Friday dates for each EndTime record.

I've tried using an expression WeekEndDay: Dat

from the extra table in my query and when the query runs, it shows 700,000 plus records instead of just the 1710.

Hope this explains it better.

Thank you,

 

[Bilbo_Baggins_Esq]

If you want to know what day of the week, any date is, you can use the "WEEKDAY()" function.

Weekday Function

Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

Weekday(date, [firstdayofweek])

The Weekday function syntax has these named arguments:

date = Required. Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.
firstdayofweek = Optional. A constant that specifies the first day of the week. If not specified, vbSunday is assumed.

Settings

The firstdayofweek argument has these settings:
Constant = Value = Description
vbUseSystem = 0 =Use the 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


Return Values

The Weekday function can return any of these values:
Constant = Value = Description
vbSunday = 1 = Sunday
vbMonday = 2 = Monday
vbTuesday = 3 =Tuesday
vbWednesday = 4 = Wednesday
vbThursday = 5 = Thursday
vbFriday = 6 = Friday
vbSaturday = 7 = Saturday

Remarks

If the Calendar property setting is Gregorian, the returned integer represents the Gregorian day of the week for the date argument. If the calendar is Hijri, the returned integer represents the Hijri day of the week for the date argument. For Hijri dates, the argument number is any numeric expression that can represent a date and/or time from 1/1/100 (Gregorian Aug 2, 718) through 4/3/9666 (Gregorian Dec 31, 9999).

 

[Bilbo_Baggins_Esq]

The reason your query is not working correctly is because it is matching each possible record with each possible date in your table.

I think you need to look at getting your result differently.
If you jion the two tables, the only results you'd get are the records where the dates in the query match the dates in the firday table exactly.

 

[Bilbo_Baggins_Esq]

is what you're trying to do, is find the first Friday after any of the given dates?

Friday_Date: iif(weekday([original_date])=6,[original_date],[original_date]+(6-weekday([original_date])))

 

[Bilbo_Baggins_Esq]

oh wait, that's not complete. just a sec

 

[KellyR]

Hi,

I do understand the Weekday() Function - Thanks.

What I'm really trying to get with this information is a chart averaging the data by week.

I can get it, but the weeks show as a week number and not the Friday end date.

I have created the charts in Access, and also exported the tables to Excel. Either way, I'm not able to figure this out.

You're right, maybe I can get this information another way.

Thanks,

 

[Bilbo_Baggins_Esq]

Ahh, well there are several ways of doing this.

I would think about writing a fiscal week function that associates a week number with a date.
Then simply match up your week numbers to get the end date of that fiscal week.

Or

Add a week number column to your date table.
Then in a new query you can do a join from the Week number column of the original query, to the week number column in the dates table, and return the associated date without getting all the extra records.

 

[Bilbo_Baggins_Esq]

oh yeah,,, d'oh!
Since the date table you mentioned holds dates for multiple years, hence duplicated week numbers, that last option would require more work to keep the records matched properly. Probably not a clean way to do this.

I would work on making a fiscal week function you can reuse everytime you need info by a certain week.

 

[KellyR]

OK, Let me give this a try. I will add the week number to my date table and see if I can figure this one out.

Thank you so much for your time.

 

[KellyR]

Alright, I have created a function for weekdays, maybe I can figure it out for week numbers? It looks like there are some examples listed in this forum.
Thank you!

 

[Bilbo_Baggins_Esq]

YOu may or may not still need this, but this formula will find the first friday after any of your original dates.

Expr1: IIf(Weekday([Original_Date],6)=1,[Original_Date],[Original_Date]+(8-Weekday([Original_Date],6)))

 

[Bilbo_Baggins_Esq]

Adding that to your query, and then doing a group by on the "Friday date" column should give what you need (a column with a date for the week instead of the week number).

 

[KellyR]

Great! This will definatley help me out. Let me give this a try and I'll post any results/questions.

Thank you so much!

 

[KellyR]

That worked perfect! All my Fridays are correct. Thank you!!!