help w/ calculated fields in a query

[Tekture]

So, I have this query...
This expression works fine:
Expr9: Nz(DateDiff("d",[Closed Externally],[Cert Returned to HR]),0)

However, this one does not:
Expr13: IIf(Nz(DateDiff("d",[Closed Externally],[Cert Returned to HR]),0)="0",[Expr7]+[Expr8],[Expr9])

What's the deal?

 

[Bilbo_Baggins_Esq]

What happens if you remove the quotes from the 0?

Expr13: IIf(Nz(DateDiff("d",[Closed Externally],[Cert Returned to HR]),0)=0,[Expr7]+[Expr8],[Expr9])

 

[Tekture]

the IIF function validates to the true answer and the false answer, so the initial condition is not the problem. The problem is my results are concatenating and not adding. (I.E. 22+33= 2233 and not 55)

in fact, the same problem happens with this simple expression:
[Expr1] + [Expr2]

I have tried this as well:
Sum([Expr1] + [Expr2])
Sum(Expr1 + Expr2)
Sum("Expr1" + "Expr2")
Sum([Expr1],[Expr2])

they are all concantenating (or not working at all)

 

[pbaldy]

If the results are concatenating rather than adding, the values are being considered text. Try wrapping each in the appropriate conversion function (this would be for a long integer):

CLng([Expr1]) + CLng([Expr2])

 

[datAdrenaline]

The expressions you have used for your aliases are returning Variant datatypes and are indeed being considered text (as paul indicated), and with the (+) doubling as a text concatenator, you get the string you got ...

You can do as Paul has suggested by coercing to a number with the VBA function CLng(), or you can convince the JET expression service that the expression is really supposed to be a numeric one with the simple addition of a 0.0 at the beginning ...

0.0 + [Expr7] + [Expr8]

Then the JET expression service will do the coercion to numeric internally for you and you can avoid the VBA call to CLng() ...

....

Note that ANY expression that returns a Variant datatype will be interpreted by JET as a string ...

Some additional information ...

 

[boblarson]

0.0 + [Expr7] + [Expr8]

Then the JET expression service will do the coercion to numeric internally for you and you can avoid the VBA call to CLng() ...

Dang Brent! Where do you get all of this good information? I feel like an amateur when you're around. Your posts are EXTREMELY appreciated!!

 

[Tekture]

Absolutly perfect! Thanks Brent.
I love when severely frustrating problems are solved so simply.

 

[datAdrenaline]

Hey Bob ...

>> Dang Brent! Where do you get all of this good information? <<

... Umm ... I usually an sitting on it through a lot of the day! ... ROFL!!!! ...

>> I feel like an amateur when you're around. <<

No need for that! ... Have you seen me try to develop a web page? ..... No? ... well neither have I! ...

>> Your posts are EXTREMELY appreciated!! <<

Thank you ...

 

[datAdrenaline]

Tekture ...

>> Absolutly perfect! Thanks Brent <<

You are most welcome! ... Glad the info helped you out!