Using Combo Box to select a month and return records for only that month (1 Viewer)

Paul Gonzaga

New member
Local time
Today, 13:35
Joined
Jun 8, 2020
Messages
14
Hi there,
Am new to access and trying to accomplish something with combo box. So i have created a combo box on a form where a i can select a month (e.g May 2020). Then when i run a query, it should return only transactions for the selected month. How do i add the combo box selection (of the month) as the criteria in my query and only get records for the for the specified month?

Not: I have the Date field in my table and its what i referenced to in my combo box. In the combo box row Source, i have the select statement below:

SELECT First([tblAdvances].[AdvanceRef]), Format([tblAdvances].[AdvanceDate],"mmmm yyyy") FROM tblAdvances GROUP BY Format([tblAdvances].[AdvanceDate],"mmmm yyyy"), Format([tblAdvances].[AdvanceDate], "yyyy mm") ORDER BY Format([tblAdvances].[AdvanceDate], "yyyy mm");

Attached i the form sample.

I appreciate any help offered.
Paul
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:35
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Do you know if the date/time field in the table also contain time?
 

isladogs

MVP / VIP
Local time
Today, 10:35
Joined
Jan 14, 2017
Messages
18,186
In case it helps, look at my Incident Analysis example app which includes code that groups/filters by month or week or year etc.
You will need to scroll down the web page for that example
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:35
Joined
Oct 29, 2018
Messages
21,357
The Bound Column is set to 1
Okay, thanks. However, that doesn't make sense. If you want to select a month and year, what is the value of AdvanceRef?

I might adjust the Combobox Row Source as follows:
SQL:
SELECT DISTINCT Format(AdvanceDate, "yyyy-mm-01"), Format(AdvanceDate, "mmmm yyyy") FROM tblAdvances ORDER BY AdvanceDate

You would then be able to use something like this for a criteria:

Between Me.ComboboxName And DateAdd("m",1,Me.ComboboxName)-1

(untested)
Hope that helps...
 
Last edited:

Paul Gonzaga

New member
Local time
Today, 13:35
Joined
Jun 8, 2020
Messages
14
Okay, thanks. However, that doesn't make sense. If you want to select a month and year, what is the value of AdvanceRef?

I might adjust the Combobox Row Source as follows:
SQL:
SELECT DISTINCT Format(AdvanceDate, "#yyyy-mm-01#"), Format(AdvanceDate, "mmmm yyyy") FROM tblAdvances ORDER BY AdvanceDate

You would then be able to use something like this for a criteria:

Between Me.ComboboxName And DateAdd("m",1,Me.ComboboxName)-1

(untested)
Hope that helps...
Thanks for this. However, the query instead returns duplicate months. E.g The combo box lists each unique date in may,, so if my table has records for 5/1/2020, 5/2/2020, and 5/3/2020, then there are 3 items with May 2020 in the combo box list. See attached snap. Th criteria doesn't seem to work at all. Complaining of the expression being complex.

I appreciate any additional assistance.

Thanks
 

Attachments

  • Date reoccurance_2 - Copy.png
    Date reoccurance_2 - Copy.png
    26.3 KB · Views: 109

theDBguy

I’m here to help
Staff member
Local time
Today, 03:35
Joined
Oct 29, 2018
Messages
21,357
Thanks for this. However, the query instead returns duplicate months. E.g The combo box lists each unique date in may,, so if my table has records for 5/1/2020, 5/2/2020, and 5/3/2020, then there are 3 items with May 2020 in the combo box list. See attached snap. Th criteria doesn't seem to work at all. Complaining of the expression being complex.

I appreciate any additional assistance.

Thanks
Interesting. Are you able to post a sample copy of your db with test data?

PS. I edited my post above. I guess I didn't need the date delimiters. Please try it again. Thanks.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
Code:
SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = [Forms]![yourFormName]![yourComboName];
    
'or like this:

SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = '" & [Forms]![yourFormName]![yourComboName] & "'";
 

Pac-Man

Active member
Local time
Today, 15:35
Joined
Apr 14, 2020
Messages
408
Hi there,
Am new to access and trying to accomplish something with combo box. So i have created a combo box on a form where a i can select a month (e.g May 2020). Then when i run a query, it should return only transactions for the selected month. How do i add the combo box selection (of the month) as the criteria in my query and only get records for the for the specified month?

Not: I have the Date field in my table and its what i referenced to in my combo box. In the combo box row Source, i have the select statement below:

SELECT First([tblAdvances].[AdvanceRef]), Format([tblAdvances].[AdvanceDate],"mmmm yyyy") FROM tblAdvances GROUP BY Format([tblAdvances].[AdvanceDate],"mmmm yyyy"), Format([tblAdvances].[AdvanceDate], "yyyy mm") ORDER BY Format([tblAdvances].[AdvanceDate], "yyyy mm");

Attached i the form sample.

I appreciate any help offered.
Paul
I had similar situation where I had to filter the query contents by an year (in which a report was issued) and my table contained dates on which report is issued.

