Complex (for me) checking values in fields to perform calcs in others

Hi all..

I am creating a Cash Flow Projection report in access that inspects
the "completed dates" of sheduled draws to calculate a remaining
balance, but I am having problems with it.

The idea is:
---------------------
Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then
Me!txtBalance = [MortgageAmount] -
[Draw1Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] <> Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount] - [Draw3Amount]

-----------------

I am sure I have butchered this, but this is what I need to do.  I get
"syntax error" "missing operator" errors.

Thanks..

0
ehorde
9/21/2007 5:31:19 PM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
633 Views

Similar Articles

[PageSpeed] 38

First, try using the isnull() function to check for Nulls, rather than = or
<>,
for example if isnull([Draw1CompletedDate]) then ...

Your code structure has to be either:

If condition1 then
  if condition2 then
    if condition3 then
    else
    endif
  endif
endif

or:

if condition1 then
elseif condition2 then
elseif condition3 then
else
endif

and they are not the same.  (note there is nothing after the 'then')

In the first structure, if condition1 fails, none of the others will be
checked.  If condition1 passes, then condition2 will be checked - if
condition2 fails, condition3 will not be checked.

In the second structure, if condition1 fails, then condition2 will be checked,
and if that fails, then condition3 etc.

Which you use depends on what you need to do.

Hope this helps

John


ehorde@hotmail.com wrote:
>Hi all..
>
>I am creating a Cash Flow Projection report in access that inspects
>the "completed dates" of sheduled draws to calculate a remaining
>balance, but I am having problems with it.
>
>The idea is:
>---------------------
>Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount]
>
>Else
>
>If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then
>Me!txtBalance = [MortgageAmount] -
>[Draw1Amount]
>
>Else
>
>If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
>[Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] -
>[Draw1Amount] - [Draw2Amount]
>
>Else
>
>If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
>[Draw3CompletedDate] <> Null then Me!txtBalance = [MortgageAmount] -
>[Draw1Amount] - [Draw2Amount] - [Draw3Amount]
>
>-----------------
>
>I am sure I have butchered this, but this is what I need to do.  I get
>"syntax error" "missing operator" errors.
>
>Thanks..

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200709/1

0
J_Goddard
9/21/2007 6:12:53 PM
Many thanks John!

You got me pointed in the right direction...

Final Code ended up being:

*******************************

Dim strMort As String

Dim strDraw1 As String
Dim strDraw2 As String
Dim strDraw3 As String
Dim strDraw4 As String
Dim strDraw5 As String

Dim strDraw1Date As String
Dim strDraw2Date As String
Dim strDraw3Date As String
Dim strDraw4Date As String
Dim strDraw5Date As String

Dim strBal As String

strMort = Me.[Mortgage]

If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 3])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 4])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 5])
End If

strDraw1 = Me.[Bank Draw 1 Amount]
strDraw2 = Me.[Bank Draw 2 Amount]
strDraw3 = Me.[Bank Draw 3 Amount]
strDraw4 = Me.[Bank Draw 4 Amount]
strDraw5 = Me.[Bank Draw 5 Amount]

If strDraw1Date = "" Then
    strBal = strMort
     ElseIf strDraw1Date <> "" & strDraw1Date = "" Then
     strBal = strMort - strDraw1
     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date =
"" Then
     strBal = strMort - strDraw1 - strDraw2
     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date = "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3
     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date = "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date <> "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
strDraw5
End If

Me.[Text127] = strBal

0
ehorde
9/21/2007 6:52:29 PM
Hi - 

I see a problem with that code.  

You have five separate If-then-else constructs, each of which sets
strDraw1Date to either blank or to  a value.  Therefore the initial value of
strDrawDate1 will depend on Me.[Actual Draw Date - Bank Draw 5] - and only
that.  I suspect you may forgotten to change strDraw1Date to strDraw2Date,
strDraw3Date etc in the second to 5th if-then-else blocks.

John




