Pound signs in field names causing problems in VBA; how do I get around this?

[Cainlynk]

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.DeleteObj ect 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.CreateQuer yDef("MyQ2")
MyQ2.SQL = sSQL2

MyQ2.ReturnsRec ords = True
MyQ2.Connect = "ODBC;DSN=H ALS"

End Sub

Running this code produces the following VBA error: Invalid use of '.', '!', or '()' in query expression 'I."ACT#"=C."AC T#'.
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

[Stewart Ross]

Hi. Problem is that the pound sign is a reserved character in Access used in pairs as a date literal delimiter, like this:

WHERE SomeDate > #1/1/2011#

The Access query engine, not VBA, sees it as a syntax error to use the pound sign as a single character following a field name. This is true for both types of recordset which you can use in Access - its native DAO and the more generic ADODB types.

The only way to get Access to accept the field name is to delimit the field name in brackets, but as you have found this precludes the use of a pass-through query in your environment.

I'm sorry to say that I can think of no obvious solution to this problem at present.

-Stewart