complex code angie 11/22/2008 6:01:01 PM i have an unbound text box in my form. the user can enter anything there and on enter runs a query that returns records that contain part or the entire value entered. this is the criteria i have entered in the deisred field of my query: Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". problem statement: each record in my table field contains a combination of letters, numbers and characters. the letters may be either greek or latin alphabet. e.g if i enter "5860H5212" the letter "H" can be either greek or latin. is there a way to enter a code that will seek for both characters?

Re: complex code "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/22/2008 10:02:57 PM angie wrote: [Quoted Text] > i have an unbound text box in my form. the user can enter anything > there and on enter runs a query that returns records that contain > part or the entire value entered. > this is the criteria i have entered in the deisred field of my query: > Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". > > problem statement: each record in my table field contains a > combination of letters, numbers and characters. the letters may be > either greek or latin alphabet. > e.g if i enter "5860H5212" the letter "H" can be either greek or > latin. is there a way to enter a code that will seek for both > characters? Is "5860H5212" with Greek, different from "5860H5212" with latin? Can there be more than one letter? If yes can there be a mix of letter in any given code? You can loop through the code with the Mid function and replace the letter with it's other possible value then construct a string with then "OR" ed together. If there is only one letter it's simple. If all the letters are the same language then you have 2 N values to construct. If they can vary then two letters gives four codes LL,GG,GL, LG. and it goes up from there N letters gives 2^N choices.

Re: complex code angie 11/24/2008 9:10:09 AM first of all thank you for your reply. "5860H5212" with Greek is different from "5860H5212" with latin, there can be more than one letter (e.g. 3680ABC90) and yes there can be a mix of letter in any given code. from what i understand it is quite complex and the problem is i am new to sql. i need ypu to guide me step by step through this! Ο χρήστης "Mike Painter" έγγραψε: [Quoted Text] > angie wrote: > > i have an unbound text box in my form. the user can enter anything > > there and on enter runs a query that returns records that contain > > part or the entire value entered. > > this is the criteria i have entered in the deisred field of my query: > > Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". > > > > problem statement: each record in my table field contains a > > combination of letters, numbers and characters. the letters may be > > either greek or latin alphabet. > > e.g if i enter "5860H5212" the letter "H" can be either greek or > > latin. is there a way to enter a code that will seek for both > > characters? > > Is "5860H5212" with Greek, different from "5860H5212" with latin? > Can there be more than one letter? > If yes can there be a mix of letter in any given code? > > > > You can loop through the code with the Mid function and replace the letter > with it's other possible value then construct a string with then "OR" ed > together. > If there is only one letter it's simple. > If all the letters are the same language then you have 2 N values to > construct. > If they can vary then two letters gives four codes LL,GG,GL, LG. and it goes > up from there > N letters gives 2^N choices. > > >

Re: complex code "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/24/2008 6:01:13 PM What do you want to do with the letter once found? If 123<greekletter>456 is found do you want to generate 123<latinletter>456? and vice versa? angie wrote: [Quoted Text] > first of all thank you for your reply. > > "5860H5212" with Greek is different from "5860H5212" with latin, > there can be more than one letter (e.g. 3680ABC90) and yes there can > be a mix of letter in any given code. > > from what i understand it is quite complex and the problem is i am > new to sql. i need ypu to guide me step by step through this! > > ? ??????? "Mike Painter" ???????: > >> angie wrote: >>> i have an unbound text box in my form. the user can enter anything >>> there and on enter runs a query that returns records that contain >>> part or the entire value entered. >>> this is the criteria i have entered in the deisred field of my >>> query: Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". >>> >>> problem statement: each record in my table field contains a >>> combination of letters, numbers and characters. the letters may be >>> either greek or latin alphabet. >>> e.g if i enter "5860H5212" the letter "H" can be either greek or >>> latin. is there a way to enter a code that will seek for both >>> characters? >> >> Is "5860H5212" with Greek, different from "5860H5212" with latin? >> Can there be more than one letter? >> If yes can there be a mix of letter in any given code? >> >> >> >> You can loop through the code with the Mid function and replace the >> letter with it's other possible value then construct a string with >> then "OR" ed together. >> If there is only one letter it's simple. >> If all the letters are the same language then you have 2 N values to >> construct. >> If they can vary then two letters gives four codes LL,GG,GL, LG. and >> it goes up from there >> N letters gives 2^N choices.

