I am trying to validate attendance at an event. If there is a no show indicator set to Yes, then attendance must be zero. If there is a no show indicator set to No, then attendance must be greater than zero. No show indicator is in same table with attendance.
View RepliesI cant figure out a fairly simple IIF statement I need for a table validation rule. I'll try to explain:
I have 2 fields in a table that are controlled through a form, which sets the fields either true (-1) or false (0). Lets call them Field1 and Field2. These fields need a validation rule which is dependent on FieldX and FieldY.
When FieldX = FieldY, I need Field1 to be either 0 or -1
if Field1 is 0 -> Field2 is -1 and the other way around.
if FieldX <> FieldY -> Field1 = 0 and Field2 = 0
Hi there!
I wonder if someone can help me please? I have an Access 2000 database used to record the daily treatments received by patients in our Intensive Care Unit. The database consists of 2 tables;
Table 1 has these fields; [First Name], [Surname], [Hospital Number], [ICU Admission Number] (Primary Key), [Date of admission], [Date of discharge].
Table 2 has these fields; [Date], [ICU Admission number], and various treatment fields requiring a true or false response.
The tables are linked through the [ICU Admission Number] field.
I enter data in the first instance through Table 1 and then click on the '+' in the far left hand column to access Table 2 for each record (Patient), whereupon I enter the daily treatments - each record in this table accounting for 1 day on the unit (i.e. there must be no duplicate values).
When I open Table 2 I need three things to happen to try and avoid mistakes being made.
(1) The first record in the [Date] field must only be equal to the [Date of admission] in Table 1. It must not be possible to enter anything else
(2) In subsequent records in Table 2 the date must increase by one day until it equals the [Date of discharge] in Table 1.
(3) Once this value has been reached it must not be possible to enter subsequent dates or duplicate dates.
If anybody can help me with this query I would be delighted to hear from you. I have tried fiddling around with Validation rules but have had no luck.
If you can help then that would be fantastic.
Best regards
Usr33t
What would the validation rules be between two dates:
>01/09/07 however <01/09/11
and also
>01/09/07 however <present day
I am trying to create a validation rule for one of my fields.. i want it to be so that the user can not select a date to be on a Sunday..
View 2 Replies View RelatedI am building a scoring system for an Archery Tournament and am trying to add some extra validation between two fields.I want to check the contents of one field against another.So if Field A has an even value, then Field B must also be even, and if Field A has an Odd value, Field B must be odd.Would it also be possible to add an exception such that if Field B = 0 then the validation rule does not apply?I am only a very occasional user of Access, and don't have a very good grasp of the code structure in it, but am prepared to try and learn.
View 4 Replies View RelatedI have a table with 2 field:
1. StartDate (format : Short Date)
2. EndDate (format : Short Date)
How can I validate for field EndDate must occur after StartDate using Validation Rule?
I want to create a field validation rule that requires the user to input a policy number in a standard format. The format has a fixed length of 13 with three underlying components. The first 3 digits identify coverage, the next 7 are the policy number and the final three must always be "-00".
Example: GLO1234567-00
Is it possible to create a validation rule for this ? If so, can you provide the code based on the simple example above ?
How can I validate data using an excel spreadsheet?
I have a table with Product information, I want only the ProductId that are in the spreadsheet to be used in the database. How can I check the ProductID entered in the form actually exists in the excel spreadsheet?
What would I need to enter into the validation rule to change the field to alphanumeric only?
View 3 Replies View RelatedI'm struggling with a validation rule that almost works but not quite.
I want to restrict the values in a feild by characters between 0-9, A-Z, _ and -
Using the following string works for all except the hyphen, which I assume is because it's an operator:
Is Null OR Not Like "*[!((a-z) or (0-9) or (_) or (-))]*"
Is there something I can do to make it recognise the - as text?
I have two tables in Access 2010:
Tbl1 - Abbreviations: with fields (1) "Abbreviation" and (2) "Definition"
Tbl2 - Piping: with multiple fields one of which is "Unit"
In Design View of Tbl2, I'd like to set the "Validation Rule" for the "Unit" field so that it is restricted to values in field 1 of the Abbreviations Table (i.e. one of the listed abbreviations").
I tried variations of "[Tables]![Abbreviations]![Abbreviation]" in the "Validation Rule" portion of the Design View for Tbl2 (Piping) but, couldn't get this to work.
Hello,
I'm doing a project for school.
Currently I have a database containing 3 tables. One table contains a list of people. Another table contains a list of competitions and the number of positions available in that competition. The last table contains competitors and the ability to select what position they came in a specific competition.
I would like to know if it is possible, once the user and selected a person and competition for a record, for the validation to check the number of places available in that specific competition they played in and to not allow the user to enter a position that is greater than the number of places available.
I don't have much hope on this being possible but I would love any help.
Thanks very much
Kris
Question I am looking at some annoying Access database for a charity and got stuck on trying to make something work that they believe used to
Two Tables
Venues
pK Venue ID
Venue Name
Venue Capacity
Booking
Venue
Name
Addr1
Addr2
Addr3
Ect
They have a query call capacity check which does the following
Count Occurrences of Venues then takes this away from venue capacity
Essentially they have a standard access form of the Booking table with a drop down box and want it to only validate if seats are still free at a venue eg if the current count for venue is less than the venue capacity.
I would love to perform this validation rule on the table
Any ideas ?
I have field (SupplierID) set as Text and the field limited to 5.
I want the users to enter the first three digits as text e.g "ABC" and the last 2 digits as numerics e.g "01", thus the input would be "ABC01", I can validate it to 5 digits but cannot get the validation working on the numerics, e.g users can enter "ABCDE", Am i correct in thinking its because my field is set as text ?, is there a simple way to do this, thankyou.
hi,
instead of providing validation at the client side or in the server script....
cant i provide validation in the tables fields...
I have a field named YearID, datatype text..
But I want to allow only text like this ==>
e.g. 'F-00'
'F-01'
Do I have to put any format property...
please do help...
thank u.
Table field (primary key) data type:Number
How can I validate / symbol as in 05/01 representing year and 1st record, etc.
Many thanks in anticipation.
I have an Orders table which needs to be able to calculate if the subtotal is less than £10.00 then enter £3.00 otherwise, enter £0.00. I have tried...
=IF [Subtotal_2] <£10.00 THEN £3.00 IF [Subtotal_2] >£10.00 THEN £0.00
Why won't it work!? :confused: :eek:
Help me please!
I have 2 tables: Components and Lot Codes
In the Components table I have the following fields:
ModelType
Component
In the Lot Codes table I have the following fields:
Serial Number
Model
Component
LotCode Number
I want to restrict the data in the Lot Code Table so that when a model number is entered the only possible values for the Component field is one where ModelType and Component match in the Component Table.
This sounds confusing. . .basically selecting one item drills down the options for the next.
Can anyone help?
I am trying to validate data in a table.
I have two fields, status and completion date.
What I want is the following:
If [status] field = "Complete" then [completion date] cannot be null.
I don't know if doing this in a table of the form is best using the forms Before Update Event, but I am having trouble figuring how to write it using the expression builder.
Can anyone help or offer suggestions?
Hello, I'm new in the Access World. I have a small question, but a bigger problem:
I have a big application developed in Access 97 with more then 200 tables. I wan't to move all the validation rules and texts from back-end to front-end (mean that I wan't to make the validation on the form controls and not directly on the tables like is now). This is needed because in future, I wan't to migrate the application to MSSQL. I wan't to write a piece of code that goes through every form, and for each control in the form, upon the control datasource, get's from database automatically the validation rules and texts and sets them to the control properties.
This is a nice thought, but I'm stuck at the part where I have to get the validation rules and texts for a specific column from a specific table.
Can anyone help me?
Thanks,
Mihai.
Hi
I have a form which people fill in (made up of fields from Table1) to record when sick days.
On the form, they have to enter the "week commencing" (which is set at Medium Date format); then they have to enter in a first day (which is set at Short Date format).
The First Day should be no more than 7 days from the date that they enter in the Week commencing field on the form.
I want an error message to pop up if they do this, for example:
Week commencing: 1st April 2007
First Day: 10th April 2007
(because the 10th April 2007 is more than 7 days from the 1st April). But it would be ok if they did this:
Week commencing: 1st April 2007
First Day: 8th April 2007
Help???
thanks!
Maria
I have a lookup which selects a 'Recipient'.
I need to add a check to see if this 'Recipient' (user selected) already exists within another table.
If so, I need a pop-up to appear.
If not, I want the user to continue unimpeded.
This is pretty simple but I need to place a validation rule in my table for a field so that users can only enter data that starts with APB. What would the rule be?
View 1 Replies View RelatedHello, all.
I have a table that contains a field (list) for categories [Inquiry Type], and a field that tracks category of any follow-ups [FU Inquiry Type].
The rule is: If [Inquiry Type]="F", than [FU Inquiry Type] Is not null.
For some reason, when I'm entering data, the validation rule does not take any effect....does not notice when [FU Inquiry Type] - which is also a list box - remains empty when [Inquiry Type] is an F.
I have entered the rule this way in the table property validation rule field:
(([Inquiry Type]="F" AND [FU Inquiry Type] Is Not Null) OR ([FU Inquiry Type] Is Null)
I already have data in this table, but have ensured that it conforms to the above rule. Can anyone help?
Hi,
Does anyone know if its possible to base a validation rule on the data that is already in the field in a table?
e.g. there are 10 records in a table each with an ID number starting at 1 and going up to 10. When new record added i need a validation rule that will prompt the user to enter a number greater than the previous number entered for the ID.
so they will not be able to enter 1 - 10.
hope thats clear
cheers.