Solved

Custom "between" criteria in Access Query.

Posted on 2008-10-23
6
575 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
  • 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now