Re: complex code "Damon Heron" <damon_327[ at ]hotmail.com> 11/24/2008 7:57:23 PM In your examples, you always show the letters in the center of the string. If this is true, then you don't have to search the whole string. For instance, if you have a latin record 123ABC678 and a greek record 123"ABC"(greek)678, then use the Left or Right function to search just the left or right part of the code which will return all of the records --- Like ("*" & Left([Forms]![Form2-WAREHOUSE]![CODE SEEK],3) & "*") that match the first 3 characters, which would include the greek. Similarly, use the right function if the records always end in a latin numerical sequence. Damon "angie" <angie[ at ]discussions.microsoft.com> wrote in message news:3CBA7FC9-873C-46D0-A25B-CF307F21C1C2[ at ]microsoft.com... [Quoted Text] > first of all thank you for your reply. > > "5860H5212" with Greek is different from "5860H5212" with latin, there > can be more than one letter (e.g. 3680ABC90) and yes there can be a mix > of > letter in any given code. > > from what i understand it is quite complex and the problem is i am new to > sql. i need ypu to guide me step by step through this! > > ? ??????? "Mike Painter" ???????: > >> angie wrote: >> > i have an unbound text box in my form. the user can enter anything >> > there and on enter runs a query that returns records that contain >> > part or the entire value entered. >> > this is the criteria i have entered in the deisred field of my query: >> > Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". >> > >> > problem statement: each record in my table field contains a >> > combination of letters, numbers and characters. the letters may be >> > either greek or latin alphabet. >> > e.g if i enter "5860H5212" the letter "H" can be either greek or >> > latin. is there a way to enter a code that will seek for both >> > characters? >> >> Is "5860H5212" with Greek, different from "5860H5212" with latin? >> Can there be more than one letter? >> If yes can there be a mix of letter in any given code? >> >> >> >> You can loop through the code with the Mid function and replace the >> letter >> with it's other possible value then construct a string with then "OR" ed >> together. >> If there is only one letter it's simple. >> If all the letters are the same language then you have 2 N values to >> construct. >> If they can vary then two letters gives four codes LL,GG,GL, LG. and it >> goes >> up from there >> N letters gives 2^N choices. >> >> >>

