Problem with Format not compiling in VBA for ACCESS 2

[jpl458]

The following bit of code is the first part of a long SQL statement to illustrate the problem.

Ballssql = "SELECT dbo_Transaction_Table.Account_Number, dbo_Master_Accounts.FirstName, _"
Ballssql = Ballssql + "dbo_Master_Accounts.LastName, dbo_Transaction_Table.Date_of_Transaction, _"
Ballssql = Ballssql + "Format([dbo_Transaction_Table]![Date_of_Transaction],"ddd") AS Dayofwk, _"

The problem is that the Format function does not compile, and neither do the other format functions in the rest of the sql. I looked and format is supposed to work in VBA, but I must be missing something. Copied the SQL out of the QBE grid.

Using ACCESS 10, Wondows 7 Pro

Thanks in advance

jpl

 

[Andrzejek]

I would start by removing _ from the end of your lines, replace + with the &, and add Spaces at the beginning and at the end (for good measure)

Ballssql = "SELECT dbo_Transaction_Table.Account_Number, dbo_Master_Accounts.FirstName, "
Ballssql = Ballssql & " dbo_Master_Accounts.LastName, dbo_Transaction_Table.Date_of_Transaction, "
Ballssql = Ballssql & " Format([dbo_Transaction_Table]![Date_of_Transaction],"ddd") AS Dayofwk, "

If that doesn't help, check for any Missing References in your Project

Have fun.

---- Andy

 

[SkipVought]

hi,

1) APOSTROPHY, not QUOTES in the Format() that is in the SQL code, not in your VBA code.
2) No UNDERSCORE @ end of string as each string statement is complete in itself and needs no continuation.

Ballssql = "SELECT dbo_Transaction_Table.Account_Number, dbo_Master_Accounts.FirstName,"
Ballssql = Ballssql + "dbo_Master_Accounts.LastName, dbo_Transaction_Table.Date_of_Transaction,"
Ballssql = Ballssql + "Format(dbo_Transaction_Table.Date_of_Transaction,'ddd') AS Dayofwk,"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

My general suggestion for SQL coding in VBA would include...

    Dim sSQL As String
    
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  tt.Account_Number"
    sSQL = sSQL & ", ma.FirstName"
    sSQL = sSQL & ", ma.LastName"
    sSQL = sSQL & ", tt.Date_of_Transaction"
    sSQL = sSQL & ", Format(tt.Date_of_Transaction,'ddd') AS DayOfWk"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  dbo_Transaction_Table tt"
    sSQL = sSQL & ", dbo_Master_Accounts   ma"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE ..."

Of course the WHERE would define the join of the 2 tables or maybe your SQL uses an INNER JOIN syntax.

This technique assures that the sql string is properly delimited (SPACES/line feed) and makes the code more readable

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

Thanks Skip, I knew about the quote thing but didn't see it. I changed the query to the format you suggested and can see how it is easier to work with and make changes to. It all compiles now, lets see if it opens and runs.

May be back to the well on this.

Thanks again

jpl

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}