I have an SQL statement that is working fine but I would like to use aVariable to alter slightly expand the field name.

The portion of the SQL statement I have within the SELECT part is:

"AS Age at, INTO etc. etc. where the Age at is a calculated Age from a specified date. This <specified date> is held in the string variable strDate.

What I would like then is the Field Name to become Age at <specified date>

I have tried syntax like

"AS [Age at ]" & strDate & ", but this comes up with a mis-spelled error.

I hope this can be done as it will make the output report so much clearer to understand.

If your field or table name has spaces in it you need to enclose it in brackets [].

Tried that - no joy - it clearly doesn't like the use of a Variable in the field/column name.

Tried that - no joy - it clearly doesn't like the use of a Variable in the field/column name.

I've never had any problems with using variables in field or column names. Can you post more of your code, is this SQL being put into a variable, or are you running it directly using RunSQL or Execute?

OK here goes - irs long and has a complex calculation in it: its being run from within a module as Docmd.runSQL mySQL and works fine. I just want the Age at column header to by Age at <variable date> and the syntax "AS Age at " & strDate & ", does not work

mySQL = "SELECT Patients.PxPracNo, Patients.Title, Patients.FirstName, Patients.LastName, Patients.Address1, " & _
"Patients.Address2, Patients.Address3, Patients.Town, Patients.PostCode, " & _
"Fix(Format(((" & myCalcDate & " -[DoB])/365.25),'#.#00'))" & _
"AS [Age at], Patients.DoB INTO BirthdaysNoAppointment FROM Patients " & _
"WHERE (((Patients.DoB) Is Not Null And (Patients.DoB) >= " & myStartDate & " And (Patients.DoB) <= " & myEndDate & ")" & _
"And ((Patients.Status) = 'Current') And ((Patients.NextAppointment) <= Date() Or (Patients.NextAppointment) Is Null))" & _
"ORDER BY [DoB] ASC;"

Thanks for your help; its an interesting one methinks!

OK here goes - irs long and has a complex calculation in it: its being run from within a module as Docmd.runSQL mySQL and works fine. I just want the Age at column header to by Age at <variable date> and the syntax "AS Age at " & strDate & ", does not work

mySQL = "SELECT Patients.PxPracNo, Patients.Title, Patients.FirstName, Patients.LastName, Patients.Address1, " & _
"Patients.Address2, Patients.Address3, Patients.Town, Patients.PostCode, " & _
"Fix(Format(((" & myCalcDate & " -[DoB])/365.25),'#.#00'))" & _
"AS [Age at], Patients.DoB INTO BirthdaysNoAppointment FROM Patients " & _
"WHERE (((Patients.DoB) Is Not Null And (Patients.DoB) >= " & myStartDate & " And (Patients.DoB) <= " & myEndDate & ")" & _
"And ((Patients.Status) = 'Current') And ((Patients.NextAppointment) <= Date() Or (Patients.NextAppointment) Is Null))" & _
"ORDER BY [DoB] ASC;"

Thanks for your help; its an interesting one methinks!

mysql = "SELECT Patients.PxPracNo, Patients.Title, Patients.FirstName, Patients.LastName, Patients.Address1, " & _
"Patients.Address2, Patients.Address3, Patients.Town, Patients.PostCode, " & _
"Fix(Format(((" & myCalcDate & " -[DoB])/365.25),'#.#00'))" & _
"AS [Age at " & strdate & "], Patients.DoB INTO BirthdaysNoAppointment FROM Patients " & _
"WHERE (((Patients.DoB) Is Not Null And (Patients.DoB) >= " & myStartDate & " And (Patients.DoB) <= " & myEndDate & ")" & _
"And ((Patients.Status) = 'Current') And ((Patients.NextAppointment) <= Date() Or (Patients.NextAppointment) Is Null))" & _
"ORDER BY [DoB] ASC;"

Can you try this, all I've done is bracket around the column name, if it doesn't work can you post the error it's giving you?

You are a genius - thanks a lot. Much appreciated. Easy when you know how !!!