Translation Month into Number (1 Viewer)

Susy

New member
Local time
Today, 02:12
Joined
Dec 19, 2019
Messages
25
Hi,
I would like to translate a month (January, February, etc.) into numbers (1, 2, 3, etc.). I have seen that several people have asked the same question before, but I am unable to apply the solutions to my DB. Any recommendations?
Thanks,
Susy
 

Attachments

  • Capture.JPG
    Capture.JPG
    13.4 KB · Views: 116

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:12
Joined
Oct 29, 2018
Messages
21,358
Hi Susy. Have you tried using the Month() function?
 

plog

Banishment Pending
Local time
Yesterday, 20:12
Joined
May 11, 2011
Messages
11,613
First, you don't store calculated values. When you need a calculated value, you calculate it in a query and then reference the query when you need that value.

Second, how is the name of the month getting into the database? Are people expected to type "March", "April", or god forbid "February" into a text field? Lastly, are you not storing an actual date instead of the Month name?

Please explain more about the entirety of yoru database and this specific table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:12
Joined
Feb 19, 2013
Messages
16,553
you are using a calculation field in a table (not recommended) but the month function should work for you - based on a date.

However, from your attachment, it looks like you may be trying to convert your month_transportation field which is a short text field (presumable January, February etc)

If this is the case then the month function a) will not work and b) a solution that would work cannot be used in a table calculated field.

There are better ways to do this but which one all depends on why you want to do this - what is the benefit? Can you explain in a few words what the db is supposed to do (at least this part of it) and what you do with the data.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:12
Joined
Feb 28, 2001
Messages
27,001
I'm with the others on this one, Susy. Store the date transported. After that, you can build a query to have either the short-name month or the month number by using the DatePart function in the query. And any place that needs this information can call a query just as easily as it could call out the table directly. Most things that need such information don't CARE whether they are dealing directly to the table or through a query.
 

Susy

New member
Local time
Today, 02:12
Joined
Dec 19, 2019
Messages
25
First of all, thanks to all for your help!

@theDBguy: Yes, I tried but it doesn't work.

@plog: I have stored a few calculations because they were very easy (simple multiplications). Is this so bad?
The people choose the months in a list. They don't have to type it.

@CJ_London: I want to do this because I created a query (Sum Total) where I would like to sort the months in chronological order. Therefore, I read that I have to translate the months into numbers.

@The_Doc_Man: I am not sure to understand. You mean by simply using a full date (01.02.20) and not only the name of the month (February), I will be able to quickly fix the issue?
 

isladogs

MVP / VIP
Local time
Today, 01:12
Joined
Jan 14, 2017
Messages
18,186
Some examples from the VBE Immediate window

PHP:
?Month(Date)
 1 
?Month(#28/03/2020#)
 3 
?MonthName(Month(#28/03/2020#))
March
?Month(#17 Feb 2019#)
 2
 

Susy

New member
Local time
Today, 02:12
Joined
Dec 19, 2019
Messages
25
Guys, I managed to translate the months into numbers. Thanks a lot for your help.
However, can anyone please explain why I shouldn't do simple calculations in my tables without using queries?
 

isladogs

MVP / VIP
Local time
Today, 01:12
Joined
Jan 14, 2017
Messages
18,186
Hi Susy
Adding a calculated field is unnecessary duplication of data. Also if you modify the source data used in the calculation, the calculated field isn’t updated until you move off that source data



Sent from my iPhone using Tapatalk
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:12
Joined
Feb 19, 2013
Messages
16,553
the other way to do it if you just have a month name, no day or year is to have a separate table

tblMonths
PK...mthName
1.....January
2.....February
etc

then in your totals query, simply link your table to tblMonths on mthName and you sort on tblMonths.mthName.

Your approach is the Excel way, databases do not work the same way.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:12
Joined
Feb 28, 2001
Messages
27,001
I am not sure to understand. You mean by simply using a full date (01.02.20) and not only the name of the month (February), I will be able to quickly fix the issue?

See Colin's answer (under the screen name IslaDogs). Using DatePart and the real date, you can get any individual part of any date any time you wanted it through a query. A calculated field is useful only in narrowly defined circumstances and I have never yet had to use one - because QUERIES are where you do data manipulation. Store only what you need to store. Compute via query when you need computation.

If you wanted the month full name, you can use MonthName. If you wanted the first three letters? Left( MonthName( date ), 3 ) would do that for you. You can also look up the DatePart function to pull out month number, day-of-the-month, or any other part you want. And yes, just storing the date is the right answer for another reason... it is easy. If you always and only want to store the current date, that is the Date() function. If you wanted to allow your users to pick a date and store it as such, that is an option in text boxes to use the ShowDatePicker property.

https://docs.microsoft.com/en-us/office/vba/api/access.textbox.showdatepicker

(Note: For best results, the text box must be bound to a date field.)
 

Users who are viewing this thread

Top Bottom