running a query in a loop

B

Bob

I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query (qryMissingCommission)(made with kind advice from
Duane Hookum) 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 wish to be able to run the query many times changing the LoanNo value
each time without user intervention.
I am assuming some form of do-while loop.
I guess the data returned from a query iteration would have to be
appended to the data from the previous iteration and stored somewhere (
maybe an array variable) so it could be used in a report.

I have begun with the following code suggested in an earlier post by
Allen Browne.

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


but I get an "Item not found" error on this line
myvalue = rst.Fields("LoanNo").Value

Any thoughts on how to solve this.

Thankyou
Bob Wickham
 
S

Steve Schapel

Bob,

I haven't thoroughly analysed the information you posted, so I am not
sure if this will completely solve the problem. But the code is
expecting a LoanNo field in the qryMissingCommission query, and such
does not exist in this query. In any case, what is the code meant to be
achieving, apart from showing a message box with the LoanNo which will be 1?

Maybe this is more along the lines you are thinking of...

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM WhateverTable")
Do Until rst.EOF
strSQL = "INSERT INTO YourTempTable ( blabla )" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing

And then, 'YourTempTable' is used as the basis of the report. The above
is incomplete, as I don't understand your data or your requirements or
your report, but hopefully it will help move you forward.
 
B

Bob

Steve said:
Bob,

I haven't thoroughly analysed the information you posted, so I am not
sure if this will completely solve the problem. But the code is
expecting a LoanNo field in the qryMissingCommission query, and such
does not exist in this query. In any case, what is the code meant to be
achieving, apart from showing a message box with the LoanNo which will
be 1?

Maybe this is more along the lines you are thinking of...

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM WhateverTable")
Do Until rst.EOF
strSQL = "INSERT INTO YourTempTable ( blabla )" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing

And then, 'YourTempTable' is used as the basis of the report. The above
is incomplete, as I don't understand your data or your requirements or
your report, but hopefully it will help move you forward.
Click to expand...
Hi Steve,
The VBA code I posted was a generic solution to a generic problem I
found in a newsgroup post.
My query:

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);

works very well.

I have been searching Google for a solution to the problem of getting
this query to work in a loop so that the query would run through a table
of LoanNo 's without user intervention. This is to check that commission
payments have been received for each Loan Number each month for the life
of the loan.

I'm hoping to show that I'm having a go at this and not just relying on
the kind efforts of people like yourself.

When I run your code:

Private Sub Command0_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM tblCommission")
Do Until rst.EOF
strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date)
" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

I get a Syntax error on:

strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date) " & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" &_
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"

No indication of which part is wrong. It just highlights the whole
statement.
All tables and fields exist.

My studies of MYSQL indicate that the keyword VALUES should be placed
after the INSERT INTO statement. Also should I place a ; at the end.
Any thoughts on these or any other reasons why I would get a syntax error.

My first major hassle with VBA in Excel years ago had something to do
the &_ at the end of each line. I'll see what happens if I write it all
in one line.

Apart from that the sql appears to be exactly what I'm looking for.

Thankyou for your assistance.
Very helpful.

Bob Wickham
 
S

Steve Schapel

Bob,

As regards your code...
strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date) " & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" &_
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
Click to expand...

.... I can only see one place where it will definitely choke, which is no
space in one of the &_ bits.

The word 'date' is a Reserved Word (i.e. has a special meaning) in
Access, and should not be used as the name of a field or control. If
you do, it should always be enclosed in []s in code. However, if this
causes an error, I doubt that Access would call it a syntax error.

Also I think there would normally be spaces in the INSERT INTO clause.

The keyword VALUES is not applicable in this case.

You can put a ; at the end if you like, but it is not necessary.

So, try it with these changes...
strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, [Date]
) " & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
 
B

Bob

Steve said:
Bob,

As regards your code...
strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date) " & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" &_
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
Click to expand...


... I can only see one place where it will definitely choke, which is no
space in one of the &_ bits.

The word 'date' is a Reserved Word (i.e. has a special meaning) in
Access, and should not be used as the name of a field or control. If
you do, it should always be enclosed in []s in code. However, if this
causes an error, I doubt that Access would call it a syntax error.

