Dsum (1 Viewer)

[sbaud2003]

Dear Sirs,
I have a Table "total Cmp" in which the fields are finyear,section. Chamt and settlement_date
I want to get the value of total amount in the filed Chamt by cliking a command button and display the result in a textbox TCMP
I am using the code below but I am getting error type mismatch

Me.TCMP.value = FormatIndian(Nz(DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] And "[settelment_date] between [Text0] and [Combo4]")))


please help me

 

[Gasman]

If the variable/control is text, surround it with single quote ', if date with #, if numeric, no character required.
Plus you are not concatenating the variables/controls correctly for text0 and Combo4 ?

Put all the criteria into a string and Debug.Print that string to see the result.

 

[sbaud2003]

If the variable/control is text, surround it with single quote ', if date with #, if numeric, no character required.
Plus you are not concatenating the variables/controls correctly for text0 and Combo4 ?

Put all the criteria into a string and Debug.Print that string to see the result.

where to correct

 

[namliam]

Make your code readable, it will help you debug it....

Me.TCMP.value = FormatIndian(Nz(DSum("Chamt"
, "total cmp"
, " finyear = " & [COMFY] & _
" and section = " & [TXTUC] And "[settelment_date] between [Text0] and [Combo4]")))

With each of the red coloured somethings wrong
The blue coloured also has a mistake.

Editted to add: storing computated values on average is a bad idea.

 

[CJ_London]

DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between #" & [Text0] & "# and #" & [Combo4] & "#")

this assumes comfy and txtuc are numeric values

Note you do not need the nz function, dsum will return 0 if there are no values, you would use it for other domain functions such as dlookup.

tip for the future. If you get this sort of error, reduce the string to a single component (e.g. "finyear = " & [COMFY]) then keep adding until you get the error - then you will know where it is

 

[Gasman]

Well I do not know what is numeric, date or text ?

However it should be along the lines of

Me.TCMP.value = FormatIndian(Nz(DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] And "[settelment_date] between #" [Text0] & "# and #" & [Combo4] &#")))

I use a constant format to get the format correct and easier to put together.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[namliam]

DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between #" & [Text0] & "# and #" & [Combo4] & "#")

You missed one

 

[Gasman]

You missed one

Well I hoped the O/P would get the general idea. ?
If they put it into a string as I always do, then they would see the error immediately.?

It is called a learning process.

 

[namliam]

It is called a learning process.

Which is why I highlighted where to look instead of putting in all kinds of fixes....

" Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime. "

 

[sbaud2003]

Thanks for the support . but still not resolve ... Code error is flashing with Red
I used in the Click event
(1)
me.TCMP.value=DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between "#" & [Text0] & "#" and "#" & [Combo4] & "#")

(2)
me.tcmp.value = DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between #" & [Text0] & "# and #" & [Combo4] & "#")
Error What to do?

 

[Minty]

You will need to tell us what the field data types are to help you any further, so what types of data are
Finyear
section

Currently access thinks they are numbers?

Where is [COMFY] coming from? Shouldn't it be Me.COMFY if it's on the form?
Where is [TXTXUC] coming from? Shouldn't it be Me.TXTXUC if it's on the form ?

And it might be really helpful to call your controls something sensible like
Text0 change to txtStartDate
Combo4 change to cboEndDate

 

[sbaud2003]

You will need to tell us what the field data types are to help you any further, so what types of data are
Finyear
section

Currently access thinks they are numbers?

Where is [COMFY] coming from? Shouldn't it be Me.COMFY if it's on the form?
Where is [TXTXUC] coming from? Shouldn't it be Me.TXTXUC if it's on the form ?

And it might be really helpful to call your controls something sensible like
Text0 change to txtStartDate
Combo4 change to cboEndDate

yes they are on the from but the problem lies with the code starting from And [settelment_date] between "#" & [Text0] & "#" and "#" & [Combo4] & "#") if I removed this code the total sum is appearing on the screen but it is not taking between dates with the codes mentiond

 

[Gasman]

If you put it all in a string and Debug.Print that, it would help me (at least) plus yourself in getting to the bottom of the problem.
Copy and paste the result of the debug.print back here.

Right now, we are just guessing

 

[Minty]

Okay so let's try this then;

Dim sCriteria As String

    sCriteria = "finyear = " & Me.COMFY & " and section = " & Me.TXTUC & " And [settelment_date] between #" & Me.Text0 & "# and #" & Me.Combo4 & "#"
    Debug.Print sCriteria

    Me.tcmp = DSum("Chamt", "total cmp", sCriteria)

 

[sbaud2003]

yes they are on the from

Okay so let's try this then;

Dim sCriteria As String

    sCriteria = "finyear = " & Me.COMFY & " and section = " & Me.TXTUC & " And [settelment_date] between #" & Me.Text0 & "# and #" & Me.Combo4 & "#"
    Debug.Print sCriteria

    Me.tcmp = DSum("Chamt", "total cmp", sCriteria)

Thank you sir,,,
but it display the Gross Total and Zero in other cases
between dates are not taken randomly..

 

[Minty]

In the immediate window (Press ctrl & G in the vba editor to see it) you will see what sCriteria is.
Please paste that here.
Don't edit it.

This is an essential debugging technique.

 

[CJ_London]

We're both wrong

gasman
DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] And "[settelment_date] between #" [Text0] & "# and #" & [Combo4] &#")

CJ
DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between #" & [Text0] & "# and #" & [Combo4] & "#")

missing a & after txttuc

 

[Gasman]

We're both wrong

gasman
DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] And "[settelment_date] between #" [Text0] & "# and #" & [Combo4] &#")

CJ
DSum("Chamt", "total cmp", "finyear = " & [COMFY] & " and section = " & [TXTUC] " And [settelment_date] between #" & [Text0] & "# and #" & [Combo4] & "#")

missing a & after txttuc

Well at least I am in good company.

 

[namliam]

missing a & after txttuc

Which has been the case since the start of the thread.

 

[Minty]

Well, I'm obviously brilliant 'cos I fixed that in my reply.