If Statement Alternatives

S

Sash

I'm creating a tab delimited file based on 3 different record sets. The NT
table contains notes and there may be 0 notes or 10 notes for each account.
I set DebtID earlier in the program based on my first record set and then
analyze the data accordingly. This works perfectly for what I'm trying to
do, but takes FOREVER to run because the NT table has 65000+ records, so each
time I look at an account it's looking at each record. I'm sure there's a
better way to do this....maybe set my record set where it's equal to DebtID?
As always, any assistance would be greatly appreciated!

Recordset Selection:
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)


If Statement:
If DebtID >= 100109 Then

rs1.MoveFirst

Do While Not rs1.EOF

If rs1.Fields("Debtor_ID") = DebtID Then
NT = "NT" & Chr(9) & rs1.Fields("NoteDate") & Chr(9) & rs1.Fields("NoteText")
Print #1, NT
rs1.MoveNext
Else
rs1.MoveNext
End If

Loop

End If
 
S

Sash

Sorry this is my recordset select statement:
strSQL3 = "SELECT * from DEBTADJ order by DEBT_ID"
 
S

Sash

Okay, so I tried the following, but keep getting "Too few parameters.
Expected 1" When I hover over the SQL, it looks like:

"SELECT * from DEBTADJ where [DEBTOR_ID] = 123"

DEBTOR_ID is a string

DebtID = rs.Fields("Debtor_ID")
strSQL3 = "SELECT * from DEBTADJ where [DEBTOR_ID] = " & DebtID & ""
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)
 
P

Paolo

Hi Sash,

