Data values contain Parentheses (N) with String inside

[DavidG]

I have a database which has data values in one field where the data contains
values that have parentheses which also contain a string (Letter). One value
has a full.stop in the middle of the name. When trying to use these data
values in an IN clause I get an error which seems to suggest Access SQL is
interpreting the parentheses as a Function, which it isn't. They are just
plain vanilla data values being used in the SQL. Actual examples of these
data values are:
Canterbury(N) M.Valley(N) Randwick(I)
How can I successfully deploy these values into (a) an IN clause and (b)
into normal SQL queries?
Thanks
David

 

[Sylvain Lafontaine]

The first thing to do would be to show us the exact error message that you
are getting and the sql code that you are using. Of course, there is a big
difference between using a sql query as a querydef or as a string to be
called/executed; so you should provide the necessary details if necessary.
You might as well include other things like the structure of the tables and
examples of data if you think that they might be useful.

Explaining what you mean with the expression "normal SQL queries" would also
be of a big help if you want to get an answer.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Dorian]

It is never a good idea to have embedded spaces or any special characters in
the column names of a table. I suggest you eliminate them.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Bob Barrows]

I have a database which has data values in one field where the data
contains values that have parentheses which also contain a string
(Letter). One value has a full.stop in the middle of the name. When
trying to use these data values in an IN clause I get an error which
seems to suggest Access SQL is interpreting the parentheses as a
Function, which it isn't. They are just plain vanilla data values
being used in the SQL. Actual examples of these data values are:
Canterbury(N) M.Valley(N) Randwick(I)
How can I successfully deploy these values into (a) an IN clause and
(b) into normal SQL queries?
Thanks
David

I hesitate to ask since it should be basic but ... I assume you are
delimiting these values like this:
IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)")

 

[John Spencer]

I assume you are entering these values in query design view and are not typing
in the text delimiters when you do so.

So the expression service is attempting to treat the city name as a function
and the letter as an argument to the function.

You need to enter the quote marks as you are typing.
In ("Canterbury(N)","M.Valley(N)","Randwick(I)")

Use a semi-colon ( as the item delimiter instead of the comma (,) if that is
the setting for your locale.

If you type
In (Canterbury(N),M.Valley(N),Randwick(I))
the expression service will attempt to parse that and return properly quoted
text as
In (Canterbury("N"),M.Valley("N"),Randwick("I"))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[DavidG]

There doesn't appear to be a "Reply to All" option to give my response to the
other individual posts. So I have written a blanket, cover-all response.

In response to Sylvain the code is as follows:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS CountOfRaceID
FROM tblRaceData
GROUP BY tblRaceData.RaceTrack
HAVING (((tblRaceData.RaceTrack) In
(Canterbury("N"),"Rosehill",Randwick("I"),"Doomben",M.Valley("N"))));

Copied directly from SQL view in Access 2007.

The Err description is "Undefined function 'Canterbury' in expression. As I
suggested it is treating the data value as if it were a function, but it is
not a function. I need to know how I work around this.

OK, In response to everyone's posting:

I am dealing with data that comes to me "as is". I am doing analysis of
the existing data. I do NOT input or create this data. I realise that I
would be causing myself grief if I were to create data with these values - I
do know that. I want to know HOW to deal with this data now that I have it!

I need to know how to deal with this data so I can analyse it.

Dorian:
I didn't create the data so I do not have any authority over how it is
created.

Bob:
Again, I didn't create this data so, yes I understand the basics. Hopefully
we can get past these basics to get an answer.

To All:
Thanks very much for the responses, I hope we can get to how to deal with
this data, knowing that I didn't have any say in how it was organised.

Thanks
David

 

[DavidG]

I understand your concerns but I'm an end user of the data. I need to know
how I can successfully deal with each of these data values in producing
summary data.
Thanks
David

 

[Bob Barrows]

There doesn't appear to be a "Reply to All" option to give my
response to the other individual posts. So I have written a blanket,
cover-all response.

In response to Sylvain the code is as follows:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS
CountOfRaceID
FROM tblRaceData
GROUP BY tblRaceData.RaceTrack
HAVING (((tblRaceData.RaceTrack) In
(Canterbury("N"),"Rosehill",Randwick("I"),"Doomben",M.Valley("N"))));

Well, as I said in my initial post, this needs to be
In
("Canterbury(""N"")","Rosehill","Randwick(""I""),"Doomben","M.Valley(""N""))));orIn ('Canterbury("N")','Rosehill','Randwick("I")','Doomben','M.Valley("N")')));... unless the data does not actually contain quotes and, as John suggested,the parser was "helping you out" by putting them in ... If the data does notcontain quotes, which is how you described it in your original post, thenthe criteria should look like what I suggested in my initial post:IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)")>> Bob:> Again, I didn't create this data so, yes I understand the basics.I don't understand your point. You're creating the query aren't you? I'm notadvising you to modify the data. I'm advising you to properly delimit thecriteria in your IN() statement.--Microsoft MVP - ASP/ASP.NET - 2004-2007Please reply to the newsgroup. This email account is my spam trap so Idon't check it very often. If you must reply off-line, then remove the"NO SPAM"

 

