Query Parameter will not pull correct number range?

[MariaL]

Hello Everyone. I have a query which displays records with ID's ranging from 01 to 300. When I use the following prompt: > [type in beginning ID]and<[type in ending ID], and I type in 01 and 2000, I only get records in the 100 range. I understand the SQL structure is reading 1 is less than 2. Is there anyway around this? How do I get records 105 through 250? I appreciate any suggestions.

 

[JoanneM]

Morning

try

between [type in beginning ID]and[type in ending ID]


this may have the desired effect

let me know if I misunderstood the question


Jo

 

[MariaL]

Thank you for your responce, but this doesn't work either. To my understanding the system will see 2000 as less than 3. Therefore, If I am looking for 01 - 2000, it will not pull the three hundred range because 3 is greater than 2. Can we get around this to make these parameters pull the correct records? I appreciate your help. Have a great day.

 

[JoanneM]

Sorry

Working in dense mode here .. of course you are not searching on numbers you must be searching on text field

so providing you don't have any nulls if you convert your ids to numbers in the query eg

numCriteria:cint(id)
and put the parameter as you have it in the criteria line

you should get the result you require.


Jo

 

[MariaL]

Thank you Jo. I know I sound like an idiot, but how do I get to the query eg? Where exactly do I write this expression? Thank you so much for your help.

 

[MariaL]

Good Morning. I went to SQL view (My guess at what eg is) and typed in the expression. Ofcourse, I'm doing something wrong.

Here is the structure:

SELECT Crew.EmployeeID, Crew.LastName, Crew.FirstName, Crew.MailingAddress, Crew.MailingCity, Crew.MailingState, Crew.MailingZipcode, Crew.EmploymentStatus
FROM Crew
WHERE (((Crew.LastName) Like [Type in Last Name] & &quot;*&quot AND ((Crew.MailingState) Like [Type in State] & &quot;*&quot AND ((Crew.EmploymentStatus) Like [Type in Employment Status] & &quot;*&quot);

Where do I type in this expression?

Thank you for your help. Have a great day.

 

[JoanneM]

Create a new query
Try copying and pasting the text below this may do it
if it doesn't I will return to plan b and use the source query


SELECT Crew.EmployeeID, Crew.LastName, Crew.FirstName, Crew.MailingAddress, Crew.MailingCity, Crew.MailingState, Crew.MailingZipcode, Crew.EmploymentStatus,CInt([Crew.EmployeeID]) AS intn
FROM Crew
WHERE (((Crew.LastName) Like [Type in Last Name] & &quot;*&quot AND ((Crew.MailingState) Like [Type in State] & &quot;*&quot AND ((Crew.EmploymentStatus) Like [Type in Employment Status] & &quot;*&quot) AND (((CInt([Crew.EmployeeID])) Between [type in beginning ID] And [type in ending ID]));



plan b

add the table crew
add Crew.employeeid to the grid
put numCriteria:cint(id) in the next column
in the criteria line of this column put

between [type in beginning ID]and [type in ending ID]

try running this and you will see all numbers returned

This is your source query
add this to your initial query joining on employee id
selecting all from your source query and matches from employee this should work.


hope this helps

jo

 

[MariaL]

Thank you Jo, but no luck. I copied straight form here to a new query, and got the same result as before. I tried option 2, and it still wasn't cooperating. I'm going to research the &quot;Cint&quot; command given above and see if I can find anything. I would appreciate any more suggestions. Thanks for your help.