Certain records get skipped?!

[Twanne]

Hi

I wrote this function:

[CODE=vb]Function UpdateLongfunct ieFEVFVC()
Set db = CurrentDb
Dim percfev As Double
Dim percfvc As Double
Set rst = db.OpenRecordse t("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).V alue <> 0 Or Nz(rst.Fields(4 ).Value)) Then
Set rst2 = db.OpenRecordse t("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).V alue & ";")
If (rst.Fields(2). Value <> Null Or rst.Fields(2).V alue <> 0 Or Nz(rst.Fields(2 ).Value)) Then
If (rst2.Fields(0) .Value = "m") Then
percfev = rst.Fields(2).V alue * 1000 / Exp(-2.86521 * Log(10) + 2.87294 * Log(rst.Fields( 4)))
Else
percfev = rst.Fields(2).V alue * 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).V alue <> 0 Or Nz(rst.Fields(3 ).Value)) Then
If (rst2.Fields(0) .Value = "m") Then
percfvc = rst.Fields(3).V alue * 1000 / Exp(-2.9236 * Log(10) + 2.936 * Log(rst.Fields( 4)))
Else
percfvc = rst.Fields(3).V alue * 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).V alue & " AND Datum = #" & rst.Fields(1).V alue & "#;")

End If

rst.MoveNext
Loop
Debug.Print rst.RecordCount
rst.Close
rst2.Close


Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

End Function[/CODE]

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.

[CODE=vb]Set rst = db.OpenRecordse t("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).V alue <> 0 Or Nz(rst.Fields(4 ).Value)) Then
Set rst2 = db.OpenRecordse t("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).V alue & ";")
If (rst.Fields(2). Value <> Null Or rst.Fields(2).V alue <> 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).V alue <> 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).V alue & " AND Datum = #" & rst.Fields(1).V alue & "#;")
End If

rst.MoveNext
Loop[/CODE]

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

[CODE=vb]If (rst.Fields(4). Value <> Null Or rst.Fields(4).V alue <> 0 Or Nz(rst.Fields(4 ).Value)) Then
[/CODE] with

[CODE=vb]If Not IsNull(rst.Fiel ds(4).Value) Then[/CODE]

and Line # 7

[CODE=vb] If (rst.Fields(2). Value <> Null Or rst.Fields(2).V alue <> 0 Or Nz(rst.Fields(2 ).Value)) Then
[/CODE] with

[CODE=vb]If Not IsNull(rst.Fiel ds(2).Value) Then
[/CODE]
and Line # 15

[CODE=vb] If (rst.Fields(3). Value <> Null Or rst.Fields(3).V alue <> 0 Or Nz(rst.Fields(3 ).Value)) Then
[/CODE] with

[CODE=vb] If Not IsNull(rst.Fiel ds(3).Value) Then[/CODE]

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