Hi!
I have a Question about validation rules. I have a table with a field where the values can either be 3 numbers (like 321,245) or 4numbers separated by . (like 321.4). Other employees update these fields so it is importatant that they can't put in text etc.
In the field for validationrule I put the text (Select Distinct [fieldname] from table) but that did not work. Do anybody know an easy way to set the validation rule?
Thanks :eek:
I am trying to validate a text field (named Customer_Surname) and i want to make it so that only letters can be entered into the field and no other numbers or symbols. Is there any way this vlaidation can be done and if so, how?
Cheers
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
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?
I have a table embedded in a form that data is entered on. The data to be entered is a project number, date, and a numerical value. I want the date to be entered only be on a Sunday. Is there a data validation rule to apply or do I need to use VBA? I do not know VBA!
View 3 Replies View RelatedHello,
I am want to validate the data in a table column as per the data in a corresponding column in teh same table.
Ex. I have a table with column "Frequency" of datatype Text.
And other 12column are in the name of the months. i.e. Jan, Feb etc of datatype "Yes/No" in checkbox format. Default value as false.
I have only 4 different values i.e. Yearly, Half Yearly, Quarterly And Monthly.
If the value is Yealy then I should able to change the value on Only "Jan" from False to True.
Similarly If the value is Yearly then I should able to change the value on Only "Jan" & "July" from False to True.
Same holds true for Quarterly changing the values only of March, June, Sep & Dec from false to true.
Can anyone guide me to do this would be great.
Thanks
Sudhir
:(:(:confused:
Hi,
I am using the following validation code for the part number field on one of my forms:
DLookUp("[Door Part Number]","2-MAIN DOOR PART DATABASE","[Door Part Number] = '" & [Door_Part_Number] & "'") Is Null And Not ""
The purpose of this is to prevent users from changing/creating parts with a number that already exists in the database (and the last bit prevents no part number being entered).
It is fine for most situations except 1. If the user changes a part number, then changes it back to the original number again, the validation rule thinks this already exists and forces the user to give another part number.
Can anyone suggest how to get around this problem? It is hopefully very simple, but I can't get my head round how to do it!
Many thanks in advance!!
John
I'm making a student report card. Teachers enter data and then I print them all.
Sometimes data is missed in a field or it's deliberately left empty becasue the data doesn't apply to that student.
I'd like to generate a rpt that shows empty fields. Basically, I'd like a list of student names and the field names that are empty/null so that I can approach teachers and ask them if it was deliberate on not before I print. With 100 or so fields, I can't seem to nut out a query to make it all work.
Any ideas??
Hi - I have an inventory management database allowing stock to be moved between various subinventories. The user choose a transaction type and then details which goods to move. However at the moment they could enter a Quantity that is greater than that available in that subinventory.
So I have a query that gives me:
Part#
Subinventory
OnHandQty
The data entry form has a control source directly to the Inventory Table. I want to compare a text field [Qty] on a new inventory transaction (the person already having selected Part# / SubInventory from combo boxes) to the On Hand Qty.
I have tried Expression Builder in the Field Validation Property and I have tried some sql code on the AfterUpdate of the text box. I'm just not getting it.
Any help appreciated.
Cheers,
Darren
I am an absolute beginner and urgently need help with a validation rule. I need to know how to set a rule to set a minimum and maximum length for a text field record. I want minimum to be 4 and maximum to be 15 (I know I can set a max field size and set to not null but not sure how to make a minimum validation rule.
I am a teacher (not ICT!) having to cover for a sick colleague and students have asked me how to do this and although I am working my way through many of the great learning resources you link to here I have not yet worked out how to do this.
Would really appreciate any help you can give me so I can do my best by the kids.
J
I have a field that looks up the JobType using a LookUp Combobox from another table where these records for JobType will never change.
I want the user to only be able to select from the Combobox for the job type they want and be able to type the beggining of the job type so that the desired job will pop up. Now, i need the field to not be able to accept any other data than what is in the LookUp, so that they cannot enter false values. Duplicates of jobtypes are possible so a primary key cant be used.
Any help guys?
-thanks
I would like to write some code to validate that when a user enters data into a text field, the user must enter 4 digits.
View 3 Replies View RelatedI am trying to validate data held in two controls on a form.
The first control records membership status and is a combobox with 5 values allowed.
1 is 'member'
2 is 'under consideration'
3 is 'left' etc.
The combobox displays the description, the underlying table the number 1-5.
The second control records 'reason for leaving' and again is a combobox with 7 entries just as above.
I need to ensure that if a value is entered into the second control the value of the first control is '1' or 'member'.
Whatever I do I cannot get any code to work.
So, what event should I link to the code attached to the second combobox? On lost focus, before update or what?
What value do I check for in the comboboxes, the text or the number?
How do I set focus back to the first combobox in the event of an error?
I have 2 combo boxes on a form. The 2nd box is not visible unless the 1st box is set to a specific value (Illness). Once the specific value is set, the 2nd box appears with appropriate data for selection.
Goal #1:
I would like to have the form checked before it is closed to ensure that if the condition in box 1 is "Illness" then the 2nd box must have a value and can not be null.
Goal #2:
I am also having problems with the 2nd combo being visible when form opens or becomes current. The field is set to visible=false normally but needs to be visible when form opens if the conditions mentioned above are meet (true).
Private Sub Form_Unload()
If IsNull(Me.Incident_Classification) Then
MsgBox "Please select a type of Illness"
Me.Incident_Classification.SetFocus
]Code] .....
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.
I have done some basic data validation on a form, but I cannot seem to apply data validation to my subtotalled column called SUM FTE.
For the field properties on the Data tab I have the following:
Control Source: =SUM([FTE])
Validation Rule: >1
Validation Text: Total FTE is >1
My test:
Employee A - .50 FTE
Employee A - .60 FTE
SUM: 1.10
Data Validation should indicate SUM FTE >1, but no validation is being reflected on form.
When I learnt to use vba in excel (to basic level) I was always advised in a userform to validate data at end of record input i.e. when pressing enter and next. Is this true also with access or should i be putting validation rules at the afterupdate stage of a particular control?
View 1 Replies View RelatedHow 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?
I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:
Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).
Or there are column name spellings and in such cases, I get no error and the simply code hangs.
Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.
'Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
On Error GoTo Macro1_Err
DoCmd.SetWarnings False
' RunSQL executes a SQL string
[Code] .....
What I have:
Table named: clients table
Form named: clients data form
The form has the client information. The client sends me a document. I review the document. If the document has the correct info needed, then I have a field named "Incomplete" that is a Yes/No field in the clients table.If the field is clicked, then I want the next date field to appear and then I can fill in the date that it was sent back to them.If the field "incomplete" field is not checked, then I don't want that date field to appear nor do I want to fill it out.
I have tried several ways to validate my data with existing table I've developed.
Actually, I want the access to validate the data (Cellphone number) I've entered in the textbox by looking it in the existing table. So, if the data is there in the table, the message will pop up such as.. "Please continue". But if not, the message should be "this number was not registered yet.." and the textbox will be empty and ready for the new number...
In vba I need to check a specific field (ID) for special characters used in that field. The characters to check for are:
' " ! * # & ^ % $ " : ; | < > ? @ { } [ ]
The code I have is:
strSQL = "SELECT ......... "
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
rstTest.AddNew
rstTest("ID").value = rs![ID]
rstTest.Update
rs.MoveNext
Loop
I need to know what I should put after "SELECT
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
Any help is appreciated.
I'm a BA so i'm converting my excel issues list into a database with a front end where i can create new issue records this uses a form that sits on top of the issues_table.
Howver, i have created a table called Projects and a table called Test_Plans
Each issue is logged against a project and a test plan. Lets say ProjectA has TestPlan1 and ProjectB has Testplan2.
On my issues form, i can select ProjectA in the project field.
In the Testplan field i can select EITHER TestPlan1 or Testplan2
Testplan1 is the only valid entry.
How do i
a) Once I have selected ProjectA in the Project field only display Testplan1 in the Testplan field?
or
b) If the user tries to enter testplan2 for it to error?
I've tried creating a query and linking the controlsource field to it but it doesn't like that!
I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:
AAnnnnnnA
...where A is any letter A-Z and n is a digit 0-9
Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?
I am working on a form that registers new training attendees. One of the fields in my form asks for dealer number.
I have a separate table where all the dealer information is stored. One of the fields is a Customer Number field
I want to ensure that entries in the Dealer Number textbox on my New Registration form matches a valid Customer Number in the Dealer Database Table
I would like this verification to occur either upon a Click event with a button or on an After Update event.