How to Use 'Between' in SQL Statement?

[jbt007]

I have a SLQ Statement using a prompt to get the value of two codes from the user. This should be an easy thing but the result is not what I expect. Can anyone spot the problem? When I type in 8000 for the start ID and 8999 for the end ID, I want only ActID between 8000 and 8999 but I get codes between 8000 and 8999 as well as codes between 80000 and 89999, and codes between 800000 and 899999! Very odd. The ActID field is a text field and all ID's have leading spaces " 8000" is the Activity ID for 8000 and " 80000" is the ID for 80000. This query should only evaluate the right 5 characters of the ActID.

Any help would be appreciated!

PARAMETERS [Start Activity ID:] Value, [End Activity ID:] Value;
SELECT qryMnth.Job, qryMnth.Phase, tblCftSum.Srt1 AS Craft, qryMnth.ActID, qryMnth.Act_Title, tblProjInfo.Period, [qryMnth]![PeriodFinish] AS RptDte, Sum(qryMnth.BHrs) AS BHrs, Sum(qryMnth.AHrs_td) AS [AHrs ToDate], Sum([qryMnth].[AHrs_td]-[qryMnth].[AHrs_lm]) AS [AHrs Period], Sum(qryMnth.EHrs_td) AS [EHrs ToDate], Sum([qryMnth].[EHrs_td]-[qryMnth].[EHrs_lm]) AS [EHrs Period], Sum(qryProj.PHrs_sl) AS SLHrs, Sum(qryProj.PHrs_wm) AS MPHrs, Sum(qryMnth.BTot) AS BCst, Sum(qryMnth.ATot_td) AS [ACst ToDate], Sum([qryMnth].[ATot_td]-[qryMnth].[ATot_lm]) AS [ACst Period], Sum(qryMnth.ETot_td) AS [ECst ToDate], Sum([qryMnth].[ETot_td]-[qryMnth].[ETot_lm]) AS [ECst Period], Sum(qryProj.PTot_sl) AS SLCst, Sum(qryProj.PTot_wm) AS PMCst, 1 AS PFT, IIf([AHrs ToDate]=0 Or [EHrs ToDate]=0,0,[AHrs ToDate]/[EHrs ToDate]) AS PFTD, IIf([EHrs Period]=0,0,[AHrs Period]/[EHrs Period]) AS PFTP, IIf([AHrs ToDate]=0 Or [EHrs ToDate]=0,0,([MPHrs]-[AHrs ToDate])/([BHrs]-[EHrs ToDate])) AS PFTG, IIf([AHrs ToDate]=0 Or [EHrs ToDate]=0,0,[MPHrs]/[BHrs]) AS PFAC
FROM tblUser_Sort RIGHT JOIN (tblCftSum RIGHT JOIN (((qryMnth LEFT JOIN tblActMap ON qryMnth.S_Key = tblActMap.S_Key) LEFT JOIN tblProjInfo ON qryMnth.PeriodFinish = tblProjInfo.Period_Dte) LEFT JOIN qryProj ON qryMnth.P_Key = qryProj.P_Key) ON tblCftSum.Srt1 = tblActMap.Srt1) ON tblUser_Sort.Srt4 = tblActMap.Srt4
WHERE (((Val(Right(Trim([qryMnth]![ActID]),5))) Between [Start Activity ID:] And [End Activity ID:]))
GROUP BY qryMnth.Job, qryMnth.Phase, tblCftSum.Srt1, qryMnth.ActID, qryMnth.Act_Title, tblProjInfo.Period, [qryMnth]![PeriodFinish]
HAVING (((tblCftSum.Srt1)<>"07") AND ((tblProjInfo.Period)>=0))
ORDER BY qryMnth.Job, qryMnth.Phase, tblCftSum.Srt1, qryMnth.ActID;

[pod]

try this:

...
WHERE (((Val(Right(Trim([qryMnth]![ActID]),5))) >= [Start Activity ID:] 
And (((Val(Right(Trim([qryMnth]![ActID]),5))) <=  [End Activity ID:]))

[jbt007]

Ya - I should have said this in my original post. This gives me the same results as the 'Between...' statement.

WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>=[Start Activity ID:] And (Val(Right(Trim([qryMnth]![ActID]),5)))<=[End Activity ID:]))

[TheSmileyCoder]

I dont have a good explanation of whats going on, but I can suggest to try:

WHERE (((cint([qryMnth]![ActID])) Between [Start Activity ID:] And [End Activity ID:]))

OR

WHERE (((Cint([qryMnth]![ActID])) Between cint([Start Activity ID:]) And cint([End Activity ID:])))

[jbt007]

I don't know why but this seems to have fixed it:

WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>[Start Activity ID:]-1 And (Val(Right(Trim([qryMnth]![ActID]),5)))<[End Activity ID:]+1))

I guess if you 'force' a calculation in the paramater value, Access decides it's ok to do the math.

[TheSmileyCoder]

Did you try the suggestion I made?

On another note, unless there is a need for the ID to be a string, I would suggest converting it to a numeric value. For one, they are faster to perform queries on.

[NeoPa]

The answer to your question is simply that you are using strings. You expect the results to reflect numbers (clearly from your question) yet you work in strings. The code is behaving exactly as it aught to when it has three strings to compare. If you want the comparison to behave as if it's dealing with numbers then you need to design it to use numbers.

[jbt007]

Smiley - Yes I did try your suggestion and the result was the same. The reason for strings is that the ID Code is 10 digits and it may contain an alpha numeric number. The root (right 5) will always be numeric.

[jbt007]

NeoPa - I don't follow your response. If a string is converted to a number with Val('string') or CInt('string'), should not the SQL treat the result as a number, not a string? And if it does not, why did this seem to work?

... 
WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>[Start Activity ID:]-1 And (Val(Right(Trim([qryMnth]![ActID]),5)))<[End Activity ID:]+1))
...

Thanks for the feedback...

[NeoPa]

The problem you describe in the OP is a classic illustration of text comparisons. The reason it's difficult to see is that the entered parameters are determined by SQL to be strings (seem to be deemed to be strings. Probably because it defaults to that, though I can't speak from much experience there as I very rarely use prompts in SQL. Such an approach is too undefined and can easily lead to misunderstandings such as this).

Why did your example seem to work?
Simply because SQL does a best guess to determine the type for you. - 1 & + 1 are pretty strong clues that the item should be numeric.

Between and Val() used together should work for you too. Fundamentally, the idea is to use the most appropriate types when defining the tables.

[jbt007]

So - knowing that changing the ID to a numeric field is not an option, is there a better approach to pulling a sub-set of data between two ranges?

[NeoPa]

JBT007:
So - knowing that changing the ID to a numeric field is not an option, is there a better approach to pulling a sub-set of data between two ranges?

This depends on what you really mean. It seems to me you may be referring specifically to the field definition itself (as the question indicates - but that would be a strange question in the circumstances) or the processing of the data within the query.

If it's the former then I'm thoroughly confused that you'd ask this - as I've already given a clear (I thought) answer that handles just that scenario (You didn't indicate you'd had trouble understanding my post so I can only reasonably assume you didn't).

If it's the latter then frankly, if the option to convert the data to numeric is invalid, then I would suggest wanting to follow that approach indicates a fundamental misunderstanding of your data. Unless the data can be considered as numeric, how can it make sense to even want to treat it as numeric (and sort it or filter it as such)?

Maybe I've missed something here. I don't feel either interpretation for what you ask makes enough sense to be likely, yet I see no other interpretation.