finding a range problem

  • Thread starter Thread starter amy
  • Start date Start date
A

amy

Hi

I have a query that pulls you a range of numbered records
in ascending order using the criteria >=[From] And <=[To]
which on running prompts me for a number range to pull
data in a form format. The records then are returned in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the range
of numbers given in a random order. The query has not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy
 
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might return the records the way
you want, or it may not.

If the query is the sorce for a report, you need to specify the order in the
Sorting And Grouping dialog instead (on the View menu in Report Design
view).

It all this is correct, and this is a calculated field and the order is not
as expected, Access does not understand the intended data type. Post the
calculated field and we can suggest further.
 
Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)
-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might return the records the way
you want, or it may not.

If the query is the sorce for a report, you need to specify the order in the
Sorting And Grouping dialog instead (on the View menu in Report Design
view).

It all this is correct, and this is a calculated field and the order is not
as expected, Access does not understand the intended data type. Post the
calculated field and we can suggest further.

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

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

Hi

I have a query that pulls you a range of numbered records
in ascending order using the criteria >=[From] And <= [To]
which on running prompts me for a number range to pull
data in a form format. The records then are returned in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the range
of numbers given in a random order. The query has not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy


.
 
Double-check that you have no other field to the left of this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could misinterpret the data type.

You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the same type as the
field. For example, if ID is an AutoNumber, your parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has special meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

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

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

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)
-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might return the records the way
you want, or it may not.

If the query is the sorce for a report, you need to specify the order in the
Sorting And Grouping dialog instead (on the View menu in Report Design
view).

It all this is correct, and this is a calculated field and the order is not
as expected, Access does not understand the intended data type. Post the
calculated field and we can suggest further.

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

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

Hi

I have a query that pulls you a range of numbered records
in ascending order using the criteria >=[From] And <= [To]
which on running prompts me for a number range to pull
data in a form format. The records then are returned in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the range
of numbers given in a random order. The query has not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy


.
 
I have change it to ID from and Id to but no change.
there is definitely no field on the left or another with
anything in the sort field.
the format is number, general number to 0 decimal places
-----Original Message-----
Double-check that you have no other field to the left of this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could misinterpret the data type.

You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the same type as the
field. For example, if ID is an AutoNumber, your parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has special meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

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

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

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)
-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might
return
the records the way
you want, or it may not.

If the query is the sorce for a report, you need to specify the order in the
Sorting And Grouping dialog instead (on the View menu
in
Report Design
view).

It all this is correct, and this is a calculated field and the order is not
as expected, Access does not understand the intended data type. Post the
calculated field and we can suggest further.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi

I have a query that pulls you a range of numbered records
in ascending order using the criteria >=[From] And
<=
[To]
which on running prompts me for a number range to pull
data in a form format. The records then are
returned
in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the range
of numbers given in a random order. The query has not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy


.


.
 
What Access version and service pack (from Help | About)?

Switch your query to SQL View (View menu in query deisgn), and post the
entire SQL statement.

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

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

I have change it to ID from and Id to but no change.
there is definitely no field on the left or another with
anything in the sort field.
the format is number, general number to 0 decimal places
-----Original Message-----
Double-check that you have no other field to the left of this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could misinterpret the data type.

You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the same type as the
field. For example, if ID is an AutoNumber, your parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has special meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

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

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

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)

-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might return
the records the way
you want, or it may not.

If the query is the sorce for a report, you need to
specify the order in the
Sorting And Grouping dialog instead (on the View menu in
Report Design
view).

It all this is correct, and this is a calculated field
and the order is not
as expected, Access does not understand the intended
data type. Post the
calculated field and we can suggest further.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Hi

I have a query that pulls you a range of numbered
records
in ascending order using the criteria >=[From] And <=
[To]
which on running prompts me for a number range to pull
data in a form format. The records then are returned
in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the
range
of numbers given in a random order. The query has not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any
kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy
 
