I’ve just built a dashboard with a bit of SQL. I’ve not done much for a while but I soon remembered that I have to pass dates very carefully.
SQL now accepts quite a lot of date formats, but as far as I know the American MM/DD/YY is still the standard of DAO? I think SQL server may now have YYYY/MM/DD as the standard.
Personally I always pass dates like this DD/MMM/YYYY, this work fine into an Access DB and that’s what I’ve always used.
I also notice that I tend to pass string variables like this
Why I don’t use a signal quote?
I don’t know, because I always pass “fixed” string that way – very strange.
And then there’s the other thing with SQL code, why so some of use the:
sSQL = sSQL & sSQL “ FROM..........................."
While others use (the more natural?)
“ FROM..........................." & _
“ WHERE..........................."
Does anyone do differently? Are there any flaws in these (my) methods? I guess I picked these habits up because they where used in the books I read while I was learning them, but I don’t know if there really the best methods?
Since most of my SQL statements are to be manipulated by code I first make an Access query in query designer. Then switch it to the SQL text. That text is copied to the inside of a formula like this:
Function SQLdemo(var1 as String.....,) as string
SQL text goes here.
End Function
Then I make 4 or 5 variables named line1, line2... line5 (one for each line of the SQL statement)
So it looks like this:
Function SQLdemo(tablenamehere as String.....,) as string
Dim line1, line2, line3, line4, line5 as String
line1 = "INSERT INTO......"
line2 = "SELECT ...."
line3 = "FROM " & tablenamehere & " INNER JOIN....."
line4 = "WHERE...."
'etc. as needed.
SQLdemo = line1 & line2 & line3 & line4 & ....
End Function
Then I go through the text and place the variables. Above just demonstrates the variable tablenamehere being the name of a table.
When done I have a formula which will output a valid SQL statement and I have control of what values I will feed it in the code that does the work.
Most of these SQL creation functions will add to a collector database table. When finished, the table has all the answers needed. I pull out the answer I need using Excel and other formulas (using Application.vlookup).
Anyway, that's how I do it.
There is a limit to how line continuations you can use (IIRC it's 24) and my SQL statements are frequently longer than that!
In the example I gave, there were no line continuations. String variables were concantenated together. What is IIRC?
IIRC = If I recall correctly