Month Year extract (1 Viewer)

[Gismo]

Hi All,

Please could you assist

Month year extract from date not working

Date format YYYY/MM/DD
Result is all Jan 2022

Period: Format([Transaction Date],"mmm\ yyyy")

 

[The_Doc_Man]

What is the data type of [Transaction Date]? The "Date Format" you quoted simply defines how the information would be presented in a table or query and is in fact immaterial to the operation of the Format function - unless [Transaction Date] is a string.

 

[Gismo]

What is the data type of [Transaction Date]? The "Date Format" you quoted simply defines how the information would be presented in a table or query and is in fact immaterial to the operation of the Format function - unless [Transaction Date] is a string.

Transaction date is set up as a short date yyyy/mm/dd

 

[ebs17]

Filter to January 2022 (without time shares):

SELECT
   *
FROM
   TableX
WHERE
   [Transaction date] BETWEEN #1/1/2022# AND #1/31/2022#

This allows the use of a possibly existing index on the date.

 

[arnelgp]

Month year extract from date not working

unless the field is Null (blank).

 

[Gismo]

Filter to January 2022 (without time shares):

SELECT
   *
FROM
   TableX
WHERE
   [Transaction date] BETWEEN #1/1/2022# AND #1/31/2022#

This allows the use of a possibly existing index on the date.

Hi,

I dont want to have any limitations in hardcoding any date ranges

 

[Gismo]

unless the field is Null (blank).

Al the transactions in the transaction table has a date

Just not sure why in this DB this is not working, I have it set up exactly as in another DB and it works perfect

 

[arnelgp]

how about the Fieldname, is it correct?

 

[ebs17]

I dont want to have any limitations in hardcoding any date ranges

Neither do I. But first let's look at how the query should look like so that it is functional and performant.

In the second step, you use parameters and make the whole thing variable.

PARAMETERS
   pStartDate Date,
   pEndDate Date
;
SELECT
   *
FROM
   TableX
WHERE
   [Transaction date] BETWEEN pStartDate AND pEndDate

A month is a period. If necessary, the time limits could also be set in such a way that 10 days, four weeks or a fiscal year (spanning the turn of the year) can be queried. That too is variability.

 

[Gismo]

how about the Fieldname, is it correct?

Yep, fieldname is correct

 

[Gismo]

Sorry all, sorted now, the format pulled through incorrectly when imported from excel

 

[Gismo]

I do have another question
How do I sort by month?

The Month year is not in sequence
This is a sum query so I can not include the actual date to sort

 

[arnelgp]

you sort it on the Base date field or create another Calculated Column:

Expr1: Format([base field], "yyyymm")

then sort on this column. you do not need to "show" this column

 

[ebs17]

Do you know the difference between strings and numbers? Strings are sorted alphanumerically.

SELECT
   Format(XDate, "mmm yyyy") AS Period
FROM
   TableX
ORDER BY
   Year(XDate),
   Month(XDate)

 

[Gismo]

you sort it on the Base date field or create another Calculated Column:

Expr1: Format([base field], "yyyymm")

then sort on this column. you do not need to "show" this column

I have the 1st query sorted by the actual date
Query 2 I did not include the date as I have a sum in this query but the Sort order for month year is not in the same sequence as in the 1st query

Not sure how to add a column to sort the second query by

 

[Gismo]

Do you know the difference between strings and numbers? Strings are sorted alphanumerically.

SELECT
   Format(XDate, "mmm yyyy") AS Period
FROM
   TableX
ORDER BY
   Year(XDate),
   Month(XDate)

A bit in the dark here, sorry

This is what I did but can not sort by period as this will give a incorrect sequence by month

 

[ebs17]

XDate should be a date, i.e. a field with data type Date. Internally, a date is a number:

? #11/8/2022# * 1   
44873

Therefore, a real date can be easily sorted, even though it looks like text because it is represented as a string with separators between the digits. However, the format statement produces text. This then makes sorting alphanumeric:

? "Aug" < "Jan"
True

 

[Gismo]

XDate should be a date, i.e. a field with data type Date. Internally, a date is a number:

? #11/8/2022# * 1 
44873

Therefore, a real date can be easily sorted, even though it looks like text because it is represented as a string with separators between the digits. However, the format statement produces text. This then makes sorting alphanumeric:

? "Aug" < "Jan"
True

I believe this is what I did but still not sorted by month correctly

SELECT Format([Transaction Date],"mmm yyyy") AS Period, Transactions.[Transaction Type Ind], Sum(Transactions.[Total (UGX)]) AS [Total (UGX)]
FROM Transactions
GROUP BY Format([Transaction Date],"mmm yyyy"), Transactions.[Transaction Type Ind]
HAVING (((Transactions.[Transaction Type Ind])=2))
ORDER BY Format([Transaction Date],"mmm yyyy");

 

[Minty]

I think the last line in your query might give you a hint...

SELECT Format([Transaction Date],"mmm yyyy") AS Period, Transactions.[Transaction Type Ind], Sum(Transactions.[Total (UGX)]) AS [Total (UGX)]
FROM Transactions
GROUP BY Format([Transaction Date],"mmm yyyy"), Transactions.[Transaction Type Ind]
HAVING (((Transactions.[Transaction Type Ind])=2))
ORDER BY Format([Transaction Date],"mmm yyyy");

Try

ORDER BY Format([Transaction Date],"mm yyyy")

Which should give you a numeric month number with a leading zero?

 

[Gasman]

So include it?