thanks a lot :

SELECT [Hardware O2uk].ID, [Hardware O2uk].LFCSTTIT,
[Hardware O2uk].LFCSTFST, [Hardware O2uk].LFCSTSNM,
[Hardware O2uk].LFMBLNBR, [Hardware O2uk].LFADRLIN1,
[Hardware O2uk].LFADRLIN2, [Hardware O2uk].LFADRLIN3,
[Hardware O2uk].LFADRLIN4, [Hardware O2uk].LFPSTTWN,
[Hardware O2uk].LFCOUNTY, [Hardware O2uk].LFPSTCDE,
[Hardware O2uk].LFCRDNBR, [Hardware O2uk].LFCRDTYP,
[Hardware O2uk].LFCRDEND, [Hardware O2uk].LFCRDSTR,
[Hardware O2uk].LFISSNBR, [Hardware O2uk].LFHNDTXT,
[Hardware O2uk].Price, [Hardware O2uk].LFMTXCDE,
[Hardware O2uk].Date, [Hardware O2uk].LFGRPPRF, [Hardware
O2uk].[Sales Channel], [Hardware O2uk].[Date Recieved],
[Hardware O2uk].[Order Number], [Hardware O2uk].[Account
Number], [Hardware O2uk].TXN, [Hardware O2uk].[Ordered?],
[Hardware O2uk].[Refused?], [Hardware O2uk].[If No, Why
Not?], [Hardware O2uk].[Completed By], [Hardware O2uk].
[Todays Date]
FROM [Hardware O2uk]
WHERE ((([Hardware O2uk].ID)>=[ID From] And ([Hardware
O2uk].ID)<=[ID To]) AND (([Hardware O2uk].[Ordered?])
="false") AND (([Hardware O2uk].[Refused?])="false")) OR
((([Hardware O2uk].[Ordered?]) Is Null) AND (([Hardware
O2uk].[Refused?]) Is Null))
ORDER BY [Hardware O2uk].ID;
-----Original Message-----
What Access version and service pack (from Help | About)?

Switch your query to SQL View (View menu in query deisgn), and post the
entire SQL statement.

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

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

I have change it to ID from and Id to but no change.
there is definitely no field on the left or another with
anything in the sort field.
the format is number, general number to 0 decimal places
-----Original Message-----
Double-check that you have no other field to the left
of
this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could
misinterpret
the data type.
You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the same type as the
field. For example, if ID is an AutoNumber, your parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has
special
meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)

-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might return
the records the way
you want, or it may not.

If the query is the sorce for a report, you need to
specify the order in the
Sorting And Grouping dialog instead (on the View
menu
in
Report Design
view).

It all this is correct, and this is a calculated field
and the order is not
as expected, Access does not understand the intended
data type. Post the
calculated field and we can suggest further.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Hi

I have a query that pulls you a range of numbered
records
in ascending order using the criteria >=[From]
And
<=
[To]
which on running prompts me for a number range to pull
data in a form format. The records then are returned
in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked fine.
Yesterday for some reason the query now returns the
range
of numbers given in a random order. The query
has
not
changed and I have even rebuilt the query and it still
happens. Have you any idea why this happens and any
kind
of solution as this is important that it is in order.

Any help on this matter would be greatly appreciated.

Thanks :-)

amy


.
 
Okay, that's actually a very simple query: one table, and sorting on one
field only.

Please add the Parameters as suggested in the previous reply. (Parameters on
Query menu.)

Also, have a go at repairing the database: Tools | Database Utilities |
Repair.

You have not told us your Access version and service pack either.

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

Reply to group, rather than allenbrowne at mvps dot org.
amy said:
thanks a lot :

