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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor Commented:
The correct syntax solved the problem.

Thanks to all.
Bob C.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.