Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-09-2020, 07:54 AM   #1
Susy
Newly Registered User
 
Join Date: Dec 2019
Posts: 9
Thanks: 6
Thanked 0 Times in 0 Posts
Susy is on a distinguished road
Translation Month into Number

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
Attached Images
File Type: jpg Capture.JPG (13.4 KB, 26 views)

Susy is offline   Reply With Quote
Old 01-09-2020, 07:57 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,280
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Translation Month into Number

Hi Susy. Have you tried using the Month() function?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-09-2020, 08:14 AM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,495
Thanks: 12
Thanked 2,305 Times in 2,256 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Translation Month into Number

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.

plog is offline   Reply With Quote
Old 01-09-2020, 08:16 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Translation Month into Number

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-09-2020, 08:19 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,098
Thanks: 102
Thanked 1,896 Times in 1,731 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Translation Month into Number

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-09-2020, 11:56 PM   #6
Susy
Newly Registered User
 
Join Date: Dec 2019
Posts: 9
Thanks: 6
Thanked 0 Times in 0 Posts
Susy is on a distinguished road
Re: Translation Month into Number

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?
Susy is offline   Reply With Quote
Old 01-10-2020, 12:10 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,961
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Translation Month into Number

Some examples from the VBE Immediate window

PHP Code:
?Month(Date)
 

?Month(#28/03/2020#)
 

?MonthName(Month(#28/03/2020#))
March
?Month(#17 Feb 2019#)
 


__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 01-10-2020, 01:32 AM   #8
Susy
Newly Registered User
 
Join Date: Dec 2019
Posts: 9
Thanks: 6
Thanked 0 Times in 0 Posts
Susy is on a distinguished road
Re: Translation Month into Number

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?
Susy is offline   Reply With Quote
Old 01-10-2020, 01:41 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,961
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Translation Month into Number

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 01-10-2020, 02:27 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Translation Month into Number

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-10-2020, 08:09 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,098
Thanks: 102
Thanked 1,896 Times in 1,731 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Translation Month into Number

Quote:
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/off...showdatepicker

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

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate date given month and week number in month here4real Modules & VBA 2 03-03-2014 01:16 PM
Function / Update Query - change Month text to Month Number (Aug -> 8) Rx_ Code Repository 1 09-19-2012 10:43 AM
How do I convert an inout month number into a month name? helen269 Reports 17 03-01-2012 11:57 AM
Function that turns string month name into month Number catbeasy General 9 08-03-2009 12:10 PM
converting Month Name to Month Number lala Forms 4 10-08-2008 03:22 PM




All times are GMT -8. The time now is 02:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World