Formatting date VB/SQL into list box 1

[Egglar]

Hello.

I have a set of records in tblPurchaseInvoice

I want to fill a list box on a form with dates that are in this table, and change the format, eg..

tblPurchaseInvoice.Date
01/01/03
01/01/03
02/01/03
05/01/03
01/02/03
01/02/03
04/03/03
04/03/03
01/06/03
01/03/03

Listbox displays:
January 2003
Febuary 2003
March 2003
June 2003

I would rather do this by the row source property of the list box with sql, but i dont mind doing it through VB.

Thanks for your help in advance, Elliot.

 

[psemianonymous]

in SQL you have something like

"SELECT myDateField from MYTABLENAME"

Switch this to:

SELECT Format([myDateField],"mmmm yyyy") as FormattedDate from MYTABLENAME

Note that the quotes around "mmmm yyyy" may disappear if you actually paste this into the SQL box - just double-check that.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[psemianonymous]

Byah, let's try this again:

SELECT Format([myDateField],"mmmm yyyy") as FormattedDate from MYTABLENAME GROUP BY FormattedDate

If that errors, then you have to do the weird GROUP BY-calculated-value-workaround:

SELECT Format([myDateField],"mmmm yyyy") as FormattedDate from MYTABLENAME GROUP BY Format([myDateField],"mmmm yyyy")

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Egglar]

I was so close with the formatting! i had Test:Format([Date],yyyy mmmm)


Thanks!