query into vba without loads and loads of queries......

[swarv]

Hi all,

I have the following problem which is driving me nuts and was wondering if anybody can point me in the right direction - please bear with me its quite a long one.

I have the following query (a20091)

SELECT holidaybookings.id, Count(holidaybookings.id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = forms!holidaymainbasic!child3!id AND holidaybookings.yearfld = 2009
GROUP BY holidaybookings.id;

this works fine and displays 2 columns, one with the ID (say 2) and one with countall number (say 8).

Now I also have this query (previous2009):

SELECT (entitlement.entitlementfld - a20091.Countall) AS daysremaining
FROM entitlement INNER JOIN a20091 ON entitlement.id =a20091.id
WHERE entitlement.id = forms!holidaymainbasic!child3!id AND entitlement.yearfld = 2009;

This displays 1 coumn with one number and works fine, in this case the number being 17.


Now I would like to place the above into vba.
I have this in vba:

If Year(Now) = "2009" Then
previous = DLookup("daysremaining", "previous2008")
End If

I would like the program to find out which year we are in (year(now)) and then get the days remaining the from the previous year.
In this case year being 2009 and then the program runs the query previous2008 and grabs the figure from that query and adds it to string previous.

Now I would like the pc to know which year it is and then pick the correct query to run for the previous year, i.e. if it 2009 then run previous 2008, if 2010 then run previous2009.
Or can this be done automatically in vba without me making loads and loads of queries?

I hope this makes sense and any questions please ask, this is driving me nuts.

Thanks

Martin

 

[jjturner]

Firstly, I would say you'll want to read up on Parameter Queries, which will save you from having to create a saved query for each year

Secondly, it looks like you're mixing your data types, coercing Numeric values into Strings.

But since the Year function returns a Variant, you should be ok, just something to be aware of...

Further to my point, you mention that the DLookup "adds it to string previous". Again, not sure what you mean by that or what your intentions are...

To your immediate problem, I might do something like this:

dim varYearPrevious As Variant

varYearPrevious = Year(Now()) - 1
previous = DLookup("daysremaining", "previous" & varYearPrevious)

But that solution aside, you'll definitely want to get out of the business of creating queries for each year.

HTH,
John

 

[swarv]

Hi,

thanks for your reply.

I have added that in but it still looks like I will need a query for previous2008, previous2009, previous2010 etc...

Is there anyway to get round this?

Thanks

Martin

 

[jjturner]

Hi Martin,

Have you considered using a parameter query?

I only mention that since it appears you're already using form control references in your WHERE clause, so I would think you could follow suit and account for the year in this manner as well...

Or maybe your question was how to do this in code?

HTH,
John

 

[swarv]

The question was 'Can this be done in code?'

Would it save on using loads of queries?

i.e. is it possible to put both queries into a few lines of vba code and still return the same figure into a variable?

Thanks

Martin

 

[jjturner]

Hi Martin,

I'm sorry, but I'm getting confused about what you're trying to do.

So, is the 2nd query ("previous2009") only meant to compare the previous year with the current year? In other words, if the 1st query ("a20091") is for 2009, is "previous2009" supposed to look at 2008?

OR:
Is the 2nd query supposed to pull everything prior to 2009 (or whatever the current year is)?

Because I would think you simply need to modify your WHERE clause in each query. Instead of having [yearfld] = 2009, you would just change it to:

[yearfld] = Year(Now())

in your first query, and:

[yearfld] = Year(Now())-1

or

[yearfld] < Year(Now())

in your second query, as the case may be.

Maybe if you posted an example of what you want your results to look like, it will help clarify it since I don't see a need for you to use code based on what I understand of your issue...

HTH,
John

 

[swarv]

Hi john,

cheers for the help so far - I'll try and explain.

a20091 returns to values when run, the ID of the user in question and a countall number.
The countall number is the amount of days holiday the person has taken so far.

previous2009 looks at 20091 and gets the countall number then it takes that away from the holiday that that person is allowed and returns a number in the daysremaining field.

When the person opens up the add holiday form and selects a date the vba code will check to see if the date is within the first 3 months on the year,

If Format(usersenddate, "YY/MM/DD") >= Format(DateSerial(Year(Now), 1, 1), "YY/MM/DD") And Format(usersenddate, "YY/MM/DD") < Format(DateSerial(Year(Now), 4, 1), "YY/MM/DD") Then

and if it is then I would like it to workout how many days you have left from the previous year and then ask the user if he would like his holiday request to be taken from last year allowance. The user can only carry over a maximum of 5 days and only use them upto end of march. Then it will start using the allowance from the current year.

Is this possible?

I hope this makes sense.

Thanks

Martin

 

[jjturner]

Oy vay! Well, this will be old hat for most of the vets here (and they'll be able to streamline all this significantly, I'm sure), but I can use some practice - so for demonstration's sake I'll try to build you a parameter query in code. In fact, this is kind of taking the scenic route approach...

As I originally suggested to you, the whole concept here is to explicitly use a Parameter query.

First the preliminaries:
1. Replace the " AND holidaybookings.yearfld = 2009" from the WHERE clause of the "a20091" query with:
" AND holidaybookings.yearfld = HolidayYear"
2. Replace the " AND entitlement.yearfld = 2009" from the WHERE clause of the "previous2009" query with:
" AND entitlement.yearfld = EntitlementYear"

Next, in the AfterUpdate event of the control holding the date selected by user (usersenddate):

Dim dteDateSelected As Date
Dim booCarryOverDate As Boolean
Dim qdf(1) As QueryDef
Dim rs As Recordset
Dim intDaysRemaining As Integer

dteDateSelected = CDate(Me!usersenddate) 'however you would reference that ctl
booCarryOverDate = (Format(dteDateSelected, "YY/MM/DD") >= _
                  & Format(DateSerial(Year(Now), 1, 1), "YY/MM/DD") And _
                  & Format(dteDateSelected, "YY/MM/DD") < _
                  & Format(DateSerial(Year(Now), 4, 1), "YY/MM/DD"))

set qdf(0) = CurrentDb.QueryDefs("a20091")
set qdf(1) = CurrentDb.QueryDefs("previous2009")

'Note:  the SQL for the following 2 lines of code you could've already saved
'       in your queries and omitted from this procedure
qdf(0).SQL = "PARAMETERS HolidayYear Long;" & qdf(0).SQL
qdf(1).SQL = "PARAMETERS EntitlementYear Long;" & qdf(1).SQL

If booCarryOverDate Then 'get entitlements from prior year
    qdf(0)!HolidayYear = Year(dteDateSelected) - 1
    qdf(1)!EntitlementYear = Year(dteDateSelected) - 1
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'account for an excess of 5 days:
    If intDaysRemaining > 5 Then
        intDaysRemaining = 5
    End If
    If MsgBox ("You are entitled to use up to " & intDaysRemaining _
        & "from prior year" & Chr(10) & Chr(10) _
        & "Do you wish to use them?", vbYesNo) = vbYes Then
        'run your code to apply the prior year entitlements
    End If
Else 'simply determine entitlements for current year
    qdf(0)!HolidayYear = Year(dteDateSelected)
    qdf(1)!EntitlementYear = Year(dteDateSelected)
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'run your code to inform user of days remaining, etc., etc.,
End If

*** NOTE: this is UNTESTED air-code so please take appropriate precautions ***

Well, hopefully that should get you started... time for me to hit the hay!

HTH,
John

 

[swarv]

Hi John,

Thaks for the reply - looks great,

Just one thing - I am getting an error here:

Private Sub txtEndDate_AfterUpdate()
Dim dteDateSelected As Date
Dim booCarryOverDate As Boolean
Dim qdf(1) As QueryDef
Dim rs As Recordset
Dim intDaysRemaining As Integer
dteDateSelected = CDate(Me!txtEndDate) 'however you would reference that ctl
booCarryOverDate = (Format(dteDateSelected, "YY/MM/DD") >= Format(DateSerial(Year(Now), 1, 1), "YY/MM/DD") And _
                   Format(dteDateSelected, "YY/MM/DD") < Format(DateSerial(Year(Now), 4, 1), "YY/MM/DD"))
Set qdf(0) = CurrentDb.QueryDefs("a20091")
Set qdf(1) = CurrentDb.QueryDefs("previous2009")
'Note:  the SQL for the following 2 lines of code you could've already saved
'       in your queries and omitted from this procedure
'qdf(0).SQL = "PARAMETERS HolidayYear Long;" & qdf(0).SQL
'qdf(1).SQL = "PARAMETERS EntitlementYear Long;" & qdf(1).SQL
If booCarryOverDate Then 'get entitlements from prior year
    qdf(0)!HolidayYear = Year(dteDateSelected) - 1
    qdf(1)!EntitlementYear = Year(dteDateSelected) - 1
    [COLOR=red]Set rs = qdf(1).OpenRecordset()
[/COLOR]    intDaysRemaining = rs(0)
    rs.Close
    'account for an excess of 5 days:
    If intDaysRemaining > 5 Then
        intDaysRemaining = 5
    End If
    If MsgBox("You are entitled to use up to " & intDaysRemaining _
        & "from prior year" & Chr(10) & Chr(10) _
        & "Do you wish to use them?", vbYesNo) = vbYes Then
        'run your code to apply the prior year entitlements
    End If
Else 'simply determine entitlements for current year
    qdf(0)!HolidayYear = Year(dteDateSelected)
    qdf(1)!EntitlementYear = Year(dteDateSelected)
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'run your code to inform user of days remaining, etc., etc.,
End If
End Sub

It errors and says too few parameters - expected 3.

Any ideas?

Thanks

Martin

 

[jjturner]

As expected, there were bound to be some glitches and syntax errors.
Try changing the PARAMETERS declaration to "INTEGER" data type (the fact that you had to comment them out is a tip-off) --- and also put a space after the semicolon:

qdf(0).SQL = "PARAMETERS HolidayYear INTEGER; " & qdf(0).SQL
qdf(1).SQL = "PARAMETERS EntitlementYear INTEGER; " & qdf(1).SQL

And going forward, as I alluded to in the code comments, you can simply save these PARAMETERS declaration strings directly in your queries and remove them from the VBA.

Let me know how that works out...

Cheers,
John

 

[swarv]

Hi John,

I get the run error 3129 Invalid SQL statement on those lines - i think that is why I commented them out.

At the top of the queries I have:

PARAMETERS EntitlementYear Long;

Should I have this in both the queries and vba code? if so any idea on the error?

Thanks

Martin

 

[jjturner]

As I said, change the word "Long" to "Integer"

If you have these lines saved in your queries, then you can comment them out in the code.

You might still have issues after that since you have a form reference in your criteria, but see how it goes with the changes I'm suggesting

 

[swarv]

I tried that and commented them out of the vba code, now getting the expected 4 error on the original error line.

Any ideas?

cheers for all this.

 

[jjturner]

Hi Martin,

Well, I take that back about using "Integer" (that's wrong) - - you should definitely be able to use "Long" (that's legit)

Just to be clear, you should have "HolidayYear" as the parameter in the "a20091" query and "EntitlementYear" as the parameter in the "previous2009" query.

Next we'll need to take care of the form references, which I don't have time to spell out at the moment, but essentially we'll be replacing those with legit parameters. If you want to have a crack at it yourself in the meantime go ahead, but I won't be able to catch up until later on tonight...

Cheers for now,
John

 

[swarv]

John,

This is my code so far with a couple more parameters in code:

Dim dteDateSelected As Date
Dim booCarryOverDate As Boolean
Dim qdf(1) As QueryDef
'Dim qdf(0) As QueryDef
Dim rs As Recordset
Dim intDaysRemaining As Integer
Dim userstotalid As Integer
dteDateSelected = CDate(Me!txtEndDate) 'however you would reference that ctl
booCarryOverDate = (Format(dteDateSelected, "YY/MM/DD") >= Format(DateSerial(Year(Now), 1, 1), "YY/MM/DD") And _
                   Format(dteDateSelected, "YY/MM/DD") < Format(DateSerial(Year(Now), 4, 1), "YY/MM/DD"))
Set qdf(0) = CurrentDb.QueryDefs("a20091")
Set qdf(1) = CurrentDb.QueryDefs("previous2009")
Forms!holidaymainbasic!Child3!ID.SetFocus
userstotalid = Forms!holidaymainbasic!Child3!ID.Text
'Note:  the SQL for the following 2 lines of code you could've already saved
'       in your queries and omitted from this procedure
'qdf(0).SQL = "PARAMETERS HolidayYear Integer; " & qdf(0).SQL
'qdf(1).SQL = "PARAMETERS EntitlementYear Integer; " & qdf(1).SQL
If booCarryOverDate Then 'get entitlements from prior year
    qdf(0)![HolidayYear] = Year(dteDateSelected) - 1
    qdf(1)![usersid1] = userstotalid
    qdf(1)![EntitlementYear] = Year(dteDateSelected) - 1
    qdf(0)![usersid] = userstotalid
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'account for an excess of 5 days:
    If intDaysRemaining > 5 Then
        intDaysRemaining = 5
    End If
    If MsgBox("You are entitled to use up to " & intDaysRemaining _
        & "from prior year" & Chr(10) & Chr(10) _
        & "Do you wish to use them?", vbYesNo) = vbYes Then
        'run your code to apply the prior year entitlements
    End If
Else 'simply determine entitlements for current year
    qdf(0)![HolidayYear] = Year(dteDateSelected) - 1
    qdf(1)![usersid1] = userstotalid
    qdf(1)![EntitlementYear] = Year(dteDateSelected) - 1
    qdf(0)![usersid] = userstotalid
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'run your code to inform user of days remaining, etc., etc.,
End If

This still erros and says too few parameters

previous 2009 query:

PARAMETERS EntitlementYear Long;
SELECT (entitlement.entitlementfld - a20091.Countall) AS daysremaining
FROM entitlement INNER JOIN a20091 ON entitlement.id =a20091.id
WHERE entitlement.id = usersid1 AND entitlement.yearfld = EntitlementYear;

a20091 query:
PARAMETERS HolidayYear Long;
SELECT holidaybookings.id, Count(holidaybookings.id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = usersid AND holidaybookings.yearfld = HolidayYear
GROUP BY holidaybookings.id;


Any ideas on this as its driving me nuts.

Thanks

Martin

 

[jjturner]

Hi Martin,

You're on the right track.

For "a20091" your PARAMETERS declaration should then be:

PARAMETERS HolidayYear Long, usersid Long;

And for "previous2009":

PARAMETERS EntitlementYear Long, usersid1 Long;

Now in your VBA just an FYI - if you need to capture the value of a control you can do so without using SetFocus and .Text property.
Use the .Value property (and remove the SetFocus statement):

userstotalid = Forms!holidaymainbasic!Child3!ID.Value

And a final note: the dimensioning of the qdf variable is for an array. Arrays can hold multiple values. That's why you see it followed by parentheses. By default, arrays are zero-based, so Dim qry(1) As QueryDef is actually prepped to hold 2 values: qdf(0) and qdf(1)

Hopefully you should have things working once you get these remaining bits sorted.

HTH,
John

 

[swarv]

Hi John,

I have changed all the above and it is still erroring saying it has too few parameters - expected 4.

When you run the query on its own it asks for holidayyear, entitlement year, usersid and usersid1 and surely in the vba code it should place these values in automatically?

Thanks

Martin

 

[jjturner]

Hi Martin,

The problem may be stacked queries both using parameters, in which case you may want to refer to this post, or you can try using Eval() around your parameter value assignments...

This is getting tricky, though, I have to admit. Maybe someone will jump in here to save the day

Also for good measure, you may want to encase the parameters in your WHERE clause with brackets [], although I don't see why this would be necessary.

HTH,
John

 

[swarv]

Hi John,

I haven't finished with this yet and this is just a quick post to show you how im getting on. I think it is working how I want it to now, below are the relavant codes - what do you think? Like I said its not finished and there are probably a few errors but i'll check through it again tomorrow.
Let me know what you think.

main vba code:

Dim todaysdate As Date
Dim todaysyear As Integer
Dim usersstartdate As Date
Dim usersenddate As Date
usersstartdate = txt_date
usersenddate = txtEndDate
Dim yesitis As String
Dim previousyear As String
Dim remainingdays2009 As String
Dim variable1 As String
Dim previousremaining As String
Dim previous As String
Dim strsql
Dim dteDateSelected As Date
Dim booCarryOverDate As Boolean
Dim qdf(2) As QueryDef
'Dim qdf(0) As QueryDef
Dim rs As Recordset
Dim intDaysRemaining As Integer
Dim userstotalid As Integer
On Error Resume Next
Dim newamount As Integer
Dim enddate As Date
Dim thismany As Integer
dteDateSelected = CDate(Me!txtEndDate) 'however you would reference that ctl
booCarryOverDate = (Format(dteDateSelected, "YY/MM/DD") >= Format(DateSerial(Year(Now), 1, 1), "YY/MM/DD") And _
                   Format(dteDateSelected, "YY/MM/DD") < Format(DateSerial(Year(Now), 4, 1), "YY/MM/DD"))
Set qdf(0) = CurrentDb.QueryDefs("a20091")
Set qdf(1) = CurrentDb.QueryDefs("previous2009")
Set qdf(2) = CurrentDb.QueryDefs("currentyear")
userstotalid = Forms!holidaymainbasic!Child3!ID.Value
'Note:  the SQL for the following 2 lines of code could've been already saved in queries and omitted from this procedure
'qdf(0).SQL = "PARAMETERS HolidayYear Integer; " & qdf(0).SQL
'qdf(1).SQL = "PARAMETERS EntitlementYear Integer; " & qdf(1).SQL
dtmDateLower = Me.txt_date
dtmDateUpper = Me.txtEndDate
enddate = Me.txtEndDate
If booCarryOverDate Then 'get entitlements from prior year
    'qdf(0)![HolidayYear] = Year(dtmDateUpper) - 1
    'qdf(1)![usersid1] = userstotalid
    'qdf(1)![EntitlementYear] = Year(dtmDateUpper) - 1
    'qdf(0)![usersid] = userstotalid
    Set rs = qdf(1).OpenRecordset()
    intDaysRemaining = rs(0)
    rs.Close
    'account for an excess of 5 days:
        If days > 5 Then
            If intDaysRemaining > days Then
                If MsgBox("You can carry over 5 days, shall we use these 5 up from last year and the rest from this year?", vbYesNo) = vbYes Then
                intDaysRemaining = 5
                newamount = days - intDaysRemaining
                MsgBox "you have taken " & "intdaysremaining " & " from last year and " & newamount & " from this year"
                days = 0
                End If
            'intDaysRemaining = 5
            Else
            End If
                If MsgBox("You are entitled to use up to " & intDaysRemaining _
                & "from prior year, you are requesting " & days & " & Chr(10) & Chr(10)" _
                & "Do you wish to use them?", vbYesNo) = vbYes Then
                'run your code to apply the prior year entitlements
                End If
        End If
Else 'simply determine entitlements for current year
    'qdf(0)![HolidayYear] = Year(dteDateSelected) - 1
    'qdf(1)![usersid1] = userstotalid
    'qdf(1)![EntitlementYear] = Year(dteDateSelected) - 1
    'qdf(0)![usersid] = userstotalid
    Set rs = qdf(2).OpenRecordset()
    intDaysRemaining = rs(0)
    thismany = Forms!frm_holiday!Text29
    MsgBox ("you have " & intDaysRemaining & " days remaining to use and are requsting " & days & " days")
            If intDaysRemaining < days Then
            MsgBox "You cant have this many days off. You have requested " & Format(TotalBusinessDays, "###,###,###") & " and you can only have " & Format(intDaysRemaining, "###,###,###") & " days off.", "Vertex Law"
            DoCmd.Hourglass (0)
            DoCmd.Close acForm, "frm_holiday"
            Else '<< user pressed canel
            End If
    rs.Close
    'run your code to inform user of days remaining, etc., etc.,
End If

a20091:

SELECT holidaybookings.id, Count(holidaybookings.id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = usersid3() AND holidaybookings.yearfld = GetDateUpper2()
GROUP BY holidaybookings.id;

previous2009:

SELECT (entitlement.entitlementfld - a20091.Countall) AS daysremaining
FROM entitlement INNER JOIN a20091 ON entitlement.id =a20091.id
WHERE entitlement.id = usersid2() AND entitlement.yearfld = GetDateUpper();

publicmod module:

Option Compare Database
'Declare paublic variables as application level
'So that they can be accessed throughout the life of the session
Public dtmDateLower As Date
Public dtmDateUpper As Date
Public lngClientID As Long
Public b As Integer
Public a As Integer
'Public usersid2 As Integer
Public strInvoiceNo As String
Public dblAmtLower As Currency
Public dblAmtUpper As Currency

Public Function GetDateLower() As Date
'read the value in the public variabe
'if no date found provide a date in the past
    GetDateLower = IIf(IsEmpty(dtmDateLower), #1/1/2000#, dtmDateLower)
End Function
 
Public Function GetDateUpper() As Integer
'read the value in the public variable
'if no date found use todays date
    'GetDateUpper = IIf(IsEmpty(dtmDateUpper), Date, dtmDateUpper)
a = Year(dtmDateUpper) - 1
GetDateUpper = a '- 1 'Year(dtmDateUpper) - 1
End Function

Public Function GetDateUpper2() As Integer
'read the value in the public variable
'if no date found use todays date
    'GetDateUpper2 = IIf(IsEmpty(dtmDateUpper), Date, dtmDateUpper)
'GetDateUpper2 = Year(dtmDateUpper) - 1
b = Year(dtmDateUpper) - 1
GetDateUpper2 = b 'Year(dtmDateUpper) - 1
End Function
    
Public Function GetClientId() As Long
    GetClientId = lngClientID
End Function

Public Function usersid2() As Integer
usersid2 = Forms!holidaymainbasic!Child3!ID
End Function

Public Function usersid3() As Integer
usersid3 = Forms!holidaymainbasic!Child3!ID
End Function

Public Function currentyeara() As Integer
'read the value in the public variable
'if no date found use todays date
    'GetDateUpper = IIf(IsEmpty(dtmDateUpper), Date, dtmDateUpper)
a = Year(dtmDateUpper) '- 1
currentyeara = a '- 1 'Year(dtmDateUpper) - 1
End Function

Will let you know how i get on

cheers

 

[jjturner]

Hi Martin,

That's a lot of work you put into it. I'm sure you've been learning a boat load. My hats off to you! I actually didn't have a chance to read through the document I linked you to or test out the demo, but I'm guessing you've modeled your solution on David's public variable strategy.

Well, if this gets things working for you, you have a major accomplishment on your hands

I must confess, as helpful and expedient as the public variable strategy is, I'm still not happy about getting thwarted by the QueryDef parameters. So, whenever we run into a particularly intractable problem, it's a good time to take a step back and see if we can solve the thing in pieces.

And the first thing I should mention regarding perspective is that we've basically been using a sledgehammer to swat a gnat (opportunities to brush up on VBA notwithstanding).

Fact 1: "a20091" returns 1 record which has 2 values - ID, and Countall. As long as we're handling the user action in your form events, there is no need to carry ID and Countall into "previous2009" via an INNER JOIN with "a20091". These values can simply be stored in good ol' VBA variables.

Fact 2: userid2 and userid3 are completely redundant because they reference the exact same form control - Forms!holidaymainbasic!Child3!ID. Thus you can completely eliminate one of these functions.

Hypothesis 1: Once the queries are "disassociated" from each other, our original strategy to use SQL PARAMETERS via VBA QueryDefs should work ---> although I say this with a grain of salt; as we're painfully aware, bridging the gap between SQL and VBA is often fraught with peril and unpredictable results!

Alternative 1: Really your 2 queries are domain aggregate functions, so "a20091" would translate to this:

Dim intCountall As Integer
Dim intUserID As Integer

intUserID = Forms!holidaymainbasic!Child3!ID.Value
intCountall = DCount("*", "holidaybookings", "[ID] = " & intUserID & _
                     " AND [yearfld] = " & intMyYearVariable)

And for "previous2009", we'll have (continuing on from above):

Dim intEntitlement As Integer
Dim intDaysRemaining As Integer

intEntitlement = DLookup("[entitlementfld]", "entitlement", "[ID] = " _
                     & intUserID & " AND [yearfld] = " & intMyYearVariable)
intDaysRemaining = intEntitlement - intCountall

So with this alternative, we avoid explicit QueryDefs, Recordsets, and Public variables altogether! What could be simpler!!!
(Although it would be nice to see those darn QueryDef Parameters work )

HTH,
John