<div>
<br />
<br />
good morning!<br />
<br />
try this...<br />
<br />
select distinct(ID) Where right(trim(id),2) &lt;&gt;&nbsp;'-X'<br />
<br />
this query simply mean that &nbsp;u are selecting distinct ID which<br />
is the last two characters is not equal to -X.<br />
<br />
<br />
i hope this will give u some idea..<br />
<br />
<br />
game-master
</div>


<div>
<br />
<br />
im sorry i did not specified the table nam..<br />
<br />
select distinct(ID) From info Where right(trim(id),2) &lt;&gt;&nbsp;'-X'
</div>


<div>
The WHERE clause would leave out 888-X from the sample data. You can use this expressions to get all numbers, with or without the suffix:<br />
<br />
&nbsp; &nbsp; SELECT DISTINCT Left(ID,InStr(ID&amp;'-','-')-<wbr />1) AS N FROM info<br />
<br />
(°v°)
</div>


<div>
<br />
<br />
<br />
<br />
i just use the WHERE clause for filtering<br />
because that what he asking... :-)<br />
<br />

</div>


<div>
harfang:<br />
You're right, what I meant was &quot;strip the '-X' from all IDs&quot;.<br />
I have another question raised @ <a href="https://www.experts-exchange.com/questions/23580856/You-tried-to-execute-a-query-that-does-not-include-the-specified-expression-'Left-ID-InStr-ID-'-'-'-'-1-'-as-part-of-an-aggregate-function.html" rel="ugc">https://www.experts-exchange.com/questions/23580856/You-tried-to-execute-a-query-that-does-not-include-the-specified-expression-'Left-ID-InStr-ID-'-'-'-'-1-'-as-part-of-an-aggregate-function.html</a><br />

</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Is there a way to select from a filtered (while selecting) field at the same time? E.g. I have a table named &quot;info&quot; like following. I need to remove all &quot;-X&quot; and left with only the numerics. Result will be:<br />
<br />
ID: 123, 888, 333<br />
<br />
It seems like I couldn't do it from Access. Only way I thought of is creating a temporary duplicate table, and filtered it out to a new field, then selecting it.<br />
<br />
However, I would like to have it while selecting, it that possible with Access?<br />
<br />
Thanks..!<br />
<br />

<pre><code class="code-10 nolinks" id="code-122452"><span>ID</span>
<span>----------</span>
<span>123</span>
<span>123</span>
<span>123-X</span>
<span>888-X</span>
<span>333</span>
<span>333-X</span>
<span> </span>
<span>SELECT DISTINCT(Left([ID],InStr([ID],'-')-1)) FROM info</span>
</code></pre>
</div>
</div>


Hi,

Is there a way to select from a filtered (while selecting) field at the same time? E.g. I have a table named "info" like following. I need to remove all "-X" and left with only the numerics. Result will be:

ID: 123, 888, 333

It seems like I couldn't do it from Access. Only way I thought of is creating a temporary duplicate table, and filtered it out to a new field, then selecting it.

However, I would like to have it while selecting, it that possible with Access?

Thanks..!


SQL SELECT with Filtering

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.