Solved

Custom "between" criteria in Access Query.

Posted on 2008-10-23
6
585 Views
Last Modified: 2013-11-29
Looking to retrieve all values between records listing AA-1 through AA-96 (ie. between "AA-1" and "AA-96"). Since its not a whole number, I assume it won't retrieve the exact values I'm looking for.
0
Comment
Question by:vacnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22789167
try this

select * from <table_name>
where convert(int, replace(<your_column_name>, 'AA-', ''))  between 1 and 96;

0
 
LVL 1

Author Comment

by:vacnet
ID: 22789246
jamesqu,

Returns "Undefined function 'convert' in expression.
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22789436
select * from <table_name>
where CInt(replace(<your_column_name>, 'AA-', ''))  between 1 and 96;

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 1

Author Comment

by:vacnet
ID: 22789802
jamesqu,

Returns "Overflow".
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 total points
ID: 22789821
i think you have some values other than 'AA-???' in the table, if you don't want those records,

do

select * from <table_name>
where CInt(replace(<your_column_name>, 'AA-', ''))  between 1 and 96
and <your_column_name> like 'AA-*'


0
 
LVL 1

Author Closing Comment

by:vacnet
ID: 31509336
Works perfectly! Thank you jamesqu.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question