Data across multiple financial years 1

[Zelandakh]

Been browsing this forum for the first time in months - you guys are getting a bit clever.

Here's one I can understand but can't get my head around the actual query.

Table:
Invoice number (text field, could have anything in it)
Year (2002, 2003 etc)
Period (from 1 to 12)
Invoice amount
(etc, there are loads more fields)

I want to start with a simple query that will sum the invoice amounts across periods.

Fields you enter (they are already on a form) are cblperiodfrom, cblperiodto, cblyearfrom, cblyearto and are all combo box lists (hence the name) that only show valid entries.

So asking for 8/2002 to 2/2003 should give 8, 9, 10, 11, 12, 1 and 2. My current query works in the same financial year, but not across years as I can't work it out.

I know it isn't difficult, just can't work the logic.

 

[GingerR]

please paste your current query/sql here.

thanks

 

[PauloRico]

Place a field in your query that is made up of the following-

Selection: Format([Year],"yyyy&quot & Format([Period],"00&quot

Place the criteria for this field as-

Between (forms!yourformname!cblyearfrom & forms!yourformname!cblperiodfrom) And (forms!yourformname!cblyearto & forms!yourformname!cblperiodto)

For this to work you must format your Period combo boxes to two digits, i.e- 01,02,03,04 e.t.c.

 

[Zelandakh]

SQL as follows:

SELECT
tblnominal.NLL_YEAR,
tblnominal.NLL_PERIOD,
tblnominal.NLL_AC_CODE,
tblnominal.NLL_LINE,
tblnominal.NLL_DATE,
tblnominal.NLL_SUPPLIER,
tblnominal.NLL_AC_AMOUNT,
FROM
tblnominal
WHERE
(((tblnominal.NLL_YEAR)>=[forms]![frmInput]![cblFromYear]
And (tblnominal.NLL_YEAR)<=[forms]![frmInput]![cblToYear])
AND ((tblnominal.NLL_PERIOD)>=[forms]![frmInput]![cblFromPeriod]
And (tblnominal.NLL_PERIOD)<=[forms]![frmInput]![cblToPeriod])
AND ((tblnominal.NLL_SUPPLIER)=[forms]![frmInput]![cblsupplier])
;

I can see its close to being right, but can't get my head around it. Period is from 01 to 12, year is from 1988 to 2003.

Also, want to show the detail on the output report if you tick the tick box called frmInput.bDetail

Somewhere I need to specify before the report opens that the detail.visible is whatever is in frmInput.bDetail. Can I do it in VB next to the docmd.openreport or do I put it somewhere in the report itself?

Thanks.

 

[GingerR]

did you copy this from your query? the parentheses seem incorrect. there are an unequal number of left and right parentheses.

question: does this come out correctly if you take out the supplier bit? is this written in a query grid, or just in sql in a report rowsource? if not in a query, try doing that in the query grid and see if it works.

also you can put BETWEEN instead of the >= and <=.

 

[Zelandakh]

the brackets may not be quite right as I pulled out a number of other fields to make things easier for TT / this question. Its directly in a query cos I am an Access 1.0 person who hasn't upgraded his skillset...

 

[GingerR]

try what paulo suggested in your query criteria instead of what you have.
take out everything else and see if it works.
the best is to strip it all down to the date thing, make sure it works first, then go from there.

 

[Zelandakh]

Tried what Paulo suggested. Worked first time! Very many thanks - I knew it was possible, just couldn't see the wood for the trees.

Any idea how I show or hide the detail section of the report based on a tick box on a report with a button called preview report next to it?

I know it is possible, just beyond my knowledge as to where to put it.