UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Criteria To Call Out Data With Differing Numbers Of Asterisks, Access 2013    
 
   
TeddyFranks
post Jan 13 2020, 07:08 PM
Post#1



Posts: 14
Joined: 30-December 19



Hello,

This may sound simple and likely is, however, I have tried various versions of capturing wildcards and can't seem to figure this out.

Problem: My data set is always 14 characters (short text). Sometimes I have no asterisks, sometimes 2 asterisks and sometimes 4. I need help understanding how to differentiate the data so they can go to separate tables.

In the criteria field I type:

Attempt 1: "*[*]" and can capture all data with an asterisk but can't differentiate any further.
Attempt 2: "*[****]" same result as attempt 1
Attempt 3: "*[**]" same result as attempt 1

Example of what I am trying to accomplish:

XXXXXXXXXX**** -> Table A

XXXXXXXXXXXX** -> Table B

XXXXXXXXXXXXXX - > Table C

Thank you in advance.
Go to the top of the page
 
June7
post Jan 13 2020, 07:22 PM
Post#2



Posts: 1,152
Joined: 25-January 16
From: The Great Land


Are you saying your data actually has asterisk characters to pad value so it always has 14 characters?

The * wildcard matches ALL characters that follow the explicit text. Escaping * so it is treated like literal text is tricky. Easier to use some character other than * to pad values, such as ^.

Replace([fieldname], "*", "^") LIKE "*^^^^"

Construct a field that will assign a group value and apply filter criteria to that calculated value.

Switch(Replace([fieldname],"*","^") LIKE "*^^^^", 1, Replace([fieldname],"*","^") LIKE "*^^", 2, True, 3)

This post has been edited by June7: Jan 13 2020, 07:54 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
John Vinson
post Jan 13 2020, 07:44 PM
Post#3


UtterAccess VIP
Posts: 4,288
Joined: 6-January 07
From: Parma, Idaho, US


As June7 said, using a wildcard as a padding character is a pain. You can escape the wildcard by escaping each individual character:

LIKE "*[*][*][*][*]"

will find strings ending in four asterisks; finding those with two is trickier, since a string ending in four asterisks ipso facto ends in two:

LIKE "*[-*][*][*]"

should work.


--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
GroverParkGeorge
post Jan 13 2020, 10:49 PM
Post#4


UA Admin
Posts: 36,489
Joined: 20-June 02
From: Newcastle, WA


Wow, that's a tough requirement to put on yourself. Is there not a different way to get here, one that doesn't create confusion over the wildcard character (*) itself?

If not, perhaps you can make an assumption about the nature of the values you will that allows you to look at the leading characters, not the trailing padding characters.

In other words, a 14 character string with three asterisks in it would have 11 NON-asterisk characters. A 14 character string with 1 asterisk in it would have 13 NON-asterisk characters, and so on.

I'd look into using that as the criteria, instead of trying to out-fox the wildcards themselves.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TeddyFranks
post Jan 13 2020, 10:51 PM
Post#5



Posts: 14
Joined: 30-December 19



Thank you!

LIKE "*[*][*][*][*]" found only XXXXXXXXXX****. Works perfectly.

the LIKE "*[-*][*][*] still found the XXXXXXXXXX**** but I am able to work around this by using LIKE "*[*][*][*][*]" to remove the 4 asterisk data first, then removing the 2 asterisk text vs the no asterisk.

I appreciate your help.
Go to the top of the page
 
GroverParkGeorge
post Jan 13 2020, 11:13 PM
Post#6


UA Admin
Posts: 36,489
Joined: 20-June 02
From: Newcastle, WA


How about this?

SQL
SELECT tblTestAsterisk.TestAsteriskID, tblTestAsterisk.TestAsterisk, Replace([TestAsterisk],"*","") AS ReplaceAsterisks, Len(Replace([TestAsterisk],"*","")) AS LenNonAsterisks
FROM tblTestAsterisk;


Results:

Attached File  Asterisks.png ( 12.01K )Number of downloads: 0


Instead of identifying asterisks, count NON-asterisks.



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TeddyFranks
post Jan 13 2020, 11:44 PM
Post#7



Posts: 14
Joined: 30-December 19



GroverParkGeorge,

Believe it or not, that is really going to help me on a task in the near future. Thank you in advance for that piece.

The reason I need to keep the asterisks in place is because the data I compare against has the asterisks in place as well.

On the assumption question, there are too many variables in the database (01************ thru 99************) with each asterisk serving as a true wildcard.

The client can pick and choose how specific their submission is. Where 14 non * characters yields the most specific product. The more asterisks in the submission the broader the categories become.

Go to the top of the page
 
GroverParkGeorge
post Jan 14 2020, 07:55 AM
Post#8


UA Admin
Posts: 36,489
Joined: 20-June 02
From: Newcastle, WA


Excellent. But I am not suggesting that you actually remove them. I am saying that your routing to different target tables could be based on counting non-asterisks instead of trying to count asterisks. Flip the logic on its head.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 08:11 AM
Post#9


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


If you are routing the records to different tables (not normally advisable, but that is a different matter), then June7's answer is probably simplest:
CODE
  strSQL = "INSERT INTO TableWith2Asterisks SELECT * FROM YourTable WHERE Replace(YourField, "*", "^") LIKE '*^^';
  CurrentDb.Execute(strSQL, dbFailOnError)
  strSQL = "INSERT INTO TableWith4Asterisks SELECT * FROM YourTable WHERE Replace(YourField, "*", "^") LIKE '*^^^^';
  CurrentDb.Execute(strSQL, dbFailOnError)
  strSQL = "INSERT INTO TableWithNoAsterisks SELECT * FROM YourTable WHERE Replace(YourField, "*", "^") NOT LIKE '*^*';
  CurrentDb.Execute(strSQL, dbFailOnError)


EDIT: please ignore the above

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
June7
post Jan 14 2020, 02:21 PM
Post#10



Posts: 1,152
Joined: 25-January 16
From: The Great Land


@cheekybuddha, actually, I like GPG's suggestion better. It's a shorter expression, the length value acts as group identifier, and doesn't have to deal with issue (as JV points out) that value with four ^ still ends with two ^. This is why I used Switch function to calculate a group identifier. Your query for two ^ will also capture values with four ^.

This post has been edited by June7: Jan 14 2020, 02:22 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 02:25 PM
Post#11


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


You're dead right!

Scratch my recommendation! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
TeddyFranks
post Jan 14 2020, 07:43 PM
Post#12



Posts: 14
Joined: 30-December 19



Thanks GPG,

Missed your point initially. This will work well.

Thank you to the other replies.
Go to the top of the page
 
GroverParkGeorge
post Jan 14 2020, 08:00 PM
Post#13


UA Admin
Posts: 36,489
Joined: 20-June 02
From: Newcastle, WA


Sometimes being a contrarian pays off.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th January 2020 - 07:54 AM