Exclude when the current month EXCEPT when?

[helpangel]

Does anyone know how to INCLUDE the month of May or June data when the current month is May and if the current month is June but all other months exclude the "current" month?

I'm working with

  <>Month(Date())

to exclude current month, however I need ALL months when the current month is May or current month is June. Meaning only in the month of May or month of June I need the <>Month(Date()) to NOT take affect.

<>Month(Date()) and (Month(Date()) Not In (5,6))

does not work, it results in Null

<>Month(Date()) and (Month(Date()) In (5,6))

does not work, it also results in Null

There has to be a way to do this, anyone know the correct formula?

 

[JHB]

You've to do it in another way, (I've a table with a field called ADate).
If Month current date is 5 or 6, then return true.
If the above is not true, then test the month for the ADate field, if it is 5 or 6, then return false, else return true.

SELECT Table1.ADate
FROM Table1
WHERE IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([Adate])=5 Or Month([Adate])=6,False,True))=True;

 

[helpangel]

Sorry, not sure what you mean with the ADate and using Table1?

 

[plog]

I think you need ORs not ANDs:

<>Month(Date()) Or 5 Or 6

The first criteria exludes the current month, but the next two makes sure you always include may and june.

 

[JHB]

Sorry, not sure what you mean with the ADate and using Table1?

Okay - (but it is not rocket science), change the "Table1" to your table name and the same for [ADate] change it to your field name.
Attached is an example.

 

[JHB]

I think you need ORs not ANDs:

<>Month(Date()) Or 5 Or 6

The first criteria exludes the current month, but the next two makes sure you always include may and june.

I don't think you can do it in this way, but I'm willing to learn, so could you show it?

 

[Solo712]

Does anyone know how to INCLUDE the month of May or June data when the current month is May and if the current month is June but all other months exclude the "current" month?

I'm working with

  <>Month(Date())

to exclude current month, however I need ALL months when the current month is May or current month is June. Meaning only in the month of May or month of June I need the <>Month(Date()) to NOT take affect.

<>Month(Date()) and (Month(Date()) Not In (5,6))

does not work, it results in Null

<>Month(Date()) and (Month(Date()) In (5,6))

does not work, it also results in Null

There has to be a way to do this, anyone know the correct formula?

Exclude if current month if

 NOT ((Month(Date())=5  OR Month(Date())= 6)

Jiri

 

[JHB]

Exclude if current month if

 NOT ((Month(Date())=5  OR Month(Date())= 6)

Jiri

Please could you make some working sample, I would like to see how you do it with the above?

 

[Solo712]

Please could you make some working sample, I would like to see how you do it with the above?

Oops, I was thinking VBA, not realizing that what was wanted was an expression.. my solution would be:

IIf((Month(Date())=5 Or Month(Date())=6) And (Month([txtDate])=5 Or Month([txtDate])=6),True,False)

Best,
Jiri

 

[JHB]

... my solution would be:

IIf((Month(Date())=5 Or Month(Date())=6) And (Month([txtDate])=5 Or Month([txtDate])=6),True,False)

Best,
Jiri

And what about the other months, it will only give data for month May and June if the current month is May or June, else nothing?
Did you really read what the OP wants?

 

[plog]

Here's mine using <>Month(Date()) Or 5 Or 6

YourTableName
ID_Field, DateField
1, 1/1/2015
2, 2/1/2015
3, 3/1/2015
...
12, 12/1/2015

Here's my query:

SELECT ID_Field, DateField
FROM YourTableName
WHERE (((Month([DateField]))<>Month(Date()) Or (Month([DateField]))=5 Or (Month([DateField]))=6));

Because its May, the results are every record in my table. We can also test my criteria as if it were March by replacing the WHERE clause with this:

WHERE (((Month([DateField]))<>3 Or (Month([DateField]))=5 Or (Month([DateField]))=6))

When you do that it returns every record except the one occuring in March.

 

[helpangel]

I think you need ORs not ANDs:

<>Month(Date()) Or 5 Or 6

The first criteria exludes the current month, but the next two makes sure you always include may and june.

I think this works!

Thank you! Testing further ...

One other question, how do you say what you have but then add an exception IF May or Jun is in the CURRENT YEAR?

Basically wanting to exclude May or Jun in the month of May or Jun ONLY IIF the Payment year is NOT the CURRENT year? Year([PaymentDate]) <> Year(Date())? if so how to add with your current formula?

I tried this and I get Null

IIf(Year([PaymentDate])<>Year(Date()),<>Month(Date()) Or 5 Or 6)

 

[helpangel]

Exclude if current month if

 NOT ((Month(Date())=5  OR Month(Date())= 6)

Jiri

This also results in Null

 

[helpangel]

Oops, I was thinking VBA, not realizing that what was wanted was an expression.. my solution would be:

IIf((Month(Date())=5 Or Month(Date())=6) And (Month([txtDate])=5 Or Month([txtDate])=6),True,False)

Best,
Jiri

This also results in NULL where "txtDate" is Date()

 

[plog]

The way you just explained the current year criteria is that it only applies to May and June. Is that correct? Does the current year criteria not apply to the other months?

 

[helpangel]

The way you just explained the current year criteria is that it only applies to May and June. Is that correct? Does the current year criteria not apply to the other months?

Yes, sorry, ONLY for May or Jun.

Although thinking about I think it'll be fine.
I was thinking that there might be current year May or Jun payments for the current seasons but I think they wouldn't show anyway.
BUT in case they do they need to be excluded.

 

[helpangel]

plog,
So with the further testing it seems it's not right

I was testing this

 <>Month(Date())-2 Or 5 Or 6

and it's not correct
should be:

Feb
935 874 921 1042
but getting
940 876 931 1047

See attached db and the queries:
ByFiscalYear and Query1

They should match which ever month it is with the formula but it's not.

 

[helpangel]

You've to do it in another way, (I've a table with a field called ADate).
If Month current date is 5 or 6, then return true.
If the above is not true, then test the month for the ADate field, if it is 5 or 6, then return false, else return true.

SELECT Table1.ADate
FROM Table1
WHERE IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([Adate])=5 Or Month([Adate])=6,False,True))=True;

