Its been done to death - I know but """" Again!

[Kahuna]

I need to create a query to check one field for extra ' " ' (speech quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16"" and
20"".

I need to check for these errors. My tack would be to find the position of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'


Any help gratefully received.

Cheers

 

[Marshall Barton]

I need to create a query to check one field for extra ' " ' (speech quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16"" and
20"".

I need to check for these errors. My tack would be to find the position of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'

I think this would be a lot easier if you just used the
Replace function:
Replace(diameter, """""", """")

 

[Kahuna]

Sorry Marshall, should have said its A97 - don't think I have a Replace
Function. There has to be a simpler way than creating a Replace Function
too.

I have the query column showing the content and all I need to do is say 'Is
this content = " '.

SELECT fabric_condition.fc_id, fabric_condition.fc_diameter,
Mid([fc_diameter],Len([fc_diameter]),1) AS FindString,
Mid([fc_diameter],Len([fc_diameter])-1,1) AS FindString2
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null) AND
((Mid([fc_diameter],Len([fc_diameter]),1))<>"));

Is it possible to use CHR(34) to do this check somehow?
--
Kahuna
------------

I need to create a query to check one field for extra ' " ' (speech
quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16""
and
20"".

I need to check for these errors. My tack would be to find the position of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'

I think this would be a lot easier if you just used the
Replace function:
Replace(diameter, """""", """")

 

[Kahuna]

Got a workaround here - but its not really satisfying!

SELECT fabric_condition.fc_diameter,
InStr(1,Mid([fc_diameter],Len([fc_diameter])-1,1),"""")>0 AS Test
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null));

Checking the second character from the right and if that's a ' " ' then we
know the entry is ill formed.

Any constructive comments would be welcome.
--
Kahuna
------------

I need to create a query to check one field for extra ' " ' (speech
quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16""
and
20"".

I need to check for these errors. My tack would be to find the position of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'

I think this would be a lot easier if you just used the
Replace function:
Replace(diameter, """""", """")

 

[John Spencer]

Pardon me,

If all you want to do is return those records then I would use

SELECT fabric_condition.fc_id, fabric_condition.fc_diameter,
Mid([fc_diameter],Len([fc_diameter]),1) AS FindString,
Mid([fc_diameter],Len([fc_diameter])-1,1) AS FindString2
FROM fabric_condition
WHERE fc_diameter Like "*" & Chr(34) & Chr(34) & "*"

You could try the where clause as
Where fc_Diameter Like '*""*'

Sorry Marshall, should have said its A97 - don't think I have a Replace
Function. There has to be a simpler way than creating a Replace Function
too.

I have the query column showing the content and all I need to do is say
'Is this content = " '.

SELECT fabric_condition.fc_id, fabric_condition.fc_diameter,
Mid([fc_diameter],Len([fc_diameter]),1) AS FindString,
Mid([fc_diameter],Len([fc_diameter])-1,1) AS FindString2
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null) AND
((Mid([fc_diameter],Len([fc_diameter]),1))<>"));

Is it possible to use CHR(34) to do this check somehow?
--
Kahuna
------------

I need to create a query to check one field for extra ' " ' (speech
quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16""
and
20"".

I need to check for these errors. My tack would be to find the position
of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'

I think this would be a lot easier if you just used the
Replace function:
Replace(diameter, """""", """")

 

[Marshall Barton]

Got a workaround here - but its not really satisfying!

SELECT fabric_condition.fc_diameter,
InStr(1,Mid([fc_diameter],Len([fc_diameter])-1,1),"""")>0 AS Test
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null));

Checking the second character from the right and if that's a ' " ' then we
know the entry is ill formed.

I think you are over simplifying your problem.

This is probably not sufficient either, but if you want to
find all records that are not a bunch or digits followed
by a single " mark:

SELECT fc_diameter,
FROM fabric_condition
WHERE fc_diameter Like "*[!0-9]*?"
Or Right(fc_diameter ,1) <> """"

 

[Kahuna]

That's exactly where I was trying to get to John - that's the elegant
solution that I couldn't determine the format for.

Cheers my friend.

--
Kahuna
------------

Pardon me,

If all you want to do is return those records then I would use

SELECT fabric_condition.fc_id, fabric_condition.fc_diameter,
Mid([fc_diameter],Len([fc_diameter]),1) AS FindString,
Mid([fc_diameter],Len([fc_diameter])-1,1) AS FindString2
FROM fabric_condition
WHERE fc_diameter Like "*" & Chr(34) & Chr(34) & "*"

You could try the where clause as
Where fc_Diameter Like '*""*'

Sorry Marshall, should have said its A97 - don't think I have a Replace
Function. There has to be a simpler way than creating a Replace Function
too.

I have the query column showing the content and all I need to do is say
'Is this content = " '.

SELECT fabric_condition.fc_id, fabric_condition.fc_diameter,
Mid([fc_diameter],Len([fc_diameter]),1) AS FindString,
Mid([fc_diameter],Len([fc_diameter])-1,1) AS FindString2
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null) AND
((Mid([fc_diameter],Len([fc_diameter]),1))<>"));

Is it possible to use CHR(34) to do this check somehow?
--
Kahuna
------------

Kahuna wrote:

I need to create a query to check one field for extra ' " ' (speech
quotes).
Its a field with diameters such as 16" or 20" etc.

I have discovered some entries with double quotes erroneously i.e. 16""
and
20"".

I need to check for these errors. My tack would be to find the position
of
the first ' " ' and then check for a similar character in the next left
character along.

But as usual - I cant get the quotes right in the definition:

Mid([diameter], Len([diameter]),1) = 'Quotes' ('"' or """ or """")?

&

Mid([diameter], Len([diameter])-1,1) = 'Quotes'


I think this would be a lot easier if you just used the
Replace function:
Replace(diameter, """""", """")

 

[Kahuna]

Thanks Marshal - Johns solution worked exactly as I had been hoping.

Final SQL:

SELECT fabric_condition.fc_diameter
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Like "*" & Chr(34) & Chr(34) & "*"));

Cheers for the help

--
Kahuna
------------

Got a workaround here - but its not really satisfying!

SELECT fabric_condition.fc_diameter,
InStr(1,Mid([fc_diameter],Len([fc_diameter])-1,1),"""")>0 AS Test
FROM fabric_condition
WHERE (((fabric_condition.fc_diameter) Is Not Null));

Checking the second character from the right and if that's a ' " ' then we
know the entry is ill formed.

I think you are over simplifying your problem.

This is probably not sufficient either, but if you want to
find all records that are not a bunch or digits followed
by a single " mark:

SELECT fc_diameter,
FROM fabric_condition
WHERE fc_diameter Like "*[!0-9]*?"
Or Right(fc_diameter ,1) <> """"