String Functions in Query not compiling???

[WallyZ]

I have a simple table that contains numeric values in a text column (TaskNo).

tblTasks
ID Auto Number
TaskYear Date
TaskNo ShortText (10)
other fields ....

I would like to sort the table by TaskNo numerically and found this on the net - Expr1:IIF([FIELD] IS NULL, 0, VAL([FIELD]))
So I wrote this in the SQL view of my query

SELECT tblTasks.[TaskYear], tblTasks.[TaskNo], IIF(tblTasks.[TaskNo] IS NULL, 0, VAL(tblTasks.[TaskNo])) AS NumericTaskNo FROM tblTasks

I've left off the ORDER BY clauses for now as it doesn't change my issue.

When I change to design view I see the Expr has been correctly formed in the field.

But when I change to Data Sheet view I get the error:

Compile error in query expression: 'IIF(tblTasks.[TaskNo] IS NULL, 0, VAL(tblTasks.[TaskNo])' <-- notice it always leaves off the last character



The 'VAL' is highlighted. This occurs also with other functions such as MID, LEFT, RIGHT, etc.

I checked my references and they are all good (none missing, VBA is there, MS Access 15 Object Lib is there) and I can use all these functions without issue from the Access VB editor.

Also I cannot enter any function directly into the expression field while in design mode as it immediately complains it doesn't known about that function.

What am I missing?

[CJ_London]

sql evaluates the entire expression, so I suspect it is failing on trying to evaluate a null value for the val function

try changing

'IIF(tblTasks.[TaskNo] IS NULL, 0, VAL(tblTasks.[TaskNo])'

to

val(nz(tblTasks.[TaskNo],0))

or perhaps

val(nz(tblTasks.[TaskNo],"0"))

[WallyZ]

Ajax suggested:

val(nz(tblTasks.[TaskNo],0))

or perhaps

val(nz(tblTasks.[TaskNo],"0"))

Query designer complained about both the VAL and NZ functions.

I also tried these:

Expr1: VAL(tblTasks.[TaskNo])

Expr1: NZ(tblTasks.[TaskNo], "0")

But query designer didn't like either.

I believe the query designer is not seeing all the references provided in Tools->References! Why? Don't know!!!

This worked:

Expr1: IIF(tblTasks.[TaskNo], "0", "0")

Not that it does anything but it shows it can resolve the IIF function.

IIF is part of VBA.Interaction while all the other string functions are part of VBA.Conversion.

So the designer is not happy about all the VBA functions!!!

[WallyZ]

More Info

I tried all the previous expressions on a system with Access 2010 and they all worked no problem.

The problem occurs only on my Access 2013 system!

[WallyZ]

This newby managed to figure it out.

The problem was created by me failing to compile the VBA code completely. A duplicate function name prevented the Designer from recognizing the VAL function!