SELECT [Hardware O2uk].ID, [Hardware O2uk].LFCSTTIT,
[Hardware O2uk].LFCSTFST, [Hardware O2uk].LFCSTSNM,
[Hardware O2uk].LFMBLNBR, [Hardware O2uk].LFADRLIN1,
[Hardware O2uk].LFADRLIN2, [Hardware O2uk].LFADRLIN3,
[Hardware O2uk].LFADRLIN4, [Hardware O2uk].LFPSTTWN,
[Hardware O2uk].LFCOUNTY, [Hardware O2uk].LFPSTCDE,
[Hardware O2uk].LFCRDNBR, [Hardware O2uk].LFCRDTYP,
[Hardware O2uk].LFCRDEND, [Hardware O2uk].LFCRDSTR,
[Hardware O2uk].LFISSNBR, [Hardware O2uk].LFHNDTXT,
[Hardware O2uk].Price, [Hardware O2uk].LFMTXCDE,
[Hardware O2uk].Date, [Hardware O2uk].LFGRPPRF, [Hardware
O2uk].[Sales Channel], [Hardware O2uk].[Date Recieved],
[Hardware O2uk].[Order Number], [Hardware O2uk].[Account
Number], [Hardware O2uk].TXN, [Hardware O2uk].[Ordered?],
[Hardware O2uk].[Refused?], [Hardware O2uk].[If No, Why
Not?], [Hardware O2uk].[Completed By], [Hardware O2uk].
[Todays Date]
FROM [Hardware O2uk]
WHERE ((([Hardware O2uk].ID)>=[ID From] And ([Hardware
O2uk].ID)<=[ID To]) AND (([Hardware O2uk].[Ordered?])
="false") AND (([Hardware O2uk].[Refused?])="false")) OR
((([Hardware O2uk].[Ordered?]) Is Null) AND (([Hardware
O2uk].[Refused?]) Is Null))
ORDER BY [Hardware O2uk].ID;
-----Original Message-----
What Access version and service pack (from Help | About)?

Switch your query to SQL View (View menu in query deisgn), and post the
entire SQL statement.

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

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

I have change it to ID from and Id to but no change.
there is definitely no field on the left or another with
anything in the sort field.
the format is number, general number to 0 decimal places

-----Original Message-----
Double-check that you have no other field to the left of
this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could misinterpret
the data type.

You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the
same type as the
field. For example, if ID is an AutoNumber, your
parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has special
meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)

-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might
return
the records the way
you want, or it may not.

If the query is the sorce for a report, you need to
specify the order in the
Sorting And Grouping dialog instead (on the View menu
in
Report Design
view).

It all this is correct, and this is a calculated field
and the order is not
as expected, Access does not understand the intended
data type. Post the
calculated field and we can suggest further.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

message
Hi

I have a query that pulls you a range of numbered
records
in ascending order using the criteria >=[From] And
<=
[To]
which on running prompts me for a number range to
pull
data in a form format. The records then are
returned
in
ascending number order. This has been the case for
approximately the last 6 weeks. This has worked
fine.
Yesterday for some reason the query now returns the
range
of numbers given in a random order. The query has
not
changed and I have even rebuilt the query and it
still
happens. Have you any idea why this happens and any
kind
of solution as this is important that it is in
order.

Any help on this matter would be greatly
appreciated.

Thanks :-)

amy
 
It is access version 8.0.0.5903 and i am not sure about
service pack

thanks :-)
-----Original Message-----
Okay, that's actually a very simple query: one table, and sorting on one
field only.

Please add the Parameters as suggested in the previous reply. (Parameters on
Query menu.)

Also, have a go at repairing the database: Tools | Database Utilities |
Repair.

You have not told us your Access version and service pack either.

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

Reply to group, rather than allenbrowne at mvps dot org.
thanks a lot :

