search within a text field

[Guest]

I have a text field that I would like to seach and sort from a section of it.
The field looks like this:

First Order:10/13/1999
Number of Orders:86
Total Spent:3947.54
Last Order:2/8/2005

What I would like to do is find all the instances wher the "Total Spent:" is
greater than a certain amount. Is this possible?

 

[Rick B]

Ummmm. Why are you storing four different types of information in one
field? What is this field called?

You need to normalize your data.

If you have a table where you store each order and the dollar value, then
you can easily find the totals per customer and only pull records where the
total is over a certain amount. Do you have a transaction table of some
kind where the actual orders are stored?

 

[Guest]

I unfourtunatly inherited this problem. The source table is approx. 7000
entries so seperating is a bit of a task in itself. The field is called
"Notes"

 

[John Spencer]

You could try something like:

Field: TotalSpent: Val(MID([TheField], Instr(1,[TheField],"Total
Spent:")+1)))
Criteria: >= 1500

Problems with that are ther reliability of the data entry. Entries like
Totl Spent versus Total Spent will cause an error.

 

[Guest]

I asume [TheField] should be replaced with the field name? I am getting
operator without operand error on criteria?

You could try something like:

Field: TotalSpent: Val(MID([TheField], Instr(1,[TheField],"Total
Spent:")+1)))
Criteria: >= 1500

Problems with that are ther reliability of the data entry. Entries like
Totl Spent versus Total Spent will cause an error.

I have a text field that I would like to seach and sort from a section of
it.
The field looks like this:

First Order:10/13/1999
Number of Orders:86
Total Spent:3947.54
Last Order:2/8/2005

What I would like to do is find all the instances wher the "Total Spent:"
is
greater than a certain amount. Is this possible?

 

[John Spencer]

Dang it! I hate when I miscount parentheses.

One: Yes, you replace TheField with your field name.

Two: I should have added 13 to the result of the Instr calc and not 1 (The
length of "Total Spent:" plus 1) to start new string at the character after
"Total Spent:".

I had one too many closing parentheses on the calculated column.
Field: TotalSpent: Val(MID([TheField], Instr(1,[TheField],"Total
Spent:")+13))

This should work as long as the value of your field is not null. If the
field is null, this will generate an error - invalid use of null. IF that
is a problem for you then use [TheField] & "" in place of just [TheField].

I asume [TheField] should be replaced with the field name? I am getting
operator without operand error on criteria?

You could try something like:

Field: TotalSpent: Val(MID([TheField], Instr(1,[TheField],"Total
Spent:")+1)))
Criteria: >= 1500

Problems with that are ther reliability of the data entry. Entries like
Totl Spent versus Total Spent will cause an error.

I have a text field that I would like to seach and sort from a section
of
it.
The field looks like this:

First Order:10/13/1999
Number of Orders:86
Total Spent:3947.54
Last Order:2/8/2005

What I would like to do is find all the instances wher the "Total
Spent:"
is
greater than a certain amount. Is this possible?