Trim Left Totals Group By problem 2

[wvmbark]

I'm using the following expression to trim text preceeding "TECH REC" in a Text field:

Left([ALERTtxt]![Description],InStr([ALERTtxt]![Description],"TECH REC")-1)

I'd like to use this field with Totals selected - Group By, First, etc. but I get errors with each. If I use Expression it says it's not part of an aggregate function. With Group By, First, etc. it says Invalid Procedure Call.

Could someone help me out with this?

Thanks!

Mike

 

[lespaul]

what's the entire SQL of the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[wvmbark]

SELECT ALERTtxt.[A/C] AS Tail, ALERTtxt.Alert, ALERTtxt.ATA, ALERTtxt.Status, Left(ALERTtxt!Description,InStr(ALERTtxt!Description,"TECH REC")-1) AS [Desc]
FROM (ALERTtxt INNER JOIN [Alert tbl] ON ALERTtxt.Alert = [Alert tbl].Alert) INNER JOIN [Alert compliance tbl] ON [Alert tbl].Alert = [Alert compliance tbl].ALERTNO
GROUP BY ALERTtxt.[A/C], ALERTtxt.Alert, ALERTtxt.ATA, ALERTtxt.Status, Left(ALERTtxt!Description,InStr(ALERTtxt!Description,"TECH REC")-1);

 

[wvmbark]

ok, now what??

 

[dhookom]

Does every value of Description field contain TECH REC?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wvmbark]

That's it! Every record should contain TECH REC, but it could be miss-keyed (as it was in this case). Would I use an IIf function to remedy this? If so, could you show me how I'd set up the evaluate part of the expression? If it doesn't contain TECH REC, then I want to leave as is.

Thanks!!

Mike

 

[dhookom]

This expression will error

Left(ALERTtxt!Description,InStr(ALERTtxt!Description,"TECH REC")-1)

You need to either filter out the records without the TECH REC or add an IIf() to not subtract the 1 if the value is not present.

Try it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

You may try something like this:
SELECT T.[A/C] AS Tail, T.Alert, T.ATA, T.Status
, IIf(InStr(T.Description,'TECH REC')>0,Left(T.Description,InStr(T.Description,'TECH REC')-1),T.Description) AS [Desc]
FROM (ALERTtxt As T
INNER JOIN [Alert tbl] As A ON T.Alert = A.Alert)
INNER JOIN [Alert compliance tbl] As C ON A.Alert = C.ALERTNO
GROUP BY T.[A/C] AS Tail, T.Alert, T.ATA, T.Status
, IIf(InStr(T.Description,'TECH REC')>0,Left(T.Description,InStr(T.Description,'TECH REC')-1),T.Description);

Anyway, why an aggregate query without aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wvmbark]

PH,

"why an aggregate query without aggregate function ?" - I was eliminating fields from the query to determine where my problem was. The aggregate function is actually being used to capture the most current ActionDate from a details table...

SELECT ALERTtxt.[A/C] AS Tail, ALERTtxt.Alert, ALERTtxt.ATA, ALERTtxt.Open, Max([Alert compliance tbl].[ACTION DATE]) AS LastActionDate, ALERTtxt.Status, [Alert tbl].Description
FROM (ALERTtxt INNER JOIN [Alert tbl] ON ALERTtxt.Alert = [Alert tbl].Alert) INNER JOIN [Alert compliance tbl] ON [Alert tbl].Alert = [Alert compliance tbl].ALERTNO
GROUP BY ALERTtxt.[A/C], ALERTtxt.Alert, ALERTtxt.ATA, ALERTtxt.Open, ALERTtxt.Status, [Alert tbl].Description;

Your expression works great. Thanks!!

Mike