Subform producing error...?

[Pascoe via AccessMonster.com]

Folks

I am trying to sum a "simple" expression in a text box on a sub form.
It is producing #Error# - any ideas what I am doing wrong, I presume
something simple and obvious, but I can't spot it.

Expression is:

=Sum(IIf([Date Paid]>=[Forms]![01 Anna Start]![2 Months Start],[Amount],0))

[Forms]![01 Anna Start]![2 Months Start] is calculated using this expression
on the main form [01 Anna Start], and is producing the right date:

=DateSerial(Year(Date()),Month(Date())-2,1)

All responses gratefully received!

Russell.

 

[ghetto_banjo]

i've had a similar error, which was resolved when I put a CDate()
around my date serial function. not sure if this will fix your
problem or not, but it's worth a try.


try this for your [2 Months Start] field:

=CDate(DateSerial(Year(Date()),Month(Date())-2,1))

 

[Daryl S]

Pascoe -

Is this text box in the header or footer of the subform?

Do you have the expression IIf([Date Paid]>=[Forms]![01 Anna Start]![2
Months Start],[Amount],0) calculated in the detail section of the subform
(even if it is not visible)?

 

[Pascoe via AccessMonster.com]

Ghetto_Banjo

Thanks, but no joy. Tried a few variants, but none seemed to work.

Thanks anyway!

Russell.

i've had a similar error, which was resolved when I put a CDate()
around my date serial function. not sure if this will fix your
problem or not, but it's worth a try.

try this for your [2 Months Start] field:

=CDate(DateSerial(Year(Date()),Month(Date())-2,1))

 

[Pascoe via AccessMonster.com]

Hey Daryl S.

In the Detail Section, not in either Header or Footer...

Thanks!
Russell.

Pascoe -

Is this text box in the header or footer of the subform?

Do you have the expression IIf([Date Paid]>=[Forms]![01 Anna Start]![2
Months Start],[Amount],0) calculated in the detail section of the subform
(even if it is not visible)?

Folks

[quoted text clipped - 14 lines]

Russell.

 

[Douglas J. Steele]

You can't use Sum like that: it's a SQL function, not a VBA one.

Try using the DSum aggregate function instead.

 

[Pascoe via AccessMonster.com]

Douglas,

Thanks for your reply.

I have tried a few iterations on this but don't seem to be able to make it
work.

I am using the sub form in a Datasheet format. In the detail section I tried
DSum ("[Amount]","Tenant Payments",[Date Paid] >=[Forms]![01 Anna Start]![2
Months Start]) but apparently I have the "wrong number of arguments"!

I presume I have managed to mess the order/syntax up, but as it appears to
conform to a) the MS Access help notes, and b) the syntax on MS Access
Builder, I am at a loss to explain what I am doing. Plus I'm not familiar
with the function.

Thanks in advance.
Russell.

You can't use Sum like that: it's a SQL function, not a VBA one.

Try using the DSum aggregate function instead.

Folks

[quoted text clipped - 16 lines]

Russell.

 

[Daryl S]

Pascoe -

If it is on a detail record, then I am not sure what the 'Sum' is for - do
you really just need this in the detail section:

IIf([Date Paid]>=[Forms]![01 Anna Start]![2 Months Start],[Amount],0)

Also, when you say it is not working, are you getting an error message, or
is the result not what you expect? If it is the result, can you show us what
you expect (and why), and what the result is?


--
Daryl S

Hey Daryl S.

In the Detail Section, not in either Header or Footer...

Thanks!
Russell.

Pascoe -

Is this text box in the header or footer of the subform?

Do you have the expression IIf([Date Paid]>=[Forms]![01 Anna Start]![2
Months Start],[Amount],0) calculated in the detail section of the subform
(even if it is not visible)?

Folks

[quoted text clipped - 14 lines]

Russell.

--



.

 

[Douglas J. Steele]

Try:

=DSum ("[Amount]","Tenant Payments","[Date Paid] >=" & Format([Forms]![01
Anna Start]![2
Months Start], "\#yyyy\-mm\-dd\#"))

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Douglas,

Thanks for your reply.

I have tried a few iterations on this but don't seem to be able to make it
work.

I am using the sub form in a Datasheet format. In the detail section I
tried
DSum ("[Amount]","Tenant Payments",[Date Paid] >=[Forms]![01 Anna
Start]![2
Months Start]) but apparently I have the "wrong number of arguments"!

I presume I have managed to mess the order/syntax up, but as it appears to
conform to a) the MS Access help notes, and b) the syntax on MS Access
Builder, I am at a loss to explain what I am doing. Plus I'm not familiar
with the function.

Thanks in advance.
Russell.

You can't use Sum like that: it's a SQL function, not a VBA one.

Try using the DSum aggregate function instead.

Folks

[quoted text clipped - 16 lines]

Russell.