asked on

Form Event Data Range Selection not working correctly.

I am trying to select records that fall within a range.

The Range is specified on a Form as two strings (i.e. ParmFromTeamIdInt = 000 and F45300ParmToTeamId = 001) and converted to a Long Integer before comparison.

TEAM_ID is specified in the Table as Long with a Format of "000".

Using the following code if TEAM_ID is 0 or 1 I expect the 'In Range' message to display which it does. However if TEAM_ID is 2 it also displays 'In Range'.

A can't figure out why this is happening. What have I done wrong?

Dim ParmFromTeamIdInt As Long
ParmFromTeamIdInt = CLng(F45300ParmFromTeamId)
Dim ParmToTeamIdInt As Long
ParmToTeamIdInt = CLng(F45300ParmToTeamId)

If RS45EventTeamMstrLkup003!TEAM_ID >= ParmFromTeamIdInt & "And " & _
    RS45EventTeamMstrLkup003!TEAM_ID <= ParmToTeamIdInt Then
    MsgBox "In Range"
Else
    MsgBox "Out Of Range"
End If

Open in new window

Thanks,
Bob C.

Fabrice Lambert

Hi,

The if statement evaluate an expression.
But, you're concatenating with a string literal (wich is always true), the result is always true.
Maybe you want Something like this:

If RS45EventTeamMstrLkup003!TEAM_ID >= ParmFromTeamIdInt And  _
    RS45EventTeamMstrLkup003!TEAM_ID <= ParmToTeamIdInt Then
    MsgBox "In Range"
Else
    MsgBox "Out Of Range"
End If

Open in new window

Bob Collison

ASKER

Hi Fabrice,

Thanks for the explanation / suggestion. It works.

I prefer to do this within a Select Statement like the following. What should it be?
'Public Const DblDblQuote = """" 'Use to replace Chr$(34)
Dim ParmFromTeamIdInt115 As Long
ParmFromTeamIdInt115 = CLng(F45300ParmFromTeamId)
Dim ParmToTeamIdInt115 As Long
ParmToTeamIdInt115 = CLng(F45300ParmToTeamId)
Cmd45EventTeamMstrLkup111 = "SELECT * " & _
"FROM 45_EVENT_TEAM_MSTR " & _
"WHERE TEAM_ID >= " & DblDblQuote & ParmFromTeamIdInt115 & DblDblQuote And _
"TEAM_ID <= " & DblDblQuote & ParmToTeamIdInt115 & DblDblQuote

Thanks,
Bob C.

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Collison

ASKER

The correct syntax solved the problem.

Thanks to all.
Bob C.