473,322 Members | 1,538 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,322 software developers and data experts.

a query with wildcard

Joe
Here are some of the values in my table:
AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.

I want to have a list that will sort this items regardless the three
first charachters. So, the new list will look like:
MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.

Thanks,

Jeff
Nov 12 '05 #1
5 1747
You need to create a calculated field. You can uncheck the Show checkbox so
that it doesn't appear in the queries output, if you wish. You would then
sort this field.

Example:
SortMe: Right([FieldName], 7)
Set the sort to Ascending or Descending as desired

You may run into a problem since these are text values with the numbers not
sorting in numerical order. If the numbers are all 4 digits - 2 digits, you
may be ok. If not, then you will need to repeat the above process, breaking
it into each set of numbers, using CInt to change them to numbers, and
sorting on each one individually. Also, if the length after the first 3
characters varies from 7, then you'll need to change the 7 to be the full
length - 3 (i.e. Right([FieldName], Len([FieldName])-3).

--
Wayne Morgan
MS Access MVP
"Joe" <ga*****@aol.com> wrote in message
news:b4*************************@posting.google.co m...
Here are some of the values in my table:
AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.

I want to have a list that will sort this items regardless the three
first charachters. So, the new list will look like:
MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.

Nov 12 '05 #2


Thanks, where should I put this line? VBA?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3
No, it would be a calculated field in the query. Place the line in the query
grid where you would normally place the name of a field. The name of the
calculated field would be the part before the ":" and the calculation is the
part after the ":".

--
Wayne Morgan
MS Access MVP
"Aaron Gal" <ga*****@aol.com> wrote in message
news:40*********************@news.frii.net...


Thanks, where should I put this line? VBA?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4


Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5
Actually, a better way to get character #4 and those following is with
Mid([FieldName],4) which will return a string starting in the fourth
position and continuing to the end regardless of original string's length.

S.A.M.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Kq****************@newssvr24.news.prodigy.com ...
You need to create a calculated field. You can uncheck the Show checkbox so that it doesn't appear in the queries output, if you wish. You would then
sort this field.

Example:
SortMe: Right([FieldName], 7)
Set the sort to Ascending or Descending as desired

You may run into a problem since these are text values with the numbers not sorting in numerical order. If the numbers are all 4 digits - 2 digits, you may be ok. If not, then you will need to repeat the above process, breaking it into each set of numbers, using CInt to change them to numbers, and
sorting on each one individually. Also, if the length after the first 3
characters varies from 7, then you'll need to change the 7 to be the full
length - 3 (i.e. Right([FieldName], Len([FieldName])-3).

--
Wayne Morgan
MS Access MVP
"Joe" <ga*****@aol.com> wrote in message
news:b4*************************@posting.google.co m...
Here are some of the values in my table:
AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.

I want to have a list that will sort this items regardless the three
first charachters. So, the new list will look like:
MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.



Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Wm | last post by:
This is very peculiar -- for some reason, I'm getting 6-8 results from each of these queries, although only one listing matches. I have a pair of forms on one page: <FORM> Search for lastname:...
11
by: Randy Weber | last post by:
I am getting unexpected results with a Like Query using ASP and an Access database. This query - SELECT PN, Qty From Inventory Where PN Like 'SSW-10%'; returns what I expect: PN ...
1
by: deko | last post by:
I have a form where users can enter a string with asterisks to perform a wildcard search. Currently, the string entered by the user looks like this: *somestring* The purpose is to match any...
3
by: dnl | last post by:
I have a parameter query that I would like to simplify so that I wouldn't need to use wildcards when entering in partial data. The following line is in the criteria field when designing the query:...
3
by: george.lengel | last post by:
Hello experts, I have been struggling for days to solve this problem and every suggestion I find via Google does not work for me. There is probably a solution out there that will do what I want,...
2
by: googlegroups.dsbl | last post by:
I'm really confused here, and am wondering if someone knows what could be the issue with my TableAdapter query. A few months ago, I created a really neat program that has th ability to search by...
1
by: hardik | last post by:
hi friends i need help in this sql query i have table like, id fid __ _____ autonumber text and i am storing values like
9
by: romanko | last post by:
I'm running Access 2000 on Windows XP. I have a simple select query, with two tables joined at the "Account" field. The account field has 5 characters of text in both tables Table A is a...
4
by: Konrad Hammerer | last post by:
Hi! I have the following problem: I have a query (a) using another query (b) to get the amount of records of this other query (b), means: select count(MNR) as Number from...
5
by: DeanL | last post by:
Hi all, I'm trying to set up a query that runs from a command button on a form (simple enough so far), what I want the query to do is take values from the fields on the form (seven fields in...
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
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.