Query calculated field issue

[MazeWorX]

Just curious anyone else run into this problem with Access 2010. I have a query that has several calculated fields and 2010 insists on concatenating the fields instead of adding them. funny thing is not all of the calculated fields only some of them and concatenates them with the + . Ive seen/used the + in other environments to concatenate but never in access. All data types are numerical. An example:

values=300,50

Expr2: [Delpcs300a]+[Delpcs300p] Result 30050

Whats odd is if i add a division by to the first field then access recognizes it as math like

Expr2: [Delpcs300a]/1+[Delpcs300P]
then it calculates properly
result 350

thanks in advance

MW

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[SkipVought]

Multiply rather than divide

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

The issue is not Access.

The PLUS operator is used for both addition and concatenation. Multiplying by 1 coerces a mathematical calculation.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MazeWorX]

grrr thanks Skip ... never had any issues in all of the db's ive every created before

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

Another way:
Expr2: Val([Delpcs300a])+Val([Delpcs300p])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Cool thanks PHV ... never even thought of using Val. Just goes to show you when your so fixed on the why(tunnel vision) the most obvious is not so obvious

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

MazeWorX,
You state [Delpcs300a] and [Delpcs300p] are both numeric. Are these actual field names from an Access table? When you view these fields as columns in a datasheet are they left or right aligned? Are the fields a result of a calculation that might use Format() or Nz()?



Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

These are Access tables both fields are Number data types in datsheet view they align right and yes an Nz function was used in the query to deal with null values not all fields have data and further calculations were required.

just a note i have resolved the issue with a *1 as originally suggested by Skip.

But would be interested to know if something specifically is causing the issue other then what I should of known as 'expected' behavior

MW

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

What is the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

I have several queries as a source for the final query. There are 2 types of record data in one table with an identifier('A' or 'P') for ea. which I combine into one record (2 queries on the same table with a where criteria on the identifier). In each query I rename to identify and transform for nulls with Nz e.g. PUStops300P: Nz([PUStops],0). In the Final query I'm combining a date table with budget and the 2 types of Actual data. The date table is to ensure I have all working days for all locations in case they are missing actual data for a specific date which I can then identify in the reporting. In the reporting I need to calculate a total for some of the fields and this is where I run into the issue. I tried adding them in the Final query instead of the report but same result. having said that I do perform some calculations in ea. query without any issues. The issue is when I try to add them to each other

All pretty straight forward I’m not really trying to accomplish anything I haven’t done many times before. I can’t really post the entire Query because of confidentiality rules


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

The Nz() function returns a variant data type. As PH suggests you need to wrap the function in Val() or some other conversion to assure it is a numeric.

Duane
Hook'D on Access
MS Access MVP