Could someone give me example code to put in the validation rule to to do the following:

1) Check a field is not null
2) Check a field is between two numbers
3) Check a field is a valid date
4) check a field is in a date range

It would be good if this was a 'best practice' example rather than a 'quick and dirty answer'.  For example they may use global
functions etc.

Regards,
Ben

PS thanks for everyones help so far, I seems to be learning Access 2000, SQL Server and Accounting/Double entery book keeping all at once!

<<Could someone give me example code to put in the validation rule to do
the following:

1) Check a field is not null
2) Check a field is between two numbers
3) Check a field is a valid date
4) check a field is in a date range

It would be good if this was a 'best practice' example rather than a 'quick
and dirty answer'.  For example they may use global functions etc.>>

1) Not IsNull([Tablename]![FieldName]) then.....
2) If [Tablename]![FieldName] >= LowerLimit and [Tablename]![FieldName] <
UpperLimit then
3) IsDate([Tablename]![FieldName])
4) If [Tablename]![FieldName] >= Int(LowerDate) and [Tablename]![FieldName]
<= UpperDate then


There are a number of general functions to check for states (NOT preceding
them reverses the meaning):
for NULL       IsNull
for Numbers         IsNumeric
for Dates      IsDate
for NULL or zero    NZ

range checking is generally straightforward but the best approach is to
create a function and pass parameters - this saves you re-inventing the
wheel all the time. For example:

Public Function InValueRange(dblVALUE as double, dblLOW as double, dblHIGH
as double) as Boolean

If dblVALUE >= dblLOW and dblVALUE <= dblHIGH then
     InValueRange = True
Else
     InValueRange = False
End Function

Call the function, passing the appropriate values and test the result
e.g. in a query, you could derive a field as:

IsChargeValid: InValueRange([TableName]![FieldName], 15.00, 50.00)

and test on the result being TRUE


Do not forget that in a query criteria, you can use BETWEEN...AND.... to
test ranges.

Is this what you need?

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Thanks, that was the kind of thing I was wanting.  Is there a function/property that gives the value of
the active field.  That way I gan write a generic function?

Regards,
Ben

>There are a number of general functions to check for states (NOT preceding
them reverses the meaning):
for NULL       IsNull
for Numbers         IsNumeric
for Dates      IsDate
for NULL or zero    NZ

range checking is generally straightforward but the best approach is to
create a function and pass parameters - this saves you re-inventing the
wheel all the time. For example:

Public Function InValueRange(dblVALUE as double, dblLOW as double, dblHIGH
as double) as Boolean

If dblVALUE >= dblLOW and dblVALUE <= dblHIGH then
     InValueRange = True
Else
     InValueRange = False
End Function

Call the function, passing the appropriate values and test the result
e.g. in a query, you could derive a field as:

IsChargeValid: InValueRange([TableName]![FieldName], 15.00, 50.00)

and test on the result being TRUE


Do not forget that in a query criteria, you can use BETWEEN...AND.... to
test ranges.

Is this what you need?

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Just tried to implement this but can't work out where to put it, I have a 'OK' button, chould I put it
on the click event or is there an event (validate, change) I can use?  Idealy I want the error to hapen if user navigates out of the
field but I guess I also need to do it if the user presses OK as they may never actualy go into the field.  Oracle forms has a
'Required' property, and upper/lower range properties.  Pity access don't have this.

Ben

>1) Not IsNull([Tablename]![FieldName]) then.....
2) If [Tablename]![FieldName] >= LowerLimit and [Tablename]![FieldName] <
UpperLimit then
3) IsDate([Tablename]![FieldName])
4) If [Tablename]![FieldName] >= Int(LowerDate) and [Tablename]![FieldName]
<= UpperDate then


There are a number of general functions to check for states (NOT preceding
them reverses the meaning):
for NULL       IsNull
for Numbers         IsNumeric
for Dates      IsDate
for NULL or zero    NZ

range checking is generally straightforward but the best approach is to
create a function and pass parameters - this saves you re-inventing the
wheel all the time. For example:

Public Function InValueRange(dblVALUE as double, dblLOW as double, dblHIGH
as double) as Boolean

If dblVALUE >= dblLOW and dblVALUE <= dblHIGH then
     InValueRange = True
