Day/Time Format

A

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!
 
D

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.
 
A

Antney

Daryl S,

Thanks, I'll try it.

Antney

Daryl S said:
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


Antney said:
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!
Click to expand...
Click to expand...
 
A

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?

Daryl S said:
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


Antney said:
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!
Click to expand...
Click to expand...
 
J

JimBurke via AccessMonster.com

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 -
Click to expand...
[quoted text clipped - 81 lines]
Click to expand...
 
A

Antney

Jim,

Thank you, that did the trick!

Antney

JimBurke via AccessMonster.com said:
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 -
Click to expand...
[quoted text clipped - 81 lines]
Thank you!
Click to expand...
Click to expand...

--
Jim Burke




.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping/Deleting Dates/Times (HELP ANYONE!) 2
Grouping/Deleting Dates/Times 8
calculate hours using start time & end time, excluding weekends 5
Calculate Total hours worked during workdays within given shift time. 0
Calculate Total hours worked during workdays within given shift time. 2
Time Format 6
Time format via Worksheet Change 5
Adding time in 24 hour format to produce hours in decimal format 11

Top