Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- From: Steve Stad <SteveStad@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 23 Jan 2007 09:29:03 -0800
John/Jamie/Doug,
Thank you all for your input. I was able to use the validation rules and
logic provided and add to it for additional input validation/criteria.
John - BTW what did 'PMFJI,' mean in your first email?
"John Nurick" wrote:
Hi Steve,.
The [! ... ] construct means "any single character except those listed.
[0-9A-Z] is a convenient shorthand for [0123456789ABC...Z], and you can
add other characters or ranges to the list as needed.
A hyphen has to go at the beginning so it doesn't get confused with a
hyphen indicating a range of characters. E.g. [-0-9] NOT [0-9-].
Conversely a screamer mustn't go at the begining or it will negate the
rest of the list, e.g. [A-Z!] not [!A-Z].
So if you want to allow a dash somewhere, you need to change the
corresponding [0-9A-Z] to [-0-9A-Z].
On Mon, 22 Jan 2007 12:12:03 -0800, Steve Stad
<SteveStad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
John, Thank you for reply and sorry for my delayed response. All three
rules combined appear to enforce the data validation intended. When I tested
the lines (rules) separately it appears the [!0Y] will not allow the first
char to be 0 or Y -- correct? Whereas [0Y] allows the char to be 0 or Y.
Also, sometimes I might need to type in a dash '-'. How can I edit your
validation rule below to allow a dash '-' or other special character?
Thanks,
Steve
"John Nurick" wrote:
PMFJI, but it sounds as if you need a validation rule along these lines:
Is Null
Or Like "[0Y][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]"
Or Like
"[!0Y][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]"
On Tue, 16 Jan 2007 05:53:01 -0800, Steve Stad
<SteveStad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Doug,
Thank you for reply. My issue really involves data validation. I have an
ID field in a Temp table which gets loaded into a Master table. The ID field
size is 9. But if the ID begins with a '0' or a 'Y' the length should only
be 8 charachters. So I am trying to figure the best way to enforce that
rule. A field size of 9 is required for the master table because sometimes a
9th charachter is appended. But when I add (Y or 0) ID's to the temp table
the length should only be 8 charachters. I am trying to eliminate the
possibility of spaces being included in the last (9th) position.
Thanks again,
Steve
"Douglas J. Steele" wrote:
It's actually a violation of database normalization principles to have
fields that are based solely on the value of other fields in the same row of
the table, so it's not supported in Access.
You could always leave the size field out of your table, and create a query
with a computed field that returns the length of the other field. You'd then
use the query wherever you would otherwise have used the table.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Steve Stad" <SteveStad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:33D385F3-71EA-431D-9882-CAB4B31F9D55@xxxxxxxxxxxxxxxx
Hi,
I would like to create a default value in a table which auto populates the
number of charachters entered in another field (e.g., ID) in the same
table.
For example, in a query this formula will display the number of
charachters
typed in the ID field -- LEN: Len([ID]) - I would like to replicate this
function in my table but using the 'default value' changes it to
=Len("BILL_ID2") and displays the field size only - not the number of
charachters typed.
Your assistance is appreciated and let me know if further clarification is
needed.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
- References:
- Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- From: Douglas J. Steele
- Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- From: John Nurick
- Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- From: Steve Stad
- Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- From: John Nurick
- Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- Prev by Date: Re: Unique fields in table design
- Next by Date: Re: Date Updated Field
- Previous by thread: Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- Next by thread: Re: Default Value in table -- LEN: Len([ID]) vs, =Len("BILL_ID2")
- Index(es):
Relevant Pages
|