ehorde@hotmail.com wrote:
>Many thanks John!
>
>You got me pointed in the right direction...
>
>Final Code ended up being:
>
>*******************************
>
>Dim strMort As String
>
>Dim strDraw1 As String
>Dim strDraw2 As String
>Dim strDraw3 As String
>Dim strDraw4 As String
>Dim strDraw5 As String
>
>Dim strDraw1Date As String
>Dim strDraw2Date As String
>Dim strDraw3Date As String
>Dim strDraw4Date As String
>Dim strDraw5Date As String
>
>Dim strBal As String
>
>strMort = Me.[Mortgage]
>
>If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
> strDraw1Date = ""
> Else
> strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
>End If
>
>If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
> strDraw1Date = ""
> Else
> strDraw1Date = (Me.[Actual Draw Date - Bank Draw 2])
>End If
>
>If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
> strDraw1Date = ""
> Else
> strDraw1Date = (Me.[Actual Draw Date - Bank Draw 3])
>End If
>
>If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
> strDraw1Date = ""
> Else
> strDraw1Date = (Me.[Actual Draw Date - Bank Draw 4])
>End If
>
>If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
> strDraw1Date = ""
> Else
> strDraw1Date = (Me.[Actual Draw Date - Bank Draw 5])
>End If
>
>strDraw1 = Me.[Bank Draw 1 Amount]
>strDraw2 = Me.[Bank Draw 2 Amount]
>strDraw3 = Me.[Bank Draw 3 Amount]
>strDraw4 = Me.[Bank Draw 4 Amount]
>strDraw5 = Me.[Bank Draw 5 Amount]
>
>If strDraw1Date = "" Then
>    strBal = strMort
>     ElseIf strDraw1Date <> "" & strDraw1Date = "" Then
>     strBal = strMort - strDraw1
>     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date =
>"" Then
>     strBal = strMort - strDraw1 - strDraw2
>     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
>"" & strDraw4Date = "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3
>     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
>"" & strDraw4Date <> "" & strDraw5Date = "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
>     ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
>"" & strDraw4Date <> "" & strDraw5Date <> "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
>strDraw5
>End If
>
>Me.[Text127] = strBal

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200709/1

0
J_Goddard
9/21/2007 7:34:12 PM
You are right, right after my last post I had to correct those
typo's.  I'm still having another problem with the code though, not
quite sure what it is or how to describe it yet.

On Sep 21, 3:34 pm, "J_Goddard via AccessMonster.com" <u37558@uwe>
wrote:
> Hi -
>
> I see a problem with that code.  
>
> You have five separate If-then-else constructs, each of which sets
> strDraw1Date to either blank or to  a value.  Therefore the initial value of
> strDrawDate1 will depend on Me.[Actual Draw Date - Bank Draw 5] - and only
> that.  I suspect you may forgotten to change strDraw1Date to strDraw2Date,
> strDraw3Date etc in the second to 5th if-then-else blocks.
>
> John
>

0
ehorde
9/21/2007 7:51:53 PM
I getting Type Mismatches here.. (arrow)

---------------------
If strDraw1Date = "" Then
     strBal = strMort
->  ElseIf strDraw1Date <> "" & strDraw2Date = "" Then
     strBal = strMort - strDraw1
     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date =
"" Then
     strBal = strMort - strDraw1 - strDraw2
     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date = "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3
     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date = "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date <> "" Then
     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
strDraw5
End If

0
ehorde
9/21/2007 7:58:43 PM
While it would be nice, you can't use "&" as a shorthand for "AND", so your
elseif becomes:

ElseIf strDraw1Date <> ""  and  strDraw2Date = "" Then

and the same for the others, of course.

John



ehorde@hotmail.com wrote:
>I getting Type Mismatches here.. (arrow)
>
>---------------------
>If strDraw1Date = "" Then
>     strBal = strMort
>->  ElseIf strDraw1Date <> "" & strDraw2Date = "" Then
>     strBal = strMort - strDraw1
>     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date =
>"" Then
>     strBal = strMort - strDraw1 - strDraw2
>     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
>"" & strDraw4Date = "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3
>     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
>"" & strDraw4Date <> "" & strDraw5Date = "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
>     ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
>"" & strDraw4Date <> "" & strDraw5Date <> "" Then
>     strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
>strDraw5
>End If

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200709/1

0
J_Goddard
9/21/2007 8:24:55 PM
Sharing final report's code.  Many thanks to John for helping me out
with this one!