I am sharing here what I did (cannot modify the code exactly as yours because I'm writing this post from mobile phone so you have to edit my code like field names to use in your db). In my case I had to filter only with one criteria i.e. year whereas in your case you have two criteria i.e. month and year. You can apply this method by splitting your month field into two i.e. cboMonth and cboYear. Additional benefit of this is if you select only an year in the form, it'll show you all reports of that year irrespective of the month.

In the design view of query, I defined new field using Year([ReportDate]) and in the criteria row of the field, I used

Code:
IIF([Forms]![frmSearch]![cboYear].[ListIndex]=-1, Year([ReportDate]), [Forms]![frmSearch]![cboYear]

You can similarly defined other field using Month([ReportDate]) and in the criteria for the field as

Code:
IIF([Forms]![frmSearch]![cboMonth].[ListIndex]=-1, Month([ReportDate]), [Forms]![frmSearch]![cboMonth]

There may be better method to do this is suggested by experts above. I'm new and this approach came to my mind when I was doing in my db. Hope it helps.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:35
Joined
Jan 20, 2009
Messages
12,849
I might adjust the Combobox Row Source as follows:
SQL:
SELECT DISTINCT Format(AdvanceDate, "yyyy-mm-01"), Format(AdvanceDate, "mmmm yyyy") FROM tblAdvances ORDER BY AdvanceDate

You would then be able to use something like this for a criteria:

Between Me.ComboboxName And DateAdd("m",1,Me.ComboboxName)-1

Not a good strategy. You are converting dates to strings with Format(), implicitly casting the strings back to dates with DateAdd() subtracting an integer then implicitly casting back to a date again.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:35
Joined
Jan 20, 2009
Messages
12,849
Code:
SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = [Forms]![yourFormName]![yourComboName];
  
'or like this:

SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = '" & [Forms]![yourFormName]![yourComboName] & "'";

That is a terribly inefficient technique. Every record must be processed to convert the date to a string before the Select can be applied so the index on the date field cannot be used.. The slow performance won't be noticed with small numbers of records but will progressively deteriorate as the number of records increases.

If you don't understand why it is bad, I suggest you Google SARGable.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
to make it SARGable, as suggested by MVP Galaxiom:

add two Unbound Textbox to your form (txtStartDate and txtEndDate).
set their Visible property to No (hide them).
on AfterUpdate Event of your Combobox, set the Values of these
two unbound Textbox:

Code:
Private Sub Combo0_AfterUpdate()
    If Me.Combo0.ListIndex = -1 Then
        Me.txtStartDate = 1
        Me.txtEndDate = 1
        
    Else
        Me.txtStartDate = CDate("1 " & Me.Combo0)
        Me.txtEndDate = DateSerial(Year(Me.Text1), Month(Me.Text1) + 1, 0)
    End If
End Sub

your Query need to be change to SARgable query:
Code:
SELECT * FROM tblAdvances
    WHERE
    AdvanceDate Between [Forms]![yourFormName]![txtStartDate]
        And
            [Forms]![yourFormName]![txtEndDate];
 
Last edited:

Paul Gonzaga

New member
Local time
Today, 13:35
Joined
Jun 8, 2020
Messages
14
Code:
SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = [Forms]![yourFormName]![yourComboName];
   
'or like this:

SELECT * FROM tblAdvances
    WHERE
    Format(AdvanceDate, "mmmm yyyy") = '" & [Forms]![yourFormName]![yourComboName] & "'";
Hi arnelgp,
My reply is kinda delayed but its because am on a different time zoom.
Where should i use this select statement? It looks like the criteria but not sure where to use it at. Am new to access, i haven't grasped most of the basics.

Thanks
 

Paul Gonzaga

New member
Local time
Today, 13:35
Joined
Jun 8, 2020
Messages
14
Interesting. Are you able to post a sample copy of your db with test data?

PS. I edited my post above. I guess I didn't need the date delimiters. Please try it again. Thanks.
Hi there,
My response is late because of the difference in the time zoom.
See the attached sample DB

1. There are 3 table but for this query in question, am using tblAdvances

2. The query I want to run is QryAllAdvances

3. And the form I want to read the month from is frmDateSelection

Thanks form your help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
what timezone are you in?

there is no Attachment.

you create New query out of QryAllAdvances

add a New Column:
Field: Expr: Format(AdvanceDate, "mmmm yyyy")
Table: QryAllAdvances
Sort:
Show: (untick)
Criteria: [Forms]![frmDateSelection]![theComboName]

save query to new name.

when you are done making selection on the Combo on frmDateSelection, run the query you made.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
here is a sample for you to understand.
see Query1, see the code on the commandButton on the form.
 

Attachments

  • sampleCombo.zip
    34.8 KB · Views: 112

Paul Gonzaga

New member
Local time
Today, 13:35
Joined
Jun 8, 2020
Messages
14
here is a sample for you to understand.
see Query1, see the code on the commandButton on the form.

Its interesting how perfect your solution is with the sample DB. I cant tell why for some reason, on my DB, the combo box is empty after using the Select Statement provided. I have properly substituted the table name but the drop down is empty. I have attached my DB. Please help look at it.


Thanks,
Paul
 

Attachments

  • Tracker_DB.zip
    219.9 KB · Views: 110

Users who are viewing this thread

Top Bottom