Date Query

[SDowse]

Hi,

I have an Access db, containing 1 column which has date information in it. The field is called Added. I also have a field called "Account No".

I am wanting to create a query against the Added field to either:
- Count all the Account No's where the Added date was Friday to Sunday, if today is a Monday
- Count all the Accounts No's where the Added date was yesterday, if today is not a Monday

I am struggling to work out how to do this in a query - any ideas?

Thanks in advance,
Simon

 

[ranman256]

in a query, Q1, to pull all data, add the field TodayDOW: format(date(),"ddd")
this will give you the day of week. Mon, Tues,

in Q2, use Q1 above,
in criteria under TodayDOW , set to "Mon"
add the field AcctDow: Format( [AcctDate], "ddd") and add criteria: "Fri" or "Sat" or "Sun",
and Count the [acctNo] field.
Uncheck the AcctDow to make it a 'where' clause, unless you want to know # of accts on that dayOWeek

Q3, same, but different...
in criteria under TodayDOW , set to: <> "Mon"

 

[UncleCatwoman]

You can also use the Weekday() function to get a number 1 (Sunday) to 7 (Saturday)

Though I would do a query against the original data that counts the account numbers and, for the Added criteria, I would use:

=IIF(WeekDay(Date())=2, (BETWEEN (Date()-3) AND (Date()-1)), ((Date()-1)))

Hope that helps!

 

[SDowse]

You can also use the Weekday() function to get a number 1 (Sunday) to 7 (Saturday)

Though I would do a query against the original data that counts the account numbers and, for the Added criteria, I would use:

=IIF(WeekDay(Date())=2, (BETWEEN (Date()-3) AND (Date()-1)), ((Date()-1)))

Hope that helps!

Hi - thanks for the response. This makes sense to me and I was almost there with creating this query myself before posting. The only difference was I didn't included all the ()'s that you have stated above. The issue I am having, even using your formula, is the "Is True" part is not working. I have tried this today (changing the Date())=2 to Date())=3) and no result are returned/

If I leave as Date()) = 2 then I get the results for 22nd October. As today is "3", the false part is working.

If I change, for the purpose of testing, to Date()) = 3, no result are returned, even though I know there is data from 20th to 22nd October.

I have the Account No showing as Count, and have tried Count, Group By and Where for the Added field.

Any ideas what I am doing wrong?

Thanks in advance,
Simon

 

[xenou]

Date() is a system function that returns the current date.

If today is a Tuesday, then Weekday(Date()) will always equal 3 and never equal 2. For your test, I think you wanted this: WeekDay(Date()-1)=2

If still having trouble post your query with context (i.e. all of it), not just the bare expression (which is only a small part of it) so we can see how you are using the expression.

-- Note that for the sake of completeness, I will mention that you can adjust the weekday function to return different values by providing an optional "first day of week" parameter ... but we will assume here that Sun = 1, Mon = 2, Tues = 3 and so on. But this isn't the main thing to focus on here.