string function

[DP]

Could someone help with what I am sure is a simpe string function?

I have a table with values that tend to fall into the falling three kinds of
formats in the field keywords:

1) xxxxx- xxxxxx - xxxxx - xxxx

2) xxxx, xxxx, xxxxx, xxxxx

3) xxxx xxxxx

I'm trying to query this table in order to get

1) all the distinct rows that begin with xxxxx - (so that the first word
plus " - ") will be fetched

2) return all the other distinct rows with the FULL contents of the row.

I have the code that will filter the table to distinct "xxxxx - " rows using
this kind of function in the query:

"SELECT DISTINCT Left([Keyword],InStr([keyword],' - ')) AS Groupkey FROM
tblKeywordMasterOR"

But I need a similar line of code for a second query (which I will combine
in a UNION with the first) to fetch ALL the other rows in the table as long
as they have no " - " anywhere in the string.

Can someone help me? It seemed simple, but I can't seem to get anywhere -
the code just needs to give me the FULL line as long as there is no " - " in
it. There is no looping code necessary.

Help would be appreciated.

David Pike

 

[PC Datasheet]

Please clarify ---

#1 begins with 5 xs while #2 and #3 begin with 4 Xs. You say you have code to
filter the table to 5 xs and a dash. Does the data begin with 4xs, 5 xs or a mix
and does your code to filter distinguish whether the data begins with 4xs or 5
xs?

 

[DP]

Thanks for your request for more clarity!

With the xxxx's, I was just giving examples of three types of "formats" in
the tables. One contains different numbers of words with dashes (which I can
filter for), the other two formats have different numbers of words but do
NOT contain dashes. In the examples I gave, one had "spaces" (if there was
more than one word, of course: word1 word2 word3), the other had commas
(word1, word2, word3). (The first was: word1 - word2 - word3).

Since I am using a Union Query, the first part of the query gets all the
values with a dash in them. But for the second part (or query) of the Union
Query, I needed a criterion in the sql statement that only fetches values if
they have NO dash or hyphen in them. This would give me all the phrases that
have only spaces or commas in them, but no dashes.

So what I'm looking should be much easier:

Select tblKeywordMaster.[Keyword] AS [alias word] from tblKeywordMaster
WHERE [code that checks tblKeywordsMaster[Keyword] to make sure that there
is no dash];

Does that clarify my question?

Thank you!

David Pike

Please clarify ---

#1 begins with 5 xs while #2 and #3 begin with 4 Xs. You say you have code to
filter the table to 5 xs and a dash. Does the data begin with 4xs, 5 xs or a mix
and does your code to filter distinguish whether the data begins with 4xs or 5
xs?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Could someone help with what I am sure is a simpe string function?

I have a table with values that tend to fall into the falling three kinds of
formats in the field keywords:

1) xxxxx- xxxxxx - xxxxx - xxxx

2) xxxx, xxxx, xxxxx, xxxxx

3) xxxx xxxxx

I'm trying to query this table in order to get

1) all the distinct rows that begin with xxxxx - (so that the first word
plus " - ") will be fetched

2) return all the other distinct rows with the FULL contents of the row.

I have the code that will filter the table to distinct "xxxxx - " rows using
this kind of function in the query:

"SELECT DISTINCT Left([Keyword],InStr([keyword],' - ')) AS Groupkey FROM
tblKeywordMasterOR"

But I need a similar line of code for a second query (which I will combine
in a UNION with the first) to fetch ALL the other rows in the table as long
as they have no " - " anywhere in the string.

Can someone help me? It seemed simple, but I can't seem to get anywhere -
the code just needs to give me the FULL line as long as there is no " - " in
it. There is no looping code necessary.

Help would be appreciated.

David Pike

 

[PC Datasheet]

Where Instr([YourField],"-") = 0

will return all records where "YourField" has no dashes.

You might consider a redesign of your tables to eliminate this problem. Your
tables should be:

TblSubject
SubjectID
Subject

TblKeyword
KeyworID
SubjectID
Keyword

You might also consider this if your keywords all come from a standardized list:

TblKeyword
KeywordID
Keyword

TblKeywordFor Subject
KeywordForSubjectID
SubjectID
KeywordID


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for your request for more clarity!

With the xxxx's, I was just giving examples of three types of "formats" in
the tables. One contains different numbers of words with dashes (which I can
filter for), the other two formats have different numbers of words but do
NOT contain dashes. In the examples I gave, one had "spaces" (if there was
more than one word, of course: word1 word2 word3), the other had commas
(word1, word2, word3). (The first was: word1 - word2 - word3).

Since I am using a Union Query, the first part of the query gets all the
values with a dash in them. But for the second part (or query) of the Union
Query, I needed a criterion in the sql statement that only fetches values if
they have NO dash or hyphen in them. This would give me all the phrases that
have only spaces or commas in them, but no dashes.

So what I'm looking should be much easier:

Select tblKeywordMaster.[Keyword] AS [alias word] from tblKeywordMaster
WHERE [code that checks tblKeywordsMaster[Keyword] to make sure that there
is no dash];

Does that clarify my question?

Thank you!

David Pike

Please clarify ---

#1 begins with 5 xs while #2 and #3 begin with 4 Xs. You say you have code to
filter the table to 5 xs and a dash. Does the data begin with 4xs, 5 xs or a mix
and does your code to filter distinguish whether the data begins with 4xs or 5
xs?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Could someone help with what I am sure is a simpe string function?

