Converting list box column value to time

[John]

Hi

I have a list box on a form whose fifth column has values coming from a
datetime table field formatted as hh:mm..

I am trying to get the value of this fifth column, treat it as an hh:mm time
value, subtract 10 minutes from it and then assign it to a variable. I am
using the below code but I am getting the 'Type Mismatch' error on the line
StartTime = Format(ctl.Column(5, varItm), "hh:mm") - 10. What am I doing
wrong?

Many Thanks

Regards

= Code used ==============

Dim StartTime
Set ctl = frm!MyList

For Each varItm In MyList.ItemsSelected
' Below line gets 'Type Mismatch' error
StartTime = Format(ctl.Column(5, varItm), "hh:mm") - 10

'...

Next

 

[Tom van Stiphout]

On Thu, 4 Sep 2008 01:22:27 +0100, "John" <[email protected]>
wrote:

On this particular expression, put yourself in the position of the
poor Access (VBA?) expression evaluator:
x = y-10

You wrote (translated):
Dim x
The docs say that this means x is a variant.
Bad choice; best programming practices dictate to always use the
smallest possible datatype, rather than a sloppy one.
In this case:
option explicit 'put this at top of file.
dim x as date
On the positive side: x will be able to accept whatever y-10 yields,
even Null.

Let's look at y. It is given by Format(ctl.Column(5, varItm), "hh:mm")
The docs say that Format returns a variant. In this case it's a
variant of subtype vbString (test this with VarType()).

So we have: v = s - 10
Now put yourself in Access' shoes. You're given a string and asked to
subtract 10. 10 what? 10 characters? 10 days? 10 minutes?

It becomes evident that your original code can't stand. As others have
pointed out, DateAdd and DateDiff are the proper functions to use with
date math.

-Tom.
Microsoft Access MVP