Using # in query

[SUnderwood]

Hi All

Having some problems with using # in a query.

I realise # is used to denote a date (ANSI-89) but i've got a few dozen external data sources which use # in their datasets to denote a type of a records content. Any ideas how i can use #?

Regards

Sean

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com

 

[lespaul]

have you tried brackets?

[fieldname#]

leslie

 

[SUnderwood]

Morning,

Sorry I seem to have mislead you.

The data in the field contains the "#" character and I'm trying to use the string "*_#*" as part of the query to display only those records that have "_#" sequence as the data of field NAME.

Any ideas?

Sean

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com

 

[TonyJollans]

Hi Sean,

Can you post the SQL and/or code which is giving you a problem? I suspect quotes are at the root of it.

# (or any other character) in a string should work perfectly well and not conflict with # as a date-literal delimiter.

Enjoy,
Tony

 

[SUnderwood]

Ok, here you go. Its Access's nasty version rather than my neat std SQL version, but thats what it does.

SELECT DISTINCT UCase(Mid$([FileName],InStr(1,[FileName],"_#&quot+2,5)) AS fFType, Files.FileName
FROM Files
WHERE (((Files.FileName) Like "*_#*&quot)
ORDER BY UCase(Mid$([FileName],InStr(1,[FileName],"_#&quot+2,5));


(Oh thats nasty!)

Any ideas?

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com

 

[TonyJollans]

Hi Sean,

The # character in a pattern represents a single numeric character. To make it represent itself you must enclose it within brackets so ..

... Like "*_[#]*" ...

.. should do what you want.

Enjoy,
Tony

 

[SUnderwood]

Hay Tony, your a star!!!

I'll test that out on Monday.

Sean

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com