Mahalo...Eric

------------------------------------------
Option Compare Database

Private Sub Report_Activate()

' String Setup

Dim strMort As String
Dim strPool As String

Dim strDraw1 As String
Dim strDraw2 As String
Dim strDraw3 As String
Dim strDraw4 As String
Dim strDraw5 As String

Dim strPoolDraw1 As String
Dim strPoolDraw2 As String
Dim strPoolDraw3 As String

Dim strDraw1Date As String
Dim strDraw2Date As String
Dim strDraw3Date As String
Dim strDraw4Date As String
Dim strDraw5Date As String

Dim strPoolDraw1Date As String
Dim strPoolDraw2Date As String
Dim strPoolDraw3Date As String

Dim strMortBal As String
Dim strPoolBal As String

strMortBal = ""
strPoolBal = ""

If IsNull(Me.[Mortgage]) Then
 strMort = ""
 Else
 strMort = (Me.[Mortgage])
End If

If IsNull(Me.[Pool Loan]) Then
 strPool = ""
 Else
 strPool = (Me.[Pool Loan])
End If

'Mortgage Draw Section

If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
 strDraw1Date = ""
 Else
 strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
 strDraw2Date = ""
 Else
 strDraw2Date = (Me.[Actual Draw Date - Bank Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
 strDraw3Date = ""
 Else
 strDraw3Date = (Me.[Actual Draw Date - Bank Draw 3])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
 strDraw4Date = ""
 Else
 strDraw4Date = (Me.[Actual Draw Date - Bank Draw 4])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
 strDraw5Date = ""
 Else
 strDraw5Date = (Me.[Actual Draw Date - Bank Draw 5])
End If

strDraw1 = Me.[Bank Draw 1 Amount]
strDraw2 = Me.[Bank Draw 2 Amount]
strDraw3 = Me.[Bank Draw 3 Amount]
strDraw4 = Me.[Bank Draw 4 Amount]
strDraw5 = Me.[Bank Draw 5 Amount]

If strDraw1Date = "" Then
     strMortBal = strMort
     Else
        If strDraw1Date <> "" And strDraw2Date = "" Then
        strMortBal = strMort - strDraw1
        Else
            If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date = "" Then
            strMortBal = strMort - strDraw1 - strDraw2
            Else
                If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date <> "" And strDraw4Date = "" Then
                strMortBal = strMort - strDraw1 - strDraw2 - strDraw3
                Else
                    If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date <> "" And strDraw4Date <> "" And strDraw5Date = "" Then
                    strMortBal = strMort - strDraw1 - strDraw2 -
strDraw3 - strDraw4
                    Else
                        If strDraw1Date <> "" And strDraw2Date <> ""
And strDraw3Date <> "" And strDraw4Date <> "" And strDraw5Date <> ""
Then
                        strMortBal = strMort - strDraw1 - strDraw2 -
strDraw3 - strDraw4 - strDraw5
                        End If
                    End If
                End If
            End If
        End If
    End If

'Pool Draw Section

If IsNull(Me.[Actual Draw Date - Pool Draw 1]) Then
 strPoolDraw1Date = ""
 Else
 strPoolDraw1Date = (Me.[Actual Draw Date - Pool Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Pool Draw 2]) Then
 strPoolDraw2Date = ""
 Else
 strPoolDraw2Date = (Me.[Actual Draw Date - Pool Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Pool Draw 3]) Then
 strPoolDraw3Date = ""
 Else
 strPoolDraw3Date = (Me.[Actual Draw Date - Pool Draw 3])
End If

If IsNull(Me.[Draw Amount - Pool Draw 1]) Then
 strPoolDraw1 = ""
 Else
 strPoolDraw1 = (Me.[Draw Amount - Pool Draw 1])
End If

If IsNull(Me.[Draw Amount - Pool Draw 2]) Then
 strPoolDraw2 = ""
 Else
 strPoolDraw2 = (Me.[Draw Amount - Pool Draw 2])
End If

If IsNull(Me.[Draw Amount - Pool Draw 3]) Then
 strPoolDraw3 = ""
 Else
 strPoolDraw3 = (Me.[Draw Amount - Pool Draw 3])
End If

If strPoolDraw1Date = "" Then
     strPoolBal = strPool
     Else
        If strPoolDraw1Date <> "" And strPoolDraw2Date = "" Then
        strPoolBal = strPool - strPoolDraw1
        Else
            If strPoolDraw1Date <> "" And strPoolDraw2Date <> "" And
strPoolDraw3Date = "" Then
            strPoolBal = strPool - strPoolDraw1 - strPoolDraw2
            Else
                If strDraw1PoolDate <> "" And strPoolDraw2Date <> ""
And strPoolDraw3Date <> "" Then
                strPoolBal = strPool - strPoolDraw1 - strPoolDraw2 -
strPoolDraw3
                End If
            End If
        End If
    End If


Me.[Text127] = Format$(strMortBal, "currency")
Me.[Text146] = Format$(strPoolBal, "currency")


End Sub




0
ehorde
9/24/2007 3:10:02 PM
Reply:

Similar Artilces:

cmbo box on subform fills fields on form when chkbx is yes
I have a multi-tabbed form that details bid data ranging from pricing to contact info to departmental notes. Page two has a subform listing general contractors to whom we bid. One bid may have 10-15 contractors listed. When a purchase order is issued from one of these contractors I select a checkbox indicating that GC. I would like the opening page on this tabbed form to show the address and contact info for that GC when the checkbox is yes. Is this possible? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200803/1 On Mar 17, 3:04=A0p...

No content in message field anymore
For the last two days when I open my email client I see no content in the message field. Each email is blank with an attachment paperclip in the upper right hand corner. When I click the paperclip I have the choice of a txt file (which will not open up) or an htm file which will open. This is driving me crazy and I don't want to get rid of windows mail...any suggestions on how to fix this? Which antivirus are you running? Some of them cause problems with Windows Mail over time. =20 Try running the various repair functions in the WMUtil program: http://www.oehelp.com/...

Calculated Field in form to table
Can you take a calculated field from a form and pull it in to a table?? ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Automatically filling in "full name" field
I would like to add the "fullname" field to the Contact form. Is there someway to make this field automatically fill in with the information from "firstname" + "middlename" + "lastname" + "suffix"? If this can be automatically filled in, could the field later be manually changed? You will find that if you add the fullname field to the form, it will be greyed out, you can't type into it. It will be populated after you create a new contact and enter the first and last names and save the contact. And it will change if you change the f...

Field Options and Combo Lists?
Wondering if anyone knows how to make this work. I have two fields in a form, both Combo Boxes. I want the Value List in the first box to determine what the Value List in the second box will be. For example, if the first box is all State Names, if you select California, the second box provides all of the City names for that state. However, if you change the first box to Utah, the second box provides all the City names for Utah. In the afterupdate event of the states combobox write code to adjust the rowsource of the city combobox. Something like: Me.combo_STATE.RowSource = &quo...

Grab a value from a label to put for critera for attached query
Let me give a little background of table/query structure. I have a query that pulls jobs due Today-now() whose frequency is for today. I have table with the following fields. Job#, Location(ie.what Plant & Floor), Room(ie. mens washroom, storage room, etc.), job Desc., Frequency, est. time, resp.. This table ties in to create a query with another table - Cleaning(when the jobs are actually carried out) Cleaningid, Date Done, Who did the job) and grabs the other fields from the other table. Here goes what I need to get: The people on the floor will have a MAP of the floor ( Form)...

Lowest value in a selection of data
Hi What formula would I use to find the lowest value in a selection of data i.e. B49:E52. Much Appreciated Brian =MIN(B49:E52) -- Gary''s Student - gsnu2007a Jumping in here... Is it possible to know the address of the cell containing the minimum value? John. You can use the MATCH function to locate the (relative) position, but if you have more than one value which are minima then it will only find the first. Pete On Nov 16, 7:23 pm, John Google <JohnGoo...@hotmail.co.uk> wrote: > Jumping in here... > > Is it possible to know the address of the cell containin...

Spell check error
Every time I send a message I now get an error that reads.. "error occurred while checking spell check". No spell check is peformed. Outlook 2000 This behavior can occur under several circumstances. - The Microsoft Office shared Proofing Tools are not installed. - The spell checking features are not configured properly. - The insertion point is not inside the body of the message when you perform the spell check. Please refer to the Knowledge Base article ID: 241485.KB.EN-US for more information on how to resolve this issue by copying and pasting the link below into your ...

Excluding Duplicates in One Field
Hi, I'm stuck. I have two fields, fName and fEmail. Many of the fEmail fields have duplicates because often people in the same house use the same e-mail address. I need to query the data so the result does not contain the duplicate e-mail addresses. For some reason, "unique values" still pulls up the duplicates. I know if I exclude one of the duplicate e-mail addresses, then I also end up exluding the name in the same record, but so long as the output has at least one of the names, along with every record for which there is a unique e-mail address...

Multi-field primary key, no dupes
I can select multiple fields and make a primary key indexed with no duplicates. But I just realized my criteria for "no dupes" holds only if another field is empty (no value). For example, if my primary key is: Shirts-Mens-Style102-Large-Green-05Jan08 then I can't enter another order for the same product on the same day. That's good - unless the order's been shipped and another one is needed. So no duplicates, but only as long as the ShipDate field for that record is empty. Can this be done easily? Or should I look into a different way to prevent duplicates? Ed ...

Adding Values From Different Tabs
Is there a way to add values from different tabs on the same spreadsheet? For example A2 from tab 1 and A2 from tab 2 -- Flipkid2 ------------------------------------------------------------------------ Flipkid2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17984 View this thread: http://www.excelforum.com/showthread.php?threadid=398184 Assuming the name on Tab 1 is Sheet1 and Tab 2 is Sheet2, use thi formula: =Sheet1!A2+Sheet2!A2 note the name of the sheet is followed by "!" to indicate it is a shee name. HTH Bruc -- swatsp0 ------------...

Change order of fields in a report to a custom arrangement
In a report, I want to list sections in a certain way that is not alphabetical or numerical. Is there a way to do this? Curerntly, it is alphabetical as below: **Staff Section** Chief of Staff Command Counsel G1 G2 G3 Public Affairs Special Staff ** I would like for the fields to be organized as below: ***Staff Section*** G1 G2 G3 Chief of Staff Command Counsel Special Staff Public Affairs Thanks! Glen Try going to Sorting and Grouping. It is one of your buttons on the command bar. You should be able to set it anyway you wish. -- Milton Purdy ACCES...

Checking which fields changed
Hi Is it possible to identify individual fields changed by a user during editing on a form before or immediately after the form is saved? Thanks Regards On May 9, 4:25 pm, "John" <J...@nospam.infovis.co.uk> wrote: > Hi > > Is it possible to identify individual fields changed by a user during > editing on a form before or immediately after the form is saved? > > Thanks > > Regards I assume you mean what data was changed when you save the record? If so look at the OldValue property of your text boxes etc. Take a look at what Allen Browne has at ...

Display only duplicate values and delete UNIQUE Items
All I have a very large list of data and on a monthly basis i need to display only the duplicate items in a spreadsheet. I would like to do this in VBA and then run it as a macro on the spreadsheet. Alot of the sites that i have seen only show how to removed the duplicates. Excel 2007 has a function which removed all duplicates but so far i have found nothing that only displays the duplciates.... any ideas anyone? Assuming that the field you use to determine uniqueness is column A, you can put this formula in a helper column: =3DIF(COUNTIF(A:A,A2)>1,"Duplicate","Unique&...

Ending balance doesn't equal market value
I am using MS Money 2004. In my portfolio the Contributions ($) Market Value amount is showing a negative amount, but Today's Balance shows a zero Ending Balance. How do I get them to both show tha same number? In microsoft.public.money, Eggman wrote: >I am using MS Money 2004. In my portfolio the Contributions ($) Market Value >amount is showing a negative amount, but Today's Balance shows a zero Ending >Balance. How do I get them to both show tha same number? Try File->RepairMoneyFile->QuickFileRepair Look for future transactions. ...

OWA
OWA text field is always 'grayed out' - on new messages and replies. Small "x" button in upper left of text field seems to have no functionality. Subject field is OK. Bummer - do you have a question? If yes, then try asking it in an Exchange news group as Outlook Web Access is a function of Exchange, not Outlook. Microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted w...

Complex IF, more than 7 nests
I have a test that needs to run against 5 pair of data (total number of 25 combinations) using IF and AND. The TRUE result of the IF statement will perform an array calculation. The problem is that Excel limits the number of nested IF statements to seven. How would you go about doing this? I'm sure there is a more appropriate, powerful way that I'm just not aware of as an intermediate user. Example: Task phrased in plain English: Step 1: Look at value for This Week's Incentive Bands, New Contract to determine which array to use for "New Incentives" calculation. I'...

Deleting system locked fields
Does anyone know how I can get rid of the "Is revenue system calculated?" field on the Opportunities form? We always have user-provided values, and therefore have no need for this field, but I can't delete it. Anyone know how I can get around this? You can not delete those fields. But you can change the default value to "user provided". WIth some javascipt you should be able to hide it for the users. HTH, Frederic - Travi@ta www.microsoft-dynamics-crm.eu WiLLerZ schreef: > Does anyone know how I can get rid of the "Is revenue system > calculated?"...

DTM: USB-IF test certification ID check
MSDN Document "USB-IF Test Certification ID Check" indicates the TESTID be set to zero. When I run this test in the DTM, the log reports an error: "No valid USB IF test ID has been entered." I am testing with WLK 1.5. There is some buzz on the Internet that WinQual has a method by which to generate a Test ID that works with WLK 1.5, but I can find nothing helpful on their site. Any ideas? Thanks dmm dmm <dmm@discussions.microsoft.com> wrote: > >MSDN Document "USB-IF Test Certification ID Check" indicates the TESTID be >set ...

How to split a field into 2 fields?
I imported a spreadsheet from Excel and would like to split one field into two fields. Many of the records in Field1 have a note in parenthesis ( ). If a record has anything contained in parenthesis, I would like to move that data to Field2. What's the best way to accomplish this? To get the word inside the parenthesis try something like SecondWord: IIf(InStr([MyLeeter],"("),Mid([MyLeeter],InStr([MyLeeter],"(")+1,Len([MyLeeter])-InStr([MyLeeter],"(")-1),"") FirstWord: IIf(InStr([MyLeeter],"("),Left([MyLeeter],InStr([MyLeeter],&q...

increment a value by 1
How do I increment, automatically, a cell by 1 if a condition is met? If one of my cells reaches 10k I want another cell to increment by 1. Can anyone help me if this function can be done? Thank you, Ron Do you want the increment only when it changes from below 10K to above 10K, or anytime is is above 10K? -- Gary's Student "Workshops" wrote: > How do I increment, automatically, a cell by 1 if a condition is met? If one > of my cells reaches 10k I want another cell to increment by 1. Can anyone > help me if this function can be done? > Thank you, Ron Increm...

Exclude like values with a JOIN?
I have the following query: SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD FROM dbo.qry_UV_CARS_PlateX_Valued RIGHT OUTER JOIN dbo.qry_UV_CARS_PlateX_ShouldBeValued ON dbo.qry_UV_CARS_PlateX_Valued.Yl_iD = dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL) And I have another secondary table containing the same field as Yl_iD. What I want to do is have my main query exclude all the Yl_iD values that are in my secondary table. What JOIN and criteria will do this? Many thanks.....Jason WHERE Yl_iD N...

Adding Fields 02-14-06
Is there a way to add a picklist programmatically ? If there is, a sample code would be greatly appriciated. Thank You in advance ...

VBC 'Contains' Check
Apologies gain, still trying to hack this coders VBA as a non-coder so I can fix as he is AWOL. Anyway part of the code was supposed to see if a cell contained specific text but as I test it it only works on a complete match. This is the line of code: ElseIf InStr(UCase(CStr(wsCert.Cells(targetCertRowNumber, 1).Value)), UCase("TEXT")) And Len(CStr(wsCert.Cells(targetCertRowNumber, 2).Value)) = 0 Then Do I need to change something so it is finding "TEXT Anything" as right now it is just finding "TEST" Thanks in advance Hi, Because you...