Got the custom form to work -- now #error?

[Carol Steinel]

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts at a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).

I swear, I swear, I swear, Sue Mosher, that I purchased and read your book.


Thanks for any help, even simple redirection to a website for further
understanding, or a terse admonition that I should move immediately to
VBScript (I have the books, and I'm not afraid to use them, if it will solve
my problem -- but someone on this forum suggested I could solve my problem
through formula fields, and I've been working on it -- as you see).
Thanks,
Carol Steinel

 

[Sue Mosher [MVP]]

What's probably happening is that the converted items actually have an empty value for that date field. If that's true, then what you need to do is add data, as you found in your own testing. (Well done!)

Try using a table view grouped on the Hosted Date field. I bet you'll see two groups that say "None." My Drag all the contacts from one to the other or vice versa until you have all those None contacts in one group.

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts at a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).

 

[Carol Steinel]

Hi Sue -- Tried a drag from the ones showing "#error" to the ones showing
correct, it showed "updating contacts", but no change -- still showing up
with two groups. Is "My Drag" something I don't know about that would alter
this.

Thanks in advance for your help.

Carol


What's probably happening is that the converted items actually have an empty
value for that date field. If that's true, then what you need to do is add
data, as you found in your own testing. (Well done!)

Try using a table view grouped on the Hosted Date field. I bet you'll see
two groups that say "None." My Drag all the contacts from one to the other
or vice versa until you have all those None contacts in one group.

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts at a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).

 

[Carol Steinel]

Something else strange I have just noticed. When I looked at the ten new
contacts I'd added (which don't return the error), the Date field "Hosted
Date" contains January 1, 2001 -- Why? This seems to be what Outlook enters
for all new contacts.

Thanks
Carol


What's probably happening is that the converted items actually have an empty
value for that date field. If that's true, then what you need to do is add
data, as you found in your own testing. (Well done!)

Try using a table view grouped on the Hosted Date field. I bet you'll see
two groups that say "None." My Drag all the contacts from one to the other
or vice versa until you have all those None contacts in one group.

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts at a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).

 

[Carol Steinel]

I do see, however, that those new contacts (with 1/1/01 entered by default)
_can_ be dragged to the working group of "none" and they successfully change
to "none" -- but the old ones won't drag and change.

Hmmm -- and thanks so much for your help
Carol


What's probably happening is that the converted items actually have an empty
value for that date field. If that's true, then what you need to do is add
data, as you found in your own testing. (Well done!)

Try using a table view grouped on the Hosted Date field. I bet you'll see
two groups that say "None." My Drag all the contacts from one to the other
or vice versa until you have all those None contacts in one group.

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts at a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).

 

[Sue Mosher [MVP]]

Then you may need to open the old ones and add the data manually.

I do see, however, that those new contacts (with 1/1/01 entered by default)
_can_ be dragged to the working group of "none" and they successfully change
to "none" -- but the old ones won't drag and change.

Hmmm -- and thanks so much for your help
Carol


What's probably happening is that the converted items actually have an empty
value for that date field. If that's true, then what you need to do is add
data, as you found in your own testing. (Well done!)

Try using a table view grouped on the Hosted Date field. I bet you'll see
two groups that say "None." My Drag all the contacts from one to the other
or vice versa until you have all those None contacts in one group.

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

To all you hardworking MVPs -- yes, I have done a search (or twenty). I
am a baby Outlook form programmer and beg your assistance.

Using Outlook 2K on Win XP. I have written a custom contacts form with a
formula field that works. I have published the form and assigned it to my
contacts folder as the default post form. I have changed old forms to the
new one using the add-in.

Formula field "Pricing" is based on a series of other formula fields, and
works fine in any new contact.

However, when I open the old contacts that were migrated using the add-in,
the formula field "Pricing" shows #error. If I go into the first field
(Hosted Date -- see below) which initiates the calculations in these old
contacts and reenter "none", they work after that. However, there are MANY
of these, and I'm seeking a way to resolve them without opening every one
and changing this field manually.

This is what I was trying to ascertain:
1. Did they host in the past year?
2. If they didn't, are they a member of the club? (No end to club
membership)
3. What pricing do they qualify for, based on answers from #1, and #2?

Here are the formulas, which involve four different fields, all of which
(but one) I want to keep as individual fields in order to give me specific
information and flexibility in views:

The first field is a simple date field that shows the date (if any), that
the person hosted an event, it's called "Hosted Date". That's where I enter
the data which triggers the rest. In a new contact, it's automatically
"none" and everything else seems to work fine. In the migrated contacts,
even though this fields says "none", it returns "#error". If I go into
"Hosted Date" on a migrated contact and enter "none", it works after that.

The second field, a formula field Called "Host End" determines if the Hosted
Date happened in the last year, otherwise, it says it's been 365 days:
IIf( [Hosted Date] <>"None" ,DateDiff( "d" , [Hosted Date], Now() ), 365)
This field is not critical, but I couldn't figure out how to get the next
two formulas to work without adding it.

The third (a formula) field called "Host" returns a value telling me whether
they hosted within the last 365 days:
Iif([Host End]<365, -1 , 0)
I want to keep this field, so I see a simple check box about whether they've
hosted in the last year.

The third field "Price" calculates the rate they are charged, based on data
in a couple of fields:
IIf([Host]= -1, "Rate A", Iif ( [Club Membership Started] <> "None", "Rate
B", "Rate C"))
I want this field to show one of three rates. In the migrated records, this
field returns "#error" until I open the form and delete "none" in the
"Hosted Date" and replace it with "none". Then it works. What gives?

I KNOW my solution is inelegant, but it was my first stab at returning all
the data that I need to access several items of info about my contacts

at

a
glance. Sometimes I'm searching access to their rate, sometimes simply for
their status as hosts or club members (for sending specific email, etc.).