Also I think there would normally be spaces in the INSERT INTO clause.

The keyword VALUES is not applicable in this case.

You can put a ; at the end if you like, but it is not necessary.

So, try it with these changes...
strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, [Date] )
" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
Click to expand...
Hi Steve,

I tried as you suggested but still came up with a syntax error.
I changed the double quotes "" around mmmm,yyyy to single quotes '' as
it seemed to me that this was nested.

The following code now runs but I get a Run Time error 3075 "Missing
Operator in Query Expression"
with this line highlighted
dbs.Execute strSQL, dbFailOnError
I assume the actual error is on the line before but I can't see any
problem with it.
I've checked my tables and all names of tables and fields appear to be OK.
The error message indicates that it gets to the first LoanNo in
tblCommission but then can't go any further.
Any ideas ?
As a matter of interest, why do we need to include dbFailOnError?

Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM tblCommission")
Do Until rst.EOF

strSQL = "INSERT INTO tblMissingCommissionReport (LoanNumber, TheDate
)" & _
"SELECT tblDate.MonthYear" & _
"FROM tblDate" & _
"WHERE MonthYear < Now()" & _
"AND Format([MonthYear],'mmmm,yyyy')" & _
"Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
"FROM tblCommission" & _
"WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

Bob
 
S

Steve Schapel

Bob,

You have missed out the spaces at the beginning of every line in the SQL
statement.
 
D

Douglas J. Steele

Steve Schapel said:
Bob,

As regards your code...
strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date) " & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" &_
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
Click to expand...

... I can only see one place where it will definitely choke, which is no
space in one of the &_ bits.

The word 'date' is a Reserved Word (i.e. has a special meaning) in Access,
and should not be used as the name of a field or control. If you do, it
should always be enclosed in []s in code. However, if this causes an
error, I doubt that Access would call it a syntax error.

Also I think there would normally be spaces in the INSERT INTO clause.

The keyword VALUES is not applicable in this case.

You can put a ; at the end if you like, but it is not necessary.

So, try it with these changes...
strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, [Date] ) "
& _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format ([MonthYear],"mmmm,yyyy")" & _
" Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
Click to expand...

The INSERT INTO statement is trying to insert into 2 fields, but only one
value is being provided.
 
B

Bob

Steve said:
True. Thanks, Doug.
Click to expand...
Thankyou, Gentlemen

IT WORKS

I usually like to re-write code, rather than just copy and paste, but I
mistakenly left out the spaces.
I added the spaces and it "worked" but I got 38718 in the LoanNumber field.
I changed it to text and got 1/01/2006.
I changed the INSERT statement to just one field

Still some tidying up to do like I DO want it to record both the Loan
Number and the Date (my attempts at this so far have resulted in a
Cartesian Product), and I'll need it to delete (and then re-create) the
temporary table before adding data to it.
But you have been a terrific help.
Thankyou again.

Bob Wickham
 
S

Steve Schapel

Bob,

There are a couple of ways this could be done. I think possibly the
simplest, though not necessarily the most efficient, is to use a second
recordset. Something like this...

