Acc 2007 refering to alias in a query = no value; works fine Acc 2003?

[DougP]

I created a query and am using it in my subform. it refers to two tables. both have email in them so I created an alis in one table called [new email]
my VB code as follows returns "" in variable newemail below

    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLCode As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    Dim TheID As Integer
    Dim NewEmail As String
    
    TheID = Forms![frmUpdateEmails]![qryCompareEmailsToDirectoryDump subform].Form![ID]
    [b][COLOR=red]NewEmail = Forms![frmUpdateEmails]![qryCompareEmailsToDirectoryDump subform].Form![New Email][/color][/b]
    
    SQLCode = "Update [Data from pdf Latest] Set [Email Address] = '" & NewEmail & "' Where [ID] = " & TheID & ";"
    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
     
'  close it this way
    Set Rs1 = Nothing
    Set Conn2 = Nothing

I just opened this database in Access 2003 and it works fine. I can get an email in variable NewEmail
but then back in 2007 it's = "".
How do I find out which Service pack Office or Access 2007 has? there is no more help/about which in the other versions showed SP?

DougP

 

[genomon]

If you imported the query into 2007 instead of converting the database, the query probably has the aliased field changed or appended with "Expr01". Open the query with the [new email] field in design view to see if this has happened and manually edit if needed.
As to the "About" information, click on the Office Button, click on "Access Options" at the bottom right, then click on "Resources", then click on "About". Gotta love Microsoft, eh?

Beir bua agus beannacht!

 

[DougP]

I solved the the problem:
it was the fact that there was a character of some sort at the end of the data i.e. JohnDoe@nowhere.com[] < this showed up as a square in Access 2003 but not in Access 2007. it just showed the email if you looked at it in a table or query.
when I run the code in Access 2003 it gets the email and the character too. Access 2007 has a problem with that somehow. it retrn nothing or maybe iut is showing the character and nothing else it just shows as "" when you hover over it in the VBE.
The data came from a Word table orignally so there was a carriage return after each one (I guess). Anyway I created an Update query in Access 2003 and got the left xx characters minus one.
i.e. Left([NewEmail],Len([NewEmail]-1))

But now I am concerned if I don't have Access 2003 anywhere to figure this out?

DougP