Searching And Checking Rules Depenging On Rule

[SEAN DI''''ANNO]

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer

'ByVal RecordID As Long 'Primary key of record

Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)

'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

For Each fldField In CurrentRecord.Fields

'Look for whatever might be wrong in the field




Select Case fldField.Name


Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1

Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1

Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1

End Select 'Case fldField.Name

Next fldField

End Function 'DataScore()

 

[SEAN DI''''ANNO]

I am really trying hard to find some code to help me acheive this rather than
just relying on other to give me code but it all seems very confusing.

From what I can understand what ever field I am currently looking at is
called fldfield.name and the value of the field I am looking at is called
fldfieldname.value

The first action is to seach the rules table to see if there are any records
with the value fldfield and if so filter those records. if there are I need
to loop the records to see if I can find fldfieldname.value against any of
the rules and then see if its true...i.e In my example doe Contact Name start
with xxx. I never appreciate just how confusing this could be

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer

'ByVal RecordID As Long 'Primary key of record

Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)

'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

For Each fldField In CurrentRecord.Fields

'Look for whatever might be wrong in the field




Select Case fldField.Name


Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1

Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1

Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1

End Select 'Case fldField.Name

Next fldField

End Function 'DataScore()

 

[Tim Ferguson]

I am really trying hard to find some code to help me acheive this
rather than just relying on other to give me code but it all seems
very confusing.

I am not really sure what the question is. The code you posted is
obviously only partial -- if you don't have the skills to complete it and
you don't want someone to do it for you, in what way do you think we can
help?

The plan seems to be something like

for each rule

get the fieldname to be checked

get the value to check it against

identify the nature of the check to be done

pass the existing value and the check value to a _
suitable subroutine

inspect the value (OK or failed) from the subroutine

if it's a fail, adjust the score

next rule


Hope that helps -- if you need any more help in fleshing out the
algorithms, then post back.

B Wishes


Tim F

 

[SEAN DI''''ANNO]

Thanks for taking the time to listen to me, it was a really bad day
yesterday and some respect no better today but I will try and a bit more
composed by saying exactly what I trying to do and need help with. I am also
contious that my question is part of a few threads. I will try and tidy them
up. Sorry If I have appeared awkward

My database is a list of exported contacts showing address details. On a
day-to-day basis a new list will be compared to the previous list to collate
a list of new customer contacts which in turn will be scored according to the
accuracy of information entered. The purpose being, each user will be given
a target for achieving a certain score. For example if there are 20 fields
Contact Name, Address 1, Postcode etc, they should score at least 16 for a
new record.

Therefore I have a query with a field that simply says "Score:
DataScore([ContactList]![ContactList_ID])"
The function being the one listed below. The code that I was originally
helped with had at the end of this function different cases to check certain
values within some fields and take x away from their score. What I would
like to do is replace this with a table which can be looked up.

The table will be something like;

Rules Table
Field Value-To-Look-For Search-Method Score-Deduction
Contact Name "" (nothing entered) Exact 1
Contact Name N/A Contains 0.5

I know that the accuracy checks will have to be very basic. I.e.
Practically impossible to make sure the postcode is legal but it would help
to spot obvious mistakes. This where I need help on code.

Within the function I have two values which I know I need to use;
CurrentRecord.Fields.Name and CurrentRecord.Fields.value

The first part is what code do I need to see if there are rules in the rules
table which relate to currentrecord.fields.name? In other words I don't
suppose I can do a simple query matching Contacts ID...any field to the value
currentRecord.Fields.value or can I?

The second part is; If the answer is No then the next field will be looked
at but if the answer is yes I need to

Look at each record in the rules table where there was a match and;

Look at the search method...If its "Exact" Do Case 1, If its "Contains Do
Case 2... I just need some example code to check search method and do an
action i.e. if Contact Name is Null then take 1 from score.

Once I have a bit of code, I know I will be able to add to it and add more
rules etc.

I hope this is a clearer Email. To be honest, I was confusing myself.

Cheers Mate.


Public Function DataScore( _
ByVal RecordID As Long) _
As Integer
'ByVal RecordID As Long 'Primary key of record
Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)
'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

Point of order here: You have posted this (or a very similar) question
on at least one other newsgroup. When you do that, it's good practice
to specify all the newsgroups at one time, so that any answers get
posted onto the same newsgroups. Otherwise, you'll perhaps have people
answering who don't know that your question has already been answered,
and they may feel that their efforts were wasted, and they may be less
eager to answer other questions of yours in the future.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer

'ByVal RecordID As Long 'Primary key of record

Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)

'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

For Each fldField In CurrentRecord.Fields

'Look for whatever might be wrong in the field




Select Case fldField.Name


Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1

Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1

Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1

End Select 'Case fldField.Name

Next fldField

End Function 'DataScore()

 

[SEAN DI''''ANNO]

ok.really trying hard and it has taken me all day to come up with

Dim MyDB As Database
Dim MyRecSet As Recordset
Dim SQLString As String
Dim Criteria As String
Set MyDB = CurrentDb()
Set MyRecSet = MyDB.OpenRecordset("FaultItems", dbOpenDynaset)
Criteria = "FieldName like " & "'" & fldField.Name & "'"
MyRecSet.FindFirst Criteria
Do Until MyRecSet.NoMatch
MyRecSet.FindNext Criteria
Loop

Am I on the right lines? if the field name is found then do some function
depnding on the rule and value of fldfield.value?

I am really trying hard to find some code to help me acheive this rather than
just relying on other to give me code but it all seems very confusing.

From what I can understand what ever field I am currently looking at is
called fldfield.name and the value of the field I am looking at is called
fldfieldname.value

The first action is to seach the rules table to see if there are any records
with the value fldfield and if so filter those records. if there are I need
to loop the records to see if I can find fldfieldname.value against any of
the rules and then see if its true...i.e In my example doe Contact Name start
with xxx. I never appreciate just how confusing this could be

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer

'ByVal RecordID As Long 'Primary key of record

Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)