Re: complex code angie 11/24/2008 9:44:02 PM my table contains 25000 records with 12 fields. it is an autoparts database. the first field is the autopart code and the remaining 11 fields consist of description, genuine numbers, cross references, etc. i want to use the unbound textbox of my form like a search engine: if i enter a code or part of a code i want it to look up the 25000 records and the 12 fields to find a match. autopart genuine numbers and cross references codes like ferodo, brembo, etc consist of a combination of letters,symbols and numbers that could be anywhere in the field. (examples: 11S33BC88, FDB320, 08.3590.62, 11000-36000, etc. could i achieve my goal to use this textbox as the desired "powerfull" tool? Ο χρήστης "Mike Painter" έγγραψε: [Quoted Text] > > > What do you want to do with the letter once found? > If 123<greekletter>456 is found do you want to generate > 123<latinletter>456? and vice versa? > > angie wrote: > > first of all thank you for your reply. > > > > "5860H5212" with Greek is different from "5860H5212" with latin, > > there can be more than one letter (e.g. 3680ABC90) and yes there can > > be a mix of letter in any given code. > > > > from what i understand it is quite complex and the problem is i am > > new to sql. i need ypu to guide me step by step through this! > > > > ? ??????? "Mike Painter" ???????: > > > >> angie wrote: > >>> i have an unbound text box in my form. the user can enter anything > >>> there and on enter runs a query that returns records that contain > >>> part or the entire value entered. > >>> this is the criteria i have entered in the deisred field of my > >>> query: Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". > >>> > >>> problem statement: each record in my table field contains a > >>> combination of letters, numbers and characters. the letters may be > >>> either greek or latin alphabet. > >>> e.g if i enter "5860H5212" the letter "H" can be either greek or > >>> latin. is there a way to enter a code that will seek for both > >>> characters? > >> > >> Is "5860H5212" with Greek, different from "5860H5212" with latin? > >> Can there be more than one letter? > >> If yes can there be a mix of letter in any given code? > >> > >> > >> > >> You can loop through the code with the Mid function and replace the > >> letter with it's other possible value then construct a string with > >> then "OR" ed together. > >> If there is only one letter it's simple. > >> If all the letters are the same language then you have 2 N values to > >> construct. > >> If they can vary then two letters gives four codes LL,GG,GL, LG. and > >> it goes up from there > >> N letters gives 2^N choices. > > >

Re: complex code "Damon Heron" <damon_327[ at ]hotmail.com> 11/24/2008 11:44:07 PM The user would be typing in an autopart code? I admire your users for their memory capacity - 25000 records!! Wouldn't it be better to do the search on some other field like description "Radiator hose" and then select from the hits returned to get the correct part? Damon "angie" <angie[ at ]discussions.microsoft.com> wrote in message news:74B647F7-46EE-44AC-9FB6-6EE5F3292D28[ at ]microsoft.com... [Quoted Text] > my table contains 25000 records with 12 fields. it is an autoparts > database. > the first field is the autopart code and the remaining 11 fields consist > of > description, genuine numbers, cross references, etc. i want to use the > unbound textbox of my form like a search engine: if i enter a code or part > of > a code i want it to look up the 25000 records and the 12 fields to find a > match. > > autopart genuine numbers and cross references codes like ferodo, brembo, > etc > consist of a combination of letters,symbols and numbers that could be > anywhere in the field. (examples: 11S33BC88, FDB320, 08.3590.62, > 11000-36000, > etc. > > could i achieve my goal to use this textbox as the desired "powerfull" > tool? > > ? ??????? "Mike Painter" ???????: > >> >> >> What do you want to do with the letter once found? >> If 123<greekletter>456 is found do you want to generate >> 123<latinletter>456? and vice versa? >> >> angie wrote: >> > first of all thank you for your reply. >> > >> > "5860H5212" with Greek is different from "5860H5212" with latin, >> > there can be more than one letter (e.g. 3680ABC90) and yes there can >> > be a mix of letter in any given code. >> > >> > from what i understand it is quite complex and the problem is i am >> > new to sql. i need ypu to guide me step by step through this! >> > >> > ? ??????? "Mike Painter" ???????: >> > >> >> angie wrote: >> >>> i have an unbound text box in my form. the user can enter anything >> >>> there and on enter runs a query that returns records that contain >> >>> part or the entire value entered. >> >>> this is the criteria i have entered in the deisred field of my >> >>> query: Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". >> >>> >> >>> problem statement: each record in my table field contains a >> >>> combination of letters, numbers and characters. the letters may be >> >>> either greek or latin alphabet. >> >>> e.g if i enter "5860H5212" the letter "H" can be either greek or >> >>> latin. is there a way to enter a code that will seek for both >> >>> characters? >> >> >> >> Is "5860H5212" with Greek, different from "5860H5212" with latin? >> >> Can there be more than one letter? >> >> If yes can there be a mix of letter in any given code? >> >> >> >> >> >> >> >> You can loop through the code with the Mid function and replace the >> >> letter with it's other possible value then construct a string with >> >> then "OR" ed together. >> >> If there is only one letter it's simple. >> >> If all the letters are the same language then you have 2 N values to >> >> construct. >> >> If they can vary then two letters gives four codes LL,GG,GL, LG. and >> >> it goes up from there >> >> N letters gives 2^N choices. >> >> >>

Re: complex code "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/25/2008 12:45:58 AM Damon Heron wrote: [Quoted Text] > The user would be typing in an autopart code? I admire your users > for their memory capacity - 25000 records!! > Wouldn't it be better to do the search on some other field like > description "Radiator hose" and then select from the hits returned > to get the correct part? > A favored professor used to say, "Do you know what's wrong with that idea?" After teh worried first timer tried to figure out how badly he had screwed up, the prof would say, "I didn't think of it." Do you know what's wrong with your idea?

Re: complex code "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/25/2008 1:06:03 AM Looking up the contents of one field will be a problem. Searching all 12 fields will just make it harder. You will have to generate 12 different queries each with 2^N "OR"s. The results will have to be saved or presented in some way, possibly by using 11 append queries. Then you will have to decide how to display them. You probably need to eliminate duplicates also. In my opinion this is not a job for somebody new to Access unless you are already a proficient coder and have time. I'm also guessing that preprocessing these items as they come in might be a better way to go. However as pointed out by Damon if you can look the part up by a description you have a head start. It still would be an uphill battle because "Radiator hose" might be "Hose, Radiator" or "12 cm Radiator hose" depending on vendor, assuming the language is all the same. angie wrote: [Quoted Text] > my table contains 25000 records with 12 fields. it is an autoparts > database. the first field is the autopart code and the remaining 11 > fields consist of description, genuine numbers, cross references, > etc. i want to use the unbound textbox of my form like a search > engine: if i enter a code or part of a code i want it to look up the > 25000 records and the 12 fields to find a match. > > autopart genuine numbers and cross references codes like ferodo, > brembo, etc consist of a combination of letters,symbols and numbers > that could be anywhere in the field. (examples: 11S33BC88, FDB320, > 08.3590.62, 11000-36000, etc. > > could i achieve my goal to use this textbox as the desired > "powerfull" tool? > > ? ??????? "Mike Painter" ???????: > >> >> >> What do you want to do with the letter once found? >> If 123<greekletter>456 is found do you want to generate >> 123<latinletter>456? and vice versa? >> >> angie wrote: >>> first of all thank you for your reply. >>> >>> "5860H5212" with Greek is different from "5860H5212" with latin, >>> there can be more than one letter (e.g. 3680ABC90) and yes there >>> can be a mix of letter in any given code. >>> >>> from what i understand it is quite complex and the problem is i am >>> new to sql. i need ypu to guide me step by step through this! >>> >>> ? ??????? "Mike Painter" ???????: >>> >>>> angie wrote: >>>>> i have an unbound text box in my form. the user can enter >>>>> anything there and on enter runs a query that returns records >>>>> that contain part or the entire value entered. >>>>> this is the criteria i have entered in the deisred field of my >>>>> query: Like "*" & [Forms]![Form2-WAREHOUSE]![CODE SEEK] & "*". >>>>> >>>>> problem statement: each record in my table field contains a >>>>> combination of letters, numbers and characters. the letters may be >>>>> either greek or latin alphabet. >>>>> e.g if i enter "5860H5212" the letter "H" can be either greek or >>>>> latin. is there a way to enter a code that will seek for both >>>>> characters? >>>> >>>> Is "5860H5212" with Greek, different from "5860H5212" with latin? >>>> Can there be more than one letter? >>>> If yes can there be a mix of letter in any given code? >>>> >>>> >>>> >>>> You can loop through the code with the Mid function and replace the >>>> letter with it's other possible value then construct a string with >>>> then "OR" ed together. >>>> If there is only one letter it's simple. >>>> If all the letters are the same language then you have 2 N values >>>> to construct. >>>> If they can vary then two letters gives four codes LL,GG,GL, LG. >>>> and it goes up from there >>>> N letters gives 2^N choices.

Re: complex code angie 11/25/2008 8:37:01 AM my query is based on another query that filters the records. the user selects the autopart e.g. brake pads and then enters the code to be looked up for. basically the problem is with the database i draw data from. it is a dos based program with limited fields so the genuine and cross references codes have been entered in many fields. that is why i have to search 12 fields to find a match. i have partly achieved my goal, if i enter part of the code (only numbers, symbols and latin characters like "f") it returns all records that contain that combination. i want to troubleshoot the mix with greek and latin characters. if i enter just "a" for example, i want access to return records that contain "a" either if it is greek or latin. Ο χρήστης "Mike Painter" έγγραψε: [Quoted Text] > Damon Heron wrote: > > The user would be typing in an autopart code? I admire your users > > for their memory capacity - 25000 records!! > > Wouldn't it be better to do the search on some other field like > > description "Radiator hose" and then select from the hits returned > > to get the correct part? > > > A favored professor used to say, > "Do you know what's wrong with that idea?" > > After teh worried first timer tried to figure out how badly he had screwed > up, the prof would say, > "I didn't think of it." > > Do you know what's wrong with your idea? > > >