Day/Time Format

[Antney]

Hi,

Can anyone help with this. I currently have code which lists class days and
times. Here is how it currently looks:

TuWThF, 9:00a-12:30p, 1:00p-2:30p, 9:00a-
12:30p, 1:00p-2:00p, 9:00a-12:30p, 1:00p-2:30p,
9:00a-12:30p, 1:00p-2:00p

I want it to look like this:

Tu, 9:00a-12:30p, 1:00p-2:30p
W, 9:00a- 12:30p, 1:00p-2:00p
Th, 9:00a-12:30p, 1:00p-2:30p
F, 9:00a-12:30p, 1:00p-2:30p

Here is the code I'm using:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

If anyone has any ideas or needs me to explain in further detail, I'm all
ears!

Thank you!

 

[Daryl S]

Antney -

The reason for the current format is that you are appending all the days
into one variable (sDay) and all the times into another variable (sTime) and
then appending the two together for the resulting string (DaysTimes = sDay &
", " & sTime).

Instead, use just one variable. Change all the sDay references to sTime in
the code, and change your final results string to DaysTimes = sTime as
it no longer needs the sDay piece. You can start with this and format as you
need.

 

[Antney]

Daryl S,

Thanks, I'll try it.

Antney

Antney -

The reason for the current format is that you are appending all the days
into one variable (sDay) and all the times into another variable (sTime) and
then appending the two together for the resulting string (DaysTimes = sDay &
", " & sTime).

Instead, use just one variable. Change all the sDay references to sTime in
the code, and change your final results string to DaysTimes = sTime as
it no longer needs the sDay piece. You can start with this and format as you
need.

--
Daryl S

Hi,

Can anyone help with this. I currently have code which lists class days and
times. Here is how it currently looks:

TuWThF, 9:00a-12:30p, 1:00p-2:30p, 9:00a-
12:30p, 1:00p-2:00p, 9:00a-12:30p, 1:00p-2:30p,
9:00a-12:30p, 1:00p-2:00p

I want it to look like this:

Tu, 9:00a-12:30p, 1:00p-2:30p
W, 9:00a- 12:30p, 1:00p-2:00p
Th, 9:00a-12:30p, 1:00p-2:30p
F, 9:00a-12:30p, 1:00p-2:30p

Here is the code I'm using:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

If anyone has any ideas or needs me to explain in further detail, I'm all
ears!

Thank you!

 

[Antney]

Daryl S,

Actually, I do need the sDay piece, if you'll see my example again below of
how I want it to look. So, I still need the days and the times but I want it
formatted differently. Any ideas?

Antney -

The reason for the current format is that you are appending all the days
into one variable (sDay) and all the times into another variable (sTime) and
then appending the two together for the resulting string (DaysTimes = sDay &
", " & sTime).

Instead, use just one variable. Change all the sDay references to sTime in
the code, and change your final results string to DaysTimes = sTime as
it no longer needs the sDay piece. You can start with this and format as you
need.

--
Daryl S

Hi,

Can anyone help with this. I currently have code which lists class days and
times. Here is how it currently looks:

TuWThF, 9:00a-12:30p, 1:00p-2:30p, 9:00a-
12:30p, 1:00p-2:00p, 9:00a-12:30p, 1:00p-2:30p,
9:00a-12:30p, 1:00p-2:00p

I want it to look like this:

Tu, 9:00a-12:30p, 1:00p-2:30p
W, 9:00a- 12:30p, 1:00p-2:00p
Th, 9:00a-12:30p, 1:00p-2:30p
F, 9:00a-12:30p, 1:00p-2:30p

Here is the code I'm using:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

If anyone has any ideas or needs me to explain in further detail, I'm all
ears!

Thank you!

 

[Antney]

Jim,

Thank you, that did the trick!

Antney

You don't really need to use different variables for the day and the times.
You can just use the function name variable to concatenate everything
together.
You have the separate 'prev' variables to keep track of whether or not
you're at a new day or time, so that's all you need for keeping track
of those separately.

It looks like you want to return a single string, but you want each day
on a separate line. Assuming that, something like this should work
(keep all your other code that you have before the 'while' loop with the
exception
of the variables for sDay and sTime - those won't be needed):

DaysTimes = vbnullstring
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then
if DaysTimes <> vbNullString then
DaysTimes = DaysTimes & vbcrlf
End If
DaysTimes = DaysTimes & D
End If
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then
DaysTimes = DaysTimes & ", " & T
End If
PrevD = D
PrevT = T
Days.MoveNext
Wend

Daryl S,

Actually, I do need the sDay piece, if you'll see my example again below of
how I want it to look. So, I still need the days and the times but I want it
formatted differently. Any ideas?

Antney -

[quoted text clipped - 81 lines]

Thank you!

--
Jim Burke

Message posted via AccessMonster.com


.