Check against ValidationRule in VBA

[Frink]

Hallo,

I was about to post this question when I found the BuildCriteria function which does everything I need!


Does anyone know how to perform the same check as a table field validation rule in VBA?

i.e I want to write a function which takes a value and a ValidationRule string and returns True or False. The thing is I want it to work with any ValidationRule, ie:
'-1 or Between 1 and 100'

Obviously there must be code within Access to do this, as you can add a ValidationRule string into the criteria box of a query in design view then it turns it into a valid SQL expression, i.e.
In a new query design, add any table, then add a field with the following set:
Expr1: 0
Show: Yes
Criteria: -1 or Between 1 and 100'
Set the query Top Values property to 1

If you look at the SQL View you get:
SELECT TOP 1 0 AS Expr1
FROM tblTable
WHERE (((0)=-1 Or (0) Between 1 And 100));

If you run this, then you only get a record returned if the value (in this case 0) is allowed by the expression.

I've looked at Eval, but this won't expand the expression so is no use.

Is it possible to programatically create a query?

 

[jrbarnett]

Yes - it is possible to create a query.

Look at "CreateQueryDef" in the Access help file.

John