Certain records get skipped?!

[Twanne]

Hi

I wrote this function:

Function UpdateLongfunctieFEVFVC()
    Set db = CurrentDb
    Dim percfev As Double
    Dim percfvc As Double
    Set rst = db.OpenRecordset("SELECT eadnr, datum, fev1, fvc, lengte FROM Longfunctie;")
 
    Do Until rst.EOF
        percfvc = 0
        percfev = 0
        If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
            Set rst2 = db.OpenRecordset("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).Value & ";")
            If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
                If (rst2.Fields(0).Value = "m") Then
                    percfev = rst.Fields(2).Value * 1000 / Exp(-2.86521 * Log(10) + 2.87294 * Log(rst.Fields(4)))
                Else
                    percfev = rst.Fields(2).Value * 1000 / Exp(-2.60565 * Log(10) + 2.74136 * Log(rst.Fields(4)))
                End If
                'Debug.Print percfev & " " & rst2.Fields(0).Value
            End If
 
            If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
                If (rst2.Fields(0).Value = "m") Then
                    percfvc = rst.Fields(3).Value * 1000 / Exp(-2.9236 * Log(10) + 2.936 * Log(rst.Fields(4)))
                Else
                    percfvc = rst.Fields(3).Value * 1000 / Exp(-2.704 * Log(10) + 2.8181 * Log(rst.Fields(4)))
                End If
            End If
            db.Execute ("UPDATE Longfunctie SET [%fev1] ='" & percfev * 100 & "', [%FVC] ='" & percfvc * 100 & "' WHERE eadnr = " & rst.Fields(0).Value & " AND Datum = #" & rst.Fields(1).Value & "#;")
 
        End If
 
    rst.MoveNext
    Loop
    Debug.Print rst.RecordCount
    rst.Close
    rst2.Close
 
 
    Set rst = Nothing
    Set rst2 = Nothing
    Set db = Nothing
 
End Function

It works for most of the records in the table but for some reason it skips some records. I have no idea what causes this... The table I load into rst has 8975 records and the print of the recordcount gives the correct number of records.

Can someone check this?? I realy don't see any errors in the function...

Greetz
Twanne

Please is just a word, thanx is a gift

[NeoPa]

Twanne,

I'm afraid there are a number of problems with this request.

1. You are asking for work to be done for you rather than with help on a particular topic. You may get away with this on small snippets of code but this is more than that.
2. You haven't shown what you've done so far so we know this isn't some lazy schoolkid (or similar personality) just wanting someone to do their task for them.
3. We don't have access to your data.
4. As far as I can see you haven't stripped this down to leave only the relevant code that goes wrong (an important early step in any debugging situation).

We like to help, but we have to be careful of doing things for people, both for our and your own benefits.

MODERATOR.

[NeoPa]

I managed to have a quick look anyway, and it seems that you're trying to detect Nulls by comparing them rather than using the IsNull() function.
Check it out, but I don't think that will work correctly.
In SQL you can say "WHERE X Is Null" but in VBA it should be "If IsNull(X)".

[Twanne]

Hehe,

Ok, I know it is kind of lazy to let other people do my work :p. The problem is that I've been searching on this function for a couple of hours now.

Secondly, I'm not a school kid (just graduated :D) but i'm working on this project for a hospital where I did my internship.

Third, because I'm working in a hospital it ain't that easy to let data out (secrecy of data, you know ;))

And for the record I'll strip down my code to where i know the error is.

Set rst = db.OpenRecordset("SELECT eadnr, datum, fev1, fvc, lengte FROM Longfunctie;") 
Do Until rst.EOF
   percfvc = 0
   percfev = 0
   If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
       Set rst2 = db.OpenRecordset("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).Value & ";")
       If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
           If (rst2.Fields(0).Value = "m") Then
               percfev = some formule
           Else
               percfev = some formule
           End If
        End If
 
        If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
            If (rst2.Fields(0).Value = "m") Then
               percfvc = some formule
            Else
               percfvc = some formule
            End If
        End If
        db.Execute ("UPDATE Longfunctie SET [%fev1] ='" & percfev * 100 & "', [%FVC] ='" & percfvc * 100 & "' WHERE eadnr = " & rst.Fields(0).Value & " AND Datum = #" & rst.Fields(1).Value & "#;")
    End If
 
rst.MoveNext
Loop

Some explanation: I select all records from the table longfunction. With those values from those records I need to do some calculations. But they only have to happen when there are certain values that are known. For some reason it skips some records (where everything is filled in). Normally when rst.fields(4) isn't empty it should write at least zeros to the fields %fev1 and %fvc. There is where the problem occurs. Sometimes it writes the values sometimes it doesn't.

In simple terms, it looks like some records aren't filled in where they should be.

I hope this cleared something out ;). If this isn't clear now I'll have onther attemp to it when you need it.

Greetz
Twanne

Irritating a mod just for the pleasure of working

[NeoPa]

Twanne,

We're not expecting you to go without help, just to pull some of the weight (or really to let us know you have already).
It's always a good idea to start off by explaining what you've tried. This tells us immediately that you're not a lazy kid etc. I'm sure you're not, as you've now explained the lengths you've already gone to before posting here.
I'm guessing you're from the Netherlands (We have a number of Dutch mods here who seem to like stirring things up ;) - It must be a national character trait).
I can't help too much at a detailed level with your code I'm afraid, and don't worry too much about irritating the mod - it seems that it's a favourite pasttime for many people ;)

BTW Did you catch my last post (#3)? I thought that might be the answer to your problem.

[missinglinq]

In line with NeoPa’s suggestion in Post # 3, in the code posted in Post # 4, try replacing Line # 5

If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
 

with

If Not IsNull(rst.Fields(4).Value) Then

and Line # 7

 If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
 

with

If Not IsNull(rst.Fields(2).Value) Then
 

and Line # 15

 If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
 

with

 If Not IsNull(rst.Fields(3).Value) Then

See what that does for the "missing" data. Unlike NeoPa, I knew you weren't a student doing an assignment; no instructor in his/her right mind would give out an assignemnt that involved calculating the results from Pulmonary Function Testing! Those actually involved in the testing have a hard enough time figuring things out!

Good Luck!

Linq ;0)>