Between ( ) Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database of plans that uses mile points for beginning &
ending. I want to be able to search through the plans for a certain MP. So
if I want to search SR XX at MP 3.2, How would I get the Between ( ) Function
to work. I tried to put in the query:
BETWEEN ([Enter Mile Post], "BMP", "EMP"]

where BMP & EMP are columns in the table. Did not work. Any suggestions?
If so, thanks and thanks again. If I don't use the BETWEEN function, what
would I use?
 
Switch the query to SQL View (View menu.)

Change the WHERE clause to read:
WHERE [Enter Mile Post] Between [BMP] And [Emp]

Best of luck in finding your mile post between the bitmap and an
electro-magnetic pulse. :-)
 
In the criteria for BMP put the following:
[Enter Mile Post]
Click to expand...

In the criteria for EMP put the following:
< [Enter Mile Post]

You may have to swap around the greater than and less than signs.

If fact you might need two sets of criteria as mile markers can get larger
or smaller depending on the direction that you are travelling. That case
you'd need an Or statement which would look something like:

In the criteria for BMP put the following:
[Enter Mile Post]
Click to expand...
< [Enter Mile Post]

In the criteria for EMP put the following:
< [Enter Mile Post]
[Enter Mile Post]
Click to expand...

Again I might have the signs backwards.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dtjbocala said:
I am creating a database of plans that uses mile points for beginning &
ending. I want to be able to search through the plans for a certain MP. So
if I want to search SR XX at MP 3.2, How would I get the Between ( ) Function
to work. I tried to put in the query:
BETWEEN ([Enter Mile Post], "BMP", "EMP"]

where BMP & EMP are columns in the table. Did not work. Any suggestions?
If so, thanks and thanks again. If I don't use the BETWEEN function, what
would I use?
Click to expand...
 
It works. I bow down to the master. You have no idea how wonderful this is.
I cannot thank you enough!

Jerry Whittle said:
In the criteria for BMP put the following:
[Enter Mile Post]
Click to expand...

In the criteria for EMP put the following:
< [Enter Mile Post]

You may have to swap around the greater than and less than signs.

If fact you might need two sets of criteria as mile markers can get larger
or smaller depending on the direction that you are travelling. That case
you'd need an Or statement which would look something like:

In the criteria for BMP put the following:
[Enter Mile Post]
Click to expand...
< [Enter Mile Post]

In the criteria for EMP put the following:
< [Enter Mile Post]
[Enter Mile Post]
Click to expand...

Again I might have the signs backwards.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dtjbocala said:
I am creating a database of plans that uses mile points for beginning &
ending. I want to be able to search through the plans for a certain MP. So
if I want to search SR XX at MP 3.2, How would I get the Between ( ) Function
to work. I tried to put in the query:
BETWEEN ([Enter Mile Post], "BMP", "EMP"]

where BMP & EMP are columns in the table. Did not work. Any suggestions?
If so, thanks and thanks again. If I don't use the BETWEEN function, what
would I use?
Click to expand...
Click to expand...
 
Both methods almost worked. I am now receiving an additional record that
does not fall in the search range.

If I search SR xx at MP 3.2. Access is returning the correct entry and it
is also throwing in the line from the table with BMP 9.176 to EMP 10.843.

How can I get rid of the incorrect information?

Allen Browne said:
Switch the query to SQL View (View menu.)

Change the WHERE clause to read:
WHERE [Enter Mile Post] Between [BMP] And [Emp]

Best of luck in finding your mile post between the bitmap and an
electro-magnetic pulse. :-)

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

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

dtjbocala said:
I am creating a database of plans that uses mile points for beginning &
ending. I want to be able to search through the plans for a certain MP.
So
if I want to search SR XX at MP 3.2, How would I get the Between ( )
Function
to work. I tried to put in the query:
BETWEEN ([Enter Mile Post], "BMP", "EMP"]

where BMP & EMP are columns in the table. Did not work. Any suggestions?
If so, thanks and thanks again. If I don't use the BETWEEN function, what
would I use?
Click to expand...
Click to expand...
 
If you open the table in design view, what is the data type if these fields?
Are they Number or Text?

If they are numbers, it should work. Be sure to declare the parameter in the
query as well. In query design view, choose Parameters on the Query menu.
Enter the row using exactly the same spelling (including the square
brackets), and the same type (probably Long Integer or Double - whatever the
Field Size is in your table.)

If the fields are text, Access does a character-by-characters comparision
instead of a numeric comparision. In this case, 9 is greater than 100,
because the first character in one (the 9) is greater than first character
in the other (the 1.) Changing the field types to numbers and declaring the
parameter should solve the problem.

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

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

dtjbocala said:
Both methods almost worked. I am now receiving an additional record that
does not fall in the search range.

If I search SR xx at MP 3.2. Access is returning the correct entry and it
is also throwing in the line from the table with BMP 9.176 to EMP 10.843.

How can I get rid of the incorrect information?

Allen Browne said:
Switch the query to SQL View (View menu.)

Change the WHERE clause to read:
WHERE [Enter Mile Post] Between [BMP] And [Emp]

Best of luck in finding your mile post between the bitmap and an
electro-magnetic pulse. :-)

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

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

dtjbocala said:
I am creating a database of plans that uses mile points for beginning &
ending. I want to be able to search through the plans for a certain
MP.
So
if I want to search SR XX at MP 3.2, How would I get the Between ( )
Function
to work. I tried to put in the query:
BETWEEN ([Enter Mile Post], "BMP", "EMP"]

where BMP & EMP are columns in the table. Did not work. Any
suggestions?
If so, thanks and thanks again. If I don't use the BETWEEN function,
what
would I use?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top