Private Sub Command1_Click()
Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")
Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format
([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" &
rstLoans!LoanNo & ")"
Do Until rstMissed.EOF
strSQL = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
rstMissed!MonthYear & " )"
dbs.Execute strSQL, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing
End Sub
 
S

Steve Schapel

Bob,
I changed the INSERT statement to just one field
Click to expand...

Yes, the code I gave you was on the mistaken assumption of just one
field being processed. The need to insert more than one field makes it
more complicated, see my other post.
 
B

Bob

Steve said:
Bob,

There are a couple of ways this could be done. I think possibly the
simplest, though not necessarily the most efficient, is to use a second
recordset. Something like this...

Private Sub Command1_Click()
Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")
Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format
([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" &
rstLoans!LoanNo & ")"
Do Until rstMissed.EOF
strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber,
TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
rstMissed!MonthYear & " )"
dbs.Execute strSQL, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing
End Sub
Click to expand...
Hello again,

I pasted your code into the On Click Event Procedure of another command
button.
Clicking that button results in a Compile Error: Syntax Error
and the following code is highlighted

Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format
([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" &
rstLoans!LoanNo & ")"

I'll persevere with this to solve it but I'm at a loss to know which way
to go as the syntax looks OK to me.
Its essentially the same as the code that DOES work other than we are
now declaring 2 recordsets.

Bob
 
S

Steve Schapel

Apologies, Bob. I don't know if this is the only problem, but I omitted
the ) to close the OpenRecordset function. Try like this...

Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")
 
B

Bob

Steve said:
Apologies, Bob. I don't know if this is the only problem, but I omitted
the ) to close the OpenRecordset function. Try like this...

Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")
Click to expand...
Thanks, Steve.
That got it running.
I've studied C++, Java and SQL and its frightening how many times I've
been caught out by a missing , ; ( ) etc.

Even though its running, I'm getting some strange results.

The following code:

Do Until rstMissed.EOF
strSQL = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
rstMissed!MonthYear & " )"
dbs.Execute strSQL, dbFailOnError
rstMissed.MoveNext
Loop

inserts the value of rstMissed!MonthYear into the TheDate field of the
tblMissingCommissionReport.
and rstMissed gets its data from tblDate.

The strange thing is the value that is inserted for all Loan Numbers is
December, 1899.
I'm not sure where its picking this date from as the dates in the
tblDate table start at January, 2005.

I've gone as far as checking the computer bios for the setup date but
everything looks OK.
The problem, though, does seem strangley familiar.

Any ideas.

Bob
 
S

Steve Schapel

Bob,

What is the Data Type of the MonthYear field in the tblDate table? And
what is the Data Type of the TheDate field in the
tblMissingCommissionReport table? Your results would indicate that the
data being inserted is a 1 or 0 or other small value. Hmmm, maybe we
need to delimit them as dates! Try this...

" VALUES ( " & rstLoans!LoanNo & ", #" & rstMissed!MonthYear & "# )"
 
B

Bob

Hi Steve,

The new code with the values delimited as dates as follows


Private Sub Command2_Click()
Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")
Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Do Until rstMissed.EOF
strSQL = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", #" &
rstMissed!MonthYear & "# )"
dbs.Execute strSQL, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing
End Sub


returns

LoanNumber TheDate
1 January, 2005
1 January, 2005
1 January, 2006
2 January, 2005
2 January, 2005
2 January, 2006

At least we're in the 21st century !!!

What is the Data Type of the MonthYear field in the tblDate table?
Click to expand...

Date/Time Format mmmm"," yyyy

And
what is the Data Type of the TheDate field in the
tblMissingCommissionReport table?
Click to expand...

Date/Time Format mmmm"," yyyy


The data type of tblCommission.Paymentdate is Date/Time Format Short date

I have a very small amount of data in these tables during this testing
phase. Just 2 Loan numbers with 2 months of missing commission payments
for each Loan number.

The results I get when I run a hard coded query is as follows.

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);

returns

July, 2005
August, 2005
January, 2006

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

returns

September, 2005
October, 2005
January, 2006



The following code from earlier in our correspondence returns the
correct results but of course just 1 field.

Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM tblCommission")
Do Until rst.EOF

strSQL = "INSERT INTO tblMissingCommissionReport (TheDate)" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

returns

July, 2005
August, 2005
January, 2006
September, 2005
October, 2005
January, 2006


In all cases, I dont wish to have the current month included but I think
that is a limitation of the WHERE MonthYear < Now() function and is
something I can battle with later.

Bob
 
S

Steve Schapel

Bob,

Where do you live? Do you use a non-American date format? What is the
actual data in the MonthYear field... 1st of the month?

--
Steve Schapel, Microsoft Access MVP
Hi Steve,

The new code with the values delimited as dates as follows


Private Sub Command2_Click()
Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")
Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Do Until rstMissed.EOF
strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber,
TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", #" &
rstMissed!MonthYear & "# )"
dbs.Execute strSQL, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing
End Sub


returns

LoanNumber TheDate
1 January, 2005
1 January, 2005
1 January, 2006
2 January, 2005
2 January, 2005
2 January, 2006

At least we're in the 21st century !!!

What is the Data Type of the MonthYear field in the tblDate table?
Click to expand...


Date/Time Format mmmm"," yyyy

And
what is the Data Type of the TheDate field in the
tblMissingCommissionReport table?
Click to expand...


Date/Time Format mmmm"," yyyy


The data type of tblCommission.Paymentdate is Date/Time Format Short date

I have a very small amount of data in these tables during this testing
phase. Just 2 Loan numbers with 2 months of missing commission payments
for each Loan number.

The results I get when I run a hard coded query is as follows.

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);

returns

July, 2005
August, 2005
January, 2006

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

returns

September, 2005
October, 2005
January, 2006



The following code from earlier in our correspondence returns the
correct results but of course just 1 field.

Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM tblCommission")
Do Until rst.EOF

strSQL = "INSERT INTO tblMissingCommissionReport (TheDate)" & _
" SELECT tblDate.MonthYear" & _
" FROM tblDate" & _
" WHERE MonthYear < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rst!LoanNo & ")"
dbs.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

returns

July, 2005
August, 2005
January, 2006
September, 2005
October, 2005
January, 2006


In all cases, I dont wish to have the current month included but I think
that is a limitation of the WHERE MonthYear < Now() function and is
something I can battle with later.

Bob
Click to expand...
 
S

Steve Schapel

Bob,

Ok, we'll crack this eventually! Just for fun, to cater to the
possibility that we
are dealing with non-American dates, try it like this...

strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, TheDate
)" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
 
