Multi-field index

L

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!
 
G

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".
 
L

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!

Graham Mandeno said:
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

lmcc007 said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combo box issue 22
Multi-Field Primary Key 19
DLookup 7
Select Query - Adding New record 3
Data entry for a table from a form 2
Data entry for a table from a form 1
Data Entry VBA 4
Data entry VBA 1

Top