[Bob Barrows]

What concerns? I have no concerns about the data. The data does not seem
difficult to deal with from what I can see.
I'm trying to tell you how to format your IN () statement. For the third
time, if your data looks as shown below, your IN statement should be this:

IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)")

NOT

In
(Canterbury("N"),"Rosehill",Randwick("I"),"Doomben",M.Valley("N"))

Incidently, I just noticed something else about your query which is not
related to the problem you are having but will have an impact on performance
once you do correct the formatting of your IN statement: you are using a
HAVING clause when you should be using a WHERE clause. Instead of:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS CountOfRaceID
FROM tblRaceData
GROUP BY tblRaceData.RaceTrack
HAVING (((tblRaceData.RaceTrack) In
IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)") )) ;

you should be doing this:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS CountOfRaceID
FROM tblRaceData
WHERE (((tblRaceData.RaceTrack) In
IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)") ))
GROUP BY tblRaceData.RaceTrack;

 

[DavidG]

That's good to know, because there aren't any spaces or special characters in
the field names.
Thanks
David

 

[Bob Barrows]

I don't understand what happened to the line breaks in my previous reply so
I am trying again.

There doesn't appear to be a "Reply to All" option to give my
response to the other individual posts. So I have written a blanket,
cover-all response.

In response to Sylvain the code is as follows:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS
CountOfRaceID
FROM tblRaceData
GROUP BY tblRaceData.RaceTrack
HAVING (((tblRaceData.RaceTrack) In
(Canterbury("N"),"Rosehill",Randwick("I"),"Doomben",M.Valley("N"))));

Well, as I said in my initial post, this needs to be
In
("Canterbury(""N"")","Rosehill","Randwick(""I""),"Doomben","M.Valley(""N""))));orIn('Canterbury("N")','Rosehill','Randwick("I")','Doomben','M.Valley("N")')));... unless the data does not actually contain quotes and, as Johnsuggested,the parser was "helping you out" by putting them in ...If the data does not contain quotes, which is how you described it in youroriginal post, then the criteria should look like what I suggested in myinitial post:IN ("Canterbury(N)", "M.Valley(N)", "Randwick(I)")>> Bob:> Again, I didn't create this data so, yes I understand the basics.I don't understand your point. You're creating the query aren't you? I'm notadvising you to modify the data. I'm advising you to properly delimit thecriteria in your IN() statement.--Microsoft MVP - ASP/ASP.NET - 2004-2007Please reply to the newsgroup. Thisemail account is my spam trap so Idon't check it very often. If you mustreply off-line, then remove the"NO SPAM"

 

[Sylvain Lafontaine]

Obviviously, you have hit a bug in the SQL-Viewer of Access 2007 as it
should have never generated a code like this. Like Bob has said, you must
write/use something like:

In ("Canterbury(""N"")", ...

In ('Canterbury("N")', ...

In ("Canterbury('N')", ...

Etc.

Replace the comma with a semi-comma if neccessary in your case and, of
course, replace the HAVING with a WHERE.

If Available, using another field like the RaceTrackID - if such a field
exists - could also help you.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John W. Vinson]

There doesn't appear to be a "Reply to All" option to give my response to the
other individual posts. So I have written a blanket, cover-all response.

In response to Sylvain the code is as follows:

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS CountOfRaceID
FROM tblRaceData
GROUP BY tblRaceData.RaceTrack
HAVING (((tblRaceData.RaceTrack) In
(Canterbury("N"),"Rosehill",Randwick("I"),"Doomben",M.Valley("N"))));

Try changing this to

SELECT tblRaceData.RaceTrack, Count(tblRaceData.RaceID) AS CountOfRaceID
FROM tblRaceData
WHERE tblRaceData.RaceTrack In
("Canterbury(N)","Rosehill","Randwick(I)","Doomben","M.Valley(N)")
GROUP BY tblRaceData.RaceTrack;

This assumes that the table value in fact contains strings like

Canterbury(N)

rather than

Canterbury("N")

which I recall is the case.

 

[DavidG]

Thanks very much, what you have kindly suggested has worked brilliantly and
solved the problem,
Thanks
Dave

 

[DavidG]

Thanks John, what you have said has been very helpful. I have now re-shaped
the query accordingly and it now works perfectly.
Thanks
Dave

 

[DavidG]

As in previous post, thanks for your help.
Thanks
Dave

 

[DavidG]

Sylvain, merci beaucoup.
The query now works very well,
Much appreciated.
Dave

 

[DavidG]

Thanks John, I have re-written the query as suggested and it now works very
well.
I needed to get the lesson on how to deal with special characters when using
criteria that contains them. I now better understand the role of the
"Quotes" in a string.
Thanks
Dave