Jun 02

Access Cheat Sheet

These are from ye olde Access 97 days and may no longer apply.

COUNT DISTINCT isn’t supported

To get around it: SELECT COUNT(1) FROM (SELECT DISTINCT column FROM table)


When using multiple ANSI joins, you must surround each set in parentheses.

SELECT topic.name
FROM (users INNER JOIN messages ON users.id = messages.idUser)
INNER JOIN topics ON messages.idTopic = topics.id

Object Names

If there are queer characters or reserved words in your table/query names, you can reference them in SQL statements by surrounding the name with square brackets, just like SQL Server.

SELECT email, [date] FROM [List Members]


Surround dates in SQL with #.

SELECT * FROM users WHERE joined > #3/4/2002#


Apr 16

Return the folder of the current ASP file

ASPToday ASP Tip of Day for 17 April 2001, by Ian Vink

Return the folder of the current ASP file being accessed.

set fs = server.createObject("scripting.fileSystemObject")

Full_ASP_FILE_PATH = server.mapPath(request.serverVariables("PATH_INFO"))

PATH_ONLY = fs.getParentFolderName(Full_ASP_FILE_PATH)

response.write "This ASP page is being run in the folder: " & PATH_ONLY