If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi-field index
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! |
#2
|
|||
|
|||
Multi-field index
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" wrote in message ... 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! |
#3
|
|||
|
|||
Multi-field index
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" wrote: 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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|