Else
     InValueRange = False
End Function

Call the function, passing the appropriate values and test the result
e.g. in a query, you could derive a field as:

IsChargeValid: InValueRange([TableName]![FieldName], 15.00, 50.00)

and test on the result being TRUE


Do not forget that in a query criteria, you can use BETWEEN...AND.... to
test ranges.

Is this what you need?

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

This may help!


From Access 2000 help:

ActiveControl Property


You can use the ActiveControl property together with the Screen object to
identify or refer to the control that has the focus.


Setting


This property setting contains a reference to the Control object that has
the focus at run time.


This property is available by using a macro or Visual Basic and is
read-only in all views.


Remarks


You can use the ActiveControl property to refer to the control that has the
focus at run time together with one of its properties or methods. The
following example assigns the name of the control with the focus to the
strControlName variable:


Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

You can put the code on the 'beforeupdateEvent' for each control - if you
put it on the 'lost focus' event it will trigger even if you have not
changed anything in the field.  Presumably you will want to issue an error
message and move the focus back to the offending field.

If you put the code to check one or all of the fields on the OK button -
assuming the OK button runs the code to post the record to the underlying
tables - you could check there before allowing the update / insert to go
ahead. Checking a bunch of fields together will pose problems as to what
you do when you find a problem. It may be clearer to check it field by
field - especially if you can do a generic test.

Do look into defined validation rules at the field level within tables.
There is scope there to define rules and define what text will appear (at
the foot of the screen) when the rule is broken AND any forms create from
thereon will inherit those properties.

Basic requirements like not being NULL or must be numeric and even range
checking can be set at the field level within tables

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

>You can put the code on the 'beforeupdateEvent' for each control - if you
>put it on the 'lost focus' event it will trigger even if you have not
>changed anything in the field.  Presumably you will want to issue an error
>message and move the focus back to the offending field.

