By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Certain records get skipped?!

P: 65
Hi

I wrote this function:

Expand|Select|Wrap|Line Numbers
  1. Function UpdateLongfunctieFEVFVC()
  2.     Set db = CurrentDb
  3.     Dim percfev As Double
  4.     Dim percfvc As Double
  5.     Set rst = db.OpenRecordset("SELECT eadnr, datum, fev1, fvc, lengte FROM Longfunctie;")
  6.  
  7.     Do Until rst.EOF
  8.         percfvc = 0
  9.         percfev = 0
  10.         If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
  11.             Set rst2 = db.OpenRecordset("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).Value & ";")
  12.             If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
  13.                 If (rst2.Fields(0).Value = "m") Then
  14.                     percfev = rst.Fields(2).Value * 1000 / Exp(-2.86521 * Log(10) + 2.87294 * Log(rst.Fields(4)))
  15.                 Else
  16.                     percfev = rst.Fields(2).Value * 1000 / Exp(-2.60565 * Log(10) + 2.74136 * Log(rst.Fields(4)))
  17.                 End If
  18.                 'Debug.Print percfev & " " & rst2.Fields(0).Value
  19.             End If
  20.  
  21.             If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
  22.                 If (rst2.Fields(0).Value = "m") Then
  23.                     percfvc = rst.Fields(3).Value * 1000 / Exp(-2.9236 * Log(10) + 2.936 * Log(rst.Fields(4)))
  24.                 Else
  25.                     percfvc = rst.Fields(3).Value * 1000 / Exp(-2.704 * Log(10) + 2.8181 * Log(rst.Fields(4)))
  26.                 End If
  27.             End If
  28.             db.Execute ("UPDATE Longfunctie SET [%fev1] ='" & percfev * 100 & "', [%FVC] ='" & percfvc * 100 & "' WHERE eadnr = " & rst.Fields(0).Value & " AND Datum = #" & rst.Fields(1).Value & "#;")
  29.  
  30.         End If
  31.  
  32.     rst.MoveNext
  33.     Loop
  34.     Debug.Print rst.RecordCount
  35.     rst.Close
  36.     rst2.Close
  37.  
  38.  
  39.     Set rst = Nothing
  40.     Set rst2 = Nothing
  41.     Set db = Nothing
  42.  
  43. 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
Sep 21 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,307
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.
Sep 21 '07 #2

NeoPa
Expert Mod 15k+
P: 31,307
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)".
Sep 21 '07 #3

P: 65
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.

Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("SELECT eadnr, datum, fev1, fvc, lengte FROM Longfunctie;") 
  2. Do Until rst.EOF
  3.    percfvc = 0
  4.    percfev = 0
  5.    If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
  6.        Set rst2 = db.OpenRecordset("SELECT DISTINCT sex FROM patient WHERE eadnr = " & rst.Fields(0).Value & ";")
  7.        If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
  8.            If (rst2.Fields(0).Value = "m") Then
  9.                percfev = some formule
  10.            Else
  11.                percfev = some formule
  12.            End If
  13.         End If
  14.  
  15.         If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
  16.             If (rst2.Fields(0).Value = "m") Then
  17.                percfvc = some formule
  18.             Else
  19.                percfvc = some formule
  20.             End If
  21.         End If
  22.         db.Execute ("UPDATE Longfunctie SET [%fev1] ='" & percfev * 100 & "', [%FVC] ='" & percfvc * 100 & "' WHERE eadnr = " & rst.Fields(0).Value & " AND Datum = #" & rst.Fields(1).Value & "#;")
  23.     End If
  24.  
  25. rst.MoveNext
  26. 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
Sep 21 '07 #4

NeoPa
Expert Mod 15k+
P: 31,307
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.
Sep 21 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
In line with NeoPa’s suggestion in Post # 3, in the code posted in Post # 4, try replacing Line # 5

Expand|Select|Wrap|Line Numbers
  1. If (rst.Fields(4).Value <> Null Or rst.Fields(4).Value <> 0 Or Nz(rst.Fields(4).Value)) Then
  2.  
with

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(rst.Fields(4).Value) Then
and Line # 7

Expand|Select|Wrap|Line Numbers
  1.  If (rst.Fields(2).Value <> Null Or rst.Fields(2).Value <> 0 Or Nz(rst.Fields(2).Value)) Then
  2.  
with

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(rst.Fields(2).Value) Then
  2.  
and Line # 15

Expand|Select|Wrap|Line Numbers
  1.  If (rst.Fields(3).Value <> Null Or rst.Fields(3).Value <> 0 Or Nz(rst.Fields(3).Value)) Then
  2.  
with

Expand|Select|Wrap|Line Numbers
  1.  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)>
Sep 22 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.