473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to populate a combo box with dates between two dates

99 64KB
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

2 1831
GazMathias
228 Expert 128KB
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
mshakeelattari
99 64KB
Thank you very much!
Jan 5 '18 #3

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

Similar topics

5
by: Filips Benoit | last post by:
Dear all, How can i populate a combo with the field-caption-names of 1 table? Thanks Filip
3
by: David | last post by:
Hi, I have a code for filling a dropdown, but I cannot work out how to make it fill with every date, including todays date, as required below i.e. 05/10/2006 06/10/2006 07/10/2006...
5
by: jdwyer05 | last post by:
Hello, I am trying to populate a combo box with only unique values. Currently I am using an access database and VB6 Enterprise. The program populates the combo box fine however, there are several...
3
by: joseph.mccastlain | last post by:
Hello All, I am a new user to Access. I am currently designing a database consisting of four tables for multiple users. Rather than bore you with the goals and such, here is what I am...
7
by: nareshpulipati | last post by:
Hi all, I am new to VB .net. Iam trying to populate the database item into combo box. Database Type:SQL(ODBC) My code retuns no value in combo box Public Class Form1 Private Sub...
18
by: jmarcrum | last post by:
Hi everyone! I have a form that when the user opens it, the Date combo box on the form is populated with every month of the year. But I don't want it to do that! I want it to populate with the...
4
by: =?Utf-8?B?R3JlZw==?= | last post by:
Can someone give me e simple example of to populate a combo box / list box using an ArrayList? THanks.
1
by: Ajmal5 | last post by:
I use two combo in forms. One for Country and other for City. What i do when i selected Country the City combo is populated through country. Thanks in Advance.
7
by: RG360 | last post by:
Hello. I am new in Access and I need assistance from Pro's I have a data entry form from 2 main tables and they have relationships with other tables. My main table only contains Field ID's,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.