asked on

VB.net Access SQL Month to date - problem with reserved words

Hi

I am using the following function build a query that gets month to date numbers
from an Access database table
There is, however a problem with Reserved words (Year etc)
How do I change the code so that I don't have this proble?

Public Function MonthToDateAccess(ByVal oFromTables As String, ByVal oAmountField As String, ByVal oDateField As String, ByVal oOtherFields As String) As String
Try
Dim S As String = ""

S = S & "SELECT Year(" & oDateField & ") As Year, Month(" & oDateField & ") As Month, SUM(" & oAmountField & ") As TotalAmount" & oOtherFields
S = S & " " & oFromTables
S = S & " WHERE Year(" & oDateField & ") = Year(Date()) AND Month(" & oDateField & ") = Month(Date()) "
S = S & " GROUP BY Year(" & oDateField & ") As Year, Month(" & oDateField & ") As Month " & oOtherFields
S = S & " ORDER BY Year(" & oDateField & ") As Year, Month(" & oDateField & ") As Month"

MonthToDateAccess = S

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Function

Meir Rivkin

    Public Function MonthToDateAccess(ByVal oFromTables As String, ByVal oAmountField As String, ByVal oDateField As String, ByVal oOtherFields As String) As String
        Try
            Dim S As String = ""

            S = S & "SELECT Year(" & oDateField & ") As [Year], Month(" & oDateField & ") As [Month], SUM(" & oAmountField & ") As TotalAmount" & oOtherFields
            S = S & " " & oFromTables
            S = S & " WHERE Year(" & oDateField & ") = Year(Date()) AND Month(" & oDateField & ") = Month(Date()) "
            S = S & " GROUP BY Year(" & oDateField & ") As [Year], Month(" & oDateField & ") As [Month] " & oOtherFields
            S = S & " ORDER BY Year(" & oDateField & ") As [Year], Month(" & oDateField & ") As [Month]"

            MonthToDateAccess = S

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Function

Open in new window

mbizup

Use square brackets around reserved words that are used as field names:

S = S & "SELECT Year(" & oDateField & ") As [Year], Month(" & oDateField & ") As [Month], SUM(" & oAmountField & ") As TotalAmount" & oOtherFields
'etcetera

Open in new window

Or better yet rename those fields to NOT use reserved words:
MonthOfPayment
YearOfPayment

Murray Brown

ASKER

Hi Sedgwick. Thanks. I got the following error

Murray Brown

ASKER

I tested with just two columns and got the same error with the resultant SQL expression

SELECT Year([Transactions].[Transaction Date]) As [Year], Month([Transactions].[Transaction Date]) As [Month], SUM([Transactions].[Amount]) As TotalAmount FROM [Transactions] WHERE Year([Transactions].[Transaction Date]) = Year(Date()) AND Month([Transactions].[Transaction Date]) = Month(Date()) GROUP BY Year([Transactions].[Transaction Date]) As [Year], Month([Transactions].[Transaction Date]) As [Month] ORDER BY Year([Transactions].[Transaction Date]) As [Year], Month([Transactions].[Transaction Date]) As [Month]

ASKER CERTIFIED SOLUTION

Meir Rivkin

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Murray Brown

ASKER

Great! That worked. Thanks very much