Lol! Sorry.

Ok, so this looks like it works but, sorry, not sure how to test it?

IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([PaymentDate])=5 Or Month([PaymentDate])=6,False,True))=True

Full SQL

TRANSFORM Count([02_Collegiate_Details].MembershipNumber) AS CountOfMembershipNumber
SELECT [02_Collegiate_Details].Season, Count([02_Collegiate_Details].membershipnumber) AS Total
FROM 02_Collegiate_Details
WHERE ((IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([PaymentDate])=5 Or Month([PaymentDate])=6,False,True))=True))
GROUP BY [02_Collegiate_Details].Season
ORDER BY [02_Collegiate_Details].Season
PIVOT Format([PaymentDate],"mmm");

I want to test it as if it's last month and 2 months ago to see if the totals are correct with this formula. How / what can I change to test that?

Your formula is reconciling but not sure how to see if the prior months will reconcile as well to confirm this formula works.
Total 954 889 941 1055

I should get the same as these totals in those priors months.

This is what I need to see for the prior months
Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
549 660 731 814 861 889 917 935 949 954 954
520 617 716 795 822 836 856 874 887 889 889
537 622 693 813 855 869 901 921 931 941 941
628 721 826 888 936 972 1020 1042 1050 1055 1055

 

[plog]

I don't understand what to look at. Honestly, you have too many directions going and are adding more criteria. I'm lost.

If you want any further advice from me, please provide me with 2 sets of data:

A. Sample starting data (most likely this will be 02_Collegiate_Details) along with table and field names. Include enough data to cover all cases.

B. Expected results based on A. Show me what you hope to end with when you feed it the A data.

 

[helpangel]

Sorry, let's just go with verifying your formula for other months.

Have you downloaded the database? I attached it making sure it has what I'm talking about so it makes sense from what I'm stating here to the actual queries and formulas in the db.

For purpose of testing
There is one table, as you have identified, it is "02_Collegiate_Details".

The queries to look at has been specified in the last post to you:
"ByFiscalYear" and "Query1"

The Query1, with the formula you have provided, should result in the same totals as shown in the query ByFiscalYear. And it does.
This month's total, using your formula in Query1, does reconcile to the total in the query ByFiscalYear in columns Apr, May, and Jun. So this is good!

So in trying to verify that your formula works in other months I modified it to

<>MonthDate())-1 or 5 or 6

and this still does reconcile to the column Mar in the query ByFiscalYear. This is still good!

However, in testing one more month prior, so I change the formula to

<>MonthDate())-2 or 5 or 6

BUT it does NOT reconcile to the column Feb in the query ByFiscalYear.

As shown in the prior post

Feb
935 874 921 1042
but getting
940 876 931 1047

with the

<>Month(Date())-2 or 5 or 6

Not sure why?