SELECT [Hardware O2uk].ID, [Hardware O2uk].LFCSTTIT,
[Hardware O2uk].LFCSTFST, [Hardware O2uk].LFCSTSNM,
[Hardware O2uk].LFMBLNBR, [Hardware O2uk].LFADRLIN1,
[Hardware O2uk].LFADRLIN2, [Hardware O2uk].LFADRLIN3,
[Hardware O2uk].LFADRLIN4, [Hardware O2uk].LFPSTTWN,
[Hardware O2uk].LFCOUNTY, [Hardware O2uk].LFPSTCDE,
[Hardware O2uk].LFCRDNBR, [Hardware O2uk].LFCRDTYP,
[Hardware O2uk].LFCRDEND, [Hardware O2uk].LFCRDSTR,
[Hardware O2uk].LFISSNBR, [Hardware O2uk].LFHNDTXT,
[Hardware O2uk].Price, [Hardware O2uk].LFMTXCDE,
[Hardware O2uk].Date, [Hardware O2uk].LFGRPPRF, [Hardware
O2uk].[Sales Channel], [Hardware O2uk].[Date Recieved],
[Hardware O2uk].[Order Number], [Hardware O2uk]. [Account
Number], [Hardware O2uk].TXN, [Hardware O2uk]. [Ordered?],
[Hardware O2uk].[Refused?], [Hardware O2uk].[If No, Why
Not?], [Hardware O2uk].[Completed By], [Hardware O2uk].
[Todays Date]
FROM [Hardware O2uk]
WHERE ((([Hardware O2uk].ID)>=[ID From] And ([Hardware
O2uk].ID)<=[ID To]) AND (([Hardware O2uk].[Ordered?])
="false") AND (([Hardware O2uk].[Refused?])="false")) OR
((([Hardware O2uk].[Ordered?]) Is Null) AND (([Hardware
O2uk].[Refused?]) Is Null))
ORDER BY [Hardware O2uk].ID;
-----Original Message-----
What Access version and service pack (from Help | About)?

Switch your query to SQL View (View menu in query deisgn), and post the
entire SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have change it to ID from and Id to but no change.
there is definitely no field on the left or another with
anything in the sort field.
the format is number, general number to 0 decimal places

-----Original Message-----
Double-check that you have no other field to the
left
of
this one in the
query design window that has anything in the Sort row.

In table [Hardware O2uk], the ID field is what type?
AutoNumber or Number perhaps?
If so, there should be no way Access could misinterpret
the data type.

You could (and should) declare your parameters.
Choose Parameters on the Query menu, and make them the
same type as the
field. For example, if ID is an AutoNumber, your
parameter declaration
dialog will look like this:
[From] Long Integer
[To] Long Integer
(BTW, these are not great names to use. FROM has special
meaning in a SQL
clause, so you might prefer to use [From ID] instead.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

The field is as follows:

Field: ID
Table: Hardware O2uk
Sort: Ascending
Show: Yes
Criteria: >=[From]And<=[To]
Or:

I hope this helps in any way

thankyou for your time it is appreciated :-)

-----Original Message-----
In query design view, you will see a Sorting row.
Under this field, choose:
Ascending

If you do not specify a sort order, Access might
return
the records the way
you want, or it may not.

If the query is the sorce for a report, you need to
specify the order in the
Sorting And Grouping dialog instead (on the View menu
in
Report Design
view).

It all this is correct, and this is a calculated field
and the order is not
as expected, Access does not understand the intended
data type. Post the
calculated field and we can suggest further.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

"amy" <[email protected]>
wrote
in
message
Hi

I have a query that pulls you a range of numbered
records
in ascending order using the criteria >=[From] And
<=
[To]
which on running prompts me for a number range to
pull
data in a form format. The records then are
returned
in
ascending number order. This has been the
case
for
approximately the last 6 weeks. This has worked
fine.
Yesterday for some reason the query now
returns
the
range
of numbers given in a random order. The query has
not
changed and I have even rebuilt the query and it
still
happens. Have you any idea why this happens
and
any
kind
of solution as this is important that it is in
order.

Any help on this matter would be greatly
appreciated.

Thanks :-)

amy


.
 
Back
Top