problem with crosstab query showing sales between financial years

[sdel_nevo]

Hi Guys



I have this query that returns all income and expenditure each month

TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
SELECT Format([EntryDate],"mmm/yyyy") AS [Month]
FROM tblAccountTransactions
WHERE (((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.EntryTitle) Not Like "setup")) OR (((tblAccountTransactions.TotalPayment)>0) AND ((Format([EntryDate],"yyyy/mmm")) Between DateSerial(Year(Now()),4,1) And DateSerial(Year(Now()),3,31)+1))
GROUP BY Format([EntryDate],"yyyy/mm"), Format([EntryDate],"mmm/yyyy")
ORDER BY Format([EntryDate],"yyyy/mm")
PIVOT tblAccountTransactions.TransType;

I have tried to filter the results to only show values between the 1st April of this year to the 31st March next year 01/04/2018 - 31/03/2019

i have entered a test payment in October of next year 2019 and its showing in the query results highlighted Red

what will i need to do to olny return values between the dates i require

many thanks for your help

steve

Month	Money In	Money Out
Apr/2018	£1,010.00	£50.00
May/2018	£1,010.00	£50.00
Jun/2018	£1,010.00	£100.00
Jul/2018	£1,010.00	£50.00
Aug/2018	£1,010.00	£50.00
Sep/2018	£1,010.00	£50.00
Oct/2018	£1,010.00	£50.00
Nov/2018	£1,010.00	£50.00
Dec/2018	£1,010.00	£50.00
Jan/2019	£1,010.00	£50.00
Feb/2019	£1,010.00	£50.00
Mar/2019	£1,010.00	£50.00
Oct/2019		£5,000.00

[ranman256]

make a form, put in 2 text boxes, txtStartDate, txtEndDate.
make the base query Q1, to pull data in this range,
select * from table where [date] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

then make your Crosstab query off the Q1, but you MUST add PARAMETERS in the query (query design, menu ,parameters)
forms!myForm!txtStartDate, date
forms!myForm!txtEndDate, date

crosstab queries always need parameter settings.

[sdel_nevo]

Hi Ranman256

many thanks will give that a go

steve

[isladogs]

'...then make your Crosstab query off the Q1, but you MUST add PARAMETERS in the query (query design, menu ,parameters)
forms!myForm!txtStartDate, date
forms!myForm!txtEndDate, date

crosstab queries always need parameter settings.

Not true.
Crosstab queries will run perfectly well without parameters
If you need parameters for a particular query, then add them ...but not otherwise

In the particular case described above, I very much doubt they will be needed

[sdel_nevo]

Hi Colin

Thanks for the reply

I am having problems only showing the values between the financial dates even when creating a select query as sugested

I'm wondering if its a formatting issue with the dates

as the DateSerial(Year(Now()),4,1) part of the code users the month then day (4,1) but i'm not sure if this is indeed the case

if I would not have to create a select query to use as the data for this crosstab query am I on the right track with the criteria Between DateSerial(Year(Now()),4,1) And DateSerial(Year(Now()),3,31)+"1"

many thanks

steve

[isladogs]

I am having problems only showing the values between the financial dates even when creating a select query as sugested
I'm wondering if its a formatting issue with the dates
as the DateSerial(Year(Now()),4,1) part of the code users the month then day (4,1) but i'm not sure if this is indeed the case
if I would not have to create a select query to use as the data for this crosstab query am I on the right track with the criteria Between DateSerial(Year(Now()),4,1) And DateSerial(Year(Now()),3,31)+"1"

The date criteria makes no sense.
Try typing the following into the VBE Immediate window
?DateSerial(Year(Now()),4,1)
This gives 01/04/2018 in UK date format dd/mm/yyyy (04/01/2018 using US date format)

?DateSerial(Year(Now()),3,31) gives 31/03/2018 (or 03/31/2018)
?DateSerial(Year(Now()),3,31) +1 also gives 01/04/2018 (or 04/01/2018) - the "" aren't needed

Which means you have a very convoluted way of getting Between 01/04/2018 And 01/04/2018

If you type the full string into the immediate window:
?Between DateSerial(Year(Now()),4,1) And DateSerial(Year(Now()),3,31)+1
You will get 43191 which is how the date 01/04/2018 is actually stored - as a long integer

Convert back to a 'standard date' using CDate (43191) = 01/04/2018

Sort out what your date criteria really should be - test it in the select query - then make a crosstab.
OR omit the select query and use a WHERE clause to filter the date range in the crosstab
Both methods are equally good - choose whichever you prefer

[sdel_nevo]

Hi Colin

many thanks for the pointer, I will set to work on that now.

many many thanks

Steve

[sdel_nevo]

I Colin

many many thanks for your help

I have changed the crosstab to this

TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
SELECT Format([EntryDate],"mmm") AS [Month]
FROM tblAccountTransactions
WHERE (((tblAccountTransactions.EntryDate) Between DateSerial(Year(Now()),4,1) And DateSerial(Year(Date())+1,3,31)) AND ((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.TotalPayment)>0))
GROUP BY Format([EntryDate],"yyyy/mm"), Format([EntryDate],"mmm")
ORDER BY Format([EntryDate],"yyyy/mm")
PIVOT tblAccountTransactions.TransType;

all appears to be ok now

Steve

[isladogs]

Date range is now ....Between 01/04/2018 And 31/03/2019

Not sure I understand why you have 2 different date Group By expressions - - the first yyyy/mm should do the job on its own