"SELECT * from DEBTADJ where [DEBTOR_ID] = """ & DebtID & """"

HTH Paolo

Sash said:
Okay, so I tried the following, but keep getting "Too few parameters.
Expected 1" When I hover over the SQL, it looks like:

"SELECT * from DEBTADJ where [DEBTOR_ID] = 123"

DEBTOR_ID is a string

DebtID = rs.Fields("Debtor_ID")
strSQL3 = "SELECT * from DEBTADJ where [DEBTOR_ID] = " & DebtID & ""
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)



Sash said:
Sorry this is my recordset select statement:
strSQL3 = "SELECT * from DEBTADJ order by DEBT_ID"
Click to expand...
Click to expand...
 
S

Sash

Thank you, this works perfectly. Now what if there is nothing in the
recordset?

I want to say something like:

If Not IsNull(rs3) then
......

Paolo said:
Hi Sash,

"SELECT * from DEBTADJ where [DEBTOR_ID] = """ & DebtID & """"

HTH Paolo

Sash said:
Okay, so I tried the following, but keep getting "Too few parameters.
Expected 1" When I hover over the SQL, it looks like:

"SELECT * from DEBTADJ where [DEBTOR_ID] = 123"

DEBTOR_ID is a string

DebtID = rs.Fields("Debtor_ID")
strSQL3 = "SELECT * from DEBTADJ where [DEBTOR_ID] = " & DebtID & ""
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)



Sash said:
Sorry this is my recordset select statement:
strSQL3 = "SELECT * from DEBTADJ order by DEBT_ID"

:

I'm creating a tab delimited file based on 3 different record sets. The NT
table contains notes and there may be 0 notes or 10 notes for each account.
I set DebtID earlier in the program based on my first record set and then
analyze the data accordingly. This works perfectly for what I'm trying to
do, but takes FOREVER to run because the NT table has 65000+ records, so each
time I look at an account it's looking at each record. I'm sure there's a
better way to do this....maybe set my record set where it's equal to DebtID?
As always, any assistance would be greatly appreciated!

Recordset Selection:
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)


If Statement:
If DebtID >= 100109 Then

rs1.MoveFirst

Do While Not rs1.EOF

If rs1.Fields("Debtor_ID") = DebtID Then
NT = "NT" & Chr(9) & rs1.Fields("NoteDate") & Chr(9) & rs1.Fields("NoteText")
Print #1, NT
rs1.MoveNext
Else
rs1.MoveNext
End If

Loop

End If
Click to expand...
Click to expand...
Click to expand...
 
P

Paolo

if rs3.recordcount<>0 then
Msgbox "the recordset contains data"
else
Msgbox "the recordset is empty"
endif

HTH Paolo


Sash said:
Thank you, this works perfectly. Now what if there is nothing in the
recordset?

I want to say something like:

If Not IsNull(rs3) then
.....

Paolo said:
Hi Sash,

"SELECT * from DEBTADJ where [DEBTOR_ID] = """ & DebtID & """"

HTH Paolo

Sash said:
Okay, so I tried the following, but keep getting "Too few parameters.
Expected 1" When I hover over the SQL, it looks like:

"SELECT * from DEBTADJ where [DEBTOR_ID] = 123"

DEBTOR_ID is a string

DebtID = rs.Fields("Debtor_ID")
strSQL3 = "SELECT * from DEBTADJ where [DEBTOR_ID] = " & DebtID & ""
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)



:

Sorry this is my recordset select statement:
strSQL3 = "SELECT * from DEBTADJ order by DEBT_ID"

:

I'm creating a tab delimited file based on 3 different record sets. The NT
table contains notes and there may be 0 notes or 10 notes for each account.
I set DebtID earlier in the program based on my first record set and then
analyze the data accordingly. This works perfectly for what I'm trying to
do, but takes FOREVER to run because the NT table has 65000+ records, so each
time I look at an account it's looking at each record. I'm sure there's a
better way to do this....maybe set my record set where it's equal to DebtID?
As always, any assistance would be greatly appreciated!

Recordset Selection:
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)


If Statement:
If DebtID >= 100109 Then

rs1.MoveFirst

Do While Not rs1.EOF

If rs1.Fields("Debtor_ID") = DebtID Then
NT = "NT" & Chr(9) & rs1.Fields("NoteDate") & Chr(9) & rs1.Fields("NoteText")
Print #1, NT
rs1.MoveNext
Else
rs1.MoveNext
End If

Loop

End If
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If rs3.recordcount > 0 then
...

Or

If rs.eof and rs.bof = false then
....

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sash said:
Thank you, this works perfectly. Now what if there is nothing in the
recordset?

I want to say something like:

If Not IsNull(rs3) then
.....

Paolo said:
Hi Sash,

"SELECT * from DEBTADJ where [DEBTOR_ID] = """ & DebtID & """"

HTH Paolo

Sash said:
Okay, so I tried the following, but keep getting "Too few parameters.
Expected 1" When I hover over the SQL, it looks like:

"SELECT * from DEBTADJ where [DEBTOR_ID] = 123"

DEBTOR_ID is a string

DebtID = rs.Fields("Debtor_ID")
strSQL3 = "SELECT * from DEBTADJ where [DEBTOR_ID] = " & DebtID & ""
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)



:

Sorry this is my recordset select statement:
strSQL3 = "SELECT * from DEBTADJ order by DEBT_ID"

:

I'm creating a tab delimited file based on 3 different record sets.
The NT
table contains notes and there may be 0 notes or 10 notes for each
account.
I set DebtID earlier in the program based on my first record set
and then
analyze the data accordingly. This works perfectly for what I'm
trying to
do, but takes FOREVER to run because the NT table has 65000+
records, so each
time I look at an account it's looking at each record. I'm sure
there's a
better way to do this....maybe set my record set where it's equal
to DebtID?
As always, any assistance would be greatly appreciated!

Recordset Selection:
Set rs3 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)


If Statement:
If DebtID >= 100109 Then

rs1.MoveFirst

Do While Not rs1.EOF

If rs1.Fields("Debtor_ID") = DebtID Then
NT = "NT" & Chr(9) & rs1.Fields("NoteDate") & Chr(9) &
rs1.Fields("NoteText")
Print #1, NT
rs1.MoveNext
Else
rs1.MoveNext
End If

Loop

End If
Click to expand...
Click to expand...
Click to expand...
 

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

stuck in a loop 5
next without for error - stumped 3
Split not working correctly 2
Timer event does not fire 1
SQL Select in VBA to Open Recordset 2
Recordset from a recordset 3
Run-time error 3021 6
Object required error... 7

Top