error 3078 / to few parameters, expected 2

[JITA]

I am using ACCESS 2007.
I have a table containing items with a start date and an end date.
In a query I try to calculate the number of months that the items from the table are within the period that is requested by the query.
Example: when I run my query it will pop up two dialog boxes, one asking for [Yr] and one for [Mth]. So when [Yr] = 2011 and [Mth]= 5 I trie to find from each item in the table how many months fall between Jan 1, 2011 and May 31, 2011.

Since the calculation of the number of months that fall within the required period takes several IFF statements, I tried to create a VBA function. After several hours of inquiry on the internet my table, query and function look like:

The table:
ID 'unique identifier
Anr 'code
Bdate 'begin date
Edate 'end date

The query (called qryB)
SELECT tblB.ID, tblB.Anr, tblB.BDate, tblB.EDate, NrMth("qryB",[BDate],[EDate],[Yr],[Mth]) AS NrMonths
FROM tblB
ORDER BY tblB.Anr;

The function
Public Function NrMth(qryName As String, BDate As Date, EDate As Date, Yr, Mth) As Long
Dim db As Dao.Database, rst As Dao.Recordset
Dim qdf As Dao.QueryDef
Dim prm As Dao.Parameter
Dim BDateB As Date, EDateB As Date

Set db = CurrentDb
Set qdf = db.QueryDefs(qryName)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = db.OpenRecordset(dbOpenDynaset)

BDateB = DateSerial(Yr, Mth, 1)
Select Case Mth
Case 4, 6, 9, 11
EDateB = DateSerial(Yr, Mth, 30)
Case 2
EDateB = DateSerial(Yr, Mth, 28)
Case Else
EDateB = DateSerial(Yr, Mth, 31)
End Select


' loop through each record occurs here.
Do Until rst.BOF
Select Case BDate
Case BDate < BDateB
If EDate < BDateB Then
NrMth = 0
Else
If Month(EDate) <= Month(BDateB) Then
NrMth = Month(EDate)
Else
NrMth = Month(BDateB)
End If
End If
Case Else
NrMth = 11 ' Just for testing purposes. If this works I will go on with more IF statements
End Select
rst.MovePrevious
Loop

Set rst = Nothing
Set db = Nothing
Set qdf = Nothing
End Function

At the moment my function is not complete. I just try to test the working of the principle. I get several error messages with this function.
First there is a problem with "Set rst = db.OpenRecordset(dbOpenDynaset)". I get the ERROR 3078 (it can't find table or query "2"). When I change this Set rst to "Set rst = db.OpenRecordset(qryName, dbOpenDynaset)" it returns the "too few parameters Expected 2" error

Second there is a problem with the variables [Yr] and [Mth]. When looping through the parameters the function does not seem to recognize these. When going over the Eval(prm.Name) with the mouse pointer I can see the value is "[Yr]" (including the "") or "[Mth]".

I wonder what I do wrong

 

[PHV]

Have you tried this ?
Set rst = [!]qdf.[/!].OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Wouldn't DateDiff and testing for >0 accomplish the same thing ?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JITA]

@ PHV replacing Set rst = db.OpenRecordset(qryName, dbOpenDynaset)by Set rst = qdf.OpenRecordset(qryName, dbOpenDynaset) or Set rst = qdf.OpenRecordset gives the same error messages as mentioned in my question.

@ Mazework Datdiff would probably also be usefull, thanks.

 

[JITA]

@ Mazework Datdiff (my reply was not finished). I still want to use the function. So I would like to know what is wrong with my function.

 

[JITA]

I have foud the cause of the problem. I have found that I was also using the [Yr] and [Mth] variables in an other calculated field. Apparently this can' t be done in combination with a function that is expecting the same variables. When I removed this calculated field the function at least gave results.
I have now foud some errors in the function so I am now improving the function.

 

[MazeWorX]

What i meant was this

SELECT tblB.ID, tblB.Anr, tblB.BDate, tblB.EDate, DateDiff("m",[BDate],[EDate]) AS CountMonths
FROM tblB
WHERE (((tblB.BDate) Between #1/1/2011# And #5/1/2012#)) OR (((tblB.EDate) Between #1/1/2011# And #5/1/2012#));

will return for each record in the date range the number of months. You would just have to sum them if your looking for a total.

thoughts

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JITA]

Hi Mazework.
I am familiar with the BETWEEN statement.
The reason for trying to create a function is that my "calculations" are more complicated then just BETWEEN. I will provide some examples.
Item 1 with starting date of 2-1-2010 and end date of 1-31-2011
Item 2 with starting date of 7-1-2010 and end date of 6-30-2011
Item 3 with starting date of 1-1-2011 and end date of 12-31-2011

My search period always starts with 1-1 of the year I want to search for. It ends with the month I enter as criterium. So when I give Yr 2011 and Mth 5 I want to know how many month each item overlaps with my search between 1-1-2011 and 5-31-2011.
In this example Item 1 one month (just January), and both item 2 and 3 5 months.
If I enter Yr 2011 and Mth 8, I search between 1-1-2011 and 8-31-2011 so item one will give 1 as result, item 2 will give 6 and iten 3 8.
Both my search period and the item period will not exceed one year.

So you can see I need more IF statements to get what I want. reason for my Function.
Talking about my function although it worked yesterday, I don not completely understand why. I have skipped the Set rst = db.OpenRecordset(qryName, dbOpenDynaset). I get the results that I expect. But why is the Set rst = db. etc not necessary?

 

[JonFer]

I would use DateSerial to generate the start date and end date of your desired range. Then use DateDiff as MazeWorX recommended. The CountMonths calculation uses IIf to get the lesser of EDate and the desired range end date.

SELECT tblB.ID, tblB.Anr, tblB.BDate, tblB.EDate,
DateDiff("m",DateSerial([Yr],1,1),IIf([EDate]>DateSerial([Yr],[Month]+1,0),DateSerial([Yr],[Month]+1,0),[EDate])) AS CountMonths
FROM tblB
WHERE tblB.BDate < DateSerial([Yr],[Month]+1,0) And
tblB.EDate > DateSerial([Yr],1,1) ;