SEE THIS DATE

[Guest]

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?

 

[Guest]

Just a few syntax errors. This assumes [DonationItem] is a text field. If
it is not, then leave out the single quotes.

=IIf(IsNull([TMonth]), Null,
nz(DSum("[ItemAmount]","vBalances","[DonationItem]= '" &
Forms!CashBook!RevenueID & "' And [TDate] = #" & Forms!CashBook!TMonth &
"#),0))"

 

[Guest]

Thanx.
Interestingly, your suggestion worked for the textbox I use to get the
b/forward: with the syntax ...."[TDate]<#" & Forms!CashBook!TMonth & "#")...
But it still returned 0 when the less than sign (<) was changed to the equal
sign (=). I wonder why.
Please help further
--
Glint

Just a few syntax errors. This assumes [DonationItem] is a text field. If
it is not, then leave out the single quotes.

=IIf(IsNull([TMonth]), Null,
nz(DSum("[ItemAmount]","vBalances","[DonationItem]= '" &
Forms!CashBook!RevenueID & "' And [TDate] = #" & Forms!CashBook!TMonth &
"#),0))"

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?

 

[Guest]

The < will return anything that is prior to [TDate], = will return only
values where it is equal to [TDate]. Can you view your data to see if there
is anything there for the specific date you are entering? You could also try
using = and entering a date you know should return a value.

Thanx.
Interestingly, your suggestion worked for the textbox I use to get the
b/forward: with the syntax ...."[TDate]<#" & Forms!CashBook!TMonth & "#")...
But it still returned 0 when the less than sign (<) was changed to the equal
sign (=). I wonder why.
Please help further
--
Glint

Just a few syntax errors. This assumes [DonationItem] is a text field. If
it is not, then leave out the single quotes.

=IIf(IsNull([TMonth]), Null,
nz(DSum("[ItemAmount]","vBalances","[DonationItem]= '" &
Forms!CashBook!RevenueID & "' And [TDate] = #" & Forms!CashBook!TMonth &
"#),0))"

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?

 

[Guest]

It appears Access sees TMonth as a full date when I enter Oct 1 2005, and as
a truncated date when I enter Oct 2005; either way it displays October 2005
in TMonth but the figures in Balance1 textbox are very different. It returns
expected figures for a date of October 1, 2005; but it treats October 2005 as
a null, hence the 0 it returns.
I can see some end-users will enter a full date when I just want him to
input Month and Year, and will get disastrous results.
Is there a simple way to parse out whatever is entered into TMonth to make
it appear as any full date in that month?
--
Glint

Just a few syntax errors. This assumes [DonationItem] is a text field. If
it is not, then leave out the single quotes.

=IIf(IsNull([TMonth]), Null,
nz(DSum("[ItemAmount]","vBalances","[DonationItem]= '" &
Forms!CashBook!RevenueID & "' And [TDate] = #" & Forms!CashBook!TMonth &
"#),0))"

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?

 

[Guest]

If you are looking for Oct 2005, then just use the format function to pass
the date to the query. That way it will not matter how the user enters it:

Format(Date,"mmm yyyy")
Will return Nov 2005

If you need a different format, look it up in VBA Help. It will show you a
lot of different ways to format a date.

It appears Access sees TMonth as a full date when I enter Oct 1 2005, and as
a truncated date when I enter Oct 2005; either way it displays October 2005
in TMonth but the figures in Balance1 textbox are very different. It returns
expected figures for a date of October 1, 2005; but it treats October 2005 as
a null, hence the 0 it returns.
I can see some end-users will enter a full date when I just want him to
input Month and Year, and will get disastrous results.
Is there a simple way to parse out whatever is entered into TMonth to make
it appear as any full date in that month?
--
Glint

Just a few syntax errors. This assumes [DonationItem] is a text field. If
it is not, then leave out the single quotes.

=IIf(IsNull([TMonth]), Null,
nz(DSum("[ItemAmount]","vBalances","[DonationItem]= '" &
Forms!CashBook!RevenueID & "' And [TDate] = #" & Forms!CashBook!TMonth &
"#),0))"

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?

 

[Guest]

Thanx again for your suggestions.

I tried the following in the control source of my textbox Balance1:
=IIf([TMonth] Is Null,Null,nz(DSum("[ItemAmount]","vBalances","[ItemAmount]>0
And Format([TDate],"mmmm yyyy") = #" & Forms!CashBook!TMonth & "#"),0)).

The error message that came read: The expression you entered contained an
invalid syntax. Is it that the format function cannot be used in an aggregate
function? Or how should the syntax be?

I really appreciate your help with this task. I have been using a
round-about way of achieving the objective: using some textboxes to get the
date values of October 1, 2005 and October 31, 2005 whenever a user enters
Oct 2005 in TMonth, for example. It works, but you agree, it is very untidy.

Please help.

 

[Guest]

SOMEONE PLEEEEEASE RESPOND.

 

[Guest]

=IIf(IsNull([TMonth]),Null, nz(DSum("[ItemAmount]","vBalances","[ItemAmount]>0
And Format([TDate],"mmmm yyyy") = #" & Forms!CashBook!TMonth & "#"),0)).

Is Null is strictly for SQL statements. The IsNull() function should be
used in VBA. My question is what are you putting the results of this in?
You have to be carefull returning a Null value. A Null can only go in a
Variant variable, a Text box control, and, if not restricted by the field
definition, a table field.

Thanx again for your suggestions.

I tried the following in the control source of my textbox Balance1:
=IIf([TMonth] Is Null,Null,nz(DSum("[ItemAmount]","vBalances","[ItemAmount]>0
And Format([TDate],"mmmm yyyy") = #" & Forms!CashBook!TMonth & "#"),0)).

The error message that came read: The expression you entered contained an
invalid syntax. Is it that the format function cannot be used in an aggregate
function? Or how should the syntax be?

I really appreciate your help with this task. I have been using a
round-about way of achieving the objective: using some textboxes to get the
date values of October 1, 2005 and October 31, 2005 whenever a user enters
Oct 2005 in TMonth, for example. It works, but you agree, it is very untidy.

Please help.
--
Glint

Hi All,
I am using Access 2002. I want to display cash collections from various
revenue items on a form (CashBook) based on the Revenue table (RevenueID and
Revenue as fields). The bullwork of the form is a query vBalances (a union
query that extracts dates, item and amount from 2 other queries that combine
donations details with donations on one hand, and expenses details and
expenses on the other hand). On my form is an unbound textbox (TMonth) with
format: mmmm yyyy, and it does display any valid date in month and year.

I want Balance1 textbox to display total income for the month displayed in
TMonth textbox; so I put Balance1 control source as: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] = #Forms!CashBook!TMonth#"),0)).
I get #Error message with this code.

When I remove the pound sign #, I get zero when I know for a fact that there
should be more that 0.

I changed the code to: =IIf([TMonth] Is
Null,Null,nz(DSum("[ItemAmount]","vBalances","[DonationItem]=Forms!CashBook!RevenueID And [TDate] IN #Forms!CashBook!TMonth#"),0)).
The error message is the same, with or without the pound sign.

When I use another textbox in place of TMonth that has real date values (say
October 1, 2005), the form works fine.

Can you suggest an easy alternative to accomplish this task?