Hiya, and thanks to those who replied my post.
Unfortuantly i was not detailed enough and the responces were not suited.
okay here goes:

table eg.

name code
geff a1
geff a2
fred a1

there are 120 names and thousands of codes, i need to parameter query the
code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
what code you want to search, user specs a1, then another dialogue for the
second: a2 (works sort of with Or expr). i cant do this by name as the name
may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
need to specify the code and find out who owns it, its to do with transport.
geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
selecting multiple names would result in far to many results. The Or command
almost works but i get either names with the codes stated in the parameter.

i have used multiple names as the only other way would be to have field
names with all the names or codes, too big...

please help. i need to show IT it can be done
(if it cant i'll just say i didnt want that to happen anyway

A couple of questions.
How will you input the codes you are looking for or do you want it to
automatically detect your code pairs?
Is your code pairs always the code and code incremented by one?
b1, b2 c1000, c1001 Zed1, Zed2,

"doublezer00" wrote:

> Hiya, and thanks to those who replied my post.
> Unfortuantly i was not detailed enough and the responces were not suited.
> okay here goes:
>
> table eg.
>
> name code
> geff a1
> geff a2
> fred a1
>
> there are 120 names and thousands of codes, i need to parameter query the
> code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> what code you want to search, user specs a1, then another dialogue for the
> second: a2 (works sort of with Or expr). i cant do this by name as the name
> may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> need to specify the code and find out who owns it, its to do with transport.
> geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> selecting multiple names would result in far to many results. The Or command
> almost works but i get either names with the codes stated in the parameter.
>
> i have used multiple names as the only other way would be to have field
> names with all the names or codes, too big...
>
> please help. i need to show IT it can be done
> (if it cant i'll just say i didnt want that to happen anyway
>
>

SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
= DoubleZer00_1.Name
WHERE
(((DoubleZer00_1.Code)=Left([DoubleZer00].[Code],Len([DoubleZer00].[Code])-1)
& Val(Right([DoubleZer00].[Code],1))+"1"));


"KARL DEWEY" wrote:

> A couple of questions.
> How will you input the codes you are looking for or do you want it to
> automatically detect your code pairs?
> Is your code pairs always the code and code incremented by one?
> b1, b2 c1000, c1001 Zed1, Zed2,
>
> "doublezer00" wrote:
>
> > Hiya, and thanks to those who replied my post.
> > Unfortuantly i was not detailed enough and the responces were not suited.
> > okay here goes:
> >
> > table eg.
> >
> > name code
> > geff a1
> > geff a2
> > fred a1
> >
> > there are 120 names and thousands of codes, i need to parameter query the
> > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> > what code you want to search, user specs a1, then another dialogue for the
> > second: a2 (works sort of with Or expr). i cant do this by name as the name
> > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> > need to specify the code and find out who owns it, its to do with transport.
> > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> > selecting multiple names would result in far to many results. The Or command
> > almost works but i get either names with the codes stated in the parameter.
> >
> > i have used multiple names as the only other way would be to have field
> > names with all the names or codes, too big...
> >
> > please help. i need to show IT it can be done
> > (if it cant i'll just say i didnt want that to happen anyway
> >
> >

its random codes. some names might have many different ones.
will this work? and will it give an input dialogue? need upto 5 inputs
matching all the codes, and showing who owns which codes,

thanks

"KARL DEWEY" wrote:

>
> SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
> FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
> = DoubleZer00_1.Name
> WHERE
> (((DoubleZer00_1.Code)=Left([DoubleZer00].[Code],Len([DoubleZer00].[Code])-1)
> & Val(Right([DoubleZer00].[Code],1))+"1"));
>
>
> "KARL DEWEY" wrote:
>
> > A couple of questions.
> > How will you input the codes you are looking for or do you want it to
> > automatically detect your code pairs?
> > Is your code pairs always the code and code incremented by one?
> > b1, b2 c1000, c1001 Zed1, Zed2,
> >
> > "doublezer00" wrote:
> >
> > > Hiya, and thanks to those who replied my post.
> > > Unfortuantly i was not detailed enough and the responces were not suited.
> > > okay here goes:
> > >
> > > table eg.
> > >
> > > name code
> > > geff a1
> > > geff a2
> > > fred a1
> > >
> > > there are 120 names and thousands of codes, i need to parameter query the
> > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> > > what code you want to search, user specs a1, then another dialogue for the
> > > second: a2 (works sort of with Or expr). i cant do this by name as the name
> > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> > > need to specify the code and find out who owns it, its to do with transport.
> > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> > > selecting multiple names would result in far to many results. The Or command
> > > almost works but i get either names with the codes stated in the parameter.
> > >
> > > i have used multiple names as the only other way would be to have field
> > > names with all the names or codes, too big...
> > >
> > > please help. i need to show IT it can be done
> > > (if it cant i'll just say i didnt want that to happen anyway
> > >
> > >

Can you give a better example of data?

How will you input the codes you are looking for?

Will you want it to automatically detect your code pairs?

Are the codes always in pairs (two in a combination) or a sequence of codes?


"doublezer00" wrote:

> its random codes. some names might have many different ones.
> will this work? and will it give an input dialogue? need upto 5 inputs
> matching all the codes, and showing who owns which codes,
>
> thanks
>
> "KARL DEWEY" wrote:
>
> >
> > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
> > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
> > = DoubleZer00_1.Name
> > WHERE
> > (((DoubleZer00_1.Code)=Left([DoubleZer00].[Code],Len([DoubleZer00].[Code])-1)
> > & Val(Right([DoubleZer00].[Code],1))+"1"));
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > A couple of questions.
> > > How will you input the codes you are looking for or do you want it to
> > > automatically detect your code pairs?
> > > Is your code pairs always the code and code incremented by one?
> > > b1, b2 c1000, c1001 Zed1, Zed2,
> > >
> > > "doublezer00" wrote:
> > >
> > > > Hiya, and thanks to those who replied my post.
> > > > Unfortuantly i was not detailed enough and the responces were not suited.
> > > > okay here goes:
> > > >
> > > > table eg.
> > > >
> > > > name code
> > > > geff a1
> > > > geff a2
> > > > fred a1
> > > >
> > > > there are 120 names and thousands of codes, i need to parameter query the
> > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> > > > what code you want to search, user specs a1, then another dialogue for the
> > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
> > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> > > > need to specify the code and find out who owns it, its to do with transport.
> > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> > > > selecting multiple names would result in far to many results. The Or command
> > > > almost works but i get either names with the codes stated in the parameter.
> > > >
> > > > i have used multiple names as the only other way would be to have field
> > > > names with all the names or codes, too big...
> > > >
> > > > please help. i need to show IT it can be done
> > > > (if it cant i'll just say i didnt want that to happen anyway
> > > >
> > > >

I think this might do it. When prompted for the third entry you can enter
data or just press ENTER if you only have two map codes.

SELECT DoubleZer00.intial, DoubleZer00.surname, DoubleZer00.[map code],
DoubleZer00_1.[map code], DoubleZer00_2.[map code]
FROM (DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON
(DoubleZer00.surname = DoubleZer00_1.surname) AND (DoubleZer00.intial =
DoubleZer00_1.intial)) INNER JOIN DoubleZer00 AS DoubleZer00_2 ON
(DoubleZer00.surname = DoubleZer00_2.surname) AND (DoubleZer00.intial =
DoubleZer00_2.intial)
WHERE (((DoubleZer00.[map code])=[enter route 1]) AND ((DoubleZer00_1.[map
code])=[enter route 2]) AND ((DoubleZer00_2.[map code]) Like IIf([enter route
3] Is Null,"*",[enter route 3])));


"doublezer00" wrote:

> okay the tables are like so:
>
> intial surname map code
> g davis mmo1
> g davis mmo2
> g davis gyh14
> g davis poiu11
> a smith mmo1
> a smith mmo2
> a smith adwe23
>
> query criteria: [enter route 1]Or[enter route 2] Or [enter route 3]
>
> parameters: mmo1 mmo2 gyh14
>
> results are:
>
> intial surname map code
> g davis mmo1
> g davis mmo2
> g davis gyh14
> a smith mmo1
> a smith mmo2
>
> i need just g davis as he only knows all three parameters, And does not work
> cos the way the data is laid out, only one column for map codes, reason: cant
> turn fields into map codes, ie. enter yes to drivers who know route on each
> field as there are to many and you would still not be able to query with one
> field for all routes?
>
> its a knightmare, im not all that good at VB so could you let me know what
> some of the expression are, also for sql. whether its select table or field
> etc..
>
> cheers matey
>
>
> "KARL DEWEY" wrote:
>
> > Can you give a better example of data?
> >
> > How will you input the codes you are looking for?
> >
> > Will you want it to automatically detect your code pairs?
> >
> > Are the codes always in pairs (two in a combination) or a sequence of codes?
> >
> >
> > "doublezer00" wrote:
> >
> > > its random codes. some names might have many different ones.
> > > will this work? and will it give an input dialogue? need upto 5 inputs
> > > matching all the codes, and showing who owns which codes,
> > >
> > > thanks
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > >
> > > > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
> > > > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
> > > > = DoubleZer00_1.Name
> > > > WHERE
> > > > (((DoubleZer00_1.Code)=Left([DoubleZer00].[Code],Len([DoubleZer00].[Code])-1)
> > > > & Val(Right([DoubleZer00].[Code],1))+"1"));
> > > >
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > A couple of questions.
> > > > > How will you input the codes you are looking for or do you want it to
> > > > > automatically detect your code pairs?
> > > > > Is your code pairs always the code and code incremented by one?
> > > > > b1, b2 c1000, c1001 Zed1, Zed2,
> > > > >
> > > > > "doublezer00" wrote:
> > > > >
> > > > > > Hiya, and thanks to those who replied my post.
> > > > > > Unfortuantly i was not detailed enough and the responces were not suited.
> > > > > > okay here goes:
> > > > > >
> > > > > > table eg.
> > > > > >
> > > > > > name code
> > > > > > geff a1
> > > > > > geff a2
> > > > > > fred a1
> > > > > >
> > > > > > there are 120 names and thousands of codes, i need to parameter query the
> > > > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> > > > > > what code you want to search, user specs a1, then another dialogue for the
> > > > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
> > > > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> > > > > > need to specify the code and find out who owns it, its to do with transport.
> > > > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> > > > > > selecting multiple names would result in far to many results. The Or command
> > > > > > almost works but i get either names with the codes stated in the parameter.
> > > > > >
> > > > > > i have used multiple names as the only other way would be to have field
> > > > > > names with all the names or codes, too big...
> > > > > >
> > > > > > please help. i need to show IT it can be done
> > > > > > (if it cant i'll just say i didnt want that to happen anyway
> > > > > >
> > > > > >

okay the tables are like so:

intial surname map code
g davis mmo1
g davis mmo2
g davis gyh14
g davis poiu11
a smith mmo1
a smith mmo2
a smith adwe23

query criteria: [enter route 1]Or[enter route 2] Or [enter route 3]

parameters: mmo1 mmo2 gyh14

results are:

intial surname map code
g davis mmo1
g davis mmo2
g davis gyh14
a smith mmo1
a smith mmo2

i need just g davis as he only knows all three parameters, And does not work
cos the way the data is laid out, only one column for map codes, reason: cant
turn fields into map codes, ie. enter yes to drivers who know route on each
field as there are to many and you would still not be able to query with one
field for all routes?

its a knightmare, im not all that good at VB so could you let me know what
some of the expression are, also for sql. whether its select table or field
etc..

cheers matey


"KARL DEWEY" wrote:

> Can you give a better example of data?
>
> How will you input the codes you are looking for?
>
> Will you want it to automatically detect your code pairs?
>
> Are the codes always in pairs (two in a combination) or a sequence of codes?
>
>
> "doublezer00" wrote:
>
> > its random codes. some names might have many different ones.
> > will this work? and will it give an input dialogue? need upto 5 inputs
> > matching all the codes, and showing who owns which codes,
> >
> > thanks
> >
> > "KARL DEWEY" wrote:
> >
> > >
> > > SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
> > > FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
> > > = DoubleZer00_1.Name
> > > WHERE
> > > (((DoubleZer00_1.Code)=Left([DoubleZer00].[Code],Len([DoubleZer00].[Code])-1)
> > > & Val(Right([DoubleZer00].[Code],1))+"1"));
> > >
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > A couple of questions.
> > > > How will you input the codes you are looking for or do you want it to
> > > > automatically detect your code pairs?
> > > > Is your code pairs always the code and code incremented by one?
> > > > b1, b2 c1000, c1001 Zed1, Zed2,
> > > >
> > > > "doublezer00" wrote:
> > > >
> > > > > Hiya, and thanks to those who replied my post.
> > > > > Unfortuantly i was not detailed enough and the responces were not suited.
> > > > > okay here goes:
> > > > >
> > > > > table eg.
> > > > >
> > > > > name code
> > > > > geff a1
> > > > > geff a2
> > > > > fred a1
> > > > >
> > > > > there are 120 names and thousands of codes, i need to parameter query the
> > > > > code that shows eg. a1 And a2, which would not display fred. Eg. dialogue
> > > > > what code you want to search, user specs a1, then another dialogue for the
> > > > > second: a2 (works sort of with Or expr). i cant do this by name as the name
> > > > > may appear hundreds of times, eg, lets say the codes go to b1 b2 and so on. i
> > > > > need to specify the code and find out who owns it, its to do with transport.
> > > > > geff may appear 1000s of times with extra codes b1, b2 c1000, c1001.
> > > > > selecting multiple names would result in far to many results. The Or command
> > > > > almost works but i get either names with the codes stated in the parameter.
> > > > >
> > > > > i have used multiple names as the only other way would be to have field
> > > > > names with all the names or codes, too big...
> > > > >
> > > > > please help. i need to show IT it can be done
> > > > > (if it cant i'll just say i didnt want that to happen anyway
> > > > >
> > > > >