display "DatePart("M",[OrderDate])" expression as text

[Granpa _rb]

As a retired engineer I have taught myself M S Access but I cannot resolve
how I can display "DatePart("M",[OrderDate])" expression in a query as text
eg Feb , Mar etci nstead of 2 on a form . please can any one help.

 

[Billd]

Add a function into a module:-

Public Function ShowMeTheMonth(pDate as Date) as string


ShowMeTheMonth = DatePart("M",pDate)

end Function

'--------------------------------------
IN your query line via the SQL View option "SELECT
......ShowMeTheMonth(YourTable.Date) FROM....." etc

 

[Douglas J. Steele]

Use Format([OrderDate], "mmm") or Format([OrderDate], "mmmm")

"mmm" will give you Jan, Feb, Mar, "mmmm" will give you January, February,
March.

 

[Granpa _rb]

Thank you Billd I entered the function details as per your reply but I am
NOT sure about the bit to enter in the SQL query My SQL is as follows :-

SELECT [2-ProductSalesOrders].DateofSale, DatePart("yyyy",[Dateofsale]) AS
AccntYear, DatePart("M",[dateofSale]) AS AccntMonth, "Qrt " &
DatePart("q",[dateofsale]) AS DateprtQrt2
FROM [2-ProductSalesOrders]
WHERE (((DatePart("yyyy",[Dateofsale]))=2007));

The part "DatePart("M",[dateofSale]) AS AccntMonth" being the part I would
like to appear as Text can you please explain Granpa_rb

Add a function into a module:-

Public Function ShowMeTheMonth(pDate as Date) as string


ShowMeTheMonth = DatePart("M",pDate)

end Function

'--------------------------------------
IN your query line via the SQL View option "SELECT
.....ShowMeTheMonth(YourTable.Date) FROM....." etc

As a retired engineer I have taught myself M S Access but I cannot resolve
how I can display "DatePart("M",[OrderDate])" expression in a query as text
eg Feb , Mar etci nstead of 2 on a form . please can any one help.

 

[John W. Vinson]

Thank you Billd I entered the function details as per your reply but I am
NOT sure about the bit to enter in the SQL query My SQL is as follows :-

SELECT [2-ProductSalesOrders].DateofSale, DatePart("yyyy",[Dateofsale]) AS
AccntYear, DatePart("M",[dateofSale]) AS AccntMonth, "Qrt " &
DatePart("q",[dateofsale]) AS DateprtQrt2
FROM [2-ProductSalesOrders]
WHERE (((DatePart("yyyy",[Dateofsale]))=2007));

The part "DatePart("M",[dateofSale]) AS AccntMonth" being the part I would
like to appear as Text can you please explain Granpa_rb

The DatePart function returns a month number. To display the month name use
the Format function instead:

SELECT [2-ProductSalesOrders].DateofSale, DatePart("yyyy",[Dateofsale]) AS
AccntYear, Format([dateofSale], "mmm") AS AccntMonth, "Qrt " &
DatePart("q",[dateofsale]) AS DateprtQrt2
FROM [2-ProductSalesOrders]
WHERE (((DatePart("yyyy",[Dateofsale]))=2007));

Or you could use a custom format to see the year, month and quarter all in one
string:

Format([dateofsale], "yyyy mmm \Q\r\t q")

will show 2007 Feb Qrt1.

Alternatively, just use a Form or Report to display your data (you shouldn't
be looking at query datasheets except for debugging); bind the DateOfSale
function to a textbox and set its Format property to "mmm".