Calling a built in Function from a query

[Cleis]

Hi all!

I have an accounting application where I want to produce a a query that feeds a report where I can show total receipts by month comparing year and month this year versus last year. When I run the query, I want to break the date down to two different fields Year "2005" and "Jan". I will then sort these fields on my report by year and by month. To do this I need a function that will call from my query that will do this. So far, I've used the following within a form to do this but I can't seem to get it to work within the query. Please be kind as I'm not that strong in VBA!


Private Function FinalSave()
On Error GoTo FinalSave_Err

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.AMonth FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date())) ORDER BY tblCalandar.AEnd;"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic

' Debug.Print strSQL
' Debug.Print rs.RecordCount

Me!txtDetailPeriod = Left(rs!AMonth, 3)
Me!txtPeriodDetail = rs!AMonth
Me!txtPeriodHeader = rs!AMonth

rs.Close
Set rs = Nothing
conn.Close

End Function

Thanks again for any help!!

Regards!

Itch

 

[PHV]

get it to work within the query
Which query ?
Why not using the native standard SQL functions like Year and Month ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cleis]

I'm sorry, was not clear in my original post (too close to the trees to see the forest!) I have a query that has the user post date ([UserPost]). This has the date of the original date that the transaction is posted into my db. Because I'm not on a calandar, but fiscal calandar, I need to open another query to read the start and end dates of the fiscal candar:

strSQL = "SELECT TOP 1 tblCalandar.AMonth FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date())) ORDER BY tblCalandar.AEnd;"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic

To read the the appropriate month end dates. I want to assign this date into two fields, one being year the other being month so that I can group and sum on them.


Does this help?

 

[PHV]

Does this help?
No, as we know nothing about your query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cleis]

Okay . . . .

Here is the query:

SELECT tblReceiptsHeader.CheckAmount, tblReceiptsHeader.UserDate
FROM tblReceiptsHeader
GROUP BY tblReceiptsHeader.CheckAmount, tblReceiptsHeader.UserDate;


I want to take UserDate and call a function that will break it down by Accounting Year and Accounting Month.

To do this I need to open another query:

SELECT TOP 1 tblCalandar.AMonth FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date())) ORDER BY tblCalandar.AEnd;

This reads another table that has a static Accounting calandar.

I want to break the UserDate from my first query down to a year and and a month into two seperate fields, one holding the year and the other holding the month.

I have used the following code to do something similar on on a form:

Private Function FinalSave()
On Error GoTo FinalSave_Err

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.AMonth FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date())) ORDER BY tblCalandar.AEnd;"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic

' Debug.Print strSQL
' Debug.Print rs.RecordCount

Me!txtDetailPeriod = Left(rs!AMonth, 3)
Me!txtPeriodDetail = rs!AMonth
Me!txtPeriodHeader = rs!AMonth

rs.Close
Set rs = Nothing
conn.Close

End Function


I want ot re-arrange this code in such a way that I get two vaules (for my 1st query); one is the year the other is the month?


Have I explained my situation more clearly?


Many thanks!

Itch

 

[PHV]

What is the schema of tblCalandar ?
What do you want in your query ?
Sorry, the crystal bowl is in vacation today and my preffered mind-reader is gone away.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cleis]

LOL!! You got me here!!! I really am trying very hard to explain my situation! Here is the calandar table. I want the the "AYear" aka accounting calandar and Month to show in in my 1st query. I need to open this table and based on my userdate, read through this recordset to find the appropriate month that the record was posted. So for example, if the user date = 1/29/2005, then based on this, the year would equal 2005 and the month would equal February

CalandarIndex AYear Month AMonth ABeginning AEnd
1 2005 1 January-05 1/1/2005 1/28/2005
2 2005 2 February-05 1/29/2005 2/25/2005
3 2005 3 March-05 2/26/2005 3/25/2005
4 2005 4 April-05 3/26/2005 4/29/2005
5 2005 5 May-05 4/30/2005 5/27/2005
6 2005 6 June-05 5/28/2005 6/24/2005
7 2005 7 July-05 6/25/2005 7/29/2005
8 2005 8 August-05 7/30/2005 8/26/2005
9 2005 9 September-05 8/27/2005 9/30/2005
10 2005 10 October-05 10/1/2005 10/28/2005
11 2005 11 November-05 10/29/2005 11/23/2005
12 2005 12 December-05 11/24/2005 12/29/2005
45 2006 1 January-06 12/30/2005 1/27/2006
46 2006 2 February-06 1/28/2006 2/24/2006
47 2006 3 March-06 2/25/2006 3/31/2006
48 2006 4 April-06 4/1/2006 4/28/2006
49 2006 5 May-06 4/29/2006 5/26/2006
50 2006 6 June-06 5/27/2006 6/30/2006
51 2006 7 July-06 7/1/2006 7/28/2006
52 2006 8 August-06 7/29/2006 8/25/2006
53 2006 9 September-06 8/26/2006 9/29/2006
54 2006 10 October-06 9/30/2006 10/27/2006
55 2006 11 November-06 10/28/2006 11/22/2006
56 2006 12 December-06 11/23/2006 12/29/2006


Your patience is appreciated!!

 

[PHV]

Well, here we are ;-)
Something like this (SQL code) ?
SELECT H.CheckAmount, H.UserDate, C.AYear, C.AMonth
FROM tblReceiptsHeader AS H, tblCalandar AS C
WHERE H.UserDate Between C.ABeginning And C.AEnd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886