Put a msgbox on BeforeUpdate and it dont seem to fire ever, have I missed something (it is null I am checking for so what hapens if
the field is not navigatid to and douse this event fire if the field is null?

>If you put the code to check one or all of the fields on the OK button -
>assuming the OK button runs the code to post the record to the underlying
>tables - you could check there before allowing the update / insert to go
>ahead. Checking a bunch of fields together will pose problems as to what
>you do when you find a problem. It may be clearer to check it field by
>field - especially if you can do a generic test.

I just msgbox 'Field x must be entered" the End Sub.

I could also go to the field but dont know how to do this.

>Do look into defined validation rules at the field level within tables.
>There is scope there to define rules and define what text will appear (at
>the foot of the screen) when the rule is broken AND any forms create from
>thereon will inherit those properties.

Using MSDE (SQL Server), from what I can gather you can only do this with Jet.

Regards,
Ben

 Ben

SQL Server fields have a 'Nulls' property which allows the acceptance (or otherwise) of Null values. All other data quality checks
can be done via Triggers, which can be as simple (a number range) or as complex (look up data in several other tables to validate
the entry) as required.

Using a trigger to prevent bad data being entered will work when using Access - the disadvantage is that the user will be presented
with a cryptic SQL Server error message.

Brian

> Using MSDE (SQL Server), from what I can gather you can only do this with Jet.
> 
> Regards,
> Ben

If this is required for the data in the table the correct place to put it is
in the table design.  Make it a validation for the field.  Set the field's
Required property to True, etc.  Doing it there will make it required on any
form that is bound to the field without any code at all.  Then all you need
to worry about in code is trapping the form's error event if you want custom
data error handling messages.

|-----Original Message-----
|From: Ben Ewards [mailto:ben@c...]
|Sent: Wednesday, May 22, 2002 3:22 PM
|To: Access
|Subject: [access] Re: Basic question about using validation rules
|
|
|Just tried to implement this but can't work out where to put it, I
|have a 'OK' button, chould I put it on the click event or is there
|an event (validate, change) I can use?  Idealy I want the error to
|hapen if user navigates out of the field but I guess I also need
|to do it if the user presses OK as they may never actualy go into
|the field.  Oracle forms has a 'Required' property, and
|upper/lower range properties.  Pity access don't have this.
|
|Ben
|
|>1) Not IsNull([Tablename]![FieldName]) then.....
|2) If [Tablename]![FieldName] >= LowerLimit and [Tablename]![FieldName] <
|UpperLimit then
|3) IsDate([Tablename]![FieldName])
|4) If [Tablename]![FieldName] >= Int(LowerDate) and [Tablename]![FieldName]
|<= UpperDate then
|
|
|There are a number of general functions to check for states (NOT preceding
|them reverses the meaning):
|for NULL       IsNull
|for Numbers         IsNumeric
|for Dates      IsDate
|for NULL or zero    NZ
|
|range checking is generally straightforward but the best approach is to
|create a function and pass parameters - this saves you re-inventing the
|wheel all the time. For example:
|
|Public Function InValueRange(dblVALUE as double, dblLOW as double, dblHIGH
|as double) as Boolean
|
|If dblVALUE >= dblLOW and dblVALUE <= dblHIGH then
|     InValueRange = True
|Else
|     InValueRange = False
|End Function
|
|Call the function, passing the appropriate values and test the result
|e.g. in a query, you could derive a field as:
|
|IsChargeValid: InValueRange([TableName]![FieldName], 15.00, 50.00)
|
|and test on the result being TRUE
|
|
|Do not forget that in a query criteria, you can use BETWEEN...AND.... to
|test ranges.
|
|Is this what you need?
|
|*************************************************************************
|
|This e-mail may contain confidential information or be privileged.
|It is intended to be read and used only by the named recipient(s).
|If you are not the intended recipient(s) please notify us
|immediately so that we can make arrangements for its return: you
|should not disclose the contents of this e-mail to any other
|person, or take any copies. Unless stated otherwise by an
|authorised individual, nothing contained in this e-mail is
|intended to create binding legal obligations between us and
|opinions expressed are those of the individual author.
|
|The CIS marketing group, which is regulated for Investment
|Business by the Financial Services Authority, includes:
|Co-operative Insurance Society Limited Registered in England
|number 3615R - for life assurance and pensions
|CIS Unit Managers Limited Registered in England and Wales number
|2369965  - for unit trusts and PEPs
|CIS Policyholder Services Limited Registered in England and Wales
|number 3390839 - for ISAs and investment products bearing the CIS name
|Registered offices: Miller Street, Manchester M60 0AL   Telephone
|0161-832-8686   Internet  http://www.cis.co.uk   E-mail cis@c...
|
|CIS Deposit and Instant Access Savings Accounts are held with The
|Co-operative Bank p.l.c., registered in England and Wales number
|990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and
|administered by CIS Policyholder Services Limited as agent of the Bank.
|
|CIS is a member of the General Insurance Standards Council
|
|CIS & the CIS logo (R) Co-operative Insurance Society Limited
|
|*******************************************************************
|*************

Hi, Ben here are some suggestions:

1 - If  IsNull([your fieldname]) or [your fieldname] = ""   Then (etc)

   You need to do both because "" does not necessarily mean Null, I've
found, so you can have a field which appears empty, but 'IsNull' does'nt
find it.

2 - a.  If underlying table field is numeric
                If [your fieldname] >= lowestnumber and [your fieldname] <
highestnumber Then (etc)
    b.  If underlying table field is text
                If Val([your fieldname]) >= lowestnumber and Val([your
fieldname]) <= highestnumber Then (etc)

3 - Set the fields property to one of the Date formats, then it wont accept
anything other than a date.

4 - rather like 2a. only substitute lowestnumber for lowest date enclosed in
"" 's

Hope this helps, regards, Wesley Kendrick

----- Original Message -----
From: "Ben Ewards" <ben@c...>
To: "Access" <access@p...>
Sent: Wednesday, May 22, 2002 1:46 PM
Subject: [access] Basic question about using validation rules


> Could someone give me example code to put in the validation rule to to do
the following:
>
> 1) Check a field is not null
> 2) Check a field is between two numbers
> 3) Check a field is a valid date
> 4) check a field is in a date range
>
> It would be good if this was a 'best practice' example rather than a
'quick and dirty answer'.  For example they may use global functions etc.
>
> Regards,
> Ben
>
> PS thanks for everyones help so far, I seems to be learning Access 2000,
SQL Server and Accounting/Double entery book keeping all at once!
>