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. 6 1385
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.md b"
Qry = "SELECT * FROM Tbl"
Set Rst = dbs.opendatabas e(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.
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 al********@it4u s.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.
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. al********@it4u s.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]
<al********@it4 us.nl> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bridget Willey |
last post by:
I am using ACT 6 and am trying to "split" the database between records
for customers and junk records. The accounts designated as "customers"
have that word in the ID field, and I am using that field as a lookup
to separate these records from the rest of the database, which have
nothing in the ID field.
When I try to use the Export Wizard,...
|
by: John |
last post by:
Hi All,
I have two backend databases that area link to a frontend database
where information is entered, retrieved and deleted. The information
accessed from the frontend database is coming from both databases.
Both backend databases are at least 225MB each, therefore accessing
data is slow and running queries are slow and frustrating, this...
|
by: PC Datasheet |
last post by:
Looking for suggestions ----
A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)
TblInspection
InspectionID
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this one data field - but i'm not sure)
:-)
Background info:
|
by: Sam |
last post by:
Hi all,
I have a process which first pulls one time all application IDs from a
database and stores them in a table(this process works fine everytime). I
then loop through the table, one at a time, and use application id to pull
details info and process it. For example, if I have 500 records in my table,
then I would have to open database...
| |
by: vonclausowitz |
last post by:
Hi All,
I was thinking of creating a table in my database to index all words in
the database.
That way I can quickly search for one or more words and the index table
will return the
words and records I need.
For example the iTable would look like this:
|
by: Jim Devenish |
last post by:
I have a split front end/back end system. However I create a number of
local tables to carry out certain operations. There is a tendency for
the front end to bloat so I have set 'compact on close'
I think that I have read in some threads (althoug I cannot find them
now) that others place such tables in a local, linked database. I
could...
|
by: ianenis.tiryaki |
last post by:
well i got this assignment which i dont even have a clue what i am
supposed to do. it is about reading me data from the file and load
them into a parallel array here is the question:
Step (1)
Your first task is to write a program which reads this file into two
parallel arrays in memory. One array contains the titles, and the
other array...
|
by: tamaker |
last post by:
Is this do-able with ASP / VBscript?
-- I have a database with user records (name, photo, etc. etc.)
I want to use asp to generate (on the homepage) a series of 4 randomly
selected 'user records' from the database -- say just the headshot
photo or name from the database.
In addition to the recordset being randomly generated (i.e. our of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |