Hello All,
I have a report that is in the form of a weekly calendar, the data is
from a query and it works quite well. However, I am trying to make
the output to the label field be formatted in a specific manner to
make the data clearer.

Currently the code that creates everything is

Public Sub DisplayWeek()
Dim dbs As DAO.Database
Dim FirstDayOfWeek As Date
Dim i As Integer, SQLStmt As String, rst As DAO.Recordset, h As
Integer

FirstDayOfWeek = Me.SetFieldDateTime -
Weekday(Me.SetFieldDateTime) + 2
Me.DispTitle = format(FirstDayOfWeek, "mmmm d") & " - " &
format(FirstDayOfWeek + 4, "mmmm d, yyyy")
For i = 2 To 7
Me.Controls("D" & LTrim(str(i))).Caption =
format(FirstDayOfWeek + i - 2, "dddd, mmmm d")
Me.Controls("L" & LTrim(str(i))).Caption = ""
Next i

'SQLStmt = "SELECT " & FieldText & " AS xText, " & FieldDateTime &
" AS xDateTime, " & FieldTechID & " as xTechID FROM " & DataSource & "
WHERE (" & FieldDateTime & " >= #" & Int(FirstDayOfWeek) & "# AND " &
FieldDateTime & " <= #" & Int(FirstDayOfWeek + 4) & "#)" ' AND
FieldTechID = '3'" '& CInt(SetTechID)

SQLStmt = "SELECT Customer AS xText, Area, ScheduledDateTime AS
xDateTime FROM qryVisitsSch2" & strSQLArg
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenSnapshot)
If Not rst.EOF Then
Do While Not rst.EOF
h = Weekday(rst!xDateTime)
Me.Controls("L" & Trim(str(h))).Caption = Me.Controls("L" &
h).Caption & rst!xText & vbCrLf
rst.MoveNext
Loop
End If
rst.Close
End Sub


The list prints to the label as follows:
Customer 1
Customer 2
Customer 3
etc

but I want to show them by area

Here ' area
Customer 1
Customer 2

There 'area
Customer 3
etc

I have re-written the Me.Controls section multiple times and I cannot
get it to separate.

Any ideas/suggestions would be appreciated.
Thanks
John

As an aside, I now have to work with Access 2010 and the snapshot
function (dbOpenSnapshot) is no longer supported - is there another
way to write the line
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenSnapshot)

that would comply with A2010?

"flymo" <fly_mo (AT) hotmail (DOT) com> skrev i en meddelelse
news:0ff1a3f8-308c-4dfb-a49c-1d0a5ee5c5d0 (AT) j23g2000yqc (DOT) googlegroups.com...
I think u can run a loop with area first...
loop 1 - rst.area distinct
loop 2 nested - rst.customer where area = rst.area

Look at : Docmd.OutputTo
Bjarne

On May 29, 10:40*am, "bsn" <bsnSNABELAoncableDOTdk> wrote:
Thanks Bjarne,
I had tried to nest but failed miserably, I just couldn't get the
syntax or the logic right. I will keep trying.

I did google on the snapshot/OutputTo, I should have specified
more....how does this apply to the openrecordset code with the
parenthesis - does this need to be replaces with another operator?

Thanks
John

On May 29, 11:24*am, flymo <fly... (AT) hotmail (DOT) com> wrote:
dbOpenSnapshot - replace with dbOpenDynaset ?

On May 29, 11:33*am, flymo <fly... (AT) hotmail (DOT) com> wrote:
Hello
I am working on creating the nested loop suggestion and initial runs
cause an error - 2465 "application" can't find the field 'L0'
referred to in your expression when the code gets to the line

rst1.MoveFirst
Do Until rst1.EOF
Me.Controls("L" & Trim(str(h))).Caption = Me.Controls("L" &
h).Caption & rst1!xText2 & vbCrLf

rst1!Text2 shows a correct value, but I cannot see where the error is
coming from, any suggestions would be appreciated.


SQLStmt = "Select TechID, Lastname as xText2 FROM qryVisitsSch2_tech"
& strSQLArg
SQLStmt2 = "SELECT Customer AS xText, ScheduledDateTime AS
xDateTime FROM qryVisitsSch2" & strSQLArg
Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset(SQLStmt, dbOpenDynaset)
Set rst2 = dbs.OpenRecordset(SQLStmt2, dbOpenDynaset)
If rst1.RecordCount = 0 Then Exit Sub

rst1.MoveFirst
Do Until rst1.EOF
Me.Controls("L" & Trim(str(h))).Caption = Me.Controls("L" &
h).Caption & rst1!xText2 & vbCrLf
If rst2.RecordCount = 0 Then Exit Sub
rst2.MoveFirst
Do Until rst2.EOF
h = Weekday(rst2!xDateTime)
Me.Controls("L" & Trim(str(h))).Caption =
Me.Controls("L" & h).Caption & rst2!xText & vbCrLf
rst2.MoveNext
Loop
'
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set dbs = Nothing

End Sub




Thanks John

PS If I wanted to Underline the first loop parameter, how would that
be added to the me.controls line.....

On May 29, 1:05*pm, flymo <fly... (AT) hotmail (DOT) com> wrote:
Hello All
the 'L0" refers to the Length & height values -
any suggestions to fix the confusion?
Thanks
John