Time entry for calendar of events (XP)

[Caesar3]

I've build a small database to publish a calendar of events. Of course, one field displays the time for an event. I built the table with an EventTime field with format: Date/Time. Next I built a data-entry form to allow quick and easy entry of events. I also built a report to print a "Calendar of events for [month & year]." Everything uses the table as the data source. The EventTime field allows me to enter a time as, e.g., 11:30 AM. That worked fine until I ran into an event where the sponsor wants to show a range of time, e.g., 12:00-2:00 PM.

I built a query to use as the data source with fields for Start time and End time. In the report, I replaced the Time field with an unbound text box that displays a concatenation: [EventTimeStart] &"-"& [EventTimeEnd]. Now I have some new problems.
[list=1]<LI> Wasted space

[WendellB]

A couple of suggestions - for your concatenated version (do it in a query), put an IIF function in to determine whether the end time is null, and only concatenate the hyphen and the end time when it is not. You can't solve the wasted space problem, but it's only 8 bytes per record, so unless you have millions of records, why worry? To get the short time, use the Format function to set the format in the same expression where you do the concatenation. Finally, as you've discovered, you cannot use the CanGrow and CanShrink on forms, so either set it so the size is adequate for the largest concatenated value, or you have to resort to VBA on the OnCurrent event for the form and reset it based on whether the End Time is null. I would do the former.

[Caesar3]

Wendell: Thank you very much for your response. Please accept my apology for not getting back to you sooner.

Here's a little more background: I'm not going to be the user. For years, I've watched an administrative assistant in our dean's office hammer out her monthly calendar of events. It's painful to watch, even from a distance. She builds this document in Word using tabs and spaces <img src=/S/flee.gif border=0 alt=flee width=25 height=25> to create (sort of) five columns (date/time/event/room/sponsor). No, she doesn't even use Word's table functionality. When I suggested tables, she said the dean didn't like the way Word displayed borders around everything when they posted the document on their intranet. (sigh) Anyway, the crusader in me wants to make this lady's life easier. I think I can do that by creating this Access application. I also know that Access phobia will enter the picture, so I want this thing to be as idiot-proof and user-friendly as possible. I also want the end product to be identical to what she cranks out now

[HansV]

Here you go:

Replace [EventTimeStart] &"-"& [EventTimeEnd] by

Format([EventTimeStart],"Medium Time") & IIf(IsNull([EventTimeEnd]), Null, "-") & Format([EventTimeEnd],"Medium Time")

Both dates are formatted as 'medium time' in the expression itself. The IIf function tests whether EventTimeEnd is empty (null); if so, it returns Null, otherwise it returns a dash. I hope you can make this work; if not, post back.

[Caesar3]

Thank you, Hans! I'm not at work at the moment, but I'll bet this is going to work. I'll write back if it doesn't.

[D Willett]

<P ID="edit" class=small>(Edited by D Willett on 03-Oct-03 09:39. Thanks, Solution Founf. (See Bottom Of Post)
)</P>I found this to be very useful and adopted it to a particular query in my database to the following:

Duration: "Booked " & Format([BookInDate],"dd-mmm-yy") & IIf(IsNull([BookOutDate]),Null," To ") & Format([BookOutDate],"dd-mmm-yy")

Which returns

Booked 30-Sep-03 To 01-Oct-03

And if the "BookOutDate" is null returns:

Booked 10-Oct-03

Where the BookOutDate is null, can it return "No ECD"

As in:

Booked 10-Oct-03 No ECD
or
Booked 10-Oct-03 To No ECD

'==============================
Duration: "Booked " & Format([BookInDate],"dd-mmm-yy") & IIf(IsNull([BookOutDate])," No ECD"," To ") & Format([BookOutDate],"dd-mmm-yy")
'==============================

[HansV]

Dave,

Was that a question? The expression in your post should do what you want.

[D Willett]

Thanks Hans, I found the solution just after posting.
I hadn't ticked the "Show as edited" radio button on my edit.

Duration: "Booked " & Format([BookInDate],"dd-mmm-yy") & IIf(IsNull([BookOutDate])," No ECD"," To ") & Format([BookOutDate],"dd-mmm-yy")

[Caesar3]

Hello, Hans!

It works beautifully! Thank you once again!

I have a couple more tiny details:
<UL><LI>The "Medium Time" formatting gives me stuff like 02:00 PM. How do I shed the leading zero?
<LI>I've adapted your example to deal with a similar problem with the dates. The table has two date fields: EventDateStart and EventDateEnd. Both fields are formatted as Number, not Date. (I need to display only the number portion of the date--the month appears in the form header anyway.) With your expression (modified), the dates appear as, e.g., 6 or 12-15. That's cool. However, the report sorts the events as if they're text (1, then 10-12, 11, 12, 2, 3-5, 7...you get the picture). They're supposed to appear in order by date, of course, but they don't. I have a hunch that this has something to do with the fact that I'm using an expression, right? Any suggestions?[/list]

