DSum([WOO],"tbl_main",[PERCENT_WAY_THROUGH_LEASE]>'100') - datatype mismatch

[mindbender]

I am trying to calculate a field and am getting datatype mismatch error on the 100 section.



The WOO and PERCENT_WAY_THROUGH_LEASE fields are both set to Long Integer in the table.

I am trying to sum the WOO field where the PERCENT_WAY_THROUGH_LEASE is greater than 100. I have tried single quotes, double quotes and no quotes around the 100 with the same error each time.

If I choose Woo as the column and sum it works. It is only when I try to add the criteria that it throws the error.

The table is joined to another table but it is not joined on either of these fields.


Any help would be awesome

[Welshgasman]

No quotes are there for the criteria or the field being summed and as 100 is a number that should not have quotes.?

Why not check out the syntax?

https://support.microsoft.com/en-us/...f-386056e61a32

[Minty]

I'm surprised that will work without the criteria.

As gasman has stated the syntax is each section enclosed in quotes as per here:
http://access.mvps.org/access/general/gen0018.htm

[mindbender]

I'm surprised that will work without the criteria.

As gasman has stated the syntax is each section enclosed in quotes as per here:
http://access.mvps.org/access/general/gen0018.htm

I noticed that afterwards and fixed it. It is running now. Is there a way to have it group by.

The query I have has DTT and This calculated field I have the totals on to group by

I see this:

DTT Calculated field
123 23456
345 23456
567 23456

Is there a way to get the value in the calculated field to be broken out by the DTT?
Like this:

DTT Calculated Field
123 7002
345 4943
567 11,511

[Minty]

It sounds like you need a aggregate query, but organised a little differently?

Can you post up some example starting data, and the desired output.
Generally speaking, using Domain functions in queries isn't required, and is very inefficient on larger datasets.

[mindbender]

[QUOTE=mindbender;476397]I noticed that afterwards and fixed it. It is running now. Is there a way to have it group by.


Nevermind. Huge brain fart. I figured it out.

Thanks for the help