query looping

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 06 Jan 2006 00:01 GMT

Hi,
I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query searches for any months that a commission payment
was not received for a particular Loan number. In this case Loan number 1.

SELECT tblDate.MonthYear
FROM tblDate
WHERE
MonthYear < Now()
AND Format ([MonthYear],"mmmm,yyyy")
Not In (
SELECT Format ([PaymentDate],"mmmm,yyyy")
FROM tblCommission
WHERE LoanNo =1);

I want to be able to loop through the Loan numbers running this query on
each number.
I understand loops and could probably write this in C++ or Java but I'm
not big on VBA.
Any assistance greatly appreciated.

Bob

Reply to this Message

Bob - 06 Jan 2006 07:06 GMT

> Hi,
> I have a small database in Access 2002 which records commission payments
[quoted text clipped - 19 lines]
>
> Bob

I think I should be using something like this but I'm only guessing
Dim myvalue As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryMissingCommission")
Set rst = qdf.OpenRecordset

rst.MoveFirst
Do While rst.EOF = False
myvalue = rst.Fields("LoanNo").Value
MsgBox myvalue
rst.MoveNext
Loop
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

When I run it from a command button on a form I get an error stating
that : Dim dbs As DAO.Database "User-defined type not defined"

Any Ideas ?

Reply to this Message

Glenn Siswick - 06 Jan 2006 11:17 GMT

Hi Bob

You may need to add a reference (Tools, References in the VBA editor) to
Microsoft DAO 3.6 Object Library.

Regards
Glenn

> > Hi,
> > I have a small database in Access 2002 which records commission payments
[quoted text clipped - 47 lines]
>
> Any Ideas ?

Reply to this Message

Bob - 06 Jan 2006 21:09 GMT

> Hi Bob
>
[quoted text clipped - 55 lines]
>>
>>Any Ideas ?

Thankyou, that solved that problem
I still need more help to get this going so I have stated a new thread
where I'll try to explain it better.

Reply to this Message