|  | Converting list box column value to time | | |
| | | John |  |
|  Posted: Wed Sep 03, 2008 10:22 pm Post subject: Converting list box column value to time |  |
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 |
| |
| | | Steve | |
| | Posted: Wed Sep 03, 2008 10:44 pm Post subject: Re: Converting list box column value to time | |
Dim StartTime As Date
Dim Ctl As Control
Set ctl = Me!MyList
Steve
"John" <info@nospam.infovis.co.uk> wrote in message
news:uD9aPRiDJHA.1628@TK2MSFTNGP02.phx.gbl...
| Quote: | 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
|
|
| |
| | | Linq Adams via AccessMons | |
| | Posted: Wed Sep 03, 2008 11:00 pm Post subject: Re: Converting list box column value to time | |
| |  | |
I'm a little confused as to exactly what you're trying to do. The
For Each varItm In MyList.ItemsSelected
construct is used to loop thru ***multiple*** selections made from a listbox.
If you do this, assigning each one in turen to the textbox StartTime, it will
end up with value of the last selction made, which doesn't make a lot of
sense..
If what you're trying to do is make a ***single*** selection from the listbox,
subtract 10 minutes from it then assign it to StartTime, you would simply
use
Private Sub ListBoxName_AfterUpdate()
Me.StartTime = Format(DateAdd("n", -10, Me.ListBoxName.Column(4)), "hh:mm")
End Sub
Notice that the columns index is zero-based, so the 5th column is referenced
as column 4 like:
Me.ListBoxName.Column(4)
Notice also that you also need to do your time manipulation ***first*** then
format it, ***not*** format the value from the listbox ***then*** subtract 10
minutees.
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com
LINK |
| |
| | | Tom van Stiphout | |
| | Posted: Thu Sep 04, 2008 3:28 am Post subject: Re: Converting list box column value to time | |
| | | |
On Thu, 4 Sep 2008 01:22:27 +0100, "John" <info@nospam.infovis.co.uk>
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
>StartTime = Format(ctl.Column(5, varItm), "hh:mm") - 10 |
| |
|
|
