Sorted and Unique Date Period

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies

I had some, much appreciated, assistance putting this Work Period formula
together. However, now I need to use this Work Period in a form combo to
filter my reports.

WorkPeriod: Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket
Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket
Date],6)+7,[Ticket Date]),"mmm dd/yy")

Since the data is converted to text, sorting puts it in alphabetical, not
chronological order. Also, I need to see unique values but I can not Group
it in a query because it is an expression and setting the query Uniqe Value
property produces an error. I can not sort by the Ticket Date field because
adding that to the query expression causes duplicate Work Periods.

To all those with much more knowledge than I, any suggestions?
 
Why not two dates the start date and the end date?
Also, if Ticket Date is ever null in ANY record in the query, your expression
will error on that record. If it errors, that will cause the entire query to
fail when you try to GROUP, SORT, get Unique records, etc. on that field.


StartDate: DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])
EndDate: DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date])

Then you can sort by just the start date.

If you wish to you can still create the WorkPeriod field for display purposes.

Your row source query might look like the following.
SELECT Distinct IIF(IsDate([Ticket Date]),Format(DateAdd("d",-Weekday([Ticket
Date],6)+1,[Ticket Date]),"mmm dd/yy") & " - " &
Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm
dd/yy"),Null) as WorkPeriod
, DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]) as StartDate
, DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]) as EndDate
FROM [YourTable]
ORDER BY DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])

If you want to use the StartDate and Enddate in queries, you can add two
controls to your form and set their source to
=[NameOfCombobox].Column(1)
and
=[NameOfCombobox].Column(2)

Then you can refer to the two controls in the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John. I thought of the 2 date option but wondered if there was an
alternative.

Thanks for the idea of putting the 2 dates into their Work Period formats. I
hadn't considered doing that. Good Idea!

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
John Spencer said:
Why not two dates the start date and the end date?
Also, if Ticket Date is ever null in ANY record in the query, your
expression will error on that record. If it errors, that will cause the
entire query to fail when you try to GROUP, SORT, get Unique records, etc.
on that field.


StartDate: DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])
EndDate: DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date])

Then you can sort by just the start date.

If you wish to you can still create the WorkPeriod field for display
purposes.

Your row source query might look like the following.
SELECT Distinct IIF(IsDate([Ticket
Date]),Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm
dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket
Date]),"mmm dd/yy"),Null) as WorkPeriod
, DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]) as StartDate
, DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]) as EndDate
FROM [YourTable]
ORDER BY DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])

If you want to use the StartDate and Enddate in queries, you can add two
controls to your form and set their source to
=[NameOfCombobox].Column(1)
and
=[NameOfCombobox].Column(2)

Then you can refer to the two controls in the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Groupies

I had some, much appreciated, assistance putting this Work Period formula
together. However, now I need to use this Work Period in a form combo to
filter my reports.

WorkPeriod: Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket
Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket
Date],6)+7,[Ticket Date]),"mmm dd/yy")

Since the data is converted to text, sorting puts it in alphabetical, not
chronological order. Also, I need to see unique values but I can not
Group it in a query because it is an expression and setting the query
Uniqe Value property produces an error. I can not sort by the Ticket Date
field because adding that to the query expression causes duplicate Work
Periods.

To all those with much more knowledge than I, any suggestions?
Click to expand...
Click to expand...
 
Back
Top