Cainlynk
01-13-2011, 11:02 AM
I am trying to create a passthrough query in Access using VBA code. I have done this several times before successfully, but in this instance, I have run up against a wall due to a field name that contains a pound sign.
Below is the VBA code that I am using:
Private Sub Command0_Click()
DoCmd.DeleteObject acQuery, "MyQ2"
Dim MyDb As DAO.Database
Dim MyQ2 As QueryDef
Dim sSQL2 As String
sSQL2 = "SELECT I.""ACT#"", I.TRTE, C.NME1, C.NME2, C.ADRS, C.CYST, C.ZP, C.OGDT, C.OGAM, C.CPFG FROM MTGBPN.INTRN I LEFT JOIN MTGBP1.CHTR# C ON I.ACT# = C.ACT# WHERE I.TRTE BETWEEN" & " " & Me.Date1 & " " & "AND" & " " & Me.Date2 & " " & "Order By I.""ACT#"""
Set MyDb = CurrentDb()
Set MyQ2 = MyDb.CreateQueryDef("MyQ2")
MyQ2.SQL = sSQL2
MyQ2.ReturnsRecords = True
MyQ2.Connect = "ODBC;DSN=HALS"
End Sub
Running this code produces the following VBA error: Invalid use of '.', '!', or '()' in query expression 'I."ACT#"=C."ACT#'.
If I remove the delaminating quotations from the field names in the SQL string JOIN statement, I get the following VBA error: Syntax error (missing operator) in query expression 'I.ACT#=C.ACT'.
(Note: If I remove the quotations and enclose the field names in the JOIN statement in square brackets: ON I.[ACT#]=C.[ACT], the code executes and the passthrough is created. However, the passthrough itself will not execute due to the square brackets in the SQL statement.)
I realize that this is a very nice example of why field names should not be created that contain such special characters. I wish I could change the field names but I cannot; I have read-only access to the data.
I would be extremely grateful if someone could show a way to write the string so that it is acceptable to VBA, or some sort of what around this issue.
Thanks very much in advance!
P.S. The dates in the SQL string are dates in name only, they are in number format, Example: 20110113.
Below is the VBA code that I am using:
Private Sub Command0_Click()
DoCmd.DeleteObject acQuery, "MyQ2"
Dim MyDb As DAO.Database
Dim MyQ2 As QueryDef
Dim sSQL2 As String
sSQL2 = "SELECT I.""ACT#"", I.TRTE, C.NME1, C.NME2, C.ADRS, C.CYST, C.ZP, C.OGDT, C.OGAM, C.CPFG FROM MTGBPN.INTRN I LEFT JOIN MTGBP1.CHTR# C ON I.ACT# = C.ACT# WHERE I.TRTE BETWEEN" & " " & Me.Date1 & " " & "AND" & " " & Me.Date2 & " " & "Order By I.""ACT#"""
Set MyDb = CurrentDb()
Set MyQ2 = MyDb.CreateQueryDef("MyQ2")
MyQ2.SQL = sSQL2
MyQ2.ReturnsRecords = True
MyQ2.Connect = "ODBC;DSN=HALS"
End Sub
Running this code produces the following VBA error: Invalid use of '.', '!', or '()' in query expression 'I."ACT#"=C."ACT#'.
If I remove the delaminating quotations from the field names in the SQL string JOIN statement, I get the following VBA error: Syntax error (missing operator) in query expression 'I.ACT#=C.ACT'.
(Note: If I remove the quotations and enclose the field names in the JOIN statement in square brackets: ON I.[ACT#]=C.[ACT], the code executes and the passthrough is created. However, the passthrough itself will not execute due to the square brackets in the SQL statement.)
I realize that this is a very nice example of why field names should not be created that contain such special characters. I wish I could change the field names but I cannot; I have read-only access to the data.
I would be extremely grateful if someone could show a way to write the string so that it is acceptable to VBA, or some sort of what around this issue.
Thanks very much in advance!
P.S. The dates in the SQL string are dates in name only, they are in number format, Example: 20110113.