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?
>>
Similar Threads
