Query/Update what I selected

[vtaurusv]

This will be a noob question but I cant find anything online, maybe Im searching the wrong criteria

So basically I am trying isolate data in a field and updating it to that selection.

Example, there is a sentence in the field where someone has typed on what a student owes. (Im accessing a student comment table and trying to create a mail merge using data within it.)

Sentence: Student owes $12.00 and is due by 12/12/14.



What I want to do, is only isolate the $12.00 and create that in a new field or table or just delete everything before and after it.

Appreciate any assistance!

[June7]

That will be very difficult if not impossible. Depends on text having a consistent structure. Will there always be a $ sign and only 1?

The student owe amount should be determined by querying data.

[vtaurusv]

Hi June, thanks for the quick response! I can inform the staff to use consistent data entry. I was thinking they can enter the amount like.... Amount12.00 or Dollar12.00.

[June7]

Why is this necessary? Sounds like poor database design. Depending on users to always enter by prescribed pattern is certain to fail. The $ sign would be easy to work with. Whichever pattern you decide on is not important, consistency is.

Val(Mid([fieldname], InStr([fieldname],"$")+1))

[vtaurusv]

Thanks! I will try that out. Wasn't sure the $ would cause an issue. This will help tremendously. Thank you!

[June7]

Note that the expression will fail if there is nothing in the field. Try this modification:

Val(Mid(Nz([fieldname],""), InStr(Nz([fieldname],""),"$")+1))

It will return 0 if the field is Null.

[vtaurusv]

This worked great. I talked to the staff and they forget to mention that there also could be another amount owed for another item.

Example: Student owes $12.00 and is due by 12/12/14. Also owes $40.00 for misc items.

So I thought, well maybe we could either use this format $$40.00 or &40.00 for the second item.

Is there a string similar to Val(Mid(Nz([fieldname],""), InStr(Nz([fieldname],""),"$")+1)) were it is looking at $$ or & instead where I can create another column? Thank you!

[vtaurusv]

Nevermind! got it!
Val(Mid(Nz([fieldname],""), InStr(Nz([fieldname],""),"$")+2))

[vtaurusv]

Opps

Val(Mid(Nz([fieldname],""), InStr(Nz([fieldname],""),"$$")+2))

[June7]

What about 3rd or 4th or nth occurrences of monetary info?

[vtaurusv]

I hinted to them if that happens they are "SOL" . I told them the should rethink their business practices.