QUERY OR FUNCTION TO CONVERT DATE TO AN EXPRESSION (1 Viewer)

mercystone

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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

AWF VIP
Local time
Today, 17:19
Joined
May 23, 2011
Messages
4,432
What, in plain english, determines that a date is "Term Two".
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:19
Joined
Feb 19, 2013
Messages
14,221
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

I’m here to help
Staff member
Local time
Today, 09:19
Joined
Oct 29, 2018
Messages
18,795
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

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2002
Messages
35,932
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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

I’m here to help
Staff member
Local time
Today, 09:19
Joined
Oct 29, 2018
Messages
18,795
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

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2002
Messages
35,932
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

Enthusiastic Amateur
Local time
Today, 17:19
Joined
Sep 21, 2011
Messages
10,346
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

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2002
Messages
35,932
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

Enthusiastic Amateur
Local time
Today, 17:19
Joined
Sep 21, 2011
Messages
10,346
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

You've got your good things, and you've got mine.
Local time
Today, 12:19
Joined
May 21, 2018
Messages
6,273
"Term " & month(date())\4 + 1
Untested
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:19
Joined
May 21, 2018
Messages
6,273
Code:
SELECT
 tblDemo.mm_DD_yyyy,
 "Term " & (Month([mm_dd_yyyy])-1)\4+1 AS Term
FROM tblDemo;
Query1 Query1

mm_DD_yyyyTerm
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108

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

mm_DD_yyyyTerm
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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

Member
Local time
Today, 19:19
Joined
Sep 20, 2021
Messages
108
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
 

Users who are viewing this thread

Top Bottom