numbers from a textfield (1 Viewer)

Morriske

New member
Local time
Today, 23:55
Joined
Feb 11, 2019
Messages
8
Hallo,

I have a table with more than 10.000 records. Each record is a product en has a product number which contains letters and numbers. Ex F110NB or 190B. Some letters are referring to a model some to a color. The letters and the numbers differ in every record and sometimes there are 6 characters, sometimes 4, 9 or 10. I would like a query which returns only the numbers.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:55
Joined
Oct 29, 2018
Messages
21,358
Hi. You might be able to use Regular Expressions. Are there any records with more than one set of numbers? For example: F110NB190B; and if so, would you like to return both sets?
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:55
Joined
Jan 23, 2006
Messages
15,364
I agree with theDBguy's suggestion, but I feel you may have some design issues. Relational database works very well with atomic fields. That is 1 fact---1 field.
Product identifier, model, serial, color, length, weight, manufactureDate... are separate "things"/attributes.
Concocting codes where different characters mean different things or concepts has been the downfall of many databases. Such approaches often require "work arounds" or custom coding to select or extract these "things".

You may wish to look at your design holistically before making decisions on next steps.
Good luck with your project.
 

Morriske

New member
Local time
Today, 23:55
Joined
Feb 11, 2019
Messages
8
The problem is that I don't give the product numbers, they come from the suppliers. Sometimes it happens that different suppliers give other product numbers to the same products. In my case its about textiles; with one supplier a poloshirt has the number BC110FR but with an other supplier the same shirt has the number GU110FR.
 

Micron

AWF VIP
Local time
Today, 18:55
Joined
Oct 20, 2018
Messages
3,476
I sympathize with your lack of control over the data. Seems to me that there'd be no benefit to splitting it up into fields only to put back together again in forms/queries when you have no idea what form the string might take. RegEx is the way to go, IMHO. I suck at it, but did cull together something that would probably work. Hopefully others will chime in and fix what's missing (thinking lack of ensuring there is a RegEx object in play as an example).
Code:
Sub ExtractNums(strIn As String)
Dim result As String, reo As Object

Set reo = CreateObject("VBScript.RegExp")
reo.IgnoreCase = True
reo.Global = True
reo.pattern = "[^\d.:+]"
result = reo.Replace(strIn, vbNullString)
Debug.Print result

 End Sub
Input and output:
extractnums ("g145h4er4e4")
145444

Don't ask me to explain the pattern! It will return . and : as written so if you have those and don't want them, adjust accordingly.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:55
Joined
Jan 23, 2006
Messages
15,364
Morriske,
Can you tell us more about this database? What is it used for?
In your example BC110FR from supplier and GU110FR from another, a regex approach
could get you 110 -from both. What info do you really need from these Product identifiers and for what purpose?
It would help if you gave us "the big picture".
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:55
Joined
Feb 19, 2013
Messages
16,553
you could use a different function to look through the string and build a number by ignoring alpha chars - but agree need the bigger picture - a full variety of the product codes and which bit(s) are you trying to extract. I suspect your examples of BC110FR and GR110FR are too simple
 

June7

AWF VIP
Local time
Today, 14:55
Joined
Mar 9, 2014
Messages
5,423
To use Regex with early binding, set VBA reference to Microsoft VBScript Regular Expressions x.x library.
 

Morriske

New member
Local time
Today, 23:55
Joined
Feb 11, 2019
Messages
8
The table is part of an application to take orders, keep customer data, make invoices...
I'm looking for the easiest way to make a selection, the user has to pick the right product. Now I think a temp variable, a macro and "Like" will be the most obvious.
Getting letters and numbers from a string looked to my as something that would be easy but it seems like a greater challenge then I thought.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:55
Joined
Feb 19, 2013
Messages
16,553
The table is part of an application to take orders, keep customer data, make invoices...
I'm looking for the easiest way to make a selection
sounds like you don't have the right table structure. If you have same product/different manufacturers each with their own codes you need a minimum of three tables. One that contains your product and description plus other fields as required, one that contains your manufacturers details and a joining table that links the two together and also contains the manufacturers code/description

Then on a form, use a combobox to select the required product and manufacturer, no temp variables or macros required
 

Morriske

New member
Local time
Today, 23:55
Joined
Feb 11, 2019
Messages
8
The main problem is I don't have enough info. I just have a large excel-file, I don't know how many suppliers there are. An other problem is what about updates, products will disappear en new ones will have to be added. Prices won't stay the same... We'll have to make more preparations before starting. An other lesson...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:55
Joined
Feb 19, 2013
Messages
16,553
excel files typically have denormalised data. Databases use normalised data.

You first task is to analyse the excel file to determine how you can normalise the data, then create tables in line with this normalised structure and finally create append/update queries to populate these tables. Then you are in a position to move on.
 

Users who are viewing this thread

Top Bottom