Valuation of database records: MoSCoW

[alsemgeest]

Hi,

I'd like to make a valuation of database records. Suppose you have a
table:

Name Must have
Address Should have
Zip Should have
SocialSecNr Must have
City Should have
State Should have
Salary Must have
Carbrand Would have
Kids_YN Would have
Holiday_dest Would have

Now I want to give a number to every record, that gives me an idea how
far the data is supplied. The musthave-fields give me (say) 7 points,
shouldhave-fields 4 points, and wouldhave-fields 1 point.

So the following records gives me (maximum 40 points):

Name Mark Johnson --> 7 points
Address 123 Broadway --> 4 points
Zip
SocialSecNr
City NYC --> 4 points
State
Salary
Carbrand
Kids_YN YES --> 1 points
Holiday_dest Norway --> 1 points

17 points for this record.

This way, I can valuate the way our datacollection program is making
progress.

Does anyone has an idea how to implement this?

Thanks for any suggestions.

[Holtz]

Create a form with a button and a textbox (TbxScore)
in the on click even of the button:

Dim Dbs as Database
Dim Rst as Recordset
Dim Qry as String

Set Dbs = "c:\whatever.mdb"
Qry = "SELECT * FROM Tbl"
Set Rst = dbs.opendatabase(Qry, dbopendynaset)
If not IsNull(Rst) then
.MoveFirst
While Not Rst.eof
If IsNull(!Name) Then
TbxScore.Value = 0
Else
TbxScore.value = 7
End If
If not IsNull(!Address) Then
TbxScore.value = TbxScore.value + 7
End if
etc.....
Do something with your calculated value and reset
tbxScore.value = 0
.MoveNext
Wend
.Close
End If

I deliberately mixed up the use of the IF statement so that you can
decide what you want to do. Also you need to do something with this
value you are calculating, otherwise it will just display in your form.

This is just a guideline and I have not checked anything.

[alsemgeest]

Is this something that could be done in a query? Like in Oracle
select nvl(address,0,7) + nvl(name,0,7)
from mytable;
??

regards paul

[rkc]

al********@it4us.nl wrote:

Is this something that could be done in a query? Like in Oracle
select nvl(address,0,7) + nvl(name,0,7)
from mytable;
??

Lookup "Nz Function" in help while in the VBA IDE.

[alsemgeest]

Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.

[Trevor Best]

al********@it4us.nl wrote:

Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.

When using IIf, Isnull returns a boolean result so you wouldn't need the
"=True" bit in there, so:

IIf(IsNull([tblTest].[Address]),0,4)

will work and shave a few nanoseconds off each row.

--
[OO=00=OO]

[Justin Hoffman]

<al********@it4us.nl> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.

Depending on how you have your fields set up, you may have strings of
zero-length or containing only spaces, so you could have:
IIF(Len(Trim(Nz(MyField)))=0,0,4)