working with dashes - in column headings and VBA code

[jmpWashDC]

I have inherited/been given a database to modify (someone else's work) I have noticed that having the dash - in a column named "E-mail" makes for difficulties. Whenever I try to use the data in this column in some VBA code like:

Dim db As Database
Dim rs As Recordset
Dim SQL As String
Dim E-mail As String

Set db = CurrentDb
SQL = "SELECT * FROM QRYEmail;"
Set rs = db.OpenRecordset(SQL)

rs.MoveFirst

Do While rs.EOF = False
E-mail = E-mail & rs!E-mail & ";"
rs.MoveNext
Loop

the compiler wigs out on the dash -
(it wants an 'end of statement')
is there a way to get around this?
otherwise I need a way to replace every E-Mail column heading and its occurrence in VBA code with EMail
yikes!

Dim EMail As String
...
Do While rs.EOF = False
Email = Email & rs!Email & ";"
rs.MoveNext
Loop
^ I know this code works instead and
I've seen the speedferret website. Is using their free download a good idea?
I've thought about using the Find/Replace utiliy and/or the autocorrect, I just need some expert advice before venturing further.
thanks!
jmp

 

[THoey]

Either way, you need to fix it. You can either surround field names from tables with square brackets [E-Mail] or delete all the "-"'s. I would opt for option two. It may take longer, but you would be safer in the long run.

As for SpeedFerret, I have heard good things about it, but I have never used it... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[cmmrfrds]

Try surrounding with brackets this should tell Access to ignore the construction of the field name.

Do While rs.EOF = False
[E-mail] = [E-mail] & rs![E-mail] & ";"
rs.MoveNext
Loop