Calculated Unique Text Field based on Existing text (1 Viewer)

Rich_Lovina

Registered User.
Local time
Tomorrow, 08:32
Joined
Feb 27, 2002
Messages
225
Okay, returning to Access, can this be done in the table design? or
Must it be a Query-Make Table, or
Is it a job for a Module?
I have a long text field (64chrs), some are blank, others one word, and problem samples are
Association
Association xyz xxxxxx
Association xya yyyyy xxx
abcde xxx cc ddddd Association

I seek to derive a unique textstring to serve as a primary field in a related table.

Second part: There are c.50,000 records, of which c.16,000 contain text data, and 4750 react to IS NOT NULL as if they contain data, but are in fact blank (Have checked for blank spaces and it appears none), so why IS NOT NULL or <>" " both turning up the 4750?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
Access treats zero-length strings differently from nulls. If there are some fields that are null, there is no way to use the field as a primary key since primary keys are not allowed to be null. Besides most people, me included, would frown on a 64 character text string as a pk.
 

Rich_Lovina

Registered User.
Local time
Tomorrow, 08:32
Joined
Feb 27, 2002
Messages
225
Perhaps not explained well enough, Pat. The string 64k is far too long as a PK, and that is why I seek to derive a shorter version which will be unique e.g.

1st 5k(Word1),if space then next 1stk, etc.

Part 2: You write : Access treats zero-length strings differently from nulls.

So how do I find only those records with text if <>" " or is not null will not?
 

Rich_Lovina

Registered User.
Local time
Tomorrow, 08:32
Joined
Feb 27, 2002
Messages
225
Message sinking in.....sometimes its slow

An additional thought....
Thanks especially Pat for:
"...since primary keys are not allowed to be null"
So that'll mean there can never be RI, but can the related table be a lookup only?
Or does that mean any criterion relating to this field should stay in MAINTABLE?
My two analysis fields on the 64k text field are:
-to describe the text by a 2k term e.g.
Text="Combat Data Systems" User or module sees "*data*"and adds DM to the associated field
Field 2 says, if DM is derived the person is basically an IT person, so field IT=yes.

Otherwise, as you say, there's really been little point for 3 years to have this table sitting there, since it can't work correctly as a related table and certainly can't have RI.

At least, at 53 I'm well into a p-time online Comp Science degree, (Univ New England, Armidale, NSW, Australia) and getting a handle round consistent logic & generic terminology required.

Thanks any comments in advance
 

Rich_Lovina

Registered User.
Local time
Tomorrow, 08:32
Joined
Feb 27, 2002
Messages
225
yet, hang on, for 3 years I've had two other related tables which both have blank PKs, the first has successfully lived with RI, the 2nd refuses. Perhaps then, the first survives and the others die??
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
You may define unique indexes for fields that contain nulls but NOT primary keys. Check again. If you want to select values that are null or zero-length strungs:

Where YourField Is Null or YourField = "";

Or, if you run two queries, you'll get two sets of results.

If you want to distinguih them:

Select IIf(IsNull(YourField), "Null", IIf(YourField = "", "Zero-lengthString", "Data")), YourField
From YourTable
Where YourField Is Null or YourField = "";
 

Users who are viewing this thread

Top Bottom