Between function in VBA

[pieterw]

Hi All

Who can help me out with this situation...

a form, with some items and data
on this form, a list box named check1 that returns the value of a query (only one record/value)

and a commandbutton

------------------------------------------

target: when i click the button, the value in check 1 needs to be within a specific range (between -1 and 1)
if this is the case, execute some action
if not: messagebox

-----------------------------------------
this is what i have so far


If check1.Value > -1 And check1.Value < 1 Then

DoCmd.OpenQuery ("qry_append")

Else
MsgBox "Document not OK"

End If

------------------------------------------

apparently this does not work... can i use a between function?...

who can help me out...

thanks in advance

 

[DCrake]

Select Case Me.Check1
    Case -1 Or 0 Or 1
       '/Do This
    Case Else
       '/Do That
End Select

 

[Brianwarnock]

Just change the check1 to Me.check1 should work, or do you need >= and <= as well

Brian

 

[boblarson]

> -1 AND < 1

is the same as saying

0

if we're talking integers.


But you're saying that you are wanting the value of the listbox named check1? Your first code should work except that -1 and 1 would excluded from the test because you didn't enter >= -1 or <= 1.

You can also use inclusion by using

Between -1 And 1

but you also need to make sure that the value of the list box is what you think it is being returned as. If the bound column is the wrong column you could be getting a different value. So you might want to use

MsgBox Me.Check1

to see what it is returning or use

Debug.Print Me.Check1

to use the Immediate Window in the VBA code area. But as Brian has pointed out - you probably need to add Me. to the reference of the item. Access doesn't always like it when you don't give it the reference to which object the item you are wanting is on.

 

[pieterw]

Hi Guys

Thanks for the many suggestions

I tested some things and apparently the check1 of Me.check1 returns a null value (somehow strange because i can see a value in the box when the form is open)

I used a workaround now: instead of getting the value out of the box, i get it out of an existing query using Dmax. And this works fine!

Thanks again,

Pieter