WHAT IS WRONG WITH THIS CODE?

[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?

 

[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

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?

 

[Wayne Morgan]

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

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?

 

[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

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

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?

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

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?

 

[Wayne Morgan]

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

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

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

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?

 

[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

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

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?

 

[Wayne Morgan]

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

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

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

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?

 

[Tino]

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!

 

[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

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

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

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


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?