I have an Access table with a field called Purchase Oreder Item. The field must be all numbers and the length can be from 10 characters to fourteen characters. I have no problem requiring all numeric characters with a validation rule and setting a maximum length with the input mask. The problem is requiring the minimum of ten numeric characters. The operator must enter a minimum of ten characters and no more than fourteen. Please help.
I have a field in a report that is calculated to generate a dollar amount. Well the people who need this have some strange rules about how they get their dollar amounts. Mostly they just round up to the nearest dollar, which is no problem. But they also want a minimum charge of $2.00. So my problem is if the calculation works out to be 0.01 to 1.00, it will say 1.00 and it is going to be wrong because they will want it to be 2.00. So is there a way to set a minimum value for this field, or where should I go to try this?
Expressions in Access have given me some trouble before. Mainly due to inexperience. I hardly ever work with them. What I am trying to do is make the default value of a form textbox control the minimum value of a field A in a table A. The datatype of Field A is Date.
So far I've got:
=Min([table A].[field A])
In the Default Value of the form's property sheet, but this just returns a blank value. I've had a look in the table and there is no value that is blank in field A.
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 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 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 want to set a rule where employees cannot change the "Status" from "Active" to "Closed" if there is no date entered in the "Actual Closure Date" field.
The 2 fields I am trying to create a rule for are called 'Actual Closure Date' and 'Status'.When we raise an incident the 'Status' is set to 'Active' and the 'Actual Closure Date' field is left blank as it hasn't yet been closed.What I want to do is prevent people setting the 'Status' to 'Closed' when they havent entered a date in the 'Actual Closure Date' field.The Form name is called 'Issue Details' and the information is stored in a table called 'Issues'.
I am trying to create a validation rule whereby If a box is checked it makes sure that before saving and moving to the next form several, though not all fields are valid.
Ive tried many variations of:
([x] Is Not Null) OR ([Y] is Null) And ([x] Is Not Null) OR ([Y] is Null] etc etc for the fields I need covered.
Either the rule doesnt work at all or else all data input is invalid!
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.
I am sure there is an easier way fo doing this, but I am looking one at a time!!!
I want to know what is the Max and Min number of characters used in each field of my table.
I am using this code below, the max will be at the top and then scroll to the bottom to find the min, but as can be seen this is doing one field at a time and is very time consumining!
SELECT tbl05to06.Account, Len([Account]) AS MyAccount FROM tbl05to06 ORDER BY Len([Index Key]) DESC , Len([Centre]) DESC , Len([Account]) DESC;
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
I have a field "DisplayURL" that contains values of various lengths. Some are only 12 or 13 characters long, but others are 50 characters long. I need to run an update query to "cut-off" all the characters past the 35 limit - so that all records in the field will be 35 characters or less.
I've searched this forum and online and there are similar posts but nothing quite like this, I thought it would be a trim function but I keep reading that it only removes spaces. Any ideas?
Is there documentation/reading out there on how to query fields and take only a certain number of characters from said field?
We are setting up a user database on a 3rd party web application and they have a 15 character username limit so we want to pull the first 3 letters of a persons first name, middle initial, and first 4 letters of a persons last name to use as the username. We have an Access database will all of this information but I am not sure what the correct query is to complete this task.
Any workaround so that I can place more characters than the 64 limited in a field header.
In essence, I need to export a text or CSV file from Access every day, and the row header for the first column (i.e, in cell A1 if opened in excel) needs to read:
