DSUM Function Returning TEXT fields

[quirkyjoe]

I am using the DSUM function, with query fields as inputs. The inputs
are numbers.

When I use the DSUM function on them it returns values as text - not
numbers.

There can be null values in the fields that DSUM is working off of.

Other than going back and making the inputted nulls all zeros, is
there a work around to get the values from DSUM as numbers and not
text?

Thanks.

 

[John Spencer]

That is strange. It doesn't normally happen that way. My guess would be
that it is not the DSUM that is returning a text value, but that something
is changing the returned value.

Can you post the code where you are using the DSUM function? Are you using
it in a query? If so, post the SQL of the query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[quirkyjoe]

That is strange. It doesn't normally happen that way. My guess would be
that it is not the DSUM that is returning a text value, but that something
is changing the returned value.

Can you post the code where you are using the DSUM function? Are you using
it in a query? If so, post the SQL of the query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Show quoted text -

John,

Thnaks for the help.

Below is a shortened version of the SQL (withou all the repeated DSUMs

SELECT "J02" AS TestID, DSum("[Blue_All]","qryGREEN","[RowNum] Between
14 and 74") AS IDen_Purple_All,

DSum("[Blue_All]","qryGREEN","[RowNum] Between 78 and 173") AS
IDen_Yellow_All,

<SNIP MANY MORE DSUMs>

FROM qryGREEN
WHERE (((qryGREEN.FredNum)=1));

 

[John Spencer]

I see nothing there that would cause the value to be returned as string.
Did you use the format property of the fields in the query?

You say that DSUM is returning a string. How can you tell that it is
returning a string? Is it always doing it? If

What version of Access are you using?

As a workaround you could use the Val function to force a number value.
Val(DSum("Blue_All","qryGREEN","RowNum Between 14 and 74") )

By the way, you don't **need** the brackets around your field and query
names since your field and table names are valid without them included.
DSum("Blue_All","qryGREEN","RowNum Between 14 and 74") is perfectly valid.

I might try a modification of the query.
SELECT "J02" AS TestID
, Sum(IIF(RowNum Between 14 and 74, Blue_All,Null) AS IDen_Purple_All,
, Sum(IIF(RowNum Between 78 and 173,Blue_All,Null) AS IDen_Yellow_All,

<SNIP MANY MORE DSUMs>

FROM qryGREEN
WHERE (((qryGREEN.FredNum)=1))
GROUP BY TestID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

That is strange. It doesn't normally happen that way. My guess would be
that it is not the DSUM that is returning a text value, but that
something
is changing the returned value.

Can you post the code where you are using the DSUM function? Are you
using
it in a query? If so, post the SQL of the query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Show quoted text -

John,

Thnaks for the help.

Below is a shortened version of the SQL (withou all the repeated DSUMs

SELECT "J02" AS TestID, DSum("[Blue_All]","qryGREEN","[RowNum] Between
14 and 74") AS IDen_Purple_All,

DSum("[Blue_All]","qryGREEN","[RowNum] Between 78 and 173") AS
IDen_Yellow_All,

<SNIP MANY MORE DSUMs>

FROM qryGREEN
WHERE (((qryGREEN.FredNum)=1));

 

[quirkyjoe]

John,

Thanks. I used the Val option and that worked.

I am using ACCESS 2003.

I know it was returning text because when I had it make a table the
values generated by DSUM were all text. When I used Val they truned
to number format.

I see nothing there that would cause the value to be returned as string.
Did you use the format property of the fields in the query?

You say that DSUM is returning a string. How can you tell that it is
returning a string? Is it always doing it? If

What version of Access are you using?

As a workaround you could use the Val function to force a number value.
Val(DSum("Blue_All","qryGREEN","RowNum Between 14 and 74") )

By the way, you don't **need** the brackets around your field and query
names since your field and table names are valid without them included.
DSum("Blue_All","qryGREEN","RowNum Between 14 and 74") is perfectly valid.

I might try a modification of the query.
SELECT "J02" AS TestID
, Sum(IIF(RowNum Between 14 and 74, Blue_All,Null) AS IDen_Purple_All,
, Sum(IIF(RowNum Between 78 and 173,Blue_All,Null) AS IDen_Yellow_All,

<SNIP MANY MORE DSUMs>

FROM qryGREEN
WHERE (((qryGREEN.FredNum)=1))
GROUP BY TestID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Thnaks for the help.

Below is a shortened version of the SQL (withou all the repeated DSUMs

SELECT "J02" AS TestID, DSum("[Blue_All]","qryGREEN","[RowNum] Between
14 and 74") AS IDen_Purple_All,

DSum("[Blue_All]","qryGREEN","[RowNum] Between 78 and 173") AS
IDen_Yellow_All,

<SNIP MANY MORE DSUMs>

FROM qryGREEN
WHERE (((qryGREEN.FredNum)=1));- Hide quoted text -

- Show quoted text -