Separate Time from Date in a Report Textbox

[accvbalearner]

Hello All,

Need assistance or an idea on how to proceed.

Here is my issue:

I have a report in Access that prints four columns of time along with names as such. Its a timesheet, some names are big, some are small. In short I have added the code below to the Detail Section On Print Event so that the textbox values are evaluated and the text is size adjusted as necessary to fit in the text box.

Now I have four columns of time that the code is evaluating as if it is a long date. In the source query I stripped the date from the time by subtracting the work date from the timestamp, but Access still evaluates as if it is 'Saturday, December 30, 1899 07:30 AM' and makes the text in the boxes super tiny.

Any ideas on how I can adjust the code to either bypass those time textboxes in the detail section or how I can make access see the time as only a time with none of the date information with it?

The textbox is currently formatted as Medium Time. I've tried the Format([TIME],"hh:nn") but that just changed it to Short Time and it's still tiny. I did the format in both the Report and Source query, same result.

Any assistance would be greatly appreciated. As it is now, I've removed the Event Procedure from the Property and I keep adding it back when its needed by other users.

Thanks in advance for any help or ideas that can be provided.

Take Care,
accvbalearner


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ctl As Control, strText As Variant, strName As String
' This routine uses the TextWidth methods to determine the maximum size
' of font possible to ensure a text string is printed in full in the
' report's current font without loosing any characters.

Me.ScaleMode = 1 ' set all measurments to twips

For Each ctl In Me.Detail.Controls

If ctl.ControlType = acTextBox Then

strName = ctl.Name

If Nz(ctl.Tag, "") = "" Then
ctl.Tag = ctl.FontSize
End If


' set the control's fontsize to a suitable large size to begin with
ctl.FontSize = ctl.Tag
' make sure the report font size is equal to the control's fontsize.
Me.FontSize = ctl.FontSize

' grab the text from the control
strText = ctl.Value

' evaluate the Loop until the text fits the Width of the box less 24%. Do this
' by reducing the font size incrementally and re-testing the Loop's criteria.
If Len(strText) > 0 Then
Do Until TextWidth(strText) < ctl.Width '- (ctl.Width * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextWidth function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop

' now evaluate for the height of the text to make sure it fits vertically
Do Until TextHeight(strText) < ctl.Height - (ctl.Height * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextHeight function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop

End If
End If

Next ctl
End Sub

 

[theDBguy]

Hi. Date/Time values always contain both the date and time components even if you think you have subtracted the date part. Since this is only for presenting the time on a report, maybe you could just use a Text value by using the Format() function in your query to display the medium time.

 

[June7]

Maybe set the control's Tag property to nothing in design? If Tag is empty then pass the control.

Please post code within CODE tags to retain indentation and readability.

 

[accvbalearner]

theDBguy,

I tried your idea from another recommendation on this forum but it didn't work. Can you give me an idea on how to make it work?

Here is what I did:
In the property sheet in the Data Tab Control Source I entered: Format([ST],"hh:nn"). When I run the report I get a Run-time error '13': Type Mismatch in the code I added in the On Print [Event Procedure]

When I Debug it takes me to the strText = ctl.Value

I don't know why that is an issue strText is a Variant, and it doesn't matter what type of text is in the textbox in any of the other cases, I have regular text, whole numbers, decimal numbers, dates, times (they're shrinking), currency. So I don't understand why me formatting something makes a difference.

Thanks for your idea and quick response though!

Take Care,
accvbalearner

 

[accvbalearner]

June7,

Thanks for your reply and idea.

I tried to place a value into the Tag property in design view of the [ST] Text box and got another Run-Time 13 error.

This time is stopped on ctl.Fontsize = ctl.Tag

I tried three different values in the Tag Property a spacebar - got a Run Time error, the word 'Nothing' - got a Run Time error and 11. It ran with 11 but was still really small.

Is there an Else to the If Nz(ctl.Tag, "") = "" Then ctl.Tag = FontSize that I could use to tell it to bypass the field and go to the next one?

Then maybe I could put ByPass in the Tag Property and when the code sees it, it'll just work around it.

Thanks June7, its a great idea, I just don't know how to implement it. It needs to stay in the For Next loop, just skip a Textbox if it has a ByPass Tag property.

Appreciate the ideas!

Take Care,
accvbalearner

 

[theDBguy]

theDBguy,

I tried your idea from another recommendation on this forum but it didn't work. Can you give me an idea on how to make it work?

Here is what I did:
In the property sheet in the Data Tab Control Source I entered: Format([ST],"hh:nn"). When I run the report I get a Run-time error '13': Type Mismatch in the code I added in the On Print [Event Procedure]

When I Debug it takes me to the strText = ctl.Value

I don't know why that is an issue strText is a Variant, and it doesn't matter what type of text is in the textbox in any of the other cases, I have regular text, whole numbers, decimal numbers, dates, times (they're shrinking), currency. So I don't understand why me formatting something makes a difference.

Thanks for your idea and quick response though!

Take Care,
accvbalearner

Hi. I think you misunderstood what I was suggesting. What I meant was if you had a query like this:

SELECT DateField, TimeField-DateField FROM TableName

I was saying to try it this way instead:

SELECT DateField, Format(TimeFiled, "Medium Time") FROM TableName

Sent from phone...

 

[June7]

I just did a test. Empty Tag property is empty string, not Null, so the Nz() function is useless in this context.

 

[gemma-the-husky]

you can format a date in a text box as short date, or short time (as well as many others)
You definitely should not have a problem with a time if you set the text box format as Short time., or indeed any of the options. Your field is DATETIME format, isn't it? It's not something else?

the date will show as dd/mm/yyyy (eg)
the time will show as hh:mm (eg)

One issue might be that time is evaluated modulo 24hours, so if you sum the times, you will get a nonsensical date - but you wont see the date in a time formatted text bo. If your time just exceeed 24 hours, you will get the same problem. eg a single time 36hrs just cannot be presented as either 1 day 24 hrs, or 36 hours.

eg - you times may add to 27 days, 3hrs 42 minutes.

you will just see 03:42 in your text box.
or a date like 27/11/1899 03:42 if you leave it a date.

to present a time as an "improper fraction"

You will have to manipulate the date to get a value in the form of an "improper fraction", if you will, so you end up with 651:42 (total hours/minutes for 27 days, 3hrs, 42 mins.), and you cannot get this by any normal date/time function.

You would probably have to hide the time (or sum of time) field, and have an unbound text box that presented the total in the way you want to see it.

Is that what you mean?

 

[gemma-the-husky]

Try this sample

Open form1
It takes a date number of 3.274, and shows this presented, ignoring seconds as

a general date
just the time

an "improper time". It uses a function within the form's code module for the improper time.

 

[June7]

If ctl.ControlType = acTextBox And ctl.Tag <> "ByPass" Then
'code to set font here
End If