I have a table with values that tend to fall into the falling three kinds of
formats in the field keywords:

1) xxxxx- xxxxxx - xxxxx - xxxx

2) xxxx, xxxx, xxxxx, xxxxx

3) xxxx xxxxx

I'm trying to query this table in order to get

1) all the distinct rows that begin with xxxxx - (so that the first word
plus " - ") will be fetched

2) return all the other distinct rows with the FULL contents of the row.

I have the code that will filter the table to distinct "xxxxx - " rows using
this kind of function in the query:

"SELECT DISTINCT Left([Keyword],InStr([keyword],' - ')) AS Groupkey FROM
tblKeywordMasterOR"

But I need a similar line of code for a second query (which I will combine
in a UNION with the first) to fetch ALL the other rows in the table as long
as they have no " - " anywhere in the string.

Can someone help me? It seemed simple, but I can't seem to get anywhere -
the code just needs to give me the FULL line as long as there is no " - " in
it. There is no looping code necessary.

Help would be appreciated.

David Pike

 

[rkc]

Could someone help with what I am sure is a simpe string function?

I have a table with values that tend to fall into the falling three kinds of
formats in the field keywords:

1) xxxxx- xxxxxx - xxxxx - xxxx

2) xxxx, xxxx, xxxxx, xxxxx

3) xxxx xxxxx

I'm trying to query this table in order to get

1) all the distinct rows that begin with xxxxx - (so that the first word
plus " - ") will be fetched

2) return all the other distinct rows with the FULL contents of the row.

I have the code that will filter the table to distinct "xxxxx - " rows using
this kind of function in the query:

"SELECT DISTINCT Left([Keyword],InStr([keyword],' - ')) AS Groupkey FROM
tblKeywordMasterOR"

But I need a similar line of code for a second query (which I will combine
in a UNION with the first) to fetch ALL the other rows in the table as long
as they have no " - " anywhere in the string.

WHERE KeyWord Not Like "*-*"

 

[DP]

Thanks! I knew it was a simple string function of this sort! The advice on
the tables is good, too; the trouble is, the data that go into the field
doesn't easily lend itself to being split into separate fields or table
columns and has a "scientific" or "scholarly" concept behind it.

It is also the case that our approach works perfectly for our purposes (when
searching), so that the only issue was finding code to "split" these
"phrases" into their component parts for display in a Treeview.

Thanks again!

David Pike

Where Instr([YourField],"-") = 0

will return all records where "YourField" has no dashes.

You might consider a redesign of your tables to eliminate this problem. Your
tables should be:

TblSubject
SubjectID
Subject

TblKeyword
KeyworID
SubjectID
Keyword

You might also consider this if your keywords all come from a standardized list:

TblKeyword
KeywordID
Keyword

TblKeywordFor Subject
KeywordForSubjectID
SubjectID
KeywordID


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for your request for more clarity!

With the xxxx's, I was just giving examples of three types of "formats" in
the tables. One contains different numbers of words with dashes (which I can
filter for), the other two formats have different numbers of words but do
NOT contain dashes. In the examples I gave, one had "spaces" (if there was
more than one word, of course: word1 word2 word3), the other had commas
(word1, word2, word3). (The first was: word1 - word2 - word3).

Since I am using a Union Query, the first part of the query gets all the
values with a dash in them. But for the second part (or query) of the Union
Query, I needed a criterion in the sql statement that only fetches values if
they have NO dash or hyphen in them. This would give me all the phrases that
have only spaces or commas in them, but no dashes.

So what I'm looking should be much easier:

Select tblKeywordMaster.[Keyword] AS [alias word] from tblKeywordMaster
WHERE [code that checks tblKeywordsMaster[Keyword] to make sure that there
is no dash];

Does that clarify my question?

Thank you!

David Pike

Please clarify ---

#1 begins with 5 xs while #2 and #3 begin with 4 Xs. You say you have

code
to

filter the table to 5 xs and a dash. Does the data begin with 4xs, 5

xs or
a mix

and does your code to filter distinguish whether the data begins with

4xs
or 5

xs?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Could someone help with what I am sure is a simpe string function?

I have a table with values that tend to fall into the falling three kinds of
formats in the field keywords:

1) xxxxx- xxxxxx - xxxxx - xxxx

2) xxxx, xxxx, xxxxx, xxxxx

3) xxxx xxxxx

I'm trying to query this table in order to get

1) all the distinct rows that begin with xxxxx - (so that the first word
plus " - ") will be fetched

2) return all the other distinct rows with the FULL contents of the row.

I have the code that will filter the table to distinct "xxxxx - "

rows
using

this kind of function in the query:

"SELECT DISTINCT Left([Keyword],InStr([keyword],' - ')) AS Groupkey FROM
tblKeywordMasterOR"

But I need a similar line of code for a second query (which I will combine
in a UNION with the first) to fetch ALL the other rows in the table

as
long

as they have no " - " anywhere in the string.

Can someone help me? It seemed simple, but I can't seem to get anywhere -
the code just needs to give me the FULL line as long as there is no

" -
" in

it. There is no looping code necessary.

Help would be appreciated.

David Pike

 

[DP]

Another simple solution! Too simple for a clever guy like me to have thought
of! Thanks.

DP