Proper Case Format Using Input Mask Or Rule Validation?
Jun 29, 2014
Is there a Input Mask or Rule Validation that would automatically or require user to input data into Access field in a proper case format? Such as my text would be "My Text" when viewing the actual table? If so how would you do it when in Design View of Table.
I want a customer ID field which only allows initials (3 letters) or admission number (4 numbers) and do not allow a mixture of letters and numbers. What input mask or validation rul do i use for this.
i tried: (validation rule) Like "???" Or "0000" but of course it didnt work, as the ??? represents ANY three characters (text or numbers) and "0000" is only used in input mask to represent numbers. i also tried the input mask: "LLL" or "0000" but it also doesnt work because it takes "or" as one of the field data.
I have a form that acts as a filter/search engine -- it inputs criteria into a query, then opens a report based on the criteria/query. One of the criteria options is "Supplier Number", which is to be entered into an unbound text box.
All of our suppliers have 10 digits, usually in this format: A123400000 (i.e. First character is either a Letter or digit, followed by four or five digits, then followed by four or five zeros)
Our system works with ten digit numbers, while the people in the dept are used to just typing in "A1234" assuming the zeros are irrelevant.
How do I create a validation rule or input mask where a user can simply type "A1234", then it will return zeros for the remaining of the ten required characters?
I realize the usual response to these type of simple question is "Search the Threads", but I have done so already and still don't have an answer!
I have a date field in a table which is Short Date with an input mask of 99/00/000;0;_
I do not want (for reasons too complicated to go into here) to put this as a required field even though it is a required field (makes sense from my end!).
If I set Required Field to "No" what format can I put in the validation rule field so a message (from my validation text field) comes up if the field is left blank??
Hiya everyone, I'm try to do a database for items with part numbers. & I want to be able to set the field to only accept data which has two letters at the begining, but any other characters after that (no fixed size).
ie. part num1: pn047589sdf8m3 part num2: pn6583 part num3: pnfdgmj5973m part num4: pn-fdgmj-5973m
Is this possible to do?
Cos all I can do at the moment, is to put a fixed number of characters. ie. is the field size is 30characters then I'd have to do this: >LLCCCCCCCCCCCCCCCCCCCCCCCCCCCC
but this isn't good.
I just need something like: >LL*
* mean any character for as many characters in length.
I am creating a database where the serialID can be a number of different 'formats'. TBH, I don't know if this can even be done in the first place, but on the off chance that it can....:p
Firstly, i'm not sure whether to use Input Mask or Format...
But the serial may be '1234567AB', '12345678AB' or '12AB1234AB'.
Or to put another way
'7 numbers 2 letters',
'8 numbers 2 letters'
or '2 numbers, 2 letters, 4 numbers, 2 letters'
I have tried creating a mask such as '00LL0000LL' (can't remember exactly, had some '9's in there too some where...) but just keep hitting a brick wall, whereby it not satisfying all of the types.
My Querie: I have a relational database, that is basically made up of a few tables, I will simplify for this to two. TblFieldNames and TblFieldValues
Basically using a form you can fill in a field (TblFieldNames.FieldName) and it is then appended to TblFieldValues.FieldValue.
On the form the properties of the input text box are changed according to the record selected in TblFieldNames. Ie. ListBox with FieldNames from TableFieldNames ListBox_AfterUpdate sets properties of TxtValue
The Issue: I have a Field in TblFieldNames that has a setting of Currency. (TblFieldNames.FieldType = "Currency")
I have changed the TxtValue.Format = "Currency"
It doesn't: A. Allow entry as a currency, ie. $ sign and thousand seperators B. Append the details to the TblFieldValues Table, which it is going across to into a field that is set as Text (this can't change)
I have tried: Changing format of input Txt box Changing Input Mask for inptu Txt Box "$###,###,###" but doesn't work for me.... "/$###,###,###" "/$###,###,###!"
I want it to allow me to enter in the Value as a Currency and append the value as a currency
ie. Key Press = 12345 Field Shows = $12,345 Appends this value as Text?
Key Press = 1234567890 Field Shows = $1,234,567,890
I have problem loading data from an excel file in to a table. I usually import the excel file in to a table via VBA script (because there are some changes I need to do before importing).It was working fine until this week when the data in a cell for a particular column in the excel file changed from being 3 letters to 4 letters. The column in the table is defined as text.
The data in this column usually looks like 001, 002, 003 etc but one of the cells in the excel file has data as P001.So now I get an error Runtime Error 3349 even though I have defined the column as text. When I change the data P001 to 004 similar to the other data, it works just fine. Is there a format or Input Mask I can set so that the data are correctly loaded in to the table?Or am I missing something?
This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.
But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case
Is there a way, either within Access or not, to change the city names in that table to Proper Case?
I hoping someone might know what expression I need to create to convert text in a field in an append query to proper case.
The reason for this is that I am receiving data in all uppercase and really need it to be in proper case, that is only the first character of each word in the field is capitalised.
A field name for instance is strProductDescription and I want the data to change from "FRIDGE FREEZER" to "Fridge Freezer" on appending the data to another table, hence a proper case function or expresssion.
I am trying to change all the fields of a table from All Caps to Propercase. I have used strconv propercase before, however only on a field by field basis. I have about 5 or tables that I have to do this to and am trying to save the typing. I know I can do this with a recordset (easily in asp), but am not familar enough to do this in vba. Can you do a for each fld, like in asp, or am I going at this the totally wrong way. I tried using an update query with the *, but access doesn't like that too much. Any help is greatly appreciated.
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
I have a field (date field) that when I try to imput data will tell me that that what is being imputted is not in the correct formate or to large for the field settings. It is in the right formate etc. Is the field size applicable to just that field or overall everything entered in that field in the whole database? It's a decient size data base and I'm wondering if everything in that field is over the size, but then how can that be?
Hey I have a little problem, I have this assignment for school to make a simple database, I did all that but there's one little thing I can't seem to find. I have to limit a Field's length to 15, but not through Field Size but thanks to a validation rule, and since I don't know a lot of validation rule commands I have no idea what the syntax is for the field length. How can I do this, it's pretty urgent actually, need to have this by tomorrow :s Thanks!
Relatively new to access, just trying to create a range in a validation rule. What I have are 2 cells, Date_Taken and Date_Returned. I want Date_Returned to only accept values between: Date_Taken and equal to or less than the current date.
Between "Date_Taken" And <=Now()
is what I've got at the mo', yet it's not working. I'm gathering that I'm referring to the Date_Taken cell incorrectly. Any help? I don't see any tools to fix such a thing.
Hey guys, what is the easiest way to validate a field based on another.
I have 2 combo box, lets say cboOne and cboTwo. If cboOne = 1 then cboTwo must be blank/Null. Can I place this validation in the table validation properties. If so, how do I word the validation code.
Hi,I'm really struggling to try and find a solution to a problem.I need to find a validation rule that only accepts:letters, spaces and hyphens (-) in the field.I have tried for at least 14 hours today and most of yesterday afternoon, but I just can't get it.The furthest I've got is rejecting everything except from letters and spaces, with each these codes individually:(I've only listed a few so you could see just a fraction of what I'd already tried)Is Null Or Not Like "*[!((a-z) or (Chr(32),Chr(45)))]*"Is Null Or Not Like "*[!((a-z) or (Chr(32)) or (Chr(45)))]*"Is Null Or Not Like "*[!((a-z) and (Chr(32)) and (Chr(45)))]*"Is Null OR Not Like "*[!((a-z) or (( ),(-)))]*"Is Null OR Not Like "*[!((a-z) or ( ) or (-)))]*"Is Null OR Not Like "*[!((a-z) and ( ) and (-)))]*"Is Null Or Not Like "*[!((a-z), ,-)]*"However, it is also rejecting hyphens, which I need it to accept.Also, unfortunately it is accepting bracketsHopefully someone here will have a solution, because I'm stumped, :confused:James
Question about validation rules. I have a user who has set up a validation rule to allow certain values to be entered.
Something like if the value equals 4 or equals 5 then it would accept it. Now she would like the validation be set so they can put both 4 and 5 in together. How can this be done?
What would be the 'validation rule' to input into the properties window for a Client ID? - (validation rule would make sure that none of the Client ID's are the same). ;)
Table: TRANSACTIONS Field: DATE Field: AMOUNT Field: loads more...
What I want is a validation rule on the AMOUNT field, which says that the data entered cannot be greater than the number in the PARM1 field. I can't get this to work, and I reckon it's becuase there's one record in the PARAMETERS table, but lots in TRANSACTIONS. In solving this problem I do not want to add any more records to PARAMETERS. The idea of that table is that it is an easy way to customise the database, not a way of storing data. I've putting this in the validation rule box (but it didn't work): =<Tables![PARAMETERS]![PARM1]