criteria 'between' explicit value 1 and Value 2 only.

[efandango]

When I say 'between', I don;t really mean that, but don't know of any other
way of describing it...

I have a query that uses criteria to pick from a a range of postcodes from
two combo boxes; like this:

Between [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]

the problem is that, If say I pick

FROM postcode: E1
TO Postcode: E7

it will quite rightly give me E1, E2, E3, and so on...

So I don't really want them to be 'Between' in the MS Access sense.

What I want is only the Postcodes from E1 and E7, exluding all the others,
and I need these two postcode values to go into the single field,
[Run_Point_Postcode] which in turn, will Append to a table.


Can it be done?, am I overlooking something simple here, or have I been
sitting in front of my PC for too long?...


MY SQL:

SELECT DISTINCTROW TOP 18 (select count(subtable.Point_ID)+1 from tbl_Points
as subtable where subtable.Point_ID < tbl_Points.Point_ID and
(((subtable.Run_No) Between [Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))) AS recordnum, tbl_Points.Run_No,
tbl_Points.Run_point_Venue, tbl_Points.Run_point_Address,
tbl_Points.Run_Point_Postcode
FROM tbl_Points
WHERE (((tbl_Points.Run_Point_Postcode) Between
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]) AND
((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]);



* (Apologies to the mods, I stupidly pasted this into the 'Forms Coding'
forum by mistake, please delete it from there if you can
http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[Rick Brandt]

When I say 'between', I don;t really mean that, but don't know of any
other way of describing it...

I have a query that uses criteria to pick from a a range of postcodes
from two combo boxes; like this:

Between [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]

the problem is that, If say I pick

FROM postcode: E1
TO Postcode: E7

it will quite rightly give me E1, E2, E3, and so on...

So I don't really want them to be 'Between' in the MS Access sense.

What I want is only the Postcodes from E1 and E7, exluding all the
others, and I need these two postcode values to go into the single
field, [Run_Point_Postcode] which in turn, will Append to a table.

= [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]
OR = [Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]

 

[efandango]

Beautiful!

thanks for taking the time to answer my question.

Appreciated.

When I say 'between', I don;t really mean that, but don't know of any
other way of describing it...

I have a query that uses criteria to pick from a a range of postcodes
from two combo boxes; like this:

Between [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]

the problem is that, If say I pick

FROM postcode: E1
TO Postcode: E7

it will quite rightly give me E1, E2, E3, and so on...

So I don't really want them to be 'Between' in the MS Access sense.

What I want is only the Postcodes from E1 and E7, exluding all the
others, and I need these two postcode values to go into the single
field, [Run_Point_Postcode] which in turn, will Append to a table.

= [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]
OR = [Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]