B

Bob

Steve said:
Bob,

Ok, we'll crack this eventually! Just for fun, to cater to the
possibility that we
are dealing with non-American dates, try it like this...

strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, TheDate
)" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
Click to expand...
Hi Steve,
Thats it. Fixed.

Where do you live? Tasmania, Australia (which may account for the time
lapses in our correspondence)

Do you use a non-American date format? Yes, dd/mm/yy

What is the actual data in the MonthYear field... 1st of the month?

I used Excel to produce dates from January, 2005 to December, 2030 in a
Custom format of mmmm, yyyy.
I then used the Get External Data feature of Access to import this data
into a table with a Date/Time data type and mmmm"," yyyy format
but if this format is changed to Short Date the data changes to 01/01/05
and the first of every month after that.

Thankyou for all your help with this. It'll do wonders for my credibility.

One last bit of advice if you have the time.
Am I correct in saying that CLng converts my date to a Long which is
essentially a number representing the days elapsed since January 1, 1900
and then the format of my table converts it back to a date?

Thankyou, again.

regards,

Bob Wickham
 
S

Steve Schapel

Bob,
Thats it. Fixed.
Click to expand...

Yay!! A victory for the power of perseverance!
Where do you live? Tasmania, Australia (which may account for the time
lapses in our correspondence)
Click to expand...

I'm in New Zealand.
Do you use a non-American date format? Yes, dd/mm/yy
Click to expand...

Fair enough.
What is the actual data in the MonthYear field... 1st of the month?
Click to expand...

Thought so.
I used Excel to produce dates from January, 2005 to December, 2030 in a
Custom format of mmmm, yyyy.
I then used the Get External Data feature of Access to import this data
into a table with a Date/Time data type and mmmm"," yyyy format
but if this format is changed to Short Date the data changes to 01/01/05
and the first of every month after that.
Click to expand...

It will help you to be very clear that the format of a date field only
affects the way in which it is displayed, it has no impact at all on the
value stored in the table, which is ultimately what we are working with.
Thankyou for all your help with this. It'll do wonders for my credibility.
:)

One last bit of advice if you have the time.
Am I correct in saying that CLng converts my date to a Long which is
essentially a number representing the days elapsed since January 1, 1900
and then the format of my table converts it back to a date?
Click to expand...

Well, it's actually the number of days since 31 Dec 1899, but apart from
that you'r pretty right. VBA requires dates to be in US format, if they
are expressed as dates, so that's the other way we could have gone, i.e.
to explicitly force the date into mm/dd/yy format. But the method I
suggested just lets it use the actual numerical value of the date data,
which must always be the same regardless of format.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query looping 3
data types 2
recordsets ??? 2
Send an Query output with a Email using "sendObject" function 1
A Loop in a Loop 3
Exporting multiple Excel spreadhsheets using Excel templates 7
No Current Record 8
VBA Code Help 3

Top