I am creating a database using Access 2000 that requires validation on a date in a field (it is a short date e.g. 24/11/2005). I need to create a validation rule that only accepts Saturdays. I have an algorithm (see attachment) that can turn a date into a number, which relates to a specific day however I do not know how to implement this using access. Any algorithm tutorials, help or suggestions would be greatly appreciated.
P.S. This is my first post so I am sorry if this tread is in the wrong set of topics
Hi all. As always, thanks in advance for the input.
I'm building a database of housing developments done by my organization. There are a number of rules that I would like to enforce--many of the rules for data entry in a given field, depend on the value entered in a separate field. For example, if someone enters "Residential" in the BldgType field, then the Affordability field cannot be Null (If they enter "Commercial" for BldgType, the Affordability field CAN be Null).
Similarly, if someone enters "LLC" in the PartnerType field, then the value in the PartnerRole field must be either "Member" or "Managing Member", whereas if they enter "LP" in PartnerType, then the value of PartnerRole must be either "Managing General Partner", "General Partner", or "Limited Partner."
I have little problem that I am struggling to find an answer to. When I output a piece of data from my text box, it must be either 10 or 11 characters long when it goes into the receiving data base. I just wonedered if anyone knew of a way of validating the text box to ensure that it met this criteria before output. Input mask is not an option, as other receiving databases may not require the validation.
I'm doing something wrong, as this isn't working for me.
I have 2 fields on a continuous subform Quanity and Defect_description, if a Quanity of larger than one is entered then a Defect_description must be selected. Defect_description is a combo box.
I put this on the before update of the Defect_description
if [quantity] >0 then Defect_description notnull msgbox "you entered a quanity - now select a defect description", vbinformation, "Message" Defect_description.set focus else end if end sub
I have a table Orders with fields InvoiceNo And Invoice Series.
I want if of course anyone can help me to Validate the next Invoice Number Not To Be Dublicate and to automatically Increment. Meaning that if Last Invoice number was 45 and Invoice Series is A the next to be A 46. If Last Invoice without Series is 40 the next to be 41, and so on. I can manage to increment the values in one field Invoice number but I can not incoporate the Invoice Series.
Any help would appreciated.
Note : I'm not good in VB so please be kind to me.
I have a postcode field on a form that allows 8 characters. e.g. ST10 8BY including the space in the middle. The only validation I have been able to use thus far is the above but I would like to know how to validate two letters (AA) then between 1 and 2 numbers (11) etc.
Is there any code/built in functions that allows this. I know there is an input mask but if I use that, I can set the poscode format up like ST10 8BY but it thinks that the rule has been broken if the postcode was changed to S10 8BY by removing the T. This is also a valid postcode.
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.
In a school, a customer ID (in a canteen for example) would be either student administration number or staff initials.... how can i make this customerID field so that it accepts either all numbers (for student admin. no.) or all text with only 3 letters (for staff initials)?
Found some great code, else where that perhaps someone else can use.
Each required fields tag property - change to required Dim blnContinue As Boolean Dim ctl As Control blnContinue = True For Each ctl In Me.Controls If ctl.Tag = "Required" Then If IsNull(ctl) Then MsgBox "Required Information Missing", vbCritical + vbOKOnly + vbDefaultButton1, "You must fill in the x xx xxx xxxx fields" Cancel = True ctl.SetFocus Exit For End If End If Next ctl Set ctl = Nothing End Sub
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
I've got a table called Employee Payroll. In it there are several earnings fields. They all have a drop- down box which consists of three values, £2.50, £2.75 and £3.10. Is it possible to make this into a validation rule so that the user can't enter other values? Thanks
I created a field in access 2010 called OptionalSubjects . It is a multi value field where a user can select multiple values from the combo box, the selection are ID from a query. Now i want to limit the user to only two selection from the multiple values available and not less than two. So the field is either NULL or two selection only. How can I achieve this in validation rule or vba.
In the forms for my database, I require the user to input a month and year. Due to how the reports are structured, I need the format of the month and year to be very specific. I tried to set the format as 'mmmm yyyy' however even though it is visibly displayed as this, in the table, the values are still stored as sometimes 6/1/2011 or June 2011 and Jun 2011, which in Access' eyes are two different values.
In the report, it will display June multiple times because of this format discrepancy. I am trying to create a validation rule to ensure the user will enter the month and year in the correct format, but how to go about writing the expression.
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?
How to create a validation rule in relation to another fieldname in the same table?
For example, the first field line is the serial number, and when something is entered for the serial number the OTHER field line (which is quantity) needs to be 1, otherwise it stays blank.
I use access 2007 or 2010 depending what computer I'm using.
I am wanting a text field in a table to accept only chars A - Z
In my validation rule I use
Like "a" Or "b" Or "c" Or "d" Or "e" Or "f" Or "g" Or "h" Or "i" Or "j" Or "k" Or "l" Or "m" Or "n" Or "o" Or "p" Or "q" Or "r" Or "s" Or "t" Or "u" Or "v" Or "w" Or "x" Or "y" Or "z"
Which works :D
Then validation text I use
Enter Alphabetical Letters only
Also works :D
Is there any short way to write Like A.....thorugh to Z without the long statement?
I’m trying to create a DB with some field validations. I have a field named ‘Value’ and a field named ‘Doubled’. Value is in a currency format. ‘Doubled’ is in a Y/N format. I want a to create a rule that prevents the value ‘Yes’ from being entered in the ‘Doubled’ field IF the value in the ‘Value’ field is greater than .60. How do I do that?
I entered "[Value]< .6" but that wouldn’t allow me to put any value in the ‘Value’ field – it popped an error every time.
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 ?
I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:
UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of: 1 Tigers 2 Bears 3 Sparrows
BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN
I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME
I set up a combobox and lookup in BATTLES_TABLE as follows: SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;
this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)
but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).
How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?
I tried to enter a validation rule: [ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])
I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.
Thanks for any help with this validation problem. HKP_MACK
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)?
