473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using iif(iserror( or iif(isnumeric( in Access table Order By

I am trying to sort a list of alphanumeric ID's in numeric order using the OrderBy property of a table. They are in the format of 2 letters followed by numbers. Like this "AA10199"
However the number is not an autonumber part, it is entered by users. Sometimes these users put too many letters in, like this "AAa10199" so my original sort for the table fails.
This was
Expand|Select|Wrap|Line Numbers
  1.  cint(mid([ar_id],3,8))
Sorted ascending.
But with extra letters added an error is returned and the sort fails, so I thought this may work.
Expand|Select|Wrap|Line Numbers
  1.  IIf((IsNumeric(CInt(Mid([ar_id],3,8)))),CInt(Mid([ar_id],3,8)),0)
It doesn't, it just produces an #Error value and will not sort. It fails with IsError as well. What am I doing wrong?
Oct 27 '10 #1

✓ answered by NeoPa

IIf() evaluates both options regardless of the result of the first parameter. Thus it will error if the value is wrong.

This is fundamentally down to the logic in your code being a bit back-to-front. Try instead :
Expand|Select|Wrap|Line Numbers
  1. CInt(Mid([ar_id],IIf(IsNumeric(Mid([ar_id],3)),3,4)))
I don't have your database to test in so I hope I have the parentheses balanced and I hope your data is as indicated. If tweaks are required then please post results and/or problems and we'll see what we can do to fine-tune it.

3 5788
NeoPa
32,556 Expert Mod 16PB
IIf() evaluates both options regardless of the result of the first parameter. Thus it will error if the value is wrong.

This is fundamentally down to the logic in your code being a bit back-to-front. Try instead :
Expand|Select|Wrap|Line Numbers
  1. CInt(Mid([ar_id],IIf(IsNumeric(Mid([ar_id],3)),3,4)))
I don't have your database to test in so I hope I have the parentheses balanced and I hope your data is as indicated. If tweaks are required then please post results and/or problems and we'll see what we can do to fine-tune it.
Oct 27 '10 #2
Thanks Neo. Worked great. Then realised that they will find a better fool if you make something foolproof. Have solved the problem with validation.
Expand|Select|Wrap|Line Numbers
  1. ALike "[a-z]R[123456789]" Or ALike "[a-z]R[123456789][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890][1234567890][1234567890]"
A shorter version of that I couldn't figure out, again. But it will make them input the required coding. For the error message
Expand|Select|Wrap|Line Numbers
  1. Must Be: letter A to Z, "R", Number 1-999999
Oct 28 '10 #3
NeoPa
32,556 Expert Mod 16PB
That's good thinking Phil. A helpful link on setting this type of think up can be found at Find Wildcard Characters in an Access Database.

At a rough guess I expect you could simplify that to :
Expand|Select|Wrap|Line Numbers
  1. ALike '[a-z]R[1-9]' Or ALike '[a-z]R[1-9]#' Or ALike '[a-z]R[1-9]##' Or ALike '[a-z]R[1-9]###' Or ALike '[a-z]R[1-9]####' Or ALike '[a-z]R[1-9]#####'
From your first post though (and I may be wrong here), you may want to use ...[a-zA-Z]... in place of the ...[a-z].... You decide which is more appropriate for you ;-)
Oct 28 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: boonkit | last post by:
I try to get better performance by implementing this: http://dev.mysql.com/doc/mysql/en/alter-table.html "ORDER BY allows you to create the new table with the rows in a specific order. Note...
2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
10
by: nick_faye | last post by:
Hi guys, i'm still a newbie in using MS Access and in VB programming. I am using DAO connection from my VB to access the entries on my MS Access table. I am having trouble in editting and...
5
by: premmehrotra | last post by:
I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000. When I export a table from Access to Oracle using ODBC I get error: ORA 972 identifier too long I think the error is because...
2
by: VM | last post by:
What's the fastest way to insert data into an Access table? I'm currently using ExecuteNonQuery but it seems to take too long (should filling an mdb table be slower than filling a datatable?). So...
12
by: VMI | last post by:
For some reason, the process of retrieving data (about 20 records) from an Access table that has 400K records to a dataTable is taking over 3 mins. to complete. Below is my code to connect to the...
3
by: roy_ware | last post by:
I'm using a VB.Net interface to load an Access table. The relationship is 1 input record to many table rows. The problem is that the first name of the first row is populated for every row on the...
3
by: RLN | last post by:
Re: Access 2003/Oracle 9i I have an Access app that connects to an Oracle DB via OLEDB/VBA code (no DSN or ODBC) Queries against straight Oracle tables run fine. For this query, however,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.