Zero length strings in MS Access

Question

Zero length strings in MS Access

CanuckBuck

Hall of Fame

Points: 3915
More actions
January 20, 2009 at 9:55 am

#199078

Hi.
I have a bit of an obscure problem (I think). I'll try to keep this as concise as possible.
From an old system I have mine numbers that look like this;
1788
1788/E
1789/E
1790/E/01
1790/E/02
1790/E/03
1790/E/04
1790/E/05
1790/E/06
1790/E/07
1790/E/08
1790/E/09
The first four digits are the "Mine number" and the remaining six characters are a "suffix" which is used to distinguish "sub areas" within a mine. "sub areas" may or may not exist for any given mine.
I split this up into two columns (an integer - constrained to 0-9999, and a varchar(6) with a default of a zero length string. This part works fine.
I have a table containing production data which uses the compound PK from the mine table to create the relationship (there is a year column included in the production table facilitating the 1-M relationship)
All of this works fine in SLQ Server land.
When I look at these tables in MS Access (I want to create a "quick and dirty UI" for users) any mine with a zero length string as the "suffix" does not show related production data. I know that the RI between the two tables is correct because if I change the "suffix" to be anything other than a zero length string it does a cascading update and then I can see the related production data.
This appears to be a problem with MS Access. Has anyone run into this before? Is there a workaround? Is there just something I have to do to get things working properly?
Thanks for any help you may be able to provide.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

Can you provide table description and sample data, so I can play around with it? The article in the link below describes very well how to do this.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

ngarciaholman Valued Member Points: 58 More actions · Answer 2

Hello. My name is Nicholas. I want to become a database administrator. what is the best book to buy and do most employers use SQL Server 2005? I also spoke to someone at this school and he told me to buy Exam 70-431 and take the Mircosoft Exam. Any suggestions?

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 3

Hi. r.hensbergen

I've uploaded a script which will create the database and put some data in all of the tables for you to look at.

once you look at the data (in MS Access);

The essence of the problem is this;

For the mine which has a zero length string in the value for Coal_Mine_Number_Modifier, you can not see the associated production if you simply use a subdatasheet to associate the production with the mine (linking fields are the Coal_Mine_Number, and the Coal_Mine_Number_Modifier). If you change the ;Coal_Mine_Number_Modifier to anything else besides a zero length string then the association is made properly (the production show up as if by magic).

Thanks for spending some time on this.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Access can't link on a zero-length string. Treats it as if it were null for that kind of thing. You might need to add an arbitrary string to those, like "A" (for "All your mine, are belong to us") or "W" ("Whole mine") or something like that. Would that work?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

That was the answer I feared...

Now that I know that it's a product limitation and not something that I'm doing wrong I can deal with it. I'll probably use a tilde (~) or something like that.

Kind of a pitty though... ;-(

Thanks for your help on this.

kens-685659 Valued Member Points: 57 More actions · Answer 6

Have you considered changing your table? The data looks like a hierarchy. In 2008 there is some new support for hierarchies. In 2005 you could have a self-referencing table.

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 7

Hi kens.

That's an interesting idea. One I've modeled in UML but implemented in the simple way you've seen. You are correct. The data is hierarchical. Essentially, A mine (coal mine) can be a single entity in the simplest case - in which case the production would be associated directly with that entity. In the example I supplied - which is a more complicated case, a "mine" can be an aggregation of "mine workings". In this case the production (tonnes of coal per year) are usually associated with the members of the aggregation (in certain cases we may still want to have the individual mine workings listed but account the production against the "parent" record.

Do you think you could give me an example of what a "self referencing" table might look like in code?

GSquared SSC Guru Points: 260824 More actions · Answer 8

You'd have a table of Mines or Sources. Since some are "mines" and some are "workings", I'd go with Sources and have a type column.

create table dbo.Sources (

SourceID int identity primary key,

ParentID int null references dbo.Sources(SourceID),

constraint CK_SourceParent check (SourceID != ParentID or ParentID is null),

Name varchar(100) not null,

Type char(1) not null default('M'),

constraint CK_Type check (Type in ('M','W'),

constraint CK_TypeParent check (Type='M' and ParentID is null or Type='W' and ParentID is not null));

That would be a seed of the table, as I'm sure there are other values you would need in it.

In this case, I've got a Type column that indicates if it's a Mine or a Working, and for Mines, it doesn't allow a ParentID, but for Workings it requires one. That might be overly complex, you'll know better than I will on this.

The key to it is that ParentID references the SourceID in the same table, but not the same row (that's what the first check constraint is for). That means, if there is a value in ParentID, it's another row in that same table.

Then, you can query the output of a particular mine or working, and you can also query, using a hierarchy CTE (Books Online has an example of how to build one of those for exactly this kind of thing), you can query the total output of a mine.

Note that, with the structure I've got here, a working could have another working as its parent. That may be something that's possible, or may not. Again, you'll know better than I will, and it's possible to prevent that kind of thing too, if it should not be possible.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 9

Hi GSquared.

Thanks for the reply to my post of a while ago. The final resolution was that, from a business perspective, [coal]Mines and Mine Workings are to be treated the same - no hierarchical relationship.

Now...

I do have a Hierarchical relationship problem and from the research I've done on the sqlservercentral.com (I've read your article) you're an authority on the problem. Also, your previous post is most helpful - exactly what I'm looking for, I think. There's just one detail I need help with. My hierarchy will only ever be two levels deep - a parent and potentially one or more children. If I understand your last post properly, you are saying that there is a way to control the number of levels in the hierarchy. How would that be done.

Here's the problem description;

A coal deposit (a spatial area known to have coal resources) has a volumetric estimate (tonnes of coal in the area). Some coal deposits are so large that the estimate must be done in pieces (the method of calculating a volumetric is something approaching black magic) and then summed to a total (again, the summation is quite involved). My application needs to store the "total" volumetric estimate as well as any "pieces". Thus only two levels to my hierarchy.

I do not have to do any validation between the value of the total and the sum of it's pieces or anything like that (although if the sum of the pieces was greater than X% larger than the total it would probably be an error).

Thanks for any help you can provide.