I have a report that documents what I've been working on - I have a ddd format, so today Wednesday, would be Wed 5:13 p.m. for example - I'm trying to add an alias field to my query to where if the date is today's date, return Today 5:13 p.m. otherwise if it was yesterday or the day before, I want say Tues 3:57 p.m for example.

If it was a week ago, I would want the month number and day ONLY, i.e, Tues 4/3, with no time of day

here is my sql
and the alias attempt that did NOT work:

If you are storing your [start] values with a time component, they CAN'T be the same as the value returned by Date(). In other words, 4/11/2012 5:30PM is NOT the same as 4/11/2012.

I think that's the problem here. You will need to get the DateValue from [start] and compare that to the current Date().

try:

=IIF(DateValue([start]) = Date(), TimeValue([start]), DateValue([start])

Hi -

The Switch() function may do it for you. Here are three examples (today, yesterday, 8 days ago):



HTH - Bob

This post has been edited by raskew: Apr 12 2012, 02:15 AM

You could use:

StrtDt: Format([Start], IIf(DateValue([Start])=Date(), "\T\o\d\a\y", "dddd") & " h:nn AM/PM")

/gustav

Bob and everyone, thanks !!!

Bob - how would I call that function from a query ?

Hi -

Here's a tested example. You'll need to replace the table and field names with your own. I limited it to records > 1 Feb 2012, you'll probably need to modify or eliminate this criteria.



HTH - Bob

bob - thx but I didn't get the CatID part (IMG:style_emoticons/default/frown.gif)

Steve

Uhm...? I may be missing something, but if you run a "report" and it has "Today" on it, how will you be able to tell which one of those you ran last week (that ALL say "Today" on them) "belongs" to last Thursday's data?

Jeff,
thx 4 taking a look - it's a report to be ran daily and not archived so if it's a note/comment from today, I want it to say "TODAY"



Steve

'**********************************************
This was just a field in my test table.
It's not significant to the Switch()function and can be eliminated without harm.

Bob

Hi -

I'm confused. You've double-posted the same problem -- a forum no-no.

The example I provided returns exactly what you asked for.

If there's a problem creating the query against your table, please download a sample table (with data) and
we should be able to demonstrate the solution.

Best wishes - Bob

First of all, I greatly appreciate your help - thanks so much - as for my post, I said right up front that there was a previous post where the 3 scenario thing was mentioned, and I provided a link to it - so I don't think it was a case where I was trying to increase the # of views by re-posting the question (Stealthily) in a brand new, fresh post, not even referencing the original post.... also, maybe it was 'operator error,' but I couldn't get the 3 scenario thing to work - and it's not the exact same issue, it's more of a "hey, this is great, thank you all very much, but it would be even BETTERto have the 3 scenario thing - more of a don't have to have, but a 'would be nice to have' so that's why the new post - the focus on the initial post, we were done with.



Here is the sample db, your solution with the error message, probably due to my incorrect interpretation - thanks so much for your help, I really appreciate it !

Note - you will get a couple of error messages when you open it, because I had to strip a lot of stuff out, so just hit OK a couple of times - the focus here is the q_Test_Seven_Days_Prior query

Thanks for taking a look !!!


Steve

Hi -

Copy/paste this to a new query, then run it.



It should work - it worked for me.

Best wishes - Bob

bob - appreciate your help - got an error message ...... couldn't find tblWorklog - but thanks I got the answer - appreciate all your help ! (IMG:style_emoticons/default/cool.gif)