VBA, SQL, Dates and Strings – the habits we form.

21 March 2007 by Ross McLean

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

PLAIN TEXT
"HAVING (((tblWorkedTime.BDM)=" & Chr(34) & sPerson & Chr(34) & ")

Why I don't use a signal quote?

PLAIN TEXT
"HAVING (((tblWorkedTime.BDM)='" & sPerson & "')"

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:

PLAIN TEXT
sSQL = "SELECT..........................."
sSQL = sSQL & sSQL " FROM..........................."

While others use (the more natural?)

PLAIN TEXT
sSQL =  "SELECT..........................." & _
  " 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?

Related posts:

  1. A better way with a Make Table Query?
  2. Old dates, New VBA
  3. Parameters in ADO, getting good with Access – NOT!

Tags: , ,
Categories: SQL • Permalink • 4 Comments »

Comments

  1. Ralph W Lundvall says:
    29 May 2007 at 10:40 pm

    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.

    Reply
  2. Dianne Butterworth says:
    26 July 2007 at 5:25 pm

    There is a limit to how line continuations you can use (IIRC it's 24) and my SQL statements are frequently longer than that!

    Reply
  3. Ralph W Lundvall says:
    16 October 2007 at 8:32 pm

    In the example I gave, there were no line continuations. String variables were concantenated together. What is IIRC?

    Reply
  4. Dick Kusleika says:
    16 October 2007 at 9:57 pm

    IIRC = If I recall correctly

    Reply

Submit a Comment