Solved Sort months in combobox with sql query (1 Viewer)

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
Hi all,

I have a small application taking care of my workhours, projects and invoicing. I work for different clients and projects on hourly base.

I select a project in a combo which will show all records in my WorkHoursjournal by project. Secondary I create a sql query as rowsource for a second combobox (Monthchoice)
it is a simple; SQLmonth = "SELECT DISTINCT wMonth, ProjectNr FROM WorkHoursjournal WHERE (((ProjectNr)=[Projectchoice].[Value]))" There is no problem with this, everything goes as planned. BUT, I would like the combobox sorted in sequence; Januari, februari etc.

I see two solutions,
1. create a table with two fields; monthNr; monthName and create a join in the query.
2. create the extra field 'monthNr' in my WorkHoursjournal

Both simple but I am looking for something else, is solution 3, 4 ... possible?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:47
Joined
May 21, 2018
Messages
8,463
I would do 1. Lot simpler, and if doing that you can save some more potential work for other potential fields
MonthID
MonthShortName
MonthLongName
Quarter
...

1 Jan January 1st
2 Feb February 1st
 

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
I would do 1. Lot simpler, and if doing that you can save some more potential work for other potential fields
MonthID
MonthShortName
MonthLongName
Quarter
...

1 Jan January 1st
2 Feb February 1st
It surely the most flexible solution. thx @MajP
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:47
Joined
Jul 9, 2003
Messages
16,244
There is a possible problem with this approach. Where do you derive your month name from? If you are only ever going to handle records for the current year, then there's no problem. However, if you have records for January 2020 and you move into January 2021 you now have January records from two different years, which might be a problem.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:47
Joined
May 21, 2018
Messages
8,463
However, this works for me without a field or table
Code:
"SELECT DISTINCT wMonth, ProjectNr FROM WorkHoursjournal WHERE (((ProjectNr)=[Projectchoice].[Value])) ORDER BY Month(CDate([wMonth] & " 1 2021"));"

CDate is pretty powerful and can convert anything that looks like a date into a date. Then you can get the month.
 

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
There is a possible problem with this approach. Where do you derive your month name from? If you are only ever going to handle records for the current year, then there's no problem. However, if you have records for January 2020 and you move into January 2021 you now have January records from two different years, which might be a problem.
Thought about that one too! I took the shortcut and added the field 'Year' in WorkHoursjournal. I am not using that in my query but the year is shown.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:47
Joined
May 21, 2018
Messages
8,463
Do you really need that field or do you have a real date field. If you have a real date field then just get month and year as calculated values in the query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,169
simply Sorting on this:

... Order By CDate([wMonth] & "-1-2021") Asc;
 

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
Thanks guys, It did not annoy me for the last five years and worked fine. At the moment I am changing my UI and was curious and look for a way to rework the sorting. Normally I move a table to a backup table with the year as name. As said I am using it in a private application so no harm done.

Appreciate the comments, they made me think about the proposed way to sort. I like to try, for me, difficult things which could help me in real serious applications where other user have to use your work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
Always sort by the date, you can display whatever you want. If you want to group by yyyymm, then calculate that field in the query. Do not store it. Sort by yyyymm but show the name of the month instead of yyyymm.
 

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
Always sort by the date, you can display whatever you want. If you want to group by yyyymm, then calculate that field in the query. Do not store it. Sort by yyyymm but show the name of the month instead of yyyymm.
I only need the date for the invoice. I need the weeknumber for my timesheets and invoice, I need the month for invoicing. I use the parts of a date to create week and month for easy use, if I would store a date in my table I have to calculate the only two really important parts of it for every part of my administration, I think that what a bit 'overdone'
I am not being offensive or stupid, I see the use of storing a date but not in this situation where it is of no use.
 

Attachments

  • Urenoverzicht.png
    Urenoverzicht.png
    7.9 KB · Views: 144

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
Sorting only by month doesn't work if you have more than one year's worth of data. Creating a new database for each year is a spreadsheet solution, not one that should be used with a relational database.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:47
Joined
May 21, 2018
Messages
8,463
I need the month for invoicing. I use the parts of a date to create week and month for easy use, if I would store a date in my table I have to calculate the only two really important parts of it for every part of my administration, I think that what a bit 'overdone'
I am not being offensive or stupid, I see the use of storing a date but not in this situation where it is of no use.
Actually I think you are creating more work. If you save a single date field in the proper week or month year. You can always show the week number, month, if needed year, and sort on that field. You have created multiple fields and even discussed another field just for the sort. Not quite sure how saving multiple pieces, writing a more complex sort is less overkill,

Sometimes you only need a month and year and not a specific date. I would then make a month and year picker but save that value in a single date field as the first of that month and year. I can always display Feb 2021, or week 6, or Qtr 1, or 2021, or whatever.
 

Rene vK

Member
Local time
Today, 15:47
Joined
Mar 3, 2013
Messages
123
Dear people,
This discussion started with the question if there is a another way to sort a combobox, to get my teeth in, and now we are all over the place discussing all the things I presumably do wrong.

My application works fine, nothing is wrong, and it reacts very direct.
I create a new record that contains, week, month, project and weekdays. I registrate the hours worked for every project .Every week I create a timesheet for the particular project(s) and each month or after finishing a small project I create an invoice. I do not have overlapping tables and it makes my administrative work real easy.

I backup a full year of workhours because the Dutch Law tells me to. I see no purpose for keeping it in a live table. The backup has nothing to do with an Excel use of data.

I know you all mean the best for building an application but this one is real small and fast. Thank you all and I will close this discussion!
 

Users who are viewing this thread

Top Bottom