Period query

[Gismo]

Hi all,

I need to filter a query with periods from another table
I only want to see the transaction from and to period

My formula does not seem to work

Below is the result of the query before filtering
Query1 Query1

Period	Total Sales (UGX)	Total Purchase (UGX)	Period1	Year From	Year To
Jan 2022	UGX 552000	UGX 4063226,21	01 2022	Jul 2021	Jun 2022
Feb 2022	UGX 5388200	UGX 603393,6	02 2022	Jul 2021	Jun 2022
Mar 2022	UGX 9616200	UGX 291800	03 2022	Jul 2021	Jun 2022
Apr 2022	UGX 4150000	UGX 1817739,83	04 2022	Jul 2021	Jun 2022
May 2022	UGX 11118400	UGX 4152786	05 2022	Jul 2021	Jun 2022
Jun 2022	UGX 7212200	UGX 1150950	06 2022	Jul 2021	Jun 2022
Jul 2022	UGX 648000	UGX 2553440,47	07 2022	Jul 2021	Jun 2022
Aug 2022	UGX 3776200	UGX 2980255,37	08 2022	Jul 2021	Jun 2022

I only want to see transection >= Year From and <= Year To

>=[Financial Year]![Year From] And <=[Financial Year]![Year To]

 

[CJ_London]

My formula does not seem to work

Please get into the habit of stating what 'does not work' means, otherwise we are guessing

My best guess is I don't see a table or query called 'Financial Year'

 

[Minty]

What data type is your Period data and where does [Financial Year] table come into things?

Edit - @CJ_London beat me to it

 

[ebs17]

? "Jan 2022">"Jul 2021", "Jan 2022"<"Jun 2022", #1/1/2022#>=#7/1/2021#, #1/1/2022#<#7/1/2022#
Falsch        Wahr          Wahr          Wahr

"Jan 2022" is a string, resulting of Format command. Sorting and comparing strings is done differently than numbers, from the left with the first, then second, ... character.

A full date is internally a number, so it will sort and compare in ways you'd expect.

 

[Gismo]

Please get into the habit of stating what 'does not work' means, otherwise we are guessing

My best guess is I don't see a table or query called 'Financial Year'

Apologies, I change to below

>=[Financial Summary]![Year From] And <=[Financial Summary]![Year To]

When I run the query, I only get June and July 2022 as a result

 

[Gismo]

What data type is your Period data and where does [Financial Year] table come into things?

Edit - @CJ_London beat me to it

Period come from below

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

 

[CJ_London]

see post #4

 

[Gismo]

see post #4

This will be hard coding the date, I am selecting the current financial year , active period with a yes select

 

[CJ_London]

The point about post #4 is you have turned a number into a string - and strings sor differently to numbers/dates.
Strongly recommend you do no formatting until you have got the results you want and ideally in the form or report, not the query. Or if you want to format, format in a way that gives you the order you want. With text 'April' comes before 'January' whereas 'Mth4' with come after 'Mth1'.

And whilst you are at it, take a look at your period format. '02 2021' will come after '01 2022'

 

[Gismo]

The point about post #4 is you have turned a number into a string - and strings sor differently to numbers/dates.
Strongly recommend you do no formatting until you have got the results you want and ideally in the form or report, not the query. Or if you want to format, format in a way that gives you the order you want. With text 'April' comes before 'January' whereas 'Mth4' with come after 'Mth1'.

And whilst you are at it, take a look at your period format. '02 2021' will come after '01 2022'

Thank you for that info

Still I am a but lost as the what I should do to get the required result

 

[The_Doc_Man]

What you have done is take a (probably) usable date field and converted it to a display field that follows different rules.

You said you got "Jan 2021" by converting something. You should not have done the conversion. Instead you should have used the raw dates to determine your period using your "BETWEEN...AND" syntax. BUT once you have a query to find your desired period, there is nothing to stop you from repeating the date in another field with your FORMAT function to provide the displayable date as a separate field.

 

[Gismo]

What you have done is take a (probably) usable date field and converted it to a display field that follows different rules.

You said you got "Jan 2021" by converting something. You should not have done the conversion. Instead you should have used the raw dates to determine your period using your "BETWEEN...AND" syntax. BUT once you have a query to find your desired period, there is nothing to stop you from repeating the date in another field with your FORMAT function to provide the displayable date as a separate field.

Thank you very much