Using Combo Box to select a month and return records for only that month

[Paul Gonzaga]

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

 

[theDBguy]

Hi. Welcome to AWF!

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

 

[Paul Gonzaga]

Hi. Welcome to AWF!

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

No, it only has dates..

 

[theDBguy]

No, it only has dates..

Okay, thanks. Next question: What value is in the Bound Column property of your Combobox?

 

[isladogs]

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

 

[Paul Gonzaga]

The Bound Column is set to 1

 

[Paul Gonzaga]

Okay, thanks. Next question: What value is in the Bound Column property of your Combobox?

Its 1

 

[theDBguy]

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:

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...

 

[Paul Gonzaga]

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:

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

 

[theDBguy]

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.

 

[arnelgp]

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]

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

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

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]

I might adjust the Combobox Row Source as follows:

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]

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]

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:

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:

SELECT * FROM tblAdvances
    WHERE
    AdvanceDate Between [Forms]![yourFormName]![txtStartDate]
        And
            [Forms]![yourFormName]![txtEndDate];

 

[Paul Gonzaga]

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]

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]

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]

here is a sample for you to understand.
see Query1, see the code on the commandButton on the form.

 

[Paul Gonzaga]

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