'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

For Each fldField In CurrentRecord.Fields

'Look for whatever might be wrong in the field




Select Case fldField.Name


Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1

Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1

Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1

End Select 'Case fldField.Name

Next fldField

End Function 'DataScore()

 

[Vincent Johns]

ok.really trying hard and it has taken me all day to come up with

Dim MyDB As Database
Dim MyRecSet As Recordset

Point of style: I'm not happy with this name. Could you come up with
something more suggestive, such as UsersFaultSpecifications? You read
at least two Recordsets and you probably write one, so I think their
names should convey a sense of what each one's purpose is.

Dim SQLString As String
Dim Criteria As String
Set MyDB = CurrentDb()
Set MyRecSet = MyDB.OpenRecordset("FaultItems", dbOpenDynaset)

You don't need Dynaset; Snapshot should suffice, since you're only going
to look at them, not change them. However, you might want to store your
scores in the Table of data that you're testing using these criteria, so
that recordset might need to be a Dynaset, if you want to store the
results there.

Criteria = "FieldName like " & "'" & fldField.Name & "'"
MyRecSet.FindFirst Criteria
Do Until MyRecSet.NoMatch
MyRecSet.FindNext Criteria
Loop

Am I on the right lines? if the field name is found then do some function
depnding on the rule and value of fldfield.value?

Something like this. There are probably various ways to proceed, but
I'd choose a record in the Table containing the data, then loop through
the list of criteria. For each criterion, fetch the value specified
field in the data record (but with care; some may be text strings,
others may be numbers) and apply the specified criterion to that value.
You'll have to decide how to do that, and you may need to do some
error checking on the criteria themselves to be sure that they are
expressed clearly. This should be a fun challenge. I suggest you start
simply, perhaps by specifying strings that would make sense in a "Like
...." clause, or maybe even by restricting the choices to exact matches
until you have your system debugged.

Anyway, having gone through all the criteria for that record, I'd assign
a score to it, then repeat all this with the next record.

You can do a couple of things with the score that you compute: store it
into a field in the Table of data, store it (with a copy of the primary
key value) into a separate Table of scores, or return it as a value of
your function. In this last case, your function could store the values
into an array to be returned when you've read all the records, or you
could return just one value for each function call (but then the calling
program would probably need to open the recordset of user data, call the
function once for each record, and close the recordset).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Thanks for taking the time to listen to me, it was a really bad day
yesterday and some respect no better today but I will try and a bit more
composed by saying exactly what I trying to do and need help with. I am also
contious that my question is part of a few threads. I will try and tidy them
up. Sorry If I have appeared awkward

[...]

What you have posted makes sense, although it seems to me a bit
ambitious. But it can definitely be done, and you have my best wishes.

My comment on the multiple postings was a suggestion to make clear what
you're doing, so everyone can see the conversation and not waste time
duplicating someone else's answer. (For example, if you want to post to
2 or 3 newsgroups, do it all with one message that has multiple addressees.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.