Creating a financial year query (1 Viewer)

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
I am trying to create a make table query that extracts information from another table based on its year and month.

The problem I am having occurs when it comes to selecting data across multiple years ie. April 2005 - March 2006. The table that contains the data has two fields that are year and month.

I have created a form to allow the user to enter the required years, but I am unsure how to get the query not to select duplicate months e.g. April 2005 and April 2006.:confused:

Any help would be appreciated.:)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:23
Joined
Sep 12, 2006
Messages
15,614
probably add another column to the query, that actually returns a date from the year and month you have =datevalue("01/" & month & "/" & year (for UK dates, swap the month and day for US dates.

then in the criteria you can just put between #01 Apr 2005# and #31 Mar 2006#, or construct the criteria from your form text boxes. Its better to let Access manage the dates itself, I think that to try and do it yourself.

Or you could have a formula that counts the number of months, and tests that value (ie year*12 + month). The important thing is you need another column for the compare operation.
 

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
When I enter the new column details do I place =datevalue in the field name of the column also what should I select for the total e.g. Group by.

Thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:23
Joined
Sep 12, 2006
Messages
15,614
yes = datevalue etc in the column

reminder the datavalue follows your regional settings ie
in UK date of 16/12/2006 is US date of 12/16/2006 in US

put the test in the criteria row.

I now assume you are doing a totals query, so put WHERE in the groupby row, so it does a test.

You can include or exclude the datevalue itself from the query results with the checkbox
 

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
When I check the show option it returns an error message but the error does not appear when it is not checked. Is there any way I can show the values so that I can be sure it worked correctly?
 

raskew

AWF VIP
Local time
Today, 10:23
Joined
Jun 2, 2001
Messages
2,734
Hi -

The DateValue() function, when provided with just a month and year, automatically assumes the 1st day of the month. It's not necessary to type it in. So, as an example from the debug (immediate) window:
Code:
x = datevalue("04/2005")
? "between #" & x & "# and #" & dateadd("yyyy", 1, x)-1 & "#"
between #4/1/05# and #3/31/06#

HTH - Bob
 

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
Currently the data in the tables only goes by month and year but there is no record that is specific to a day. I have got the query to display the date in the format "DD/MM/YY" but how when I put criteria in I still get values that are not within the specified range. How can I solve this?
 
Last edited:

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
I managed to get the query to work by typing in the criteria such as
Between #01/04/2005# And #01/03/2006#

But because I need it to select the values from the form I used the following code. But it seems to produce the wrong results.

Between [Forms]![frmSelect_Dates]![txtStart] And [Forms]![frmSelect_Dates]![txtEnd]

Is there anything I'm missing in this code?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:23
Joined
Sep 12, 2006
Messages
15,614
should work, but did you say your txtstart and txtend fields were not date fields - they need to be.
 

raskew

AWF VIP
Local time
Today, 10:23
Joined
Jun 2, 2001
Messages
2,734
Hi -

Since you're using only month and year (I'm assuming a field that contains 11/2006, etc..) perhaps named strMonthYear.

In your query, add a calculated field: MyDates: DateValue([strMonthYear]). This will return the 1st day of the month in each instance.

In the criteria cell, specify
Between [enter start date] And DateAdd("m",11,[enter start date]).

Click on Query/Parameters and fill the two cells with:
[enter start date] : Date/Time

Run it, and when prompted, enter the starting month, e.g. 04/2005. Should return the dates you're after. (Just tested on an existing table and it appears to work as advertised. There's no need for an ending month, provided your searching on a one-year period. The code will do it for you.)

HTH - Bob
 

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
That worked raskew and gemma. Thanks for your help. It works better than I could have hoped for.
 

M_Mike

Registered User.
Local time
Today, 15:23
Joined
Aug 10, 2005
Messages
31
Maybe that didn't sound right. It works better than how I orginally thought it would work when I was just starting to design the database.

Once again thanks for your help.:)
 

jai kushwaha

Registered User.
Local time
Today, 20:53
Joined
Nov 1, 2015
Messages
61
Hello friends, how are you?? I need a help I am making a database and have a column named date. Now using this column i want to make a querry which ask me for starting financial year and and when i type 2015 it will give me all relevant info between 1/4/15 to 31/3/16.

I need help.. ...
 

Users who are viewing this thread

Top Bottom