423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

How to populate a combo box with dates between two dates

P: 89
I have two text boxes on a form with two different dates. A table contains records with a field containing different dates. I want a combobox on the form showing all [month-year] in format "mm-yyyy" from the table between the dates on the form. Please help, how is it possible.
Jan 5 '18 #1

✓ answered by GazMathias

Hi,

There may be better ways of doing this but here I am using a subquery aliased as 'a' to select and format dates into month and year whilst also selecting the date I want displayed and in the outer query I am grouping and sorting the values to coerce the results to the desired affect.

Expand|Select|Wrap|Line Numbers
  1. select a.DisplayDate, a.DisplayYear, a.DisplayMonth FROM (
  2. SELECT Format([INVOICE_DATE],"mm-yyyy") AS DisplayDate, Format([INVOICE_DATE],"mm") AS DisplayMonth,  Format([INVOICE_DATE],"yyyy") AS DisplayYear
  3. FROM Invoices
  4. ) as a GROUP BY a.DisplayYear, a.DisplayMonth, a.DisplayDate
  5. ORDER BY a.DisplayYear, a.DisplayMonth
  6.  
You would use a query such as this in the rowsource of an unbound combobox and set it to use bound column 1.

Apologies, I realise my answer does not cover the full scope of the question but I hope it points you in the right direction. You would need to add a where condition to the subquery which you would build dynamically and change the rowsource of the combobox and refresh it. I can look at that later on if you do not understand how to do that.

Gaz

Share this Question
Share on Google+
2 Replies


GazMathias
Expert 100+
P: 189
Hi,

There may be better ways of doing this but here I am using a subquery aliased as 'a' to select and format dates into month and year whilst also selecting the date I want displayed and in the outer query I am grouping and sorting the values to coerce the results to the desired affect.

Expand|Select|Wrap|Line Numbers
  1. select a.DisplayDate, a.DisplayYear, a.DisplayMonth FROM (
  2. SELECT Format([INVOICE_DATE],"mm-yyyy") AS DisplayDate, Format([INVOICE_DATE],"mm") AS DisplayMonth,  Format([INVOICE_DATE],"yyyy") AS DisplayYear
  3. FROM Invoices
  4. ) as a GROUP BY a.DisplayYear, a.DisplayMonth, a.DisplayDate
  5. ORDER BY a.DisplayYear, a.DisplayMonth
  6.  
You would use a query such as this in the rowsource of an unbound combobox and set it to use bound column 1.

Apologies, I realise my answer does not cover the full scope of the question but I hope it points you in the right direction. You would need to add a where condition to the subquery which you would build dynamically and change the rowsource of the combobox and refresh it. I can look at that later on if you do not understand how to do that.

Gaz
Jan 5 '18 #2

P: 89
Thank you very much!
Jan 5 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.