Week ID Problem

[Vette00]

I use the Datepart() Function to pull the prior weeks records in a table. The problem is how to I tell Access that week #53 and week #1 are one in the same in the query.

 

[dhookom]

You could have provided a lot more details on what you are attempting to do.

1 mod 52 = 1
53 mod 52 = 1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Vette00]

I made a field called WeekIDatepart("ww",[Card_Date]) and the critieria for the query is Datepart("ww",[Card_Date])-1. This pulls the entire last week but will have problems in week 53/1 because Access thinks it is two different weeks. I also use the week ID to look at past weeks of data in a week at a glance view. Basically I need to look at weeks at glance but the field is a date.

 

[dhookom]

If you want to select the previous week, I would subtract 7 from the date.
DatePart("ww",[Card_Date]-7)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Vette00]

The problem with that is week 53 and week 1 fall in the same week and it won't display both. Plus when Week 1 comes it won't look at past weeks.

 

[dhookom]

Can you share your entire SQL view and explain what you are attempting to query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Vette00]

SELECT tblWork.Date, DatePart("ww",[Date]) AS WeekID, tblWork.Hours, tblWork.Status, tblWork.Comment, tblWork.ID, tblWork.Approved, FROM tblWork WHERE (((DatePart("ww",[Date]))=[Forms]![frmWorkMenu]![cboWorkWeek])) ORDER BY tblWork.Date;

I want to call up A week at a glance

 

[dhookom]

I would go with two text boxes [txtStartDate] and [txtEndDate]. You could even hide them if needed. Then populate the two text boxes with the correct values in the after update event of the combo box. Your query would then look like (add other columns back in):

SELECT tblWork.Date, DatePart("ww",[Date]) AS WeekID, tblWork.Hours, tblWork.Status, tblWork.Comment, tblWork.ID, tblWork.Approved, ...
FROM tblWork WHERE [Date] Between [Forms]![frmWorkMenu]![txtStartDate] AND [Forms]![frmWorkMenu]![txtEndDate]
ORDER BY tblWork.Date;

This will also avoid issues when spanning multiple years.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[67peshawar294]

vetto, Try this as a criteria in a query. On Monday it will pull the previous week Mon-Fri. On Tuesday it will display only Monday. On Wed it will display Mon,Tue, etc,etc.

Between IIf(Weekday(Date())=2,DateAdd("d",2-Weekday(Date()),Date())-8,DateAdd("d",2-Weekday(Date()),Date())) And IIf(Weekday(Date())=2,DateAdd("d",2-Weekday(Date()),Date())-3,Date())