Access fails to find refered experient

[oliaccount via AccessMonster.com]

Hi
I have a query that selects the middle characters of a postcode:
numbers: IIf(IsNull([postcode]),Null,Val(Mid([postcode],3)))
And then splits the resulting number:
code right: IIf(IsNull([numbers]),Null,Val(Left([numbers],1)))

This works fine until I tell access to sort acending on the second field
(which is the whole point), when it asks me to define numbers.

 

[Rick Brandt]

Hi
I have a query that selects the middle characters of a postcode:
numbers: IIf(IsNull([postcode]),Null,Val(Mid([postcode],3)))
And then splits the resulting number:
code right: IIf(IsNull([numbers]),Null,Val(Left([numbers],1)))

This works fine until I tell access to sort acending on the second
field (which is the whole point), when it asks me to define numbers.

I'm surprised the split field works. You normally cannot refer to an alias
created in the same query in other expressions. In most cases you have to use
the same expression that was used in the aliased field.

 

[oliaccount via AccessMonster.com]

I would of used an outer join, but I don't know how!

Hi
I have a query that selects the middle characters of a postcode:

[quoted text clipped - 4 lines]

This works fine until I tell access to sort acending on the second
field (which is the whole point), when it asks me to define numbers.

I'm surprised the split field works. You normally cannot refer to an alias
created in the same query in other expressions. In most cases you have to use
the same expression that was used in the aliased field.