Mid

[Guest]

I've been using the Mid function to return part of a value. I use it to return a value from position 5-8 in the account number. I use Mid([Account],5,3) to return 100 for the following value 575010012. Mid is not a numberic function correct and that is why I get an error when I try sorting on it. If this is correct how can I sort on it

 

[Cheryl Fischer]

To make sure that you are sorting on the numeric value, you can use this ...

Val(Mid([Account],5,3))


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I've been using the Mid function to return part of a value. I use it to

return a value from position 5-8 in the account number. I use
Mid([Account],5,3) to return 100 for the following value 575010012. Mid is
not a numberic function correct and that is why I get an error when I try
sorting on it. If this is correct how can I sort on it.

 

[Guest]

I've tried this and it works great except if the middle digits are 000 it displays just 0. How can I have it display 000

 

[Cheryl Fischer]

000 is not a numeric value, so in order to display 000 but sort on the
numeric value of 0, you will need two columns in your query:

1. Make sure that the Show box is checked on this one:

Mid([Account], 5, 3)

2. Uncheck the Show box on this one and set its Sort to Ascending or
Descending

Val(NZ(Mid([Account],5,3)))

I have included the NZ() function above, just in case any of your Account
fields might be null.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I've tried this and it works great except if the middle digits are 000 it

displays just 0. How can I have it display 000.