Accessing Data from within a table using recordset

[dcarr]

I am trying to access a field in a table and append the contents of that field to the end of a file name.
The name of the table is WeekNo, and the field I am trying to access is Week.
A query creates the table. All I am trying to do is append the contents to a file named

c:\avL "& Week & ".rtf

has anyone got any ideas on how to do this? The database is called test.mdb
This is the first time I have attempted to do this and have come unstuck?
Thanks

 

[Rich]

I'm sure Week is a reserved word in Access and shouldn't be used as a field name.

 

[dcarr]

Can I reference it from a table though? The table it gets the data from is tblWeek.
From here the week number is picked up from a column named 'week'.
If I was to call the recordset by something else should i be able to reference the table?

 

[dcarr]

Is this syntax valid?

Function testmacroconverttocode()
On Error GoTo testmacroconverttocode_Err


Dim mydb As Database
Dim var As Long

Set mydb = DBEngine.Workspaces(0).Databases(0)
Dim WeekNumber As Recordset
DoCmd.OpenQuery "q_Calculate week number", acViewNormal, acReadOnly
Set WeekNumber = mydb.OpenRecordset("WeekNo")
WeekNumber.MoveFirst
var = WeekNumber![Week]

This syntax is from a macro that has been converted into code