QUERY OR FUNCTION TO CONVERT DATE TO AN EXPRESSION

[mercystone]

Hi everyone. I am requesting for somebody to help me come up with a query or a function so that if for example you enter today's date 6/10/2021 it brings TERM TWO.
I tried
=IIF(Format(Now(), "mmmm")=OCTOBER, "TERM TWO"
But I get an error wrong number of arguments. Please help me

 

[bob fitz]

What, in plain english, determines that a date is "Term Two".

 

[CJ_London]

agree with Bob, not sure what determines 'Term Two'

but the problem with your expression is you are missing quotes and brackets
=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM TWO","")

Make sure you understand the difference between the date() function and the now() function

and another way to achieve the same thing

iif(month(now())=10,"Term Two","")

 

[theDBguy]

Hi. How many "terms" do you have? I think the IIf() function can only go up to seven (7). If you need more than that, you may definitely have to use a VBA function instead. However, my recommendation is to use a table instead, if you can, so you can easily update the number of terms without having to update a function.

 

[Pat Hartman]

Since we're all pretty sure that there is more to this rule than what you have provided here's another suggestion.

Create a table. Add 1 row for each month and assign it a term. Then lookup the current month in the table and return the "term"

 

[mercystone]

Since we're all pretty sure that there is more to this rule than what you have provided here's another suggestion.

Create a table. Add 1 row for each month and assign it a term. Then lookup the current month in the table and return the "term"

Demo please

 

[mercystone]

Hi. How many "terms" do you have? I think the IIf() function can only go up to seven (7). If you need more than that, you may definitely have to use a VBA function instead. However, my recommendation is to use a table instead, if you can, so you can easily update the number of terms without having to update a function.

 

[mercystone]

Hi. How many "terms" do you have? I think the IIf() function can only go up to seven (7). If you need more than that, you may definitely have to use a VBA function instead. However, my recommendation is to use a table instead, if you can, so you can easily update the number of terms without having to update a function.

Date. term
1/1/2022. Term 1
1/2/2022 Term 1
1/3/2022 term 1
1/4/2022. Term 1
1/5/2022. Term 2
1/6/2022. Term 2
1/7/2022. Term 2
1/8/2011. Term 2
1/9/2022. Term 3
1/10/2022. Term 3
1/11/2022. Term 3
1/12/2022. Term 3

 

[theDBguy]

Date. term
1/1/2022. Term 1
1/2/2022 Term 1
1/3/2022 term 1
1/4/2022. Term 1
1/5/2022. Term 2
1/6/2022. Term 2
1/7/2022. Term 2
1/8/2011. Term 2
1/9/2022. Term 3
1/10/2022. Term 3
1/11/2022. Term 3
1/12/2022. Term 3

Hi. Thank you for the additional information. Like I said earlier, I would recommend using a table for something like that.

 

[Pat Hartman]

Demo please

Why would anyone build a demo when you haven't defined the rules? Also, keep in mind that we don't get paid to help you so if you want people to do your work for you, you probably ought to offer money.

I can't even make a rule based on the original information plus the list starting with 1/1 next year.

 

[Gasman]

According to you, each term is 4 months, regardless of year?
So test Month first for < 5, then < 9 then it is term 3.
Two iifs() ?

 

[Pat Hartman]

Look at the date table again. The first four DAYS are 1, the second four are 2, the third four are 3. There is still lots of room for how the pattern continues. Does term keep incrementing every four days? Does it reset at the first day of each month? Does it reset at the first day of a year? Does it go as high as 6?

 

[Gasman]

Look at the date table again. The first four DAYS are 1, the second four are 2, the third four are 3. There is still lots of room for how the pattern continues. Does term keep incrementing every four days? Does it reset at the first day of each month? Does it reset at the first day of a year? Does it go as high as 6?

Pat, I took those dates to be dd/mm/yyyy ?
However I also think a table would be better, as I can see the terms going to 4 in the future perhaps?

 

[MajP]

"Term " & month(date())\4 + 1
Untested

 

[Pat Hartman]

Pat, I took those dates to be dd/mm/yyyy ?

Duh!
But how does

=IIF(Format(Now(), "mmmm")=OCTOBER, "TERM TWO"

fit with the month list?

 

[Gasman]

Duh!
But how does

fit with the month list?

You got me there

 

[MajP]

SELECT
 tblDemo.mm_DD_yyyy,
 "Term " & (Month([mm_dd_yyyy])-1)\4+1 AS Term
FROM tblDemo;

Query1 Query1

mm_DD_yyyy	Term
1/1/2021​	Term 1
2/1/2021​	Term 1
3/1/2021​	Term 1
4/1/2021​	Term 1
5/1/2021​	Term 2
6/1/2021​	Term 2
7/1/2021​	Term 2
8/1/2021​	Term 2
9/1/2021​	Term 3
10/1/2021​	Term 3
11/1/2021​	Term 3
12/1/2021​	Term 3

 

[mercystone]

​
​
​
​
​
​
​
​
​
​
​

SELECT
tblDemo.mm_DD_yyyy,
"Term " & (Month([mm_dd_yyyy])-1)\4+1 AS Term
FROM tblDemo;

Query1 Query1

mm_DD_yyyy	Term
1/1/2021​	Term 1
2/1/2021​	Term 1
3/1/2021​	Term 1
4/1/2021​	Term 1
5/1/2021​	Term 2
6/1/2021​	Term 2
7/1/2021​	Term 2
8/1/2021​	Term 2
9/1/2021​	Term 3
10/1/2021​	Term 3
11/1/2021​	Term 3
12/1/2021​	Term 3

This is not what I meant but thanks for your query. What I mean is the term has 4 months that is:
JANUARY, FEBRUARY, MARCH, APRIL- TERM ONE
MAY, JUNE JULY, AUGUST- TERM TWO
SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER- TERM THREE

 

[mercystone]

agree with Bob, not sure what determines 'Term Two'

but the problem with your expression is you are missing quotes and brackets
=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM TWO","")

Make sure you understand the difference between the date() function and the now() function

and another way to achieve the same thing

iif(month(now())=10,"Term Two","")

This works. Unfortunately, I am defeated on where to put separator.
=IIF(Format(Now(), "mmmm")="JANUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="FEBRUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MARCH", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="APRIL", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MAY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JUNE", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JULY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="AUGUST", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="SEPTEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="NOVEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM TWO","")

 

[mercystone]

Edited

This works. Unfortunately, I am defeated on where to put separator.
=IIF(Format(Now(), "mmmm")="JANUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="FEBRUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MARCH", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="APRIL", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MAY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JUNE", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JULY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="AUGUST", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="SEPTEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="NOVEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM TWO","")

Edited

expand...

This works. Unfortunately, I am defeated on where to put separator.
=IIF(Format(Now(), "mmmm")="JANUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="FEBRUARY", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MARCH", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="APRIL", "TERM ONE",""),=IIF(Format(Now(), "mmmm")="MAY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JUNE", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="JULY", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="AUGUST", "TERM TWO",""),=IIF(Format(Now(), "mmmm")="SEPTEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="OCTOBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="NOVEMBER", "TERM THREE",""),=IIF(Format(Now(), "mmmm")="DECEMBER", "TERM THREE","")

Somebody to assist where to put separators. I am still new in access