sql syntax and/or function reference?

[spleve]

I've started using access recently as a favor for a friend's to help his small business. I've been coding vba in excel and sql in SQL Server for several years, so I thought I wouldn't have many problems with Access. That's been true with the vba, but NOT the SQL, and I can't find a real access-specific reference anywhere (well, ok, anywhere on the net for free!).

My latest specific issue is that I have a text field that holds dates of many different formats; some as mmm-dd-yy hh:mm:ss, some just yyyy mm, others dd/mm/yy etc, etc, etc. There's probably 7 or 8 different formats. An easy task for me in excel or SQL Server using isdate and cast other format similar functions in either tool, but for the life of me I can't seem to find a list of functions that I can use in the access sql, and either my "guesses" at the syntax are wrong or the functions are not available. I noticed a post on this board which used a "CVDate" function in the SQL, and thought I could use it to find the secret stash of functions in help, but I couldn't find any reference to that function either.

I'll probably export to excel, fix the dates and then export back, but I really hate that solution, and I know without a reference, EVERYTHING I try to do is going to be much more of a chore that it should be.

So my question is where can I find a function reference for access-sql?
How about and access-specific sql syntax reference?

Thanks!

 

[dk]

Hi,

Welcome to the world of Access

Re your specific problem, Access doesn't support CAST as such. It actually provides a whole host of conversion functions such as CDate, CStr, CInt and so on which you can use to convert data.

These functions are actually part of the VBA library and this is why you probably can't find them in the 'normal' help - because they're in the VBA help. E.g. look for the Type Conversion Functions topic in VBA help and you should find out about the mysterious CVDate function.

You can access a full list of functions by right clicking in the query design grid and choosing Build to open the Expression Builder. From here you can select Functions, Built In Functions to see everything available. You can also write your own VBA functions which you can use within your SQL statements.

 

[Russell Hauf]

Also check the DateValue and DateSerial functions.

 

[spleve]

Ah, so the vba functions are available to SQL, AND they're all listed in the expression builder, nice! That's going to be a BIG help.

I think I'm finally getting used to "access-sql" also. I have to keep reminding myself to pile simple queries on top of each other, use verbose join syntax, etc..

Also, I'm starting to try to use the wizards for everything, that's helping too. Doesn't always give me exactly what I want, but it gets me close and does point out to me that access likes the set list after the join clause in an update, stuff like that.

Oh, and I did export to excel, created a "reference" table (text field, and the date I wanted), exported it back and updated. Now I know I didn't have to! I did use the DateValue and DateSerial functions, though..

Thanks again!