Developer Forum

How do I concatenate text fields together to make a field name in a table?

Dim MyDB As DAO.Database
Dim MyData As DAO.Recordset
Dim MileTable(50)
' 1 = START
' 2 = MIDDLE
' 3 = END
' ......ETC
Dim MileEvent As String
Dim MileDate As Date
Dim MileUpdDate As Date
Dim MileUser As String
Dim MileNDate As Date
Dim MileNUpdDate As Date
Dim MileNUser As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyData = MyDB.OpenRecordset("TabData", DB_OPEN_DYNASET)
With MyData
Do While Not .EOF
For Indx = 1 To MileCnt
BSRNo = !BSR_ID
MileNDate = MileTbl(Indx) & "_DTTM" ' APPEND _DTTM to
table value
MileNUpdDate = MileTbl(Indx) & "_UPD_DTTM" ' APPEND
_UPD_DTTM to table value
MileNUser = MileTbl(Indx) & "_UPD_USR_NAM" ' APPEND
UPD_USR_DTTM to table value

' **************************
' how do I define these next fields to point to the field in
the table
' **************************

MileDate = !{MileNDate} ' START_DTTM
MileNUpdDate = !{MileNUpdDate} ' START_UPD
MileNUser = !{MileUser} '
START_UPD_USR_NAME
Next Indx
.MoveNext
Loop
.Close ' Close table
End With
Exit Function

I tried the following and get no records returned

For Indx = 1 To MileCnt
MileNDate = MileTbl(Indx) & "_DTTM"
MileNUpdDate = MileTbl(Indx) & "_UPD_DTTM"
MileNUser = MileTbl(Indx) & "_UPD_USR_NAM"
Set MyDB = DBEngine.Workspaces(0).Databases(0)

' Set rs = db.OpenRecordset("Select [" & strFieldName _
' & "] Form [" & SomeTable & "]", dbOpenSnapshot)
MySelect = "Select BSR_ID as BSRNo , " & MileNDate & " as MileDate,
" _
& MileNUpdDate & " as MileUpdDate, " & MileNUser & " as MileUser
From BSR_BSR "
Set BSR = MyDB.OpenRecordset(MySelect, dbOpenSnapshot)
With BSR
.MoveFirst
Do While Not .EOF
Debug.Print BSRNo, MileDate, MileUpdDate, MileUser
If InStr(1, MileDate, "/", vbTextCompare) = 0 Then
Else
GoSub AddNewMile
End If
.MoveNext
Loop
.Close ' Close table
End With
Next Indx

MileDate = .Fields(MileNDate)
MileNUpdDate = .Fields(MileNUpdDate)
MileNUser = .Fields(MileUser)

--
Marsh
MVP [MS Access]

Marshall

That worked, thanks.

John