QUERY OR FUNCTION TO CONVERT DATE TO AN EXPRESSION

M

mercystone

Member
Local time
Today, 22:13
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
 
What, in plain english, determines that a date is "Term Two".
 
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","")
 
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 said:
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"
Click to expand...
Demo please
 
theDBguy said:
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.
Click to expand...
 
theDBguy said:
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.
Click to expand...
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
 
mercystone said:
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
Click to expand...
Hi. Thank you for the additional information. Like I said earlier, I would recommend using a table for something like that.
 
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 said:
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?
Click to expand...
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?
 
"Term " & month(date())\4 + 1
Untested
 
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
 
MajP said:
​
​
​
​
​
​
​
​
​
​
​
Click to expand...

MajP said:
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
Click to expand...
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
 
CJ_London said:
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","")
Click to 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")="OCTOBER", "TERM TWO","")
 
Edited
mercystone said:
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","")
Click to expand...
Edited
expand...
Click to 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
 
Code: 
SELECT "TERM " & ((Month(Date())-1)\4)+1 AS TERM
Code: 
Public Function getTerm(Optional ByVal p As Variant = "") As String
    If IsDate(p) Then
        p = Month(p)
    Else
        p = Val("0" & p)
    End If
    If p < 1 Then p = Month(Date)
    getTerm = "TERM " & ((p - 1) \ 4) + 1
End Function
 
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
Click to expand...
And what I provided does not work because why????
="Term " & (Month(Date)-1)\4+1

You do under stand I was showing that it works for all months.
 
arnelgp said:
Code: 
SELECT "TERM " & ((Month(Date())-1)\4)+1 AS TERM
Code: 
Public Function getTerm(Optional ByVal p As Variant = "") As String
    If IsDate(p) Then
        p = Month(p)
    Else
        p = Val("0" & p)
    End If
    If p < 1 Then p = Month(Date)
    getTerm = "TERM " & ((p - 1) \ 4) + 1
End Function
Click to expand...
Genius. Thanks. Be blessed.
 
You must log in or register to reply here.

Similar threads

C
Query to find the MIN value of multiple date fields
Replies
16
Views
1,553
Gasman
Gasman
A
Solved How to add text to a null value field in an access report
Replies
18
Views
776
Ken Sheridan
Ken Sheridan
Luclarie
DLAST FOR DATE SERIAL
Replies
19
Views
686
Ken Sheridan
Ken Sheridan
F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
2,113
June7
June7
S
|Python| To learn? How to Learn? Vibe coding? Assisted coding
Replies
6
Views
904
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom