Access fails to find refered experient

  • Thread starter oliaccount via AccessMonster.com
  • Start date
O

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.
 
R

Rick Brandt

oliaccount said:
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.
Click to expand...

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.
 
O

oliaccount via AccessMonster.com

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

Rick said:
Hi
I have a query that selects the middle characters of a postcode:
Click to expand...
[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.
Click to expand...

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.
Click to expand...
 
Top