Multi-field index

[lmcc007]

I have a tblPhoneNumber table with five fields:

PhoneNumID
PhoneTypeID
PhoneNumber
PhoneExt
CompanyID

I created two indexes using the Indexes button on the Show/Hide ribbon.

PhoneTypeIndex includes: PhoneTypeID and CompanyID. Which works--I can not
create the same phone type for a company.

PhoneNumberIndex includes: PhoneNumber and PhoneExt. It does not work. I
only want the phone number to be repeated if the phone extension is different.

What am I doing wrong?

Thanks!

 

[Graham Mandeno]

If a value in an index field is Null, then that record is omitted from the
index. This seems to happen whether or not the IgnoreNulls property is set
for the index.

If your PhoneExt field is text, then set its properties Required and
AllowZeroLength to True. Then an "empty" field will actually contain a
zero-length string, instead of Null, and the index will function as
required.

If PhoneExt is numeric then one workaround would be to make it Required with
a DefaultValue of 0, then treat 0 as a special case meaning "no extension".

 

[lmcc007]

Graham Mandeno,

Thank you so much! I knew it would be something very simple; my mine just
could not figure it out. I've been working on this since last Friday. I'm
just so happy it is working. Thanks!

If a value in an index field is Null, then that record is omitted from the
index. This seems to happen whether or not the IgnoreNulls property is set
for the index.

If your PhoneExt field is text, then set its properties Required and
AllowZeroLength to True. Then an "empty" field will actually contain a
zero-length string, instead of Null, and the index will function as
required.

If PhoneExt is numeric then one workaround would be to make it Required with
a DefaultValue of 0, then treat 0 as a special case meaning "no extension".

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a tblPhoneNumber table with five fields:

PhoneNumID
PhoneTypeID
PhoneNumber
PhoneExt
CompanyID

I created two indexes using the Indexes button on the Show/Hide ribbon.

PhoneTypeIndex includes: PhoneTypeID and CompanyID. Which works--I can
not
create the same phone type for a company.

PhoneNumberIndex includes: PhoneNumber and PhoneExt. It does not work.
I
only want the phone number to be repeated if the phone extension is
different.

What am I doing wrong?

Thanks!