Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

WHAT IS WRONG WITH THIS CODE?

Discussion in 'Information Technology' started by Glint, Nov 11, 2005.

Tags:
  1. Glint

    Glint
    Expand Collapse
    Guest

    Hi All,
    I put this code in my Textbox (named Balance1) and it works beautifully:
    =IIf([TMonth] Is Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    >= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).

    vDonationsDetails is a union query that combines DonationsDetails table with
    ExpensesDetails table. I want my form (CashBook) to display information on
    various accounts depending on the month a user picks.

    The problem is that I had to go round about when my original code was
    rejected as wrong syntax: =IIf([TMonth] Is
    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    yyyy") = Forms!CashBook!TMonth"),0)).
    I had intended that a user would simply enter TMonth as Month Year and the
    textbox would do the rest. But When I attempted to format the TDate field
    along this line, the syntax was rejected. Is it not allowed to nest a
    function within an aggregate function DSum? If it is allowed then what is the
    correct syntax?
    --
    Glint
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Try this, change the format to single quote

    IIf([TMonth] Is
    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    yyyy') = Forms!CashBook!TMonth"),0))

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Glint" wrote:

    > Hi All,
    > I put this code in my Textbox (named Balance1) and it works beautifully:
    > =IIf([TMonth] Is Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    > >= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).

    > vDonationsDetails is a union query that combines DonationsDetails table with
    > ExpensesDetails table. I want my form (CashBook) to display information on
    > various accounts depending on the month a user picks.
    >
    > The problem is that I had to go round about when my original code was
    > rejected as wrong syntax: =IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > yyyy") = Forms!CashBook!TMonth"),0)).
    > I had intended that a user would simply enter TMonth as Month Year and the
    > textbox would do the rest. But When I attempted to format the TDate field
    > along this line, the syntax was rejected. Is it not allowed to nest a
    > function within an aggregate function DSum? If it is allowed then what is the
    > correct syntax?
    > --
    > Glint
     
  4. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    A couple of things, if "Is Null" is working, great, but that is the syntax
    for a query. In VBA it is usually "IsNull([TMonth])". Since you're not
    getting an error about this, it is probably working.

    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > yyyy") = Forms!CashBook!TMonth"),0)).


    The problem you're running into here is that the " before mmmm is being
    treated as the end of the string, it is the next double quote after the one
    before Format. This places mmmm outside the string. To get double quotes to
    work inside a string that has been delimited with double quotes, you have to
    double them. Doing this would change the above to:

    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],""mmmm
    yyyy"") = Forms!CashBook!TMonth"),0)).


    --
    Wayne Morgan
    MS Access MVP


    "Glint" <Glint@discussions.microsoft.com> wrote in message
    news:E856B5C8-58BE-4594-A9E4-9668D2A536BB@microsoft.com...
    > Hi All,
    > I put this code in my Textbox (named Balance1) and it works beautifully:
    > =IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    >>= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).

    > vDonationsDetails is a union query that combines DonationsDetails table
    > with
    > ExpensesDetails table. I want my form (CashBook) to display information on
    > various accounts depending on the month a user picks.
    >
    > The problem is that I had to go round about when my original code was
    > rejected as wrong syntax: =IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > yyyy") = Forms!CashBook!TMonth"),0)).
    > I had intended that a user would simply enter TMonth as Month Year and the
    > textbox would do the rest. But When I attempted to format the TDate field
    > along this line, the syntax was rejected. Is it not allowed to nest a
    > function within an aggregate function DSum? If it is allowed then what is
    > the
    > correct syntax?
    > --
    > Glint
     
  5. Glint

    Glint
    Expand Collapse
    Guest

    I tried your suggestion: IIf([TMonth] Is
    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    yyyy') = Forms!CashBook!TMonth"),0))

    I even tried the double quotation mark "" in place of the mark ' . The
    syntax was accepted but the result was 0 when a fat figure was expected. Is
    there another way out?
    --
    Glint


    "Ofer" wrote:

    > Try this, change the format to single quote
    >
    > IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    > yyyy') = Forms!CashBook!TMonth"),0))
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Glint" wrote:
    >
    > > Hi All,
    > > I put this code in my Textbox (named Balance1) and it works beautifully:
    > > =IIf([TMonth] Is Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    > > >= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).

    > > vDonationsDetails is a union query that combines DonationsDetails table with
    > > ExpensesDetails table. I want my form (CashBook) to display information on
    > > various accounts depending on the month a user picks.
    > >
    > > The problem is that I had to go round about when my original code was
    > > rejected as wrong syntax: =IIf([TMonth] Is
    > > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > > yyyy") = Forms!CashBook!TMonth"),0)).
    > > I had intended that a user would simply enter TMonth as Month Year and the
    > > textbox would do the rest. But When I attempted to format the TDate field
    > > along this line, the syntax was rejected. Is it not allowed to nest a
    > > function within an aggregate function DSum? If it is allowed then what is the
    > > correct syntax?
    > > --
    > > Glint
     
  6. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Go to the Debug window (Ctrl+G) and enter

    ?DSum("[ItemAmount]", "vDonationsDetail", "Format([TDate],
    ""mmmmyyyy'')=Forms!CashBook!TMonth"),0)

    Press Enter and see what result is returned. Also enter

    ?Forms!CashBook!TMonth

    Press Enter and see what result is returned. The form CashBook must be open
    and you'll need a value in TMonth.

    --
    Wayne Morgan
    MS Access MVP


    "Glint" <Glint@discussions.microsoft.com> wrote in message
    news:460344DA-CFB1-4EED-BA68-AEDF8BCD80D5@microsoft.com...
    >I tried your suggestion: IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    > yyyy') = Forms!CashBook!TMonth"),0))
    >
    > I even tried the double quotation mark "" in place of the mark ' . The
    > syntax was accepted but the result was 0 when a fat figure was expected.
    > Is
    > there another way out?
    > --
    > Glint
    >
    >
    > "Ofer" wrote:
    >
    >> Try this, change the format to single quote
    >>
    >> IIf([TMonth] Is
    >> Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    >> yyyy') = Forms!CashBook!TMonth"),0))
    >>
    >> --
    >> The next line is only relevant to Microsoft''s web-based interface users.
    >> If I answered your question, please mark it as an answer. It''s useful to
    >> know that my answer was helpful
    >> HTH, good luck
    >>
    >>
    >> "Glint" wrote:
    >>
    >> > Hi All,
    >> > I put this code in my Textbox (named Balance1) and it works
    >> > beautifully:
    >> > =IIf([TMonth] Is
    >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    >> > >= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).
    >> > vDonationsDetails is a union query that combines DonationsDetails table
    >> > with
    >> > ExpensesDetails table. I want my form (CashBook) to display information
    >> > on
    >> > various accounts depending on the month a user picks.
    >> >
    >> > The problem is that I had to go round about when my original code was
    >> > rejected as wrong syntax: =IIf([TMonth] Is
    >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    >> > yyyy") = Forms!CashBook!TMonth"),0)).
    >> > I had intended that a user would simply enter TMonth as Month Year and
    >> > the
    >> > textbox would do the rest. But When I attempted to format the TDate
    >> > field
    >> > along this line, the syntax was rejected. Is it not allowed to nest a
    >> > function within an aggregate function DSum? If it is allowed then what
    >> > is the
    >> > correct syntax?
    >> > --
    >> > Glint
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try:


    IIf(IsNull([TMonth],Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([
    TDate],'mmmm
    yyyy') = '" & Forms!CashBook!TMonth & "'"),0))

    Exagerated for clarity, that last bit is

    "Format([TDate],'mmmm yyyy') = ' " & Forms!CashBook!TMonth & " ' "


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Glint" <Glint@discussions.microsoft.com> wrote in message
    news:460344DA-CFB1-4EED-BA68-AEDF8BCD80D5@microsoft.com...
    > I tried your suggestion: IIf([TMonth] Is
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    > yyyy') = Forms!CashBook!TMonth"),0))
    >
    > I even tried the double quotation mark "" in place of the mark ' . The
    > syntax was accepted but the result was 0 when a fat figure was expected.

    Is
    > there another way out?
    > --
    > Glint
    >
    >
    > "Ofer" wrote:
    >
    > > Try this, change the format to single quote
    > >
    > > IIf([TMonth] Is
    > >

    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],'mmmm
    > > yyyy') = Forms!CashBook!TMonth"),0))
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface

    users.
    > > If I answered your question, please mark it as an answer. It''s useful

    to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "Glint" wrote:
    > >
    > > > Hi All,
    > > > I put this code in my Textbox (named Balance1) and it works

    beautifully:
    > > > =IIf([TMonth] Is

    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    > > > >= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).
    > > > vDonationsDetails is a union query that combines DonationsDetails

    table with
    > > > ExpensesDetails table. I want my form (CashBook) to display

    information on
    > > > various accounts depending on the month a user picks.
    > > >
    > > > The problem is that I had to go round about when my original code was
    > > > rejected as wrong syntax: =IIf([TMonth] Is
    > > >

    Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > > > yyyy") = Forms!CashBook!TMonth"),0)).
    > > > I had intended that a user would simply enter TMonth as Month Year and

    the
    > > > textbox would do the rest. But When I attempted to format the TDate

    field
    > > > along this line, the syntax was rejected. Is it not allowed to nest a
    > > > function within an aggregate function DSum? If it is allowed then what

    is the
    > > > correct syntax?
    > > > --
    > > > Glint
     
  8. Glint

    Glint
    Expand Collapse
    Guest

    Wayne Morgan's suggestion to check the Debug Window for the value of TMonth
    revealed an interesting highlight: When TMonth is entered as oct/2005, the
    value displayed in the Debug Window is 10-1-05 (one single day instead of 31
    days that I had in mind)! I placed Input Mask "/" in the textbox to ensure
    that users enter a valid month value, and this is not likely ot have caused
    the problem.

    I think the problem I am having starts from the moment Access interpretes my
    month as a single day. So how do I re-write the code to get values of one
    month from the tables?
    --
    Glint


    "Wayne Morgan" wrote:

    > A couple of things, if "Is Null" is working, great, but that is the syntax
    > for a query. In VBA it is usually "IsNull([TMonth])". Since you're not
    > getting an error about this, it is probably working.
    >
    > > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > > yyyy") = Forms!CashBook!TMonth"),0)).

    >
    > The problem you're running into here is that the " before mmmm is being
    > treated as the end of the string, it is the next double quote after the one
    > before Format. This places mmmm outside the string. To get double quotes to
    > work inside a string that has been delimited with double quotes, you have to
    > double them. Doing this would change the above to:
    >
    > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],""mmmm
    > yyyy"") = Forms!CashBook!TMonth"),0)).
    >
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Glint" <Glint@discussions.microsoft.com> wrote in message
    > news:E856B5C8-58BE-4594-A9E4-9668D2A536BB@microsoft.com...
    > > Hi All,
    > > I put this code in my Textbox (named Balance1) and it works beautifully:
    > > =IIf([TMonth] Is
    > > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    > >>= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).

    > > vDonationsDetails is a union query that combines DonationsDetails table
    > > with
    > > ExpensesDetails table. I want my form (CashBook) to display information on
    > > various accounts depending on the month a user picks.
    > >
    > > The problem is that I had to go round about when my original code was
    > > rejected as wrong syntax: =IIf([TMonth] Is
    > > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > > yyyy") = Forms!CashBook!TMonth"),0)).
    > > I had intended that a user would simply enter TMonth as Month Year and the
    > > textbox would do the rest. But When I attempted to format the TDate field
    > > along this line, the syntax was rejected. Is it not allowed to nest a
    > > function within an aggregate function DSum? If it is allowed then what is
    > > the
    > > correct syntax?
    > > --
    > > Glint

    >
    >
    >
     
  9. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Yes, if Access thinks it's a date and all you put in is the month and year,
    it will interpret it as the first day of the month. In that case, just use
    the format command on it also:

    "Format([TDate],"mmmmyyyy") = Format(Forms!CashBook!TMonth,
    "mmmmyyyy")"),0))

    Try it with and without the doubled double quotes around the format
    "mmmmyyyy", with it being inside the Format() function, I'm not sure if it
    will be ignored as part of the function or if it will be caught as part of
    the string and cause a problem. This is something I usually just have to try
    each way until I get it to work. Also, you may have to concatenate in the
    part after the =, if so, that would look like

    "Format([TDate],""mmmmyyyy"") = """ & Format(Forms!CashBook!TMonth,
    "mmmmyyyy") & """),0))

    (This is how I think it will actually turn out.)

    --
    Wayne Morgan
    MS Access MVP


    "Glint" <Glint@discussions.microsoft.com> wrote in message
    news:C8D57538-B20E-49C0-A882-E9ED43318804@microsoft.com...
    > Wayne Morgan's suggestion to check the Debug Window for the value of
    > TMonth
    > revealed an interesting highlight: When TMonth is entered as oct/2005, the
    > value displayed in the Debug Window is 10-1-05 (one single day instead of
    > 31
    > days that I had in mind)! I placed Input Mask "/" in the textbox to ensure
    > that users enter a valid month value, and this is not likely ot have
    > caused
    > the problem.
    >
    > I think the problem I am having starts from the moment Access interpretes
    > my
    > month as a single day. So how do I re-write the code to get values of one
    > month from the tables?
    > --
    > Glint
    >
    >
    > "Wayne Morgan" wrote:
    >
    >> A couple of things, if "Is Null" is working, great, but that is the
    >> syntax
    >> for a query. In VBA it is usually "IsNull([TMonth])". Since you're not
    >> getting an error about this, it is probably working.
    >>
    >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    >> > yyyy") = Forms!CashBook!TMonth"),0)).

    >>
    >> The problem you're running into here is that the " before mmmm is being
    >> treated as the end of the string, it is the next double quote after the
    >> one
    >> before Format. This places mmmm outside the string. To get double quotes
    >> to
    >> work inside a string that has been delimited with double quotes, you have
    >> to
    >> double them. Doing this would change the above to:
    >>
    >> Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],""mmmm
    >> yyyy"") = Forms!CashBook!TMonth"),0)).
    >>
    >>
    >> --
    >> Wayne Morgan
    >> MS Access MVP
    >>
    >>
    >> "Glint" <Glint@discussions.microsoft.com> wrote in message
    >> news:E856B5C8-58BE-4594-A9E4-9668D2A536BB@microsoft.com...
    >> > Hi All,
    >> > I put this code in my Textbox (named Balance1) and it works
    >> > beautifully:
    >> > =IIf([TMonth] Is
    >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    >> >>= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).
    >> > vDonationsDetails is a union query that combines DonationsDetails table
    >> > with
    >> > ExpensesDetails table. I want my form (CashBook) to display information
    >> > on
    >> > various accounts depending on the month a user picks.
    >> >
    >> > The problem is that I had to go round about when my original code was
    >> > rejected as wrong syntax: =IIf([TMonth] Is
    >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    >> > yyyy") = Forms!CashBook!TMonth"),0)).
    >> > I had intended that a user would simply enter TMonth as Month Year and
    >> > the
    >> > textbox would do the rest. But When I attempted to format the TDate
    >> > field
    >> > along this line, the syntax was rejected. Is it not allowed to nest a
    >> > function within an aggregate function DSum? If it is allowed then what
    >> > is
    >> > the
    >> > correct syntax?
    >> > --
    >> > Glint

    >>
    >>
    >>
     
  10. Tino

    Tino
    Expand Collapse
    Guest

    in article eU8mz5v5FHA.2984@TK2MSFTNGP14.phx.gbl, Wayne Morgan at
    comprev_gothroughthenewsgroup@hotmail.com wrote on 11/11/05 3:28 PM:

    > Yes, if Access thinks it's a date and all you put in is the month and year,
    > it will interpret it as the first day of the month. In that case, just use
    > the format command on it also:
    >
    > "Format([TDate],"mmmmyyyy") = Format(Forms!CashBook!TMonth,
    > "mmmmyyyy")"),0))
    >
    > Try it with and without the doubled double quotes around the format
    > "mmmmyyyy", with it being inside the Format() function, I'm not sure if it
    > will be ignored as part of the function or if it will be caught as part of
    > the string and cause a problem. This is something I usually just have to try
    > each way until I get it to work. Also, you may have to concatenate in the
    > part after the =, if so, that would look like
    >
    > "Format([TDate],""mmmmyyyy"") = """ & Format(Forms!CashBook!TMonth,
    > "mmmmyyyy") & """),0))
    >
    > (This is how I think it will actually turn out.)

    You did it all wrong!
     
  11. Glint

    Glint
    Expand Collapse
    Guest

    Thanx Wayne,

    It worked beautifully as soon as I formatted the TMonth side of the equation
    as you suggested. It worked equally with double, double quotation marks ("")
    or a single, single quotation mark ('). I did not bother with concatenation.

    Grateful for your time and effort.
    --
    Glint


    "Wayne Morgan" wrote:

    > Yes, if Access thinks it's a date and all you put in is the month and year,
    > it will interpret it as the first day of the month. In that case, just use
    > the format command on it also:
    >
    > "Format([TDate],"mmmmyyyy") = Format(Forms!CashBook!TMonth,
    > "mmmmyyyy")"),0))
    >
    > Try it with and without the doubled double quotes around the format
    > "mmmmyyyy", with it being inside the Format() function, I'm not sure if it
    > will be ignored as part of the function or if it will be caught as part of
    > the string and cause a problem. This is something I usually just have to try
    > each way until I get it to work. Also, you may have to concatenate in the
    > part after the =, if so, that would look like
    >
    > "Format([TDate],""mmmmyyyy"") = """ & Format(Forms!CashBook!TMonth,
    > "mmmmyyyy") & """),0))
    >
    > (This is how I think it will actually turn out.)
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Glint" <Glint@discussions.microsoft.com> wrote in message
    > news:C8D57538-B20E-49C0-A882-E9ED43318804@microsoft.com...
    > > Wayne Morgan's suggestion to check the Debug Window for the value of
    > > TMonth
    > > revealed an interesting highlight: When TMonth is entered as oct/2005, the
    > > value displayed in the Debug Window is 10-1-05 (one single day instead of
    > > 31
    > > days that I had in mind)! I placed Input Mask "/" in the textbox to ensure
    > > that users enter a valid month value, and this is not likely ot have
    > > caused
    > > the problem.
    > >
    > > I think the problem I am having starts from the moment Access interpretes
    > > my
    > > month as a single day. So how do I re-write the code to get values of one
    > > month from the tables?
    > > --
    > > Glint
    > >
    > >
    > > "Wayne Morgan" wrote:
    > >
    > >> A couple of things, if "Is Null" is working, great, but that is the
    > >> syntax
    > >> for a query. In VBA it is usually "IsNull([TMonth])". Since you're not
    > >> getting an error about this, it is probably working.
    > >>
    > >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > >> > yyyy") = Forms!CashBook!TMonth"),0)).
    > >>
    > >> The problem you're running into here is that the " before mmmm is being
    > >> treated as the end of the string, it is the next double quote after the
    > >> one
    > >> before Format. This places mmmm outside the string. To get double quotes
    > >> to
    > >> work inside a string that has been delimited with double quotes, you have
    > >> to
    > >> double them. Doing this would change the above to:
    > >>
    > >> Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],""mmmm
    > >> yyyy"") = Forms!CashBook!TMonth"),0)).
    > >>
    > >>
    > >> --
    > >> Wayne Morgan
    > >> MS Access MVP
    > >>
    > >>
    > >> "Glint" <Glint@discussions.microsoft.com> wrote in message
    > >> news:E856B5C8-58BE-4594-A9E4-9668D2A536BB@microsoft.com...
    > >> > Hi All,
    > >> > I put this code in my Textbox (named Balance1) and it works
    > >> > beautifully:
    > >> > =IIf([TMonth] Is
    > >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","[TDate]
    > >> >>= Forms!CashBook!B4Date+1 And [TDate]<=Forms!CashBook!EndDate"),0)).
    > >> > vDonationsDetails is a union query that combines DonationsDetails table
    > >> > with
    > >> > ExpensesDetails table. I want my form (CashBook) to display information
    > >> > on
    > >> > various accounts depending on the month a user picks.
    > >> >
    > >> > The problem is that I had to go round about when my original code was
    > >> > rejected as wrong syntax: =IIf([TMonth] Is
    > >> > Null,Null,nz(DSum("[ItemAmount]","vDonationsDetail","Format([TDate],"mmmm
    > >> > yyyy") = Forms!CashBook!TMonth"),0)).
    > >> > I had intended that a user would simply enter TMonth as Month Year and
    > >> > the
    > >> > textbox would do the rest. But When I attempted to format the TDate
    > >> > field
    > >> > along this line, the syntax was rejected. Is it not allowed to nest a
    > >> > function within an aggregate function DSum? If it is allowed then what
    > >> > is
    > >> > the
    > >> > correct syntax?
    > >> > --
    > >> > Glint
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page