PC Review


Reply
Thread Tools Rate Thread

Sorted and Unique Date Period

 
 
CJ
Guest
Posts: n/a
 
      22nd May 2010
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?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      22nd May 2010
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

CJ wrote:
> 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?
>

 
Reply With Quote
 
 
 
 
CJ
Guest
Posts: n/a
 
      23rd May 2010
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" <(E-Mail Removed)> wrote in message
news:esUBRWc%(E-Mail Removed)...
> 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
>
> CJ wrote:
>> 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?
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
First Value in Sorted Column is Not Sorted Properly jgraves Microsoft Excel Misc 3 4th Aug 2009 07:19 PM
Sorted data is not sorted AMDRIT Microsoft ADO .NET 2 21st Oct 2005 03:03 PM
Confused by sorted list of a sorted list J L Microsoft VB .NET 1 26th Jun 2005 12:28 AM
Sorted sub-report NOT sorted in main =?Utf-8?B?RGVubmlz?= Microsoft Access Reports 3 16th May 2005 11:36 PM
Records not sorted in Report but sorted in underlying query Tina Hudson Microsoft Access Reports 6 10th Jun 2004 09:33 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:25 AM.