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.
Bob CollisonSystem ArchitectAsked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
With an SQL query, your logic operators need to be enclosed with quotes or double quotes:
Cmd45EventTeamMstrLkup111 = "SELECT * " & _
"FROM 45_EVENT_TEAM_MSTR " & _
"WHERE TEAM_ID >= " & ParmFromTeamIdInt115 & " And " & _
"TEAM_ID <= " & ParmToTeamIdInt115 & ";"

Open in new window

You don't need an DblDblQuote variable (or constant) as it only make things more difficult to read.

Final note:
I do not recommend concatenating like that, bc when you display the resulting string in the execution window, you obtain a very long string on a single line, wich is difficult to debug in case of mistakes.
I prefer adding new lines when building the string, like in the following snipet:
Dim SQL As String
SQL = vbNullString
SQL = SQL & "SELECT columnA," & vbCrLf 
SQL = SQL & "       columnB" & vbCrLf
SQL = SQL & "FROM MyTable" & vbCrLf
SQL = SQL & "WHERE columnA = 45;"

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
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

0
 
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
The correct syntax solved the problem.

Thanks to all.
Bob C.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.