Calculated Text Field

[accessmatt]

Can anyone provide some guidance about the best way to create a calculated field based on a text field. In other words how can I create a field that SUMs records where the text content of another field CONTAINS some text.

[June7]

What is the structure of the data? Are the alpha characters always the same length and in the same position? Provide examples.

[accessmatt]

Hi June, it's variable length and format but I want to count where the field always contains "text" for instance.

[June7]

You want to Count, not Sum? Try:

SELECT Count("*") FROM table WHERE [textfield] LIKE "*text*";

or

SELECT *, (SELECT Count("*") FROM table WHERE [textfield] LIKE "*text*") AS Cnt FROM table WHERE [textfield] LIKE "*text*";

or

SELECT *, DCount("*", "table", "textfield LIKE '*text*'") As Cnt FROM table WHERE [textfield] LIKE "*text*";

"text" is some input by user. If you want to extract "text" from the value and do aggregate (GROUP BY) count calc, that requires a consistent structure (length, position), otherwise gets very difficult.