Query Criteria help

[Tara]

I have table (tblReceipts) that has a receipt field with the data type set to
text. This was done because receipts are sometimes entered as 1a, 1b, 1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place. I need
help setting criteria in a query so that I can limit the receipts. For
example if someone wants to see receipts 1 through 73a. I need to figure out
how to limit the query to only those receipts. My feeling is that it either
can't be done or it's going to get complicated, but I need to try. Any
suggestions?

Thanks!

 

[Jerry Whittle]

Something like below will work if you don't need to be overly precise. For
example you want 1b but not 1a to show. Also it depends on there always being
leading numerical characters as the Val function extracts leading numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text (
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter Last
Receipt]);

 

[Tara]

I think this will work perfectly! Thank you!

Something like below will work if you don't need to be overly precise. For
example you want 1b but not 1a to show. Also it depends on there always being
leading numerical characters as the Val function extracts leading numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text (
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter Last
Receipt]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have table (tblReceipts) that has a receipt field with the data type set to
text. This was done because receipts are sometimes entered as 1a, 1b, 1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place. I need
help setting criteria in a query so that I can limit the receipts. For
example if someone wants to see receipts 1 through 73a. I need to figure out
how to limit the query to only those receipts. My feeling is that it either
can't be done or it's going to get complicated, but I need to try. Any
suggestions?

Thanks!

 

[John Spencer]

Another option that may prove more precise.

WHERE Right("000000" & Receipts, 7) Between Right("000000" & [Enter First
Receipt],7) and Right("000000" & [Enter Last Receipt],7)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I think this will work perfectly! Thank you!

Something like below will work if you don't need to be overly precise.
For
example you want 1b but not 1a to show. Also it depends on there always
being
leading numerical characters as the Val function extracts leading
numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text
(
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter
Last
Receipt]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have table (tblReceipts) that has a receipt field with the data type
set to
text. This was done because receipts are sometimes entered as 1a, 1b,
1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place. I
need
help setting criteria in a query so that I can limit the receipts. For
example if someone wants to see receipts 1 through 73a. I need to
figure out
how to limit the query to only those receipts. My feeling is that it
either
can't be done or it's going to get complicated, but I need to try. Any
suggestions?

Thanks!

 

[John Spencer]

Never mind, that doesn't work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Another option that may prove more precise.

WHERE Right("000000" & Receipts, 7) Between Right("000000" & [Enter First
Receipt],7) and Right("000000" & [Enter Last Receipt],7)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I think this will work perfectly! Thank you!

Something like below will work if you don't need to be overly precise.
For
example you want 1b but not 1a to show. Also it depends on there always
being
leading numerical characters as the Val function extracts leading
numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text
(
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter
Last
Receipt]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have table (tblReceipts) that has a receipt field with the data type
set to
text. This was done because receipts are sometimes entered as 1a, 1b,
1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place.
I need
help setting criteria in a query so that I can limit the receipts.
For
example if someone wants to see receipts 1 through 73a. I need to
figure out
how to limit the query to only those receipts. My feeling is that it
either
can't be done or it's going to get complicated, but I need to try.
Any
suggestions?

Thanks!

 

[Tara]

Thank you both for your suggestions. I just came up with another question
though. I have a report (and underlying query) that sort by receipt data.
Currently, the records are sorted in this type of order: 22, 220,
221.....228, 229, 23,231, etc, where those are the receipt *numbers*
(actually a text field) I would like it to sort data in this fashion: 1, 2,
3, 4, 5......15, 16, 17, etc. Any ideas on how to accomplish this?

Thanks again for your help so far!

Another option that may prove more precise.

WHERE Right("000000" & Receipts, 7) Between Right("000000" & [Enter First
Receipt],7) and Right("000000" & [Enter Last Receipt],7)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I think this will work perfectly! Thank you!

Something like below will work if you don't need to be overly precise.
For
example you want 1b but not 1a to show. Also it depends on there always
being
leading numerical characters as the Val function extracts leading
numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text
(
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter
Last
Receipt]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have table (tblReceipts) that has a receipt field with the data type
set to
text. This was done because receipts are sometimes entered as 1a, 1b,
1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place. I
need
help setting criteria in a query so that I can limit the receipts. For
example if someone wants to see receipts 1 through 73a. I need to
figure out
how to limit the query to only those receipts. My feeling is that it
either
can't be done or it's going to get complicated, but I need to try. Any
suggestions?

Thanks!

 

[Tara]

Thanks! You saved me some work...I was just going to try it out!

Actually after several tries, I couldn't get the other suggestion to work
either. Thankfully, I was just told to scrap that query anyway, they don't
need to view the data that way. I would be curious to try other suggestions
(just for my own knowledge) though, if you have any ideas.

Never mind, that doesn't work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Another option that may prove more precise.

WHERE Right("000000" & Receipts, 7) Between Right("000000" & [Enter First
Receipt],7) and Right("000000" & [Enter Last Receipt],7)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I think this will work perfectly! Thank you!

:

Something like below will work if you don't need to be overly precise.
For
example you want 1b but not 1a to show. Also it depends on there always
being
leading numerical characters as the Val function extracts leading
numbers. In
other words, Val(73a) will return 73 but Val(A73) will not.

PARAMETERS [Enter First Receipt] Text ( 255 ), [Enter Last Receipt] Text
(
255 );
SELECT tblReceipts.Receipts
FROM tblReceipts
WHERE Val([Receipts]) Between Val([Enter First Receipt]) And Val([Enter
Last
Receipt]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have table (tblReceipts) that has a receipt field with the data type
set to
text. This was done because receipts are sometimes entered as 1a, 1b,
1c, 2,
3, 4a, 4b etc. depending on the type of transaction that took place.
I need
help setting criteria in a query so that I can limit the receipts.
For
example if someone wants to see receipts 1 through 73a. I need to
figure out
how to limit the query to only those receipts. My feeling is that it
either
can't be done or it's going to get complicated, but I need to try.
Any
suggestions?

Thanks!

 

[Jerry Whittle]

The good old Val function comes to the rescue again. Don't worry about
sorting in the query. For a report you want to do the sorting and grouping at
the report level.

In the underlying query put another field something like:
SortOrder: Val([receipt data field name])

Then using the report sorting and grouping options, sort on that field. If
need be you might need that new SortOrder field on the report; however, you
can make it invisible.

OR if the receipt numbers are always numbers AND you don't need to see
things like leading zeros, just convert that field to a number field. Make a
backup of the table or even entire database first before trying this.

 

[Tara]

It worked beautifully! Thanks a million!

The good old Val function comes to the rescue again. Don't worry about
sorting in the query. For a report you want to do the sorting and grouping at
the report level.

In the underlying query put another field something like:
SortOrder: Val([receipt data field name])

Then using the report sorting and grouping options, sort on that field. If
need be you might need that new SortOrder field on the report; however, you
can make it invisible.

OR if the receipt numbers are always numbers AND you don't need to see
things like leading zeros, just convert that field to a number field. Make a
backup of the table or even entire database first before trying this.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you both for your suggestions. I just came up with another question
though. I have a report (and underlying query) that sort by receipt data.
Currently, the records are sorted in this type of order: 22, 220,
221.....228, 229, 23,231, etc, where those are the receipt *numbers*
(actually a text field) I would like it to sort data in this fashion: 1, 2,
3, 4, 5......15, 16, 17, etc. Any ideas on how to accomplish this?

Thanks again for your help so far!