Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Statement in Form Field Expression (Word 2003 SP2)

    I have a calculated form field (in a document protected for forms) that currently has this formula: =(-Int(-H3*Rate*1000))/1000

    I suspect there might be an easier way to get the multiplication to work, but this is the only way I could make it work, for some reason. H3 is also a calculation (odometer start and stop readings) and Rate is a bookmarked reimbursement rate. It all works fine.

    Now I need to make it work with two rates, current and the new one starting in 2007. So I need something like this:

    =If (MDate1 > "12/31/2006", (-Int(-H3*Rate*1000))/1000, -Int(-H3*Rate2*1000))/1000

    I know Word doesn't use commas in IF statements in a merge, but I can't find any documentation for how to do the statement in an expression in a text form field. Another glitch: The user enters a date into MDate1, but it is NOT a date field. It is regular text on which I run a macro to convert it to something that looks like a date, but is still a string value (i.e., they can enter 010606 and get 1/6/06).

    Is this possible? I tried using standard @ formatting options, but that just formats the string, doesn't convert it to a date. Is there a way to do this? My other option is to have two versions of the form and rely on the users to keep using the old one until they've processed all their 2006 charges. Many thanks!

    --Karyl

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    Calculations involving dates are quite complicated in Word, because unlike Excel it doesn't have built-in "knowledge" of dates, so you must do everything yourself. See macropod's Star Post <!post=Date Calculations in Word (2.50),249902>Date Calculations in Word (2.50)<!/post>.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    Hi Karyl,

    Try using a formula field - not a formfield and coding it as:
    {=INT(H3*1000*{IF{MDate1 @ yyyyMMdd}> 20061231 Rate1 Rate2})/1000}
    where the field braces (ie '{ }') are created in pairs via Ctrl-F9.

    As for your MDate1 formfield, is there any reason for not setting its properties to a date format? If you right-click the formfield and select 'properties, you can specify the 'Type' as a date and either:
    (a) select one of the available formats from the 'date format' dropdown, or
    ([img]/forums/images/smilies/cool.gif[/img] code your own format (eg 'd MMM yyyy') directly into the the dropdown.
    If you do this, you get the benefits of Word's inbuilt data validation and not having to do any string to date conversions via vba.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    As soon as you said, "Try using a formula field, not a form field," I hit myself on the side of the head. I can't imagine why that didn't occur to me! It works perfectly fine as a regular calculated field (and would have yesterday when I spent an hour trying to do the same thing within the Expression box). Thanks for pointing it out without adding the, "Duh."

    As for the date field, neither Word nor Excel seems able to interpret "020207" as a valid date. And the client wanted the option of letting users enter dates that way. They also wanted users to be able to enter dates USING delimiters if they chose to. So I'm using VBA to convert the values however they're entered. There were some tricky bits to it, one of which was using a string value instead of a date. When it is set up as a date field, Word tries to evaluate the string and convert it to a date BEFORE the VBA code runs, and it can't resolve the undelimited data. So it errors out before the code has a chance to run.

    The Excel version was tricky, also, since the code works fine on a string but errors when the cell is formatted as a date. And as soon as the code replaces 020207 with 02/02/07, Excel automatically changes the formatting of the cell to a date. So the code works fine the first time you run it, but fails if you come back later and change one date to another date. Other methods of intercepting the dates having failed, I finally just added code that runs whenever a user enters a cell containing a date, to un-format it back to General. A bit disconcerting, because the cell displays the serial value while you're in the cell, but I change it back when you either enter a new date or leave the cell. A tweaky work-around, but it works.

    Thanks, again!

    --Karyl

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    I found a way to make it work, thanks to Macropod's email and nudge in the right direction. But I appreciate the reference to his Star Post as well. I've downloaded it to keep handy for future reference. Dealing with dates in Word always seems to be problematic.

    --Karyl

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    Hi Karyl,

    Allowing users to input more or less anything in a field that's meant for date entries is a really bad idea. You give "020207" as an example. What happens if the user enters "20207" - is this to be interpreted as "2 02 07" or as "20 2 07"? And let's not forget the question of which century the '07' relates to!

    I'd strongly recommend going back to your client and arguing for dates to be input as "2 2 07" as a mnimum. Word will accept this as a valid date and it will often require no more keystrokes than the "020207" form. Personally, I'd argue for "2 2 2007" - an extra two keystrokes, but your client may be happy relying on Word to interpret the century according to Microsoft's rules.

    You can coerce Excel into showing a date as "020207" without vba, using cell formatting, but you do need vba to coerce Excel into interpreting the entry of such a string as a date. Bear in mind too that, no matter how the date appears, the underlying serial value for "020207" will equate to "02 02 2007". You say "the cell displays the serial value while you're in the cell". I'm not sure whether you're aware of this, but "020207" is not the serial value for "02 02 2007" - it's 39155.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    Well, these are sort of special-case users. They work in Finance and are used to entering six-digit dates all day long in the finance system. So it isn't a matter of the number of keystrokes, just the way they are accustomed to doing it. They're likely the only ones who will even attempt to enter the dates this way, and they're pretty good at doing it correctly.

    And the code does a pretty good job of interpreting the dates however they are entered and rejects anything that it can't resolve to a legitimate date value or if it falls outside the range of acceptable dates. Your 20207 would be correctly converted to 2/2/07. (The need to account for non-American date formats is not an issue for us, and correct century isn't a question, either, for this application.) The VBA only has a problem processing the dates if you don't use a six-digit entry format for November or December, when it uses January instead (11607 is seen as January 16, not November 6). But, again, these are folks who are accustomed to entering six-digit dates correctly. And the AP folks would catch the January-instead-of-November errors if the user didn't.

    The serial value I referred to is the 39155. When the user enters a value into any of the date cells in Excel, they become real dates. But I have to convert them back to text in order to run the code a second time in the same cell (if they go back to change a date). So if the user puts his cursor back into a cell that already has 2/2/07 in it, he suddenly sees it become 39155. As soon as he enters a new date or tabs out of the field, it changes back to 2/2/07 (or the new date). Users who are testing (in one of our larger offices) have not seemed to have a problem with it, and I suspect it seldom even happens to them. This is a fill-it-out and send-it-off kind of form, not something you return to.

    I couldn't get Excel to correctly format 020207. You can set up the formatting to M/d/yyyy, but entering 020207 gives you 4/28/1955, the corresponding date for serial value 20207. Maybe there's a method I didn't attempt. Please pass it on if you know of one.

    All that said, in general principal, I agree with you. It is not a good idea to give the users any chance to enter incorrect data. But, in this case, the code handles most of the issues and is probably as accurate as just letting users enter only dilimited dates would be, since there is no assurance in that case that they're entering the right dates, either. The best Word or Excel can do is tell the user if the entry isn't a valid date, and my code does that, too.

    Sorry for rambling on. I never should have started to reply so late at night! I ramble at the best of times, and this definitely isn't that!

    --Karyl

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF Statement in Form Field Expression (Word 2003 SP2)

    Hi Karyl,

    I don't have an alternative for Excel, though you could minimise screen flicker via
    Application.ScreenUpdating = False
    before the conversion and
    Application.ScreenUpdating = True
    after the conversion

    For Word, you can avoid the need for vba altogether if you code the formula field as:
    {=INT(H3*1000*{IF{QUOTE{MDate1 # "00'-'00'-'00"} @ yyyyMMdd}> 20061231 Rate1 Rate2})/1000}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •