Can you use the "replace" command in an access sql query?

[Guest]

I'm importing raw data from a text file into access. So to make it user
friendly, I'm trying to write an SQL query that renames the imported fields
in each column to a more user friendly name. Here is one example:

SELECT Stats.Database, Count(Stats.Database) AS [Number of Times Accessed]
FROM Stats
where stats.database = Replace("EALR1 - EAST AFRICA LAW REPORTS X.MAIN
CAMPUS", "EALR1 - EAST AFRICA LAW REPORTS X.MAIN CAMPUS", "East Africa Law
reports")
GROUP BY Stats.Database
ORDER BY Stats.Database;

Thanx

 

[MacDermott]

The part of your query which renames the column (AS [Number of Times
Accessed]) looks fine.
The WHERE clause will restrict the returned records to those where
Stats.Database="East Africa Law Reports", which I gather will return
nothing, because the entry is EALR1 - EAST AFRICA LAW REPORTS X.MAIN CAMPUS.

If I understand correctly what you want to do, something like this might
work better:
SELECT "East Africa Law reports" as [DB Name], Count(Stats.Database) AS
[Number of Times Accessed]
FROM Stats
where stats.database = "EALR1 - EAST AFRICA LAW REPORTS X.MAIN CAMPUS"
GROUP BY Stats.Database
ORDER BY Stats.Database;

Of course you have little need for the ORDERBY clause if you're only
returning one row, so I'm guessing you don't really want to use a WHERE
clause at all.
Another approach would be to make a separate lookup table which includes all
entries in Stats.Database and their user-friendly names. Then JOIN that
into your query and return the looked-up names.

HTH
- Turtle
BTW it's my understanding that Replace() works in queries since once of the
A2K Service Packs (it didn't work in the original A2K, and didn't even exist
in A97). It's just probably not what you need here.

 

[Van T. Dinh]

If you meant you want to rename the Field / Column names of the (imported)
Table then the Select Query won't work since Select Query is for data
manipulation, NOT data definition.

I am not sure but if anything, you probably need DDL (Data Definition
Language) syntax like "ALTER TABLE ..." (which I *didn't* find an example
for altering Field / Column names). The alternative is to use DAO code to
access the Field via the Fields Collection of the required TableDef.

Check Access VB / DAO Help on the TableDefs collection, TableDef object,
Fields collection and Field object.