Parameter Query for a Text Field

[jlo]

I have a parameter query using Between [Enter Start Date] and [Enter End
Date] in the Date field.

I have a Text field named Job ID. Every job has a number such as 700,
7000, 3129, 400.

I am trying to find Job IDs for the range 700-709. In the Job ID field I
used Between [From Job ID] and [To Job ID]. This gives me everything from
700 to 7000. Is there a way to just pull up what is between 700-709? I know
you can do it typing in criteria but each report when it is run will be
random. There are thousands of Job ID's so I couldn't make all of the
queries with the criteria if I wanted to.

Any suggestions? Thanks in advance.

 

[Michel Walsh]

Your field is numerical or alphanumerical?

I suspect your field is alpha-numerical, so "200" is between "1" and
"3", as "BZZ" is between "A" and "C".

Change your field datatype to numerical and then, clearly, the number 200 is
not between the numbers 1 and 3.


Vanderghast, Access MVP

 

[Allen Browne]

Obviously the real solution will be to change the Job ID field into a
Number. That way it will no longer make a Text comparsion (character by
character), and so the problem is solved.

The inefficient futzy solution would be to:
a) Declare the parameters as Long Integers
(Parameters on Query menu, in query design.)

b) Use Val() to convert the text field to a number on the fly.

c) Use Nz() to solve the fact that Val() can't handle nulls.

so the WHERE clause of the query will end up like this:
WHERE Val(Nz([Job ID], "0") Between [From Job ID] and [To Job ID]

 

[jlo]

Thank you to both of you who responded! I will try all your suggestions and
let you know if I have the problem solved.

Obviously the real solution will be to change the Job ID field into a
Number. That way it will no longer make a Text comparsion (character by
character), and so the problem is solved.

The inefficient futzy solution would be to:
a) Declare the parameters as Long Integers
(Parameters on Query menu, in query design.)

b) Use Val() to convert the text field to a number on the fly.

c) Use Nz() to solve the fact that Val() can't handle nulls.

so the WHERE clause of the query will end up like this:
WHERE Val(Nz([Job ID], "0") Between [From Job ID] and [To Job ID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a parameter query using Between [Enter Start Date] and [Enter End
Date] in the Date field.

I have a Text field named Job ID. Every job has a number such as 700,
7000, 3129, 400.

I am trying to find Job IDs for the range 700-709. In the Job ID field I
used Between [From Job ID] and [To Job ID]. This gives me everything from
700 to 7000. Is there a way to just pull up what is between 700-709? I
know
you can do it typing in criteria but each report when it is run will be
random. There are thousands of Job ID's so I couldn't make all of the
queries with the criteria if I wanted to.

Any suggestions? Thanks in advance.

 

[jlo]

Actually, I just noticed it is both. It's not a database I created. I
simply wanted to change the datatype to a Number field and found out there it
is also alphanumerical.

Your field is numerical or alphanumerical?

I suspect your field is alpha-numerical, so "200" is between "1" and
"3", as "BZZ" is between "A" and "C".

Change your field datatype to numerical and then, clearly, the number 200 is
not between the numbers 1 and 3.


Vanderghast, Access MVP

I have a parameter query using Between [Enter Start Date] and [Enter End
Date] in the Date field.

I have a Text field named Job ID. Every job has a number such as 700,
7000, 3129, 400.

I am trying to find Job IDs for the range 700-709. In the Job ID field I
used Between [From Job ID] and [To Job ID]. This gives me everything from
700 to 7000. Is there a way to just pull up what is between 700-709? I
know
you can do it typing in criteria but each report when it is run will be
random. There are thousands of Job ID's so I couldn't make all of the
queries with the criteria if I wanted to.

Any suggestions? Thanks in advance.