CODEWITHSUNDEEP
sqlvbams-access
Stefan
Stefan

Reputation: 105

Validate given conditions across the rows in a database table

I have a database table with positions and coresponding values like this

position    value

Z170S11     630
Z176S11     103
Z060S11     7162
Z143S01     47

'position' is unique for this table. The set of positions is fixed. The row entries for column 'value' will change with each new run of the application.

I also have a fixed list of conditions across all the values in this table. This list is part of an Excel file that I want to store in my database. The list has entries like this

left hand side       operator    right hand side

Z143S01 + Z060S11    =           Z176S11     
Z143S01              >=          Z170S11

Both, left and right hand side may consist of sums and differences of up to 10 different values. Operators are '=', '<=' and '>='.

For each condition I need to check that the current 'value' fields are correct. E.g.

Z143S01 + Z060S11 = Z176S11

will evaluate to

47 + 7162 = 103

which is false. The result of each check should be presented to the user.

What is a good way to store these conditions as part of my database and automatically check if they are fulfilled with the given values from the table above?

I thought of storing them as string values in a table and evaluating each row individually using VBA's 'Split'- and 'DLookup'-functions. As I expect about 1.000 conditions to check, this might turn out too slow. Is there an easier/faster way, maybe relying more on SQL?

Upvotes: 0

Views: 571

Answers (1)

June7
June7

Reputation: 21370

Custom function opens a recordset of Positions, loops records and performs replace operation on condition expression string, Eval() function evaluates new string, returns a True or False.

Function TestVal(sStr As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Positions")
Do While Not rs.EOF
    sStr = Replace(sStr, rs!Position, rs![Value])
    rs.MoveNext
Loop
Debug.Print sStr
TestVal = Eval(sStr)
End Function

Can call function in query or textbox.
SELECT Conditions.*, TestVal([LeftSide] & [Operator] & [RightSide]) AS IsTrue FROM Conditions;

Upvotes: 1

Related Questions