[HansV]

1. Use "h:mm AM/PM" instead of "Medium Time".

2. If you want to sort, do this on the individual date fields. The concatenated expression results in a text value, so it is sorted as text.

[Caesar3]

Once again, I owe you a debt of gratitude, Hans. Thank you.

The time formatting worked exactly like I want. As for the date sorting, when you say, "do this on the individual date fields," are you saying that I just can't use the concatenated expression and sort the dates properly? I wish I could remember where I saw this, but it seems someone posted some code that tells Access (or VBA...whatever) that, "Yeah, we know this data is text, but just this once let's pretend it's numbers and sort the stuff accordingly." Have you seen anything like that?

[HansV]

I can't search the Lounge any more than you can, so I don't know, but I doubt you could make it work with concatenated expressions such as you use.

[MarkD]

<P ID="edit" class=small>(Edited by MarkD on 04-Oct-03 08:43. Corrected function declaraton and query results.)</P>A while back I posted something on how to do this with a custom function, but that post would be long lost by now. Don't know if this can be used in your case because am not sure what the end result of this concatenation looks like. You can use user-defined functions like these examples to sort text field by numeric values only:

Public Function GetNumVal(ByRef strTxt As String) As Double

' Returns 0 if no numbers in text string
Dim n As Long

For n = 1 To Len(strTxt)
If IsNumeric(Mid$(strTxt, n, 1)) Then
GetNumVal = Val(Mid$(strTxt, n))
Exit For
Else
GetNumVal = 0
End If
Next n

End Function

Public Function GetNumValAll(ByRef strTxt As String) As Long

Dim n As Long
Dim strNum As String

' If no numeric characters, returns 0
' Initialize empty string with spaces:
strNum = String(Len(strTxt), Chr$(32))

For n = 1 To Len(strTxt)
If IsNumeric(Mid$(strTxt, n, 1)) Then
Mid(strNum, n, 1) = Mid$(strTxt, n, 1)
' strNum = strNum & Mid$(strTxt, n, 1)
End If
Next n

' Use Replace to remove spaces for non-numeric characters:
GetNumValAll = Val(Replace(strNum, Chr$(32), vbNullString))

End Function

You'd use 1st example if only want to return first numeric value found in text string, 2nd if want to return all numeric values as one value (a different function can be used to return each numeric value found as a separate item in an array). Note use of Mid statement with Mid function (the two are not the same!) in 2nd function - using the Mid statement in this fashion is considered to be more efficient than concatenating string in each loop (as commented out above), due to the way VB handles strings. Something to keep in mind if doing a lot of string concatenation. Also note, the Val function does not recognize number separators such as "," (it does recognize decimal point) - example:

? Val("1,000")
1
? CLng("1,000")
1000

Example of function use in a query:

SELECT Field1, GetNumVal([Field1]) AS Expr1, GetNumValAll([Field1]) AS Expr2
FROM Table1
ORDER BY GetNumVal([Field1]), GetNumValAll([Field1]);

The results of this SQL are shown on attd screen shot. Note that sorting a query based on a calculated field or custom function will not be as efficient (fast) as sorting on a field in a table that is indexed. If you have date fields in table used for this it'd be simpler to just sort on the date fields. For example when concatenating a full name from separate name fields, you would want to sort by the individual fields (LNAME, FNAME, MI) which are indexed rather than on concatenated Full Name field. Fields used for sorting need not be displayed in query results.

If above example not applicable to your case, provide some examples of concatenated text you want to sort & may be able to provide more useful reply.

HTH

[Caesar3]

Thanks, Mark! Now that I've read your reply and all the information Hans has provided, I feel the best and simplest approach will be to abandon the expression I was trying to use and just have two fields: One for the starting date, another for the end date.

Here's a little more information, Mark: In a report, I want to display the start and end dates for events. My original plan was to use an expression, which I built in the query that feeds this report. The expression used the two number fields from the table, thus: [EventDateStart] & "-" & [EventDateEnd]. I was thinking that this approach would give me, for example, <font color=blue>6-7</font color=blue>, and indeed it does. However, as Hans points out, when I put those number fields into an expression, the resulting display is no longer a number

[HansV]

Lucas, you can have the best of both worlds: you can have a calculated field such as [EventDateStart] & "-" & [EventDateEnd] for display, and still sort on the individual date fields. There is no rule that you can only sort on fields used for display.