Need help with FindFirst statement

[BobV]

Group:

I am trying to write a subroutine that will find the first record where the
[Date Sold] field has a date entered in it. After finding the record, I want
to move to that record. Eventually, I want to write code to loop through
each record that has a date entered in the [Date Sold] field and make
changes to those records. But I can't get the code to find the first field
where a date is entered.

What am I doing wrong? Any suggestions will be greatly appreciated.

Thanks,
BobV

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.GoToRecord , , acFirst
Set db = CurrentDb
Set rst = db.OpenRecordset("Query1-" & CompanyName)
rst.FindFirst "[Date Sold]>0"

 

[Ken Dales]

-----Original Message-----
Group:

I am trying to write a subroutine that will find the first record where the
[Date Sold] field has a date entered in it. After finding the record, I want
to move to that record. Eventually, I want to write code to loop through
each record that has a date entered in the [Date Sold] field and make
changes to those records. But I can't get the code to find the first field
where a date is entered.

What am I doing wrong? Any suggestions will be greatly appreciated.

Thanks,
BobV

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.GoToRecord , , acFirst
Set db = CurrentDb
Set rst = db.OpenRecordset("Query1-" & CompanyName)
rst.FindFirst "[Date Sold]>0"


.

Depending on your underlying DB properties, I suspect you
may need to try "Not([Date Sold] Is Null)" instead of the

0 you are using.

However, I would approach it a different way: I would
filter the recordset with the criteria Not([Date Sold] Is
Null) and go to the first record in the filtered
recordset; all you then have to do is step through your
records.

 

[John Vinson]

Group:

I am trying to write a subroutine that will find the first record where the
[Date Sold] field has a date entered in it. After finding the record, I want
to move to that record. Eventually, I want to write code to loop through
each record that has a date entered in the [Date Sold] field and make
changes to those records. But I can't get the code to find the first field
where a date is entered.

Ummm...

You're doing this the hard way.

Why not just use an Update query with a criterion of IS NOT NULL on
the [Date Sold] field, and another criterion on the company name? It
appears that you have separate stored queries for each company - not
needed, just use the company name as a criterion. No code needed at
all, and you can run all your updates in one swell foop.

What am I doing wrong? Any suggestions will be greatly appreciated.

Thanks,
BobV

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.GoToRecord , , acFirst
Set db = CurrentDb
Set rst = db.OpenRecordset("Query1-" & CompanyName)
rst.FindFirst "[Date Sold]>0"

The reason this isn't working is that a NULL value of [Date Sold] will
fail ANY criteria - it's not > 0, it's not equal to zero, it's not
less than zero, it's not even unequal to 0. Try

rst.FindFirst "[Date Sold] IS NOT NULL"

if you insist on doing it this way!

 

[BobV]

Ken:

Thank you for your response.

I like your idea about first filtering the recordset. Can you point me in
the right direction as to how I filter the recordset? I'd appreciate any
help you can provide.

Thanks,
BobV

-----Original Message-----
Group:

I am trying to write a subroutine that will find the first record where the
[Date Sold] field has a date entered in it. After finding the record, I want
to move to that record. Eventually, I want to write code to loop through
each record that has a date entered in the [Date Sold] field and make
changes to those records. But I can't get the code to find the first field
where a date is entered.

What am I doing wrong? Any suggestions will be greatly appreciated.

Thanks,
BobV

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.GoToRecord , , acFirst
Set db = CurrentDb
Set rst = db.OpenRecordset("Query1-" & CompanyName)
rst.FindFirst "[Date Sold]>0"


.

Depending on your underlying DB properties, I suspect you
may need to try "Not([Date Sold] Is Null)" instead of the

0 you are using.

However, I would approach it a different way: I would
filter the recordset with the criteria Not([Date Sold] Is
Null) and go to the first record in the filtered
recordset; all you then have to do is step through your
records.

 

[Guest]

Hi Bob

I have written some code that loops through records and builds up a SQL string to append to a table. I struggled with recordsets and used DLookup to find the first record. It's a bit mickey mouse (as is most of my code), but I've copied it below incase you want to use it ...

iPatient = DLookup("PatientID", "tblPatient", _
"PatientID = (SELECT TOP 1 tblPatient.PatientID FROM tblPatient " _
& " ORDER BY tblPatient.PatientID ASC)")
iLastPatient = DLookup("PatientID", "tblPatient", _
"PatientID = (SELECT TOP 1 tblPatient.PatientID FROM tblPatient " _
& " ORDER BY tblPatient.PatientID DESC)")

StartOfLoop:
Do Until iPatient = iLastPatient + 1
'if no patient with that ID, skip record
If IsNull(DLookup("PatientID", "tblPatient", _
"PatientID = " & iPatient)) Then
iPatient = iPatient + 1
GoTo StartOfLoop
End If

'my sql string in here

iPatient = iPatient + 1

Loop

Hope it helps

Donna

 

[BobV]

Donna:

Thank you for your response to my question. Your sample code was a help in
fixing my code.

Thanks,
BobV

Hi Bob

I have written some code that loops through records and builds up a SQL

string to append to a table. I struggled with recordsets and used DLookup
to find the first record. It's a bit mickey mouse (as is most of my code),
but I've copied it below incase you want to use it ...