SQL Mid function doesn't seem to be working.

[MkIIISupra]

SELECT 
	CMPS_CDS_SITE_CALL_LOG.TRACKER, 
	CMPS_CDS_SITE_CALL_LOG.CALLDESC, 
	CMPS_CDS_SITE_CALL_LOG.RECVDDATE, 
	CMPS_CPM_ENROLLMENT_SITE.SITE_NAME, 
	CMPS_CPM_ENROLLMENT_SITE.SITE_NBR, 
	CMPS_CDS_SITE_CALL_JOURNAL.ENTRYTEXT, 
	CMPS_CDS_SITE_CALL_LOG.CALLSTATUS, 
	CMPS_CDS_SITE_CALL_LOG.SHORTDESC, 
	CMPS_CPM_PHASE.PHASE_NAME, 
	CMPS_CDS_SITE_CALL_LOG.CAUSE

FROM 
	CMPS_CDS_SITE_CALL_JOURNAL INNER JOIN CMPS_CDS_SITE_CALL_LOG ON 
	CMPS_CDS_SITE_CALL_JOURNAL.CALLID = CMPS_CDS_SITE_CALL_LOG.CALLID, 

	CMPS_CPM_ENROLLMENT_SITE INNER JOIN CMPS_CPM_PHASE ON 
	CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID = CMPS_CPM_PHASE.PHASE_SYS_ID

WHERE 
	(((CMPS_CPM_ENROLLMENT_SITE.SITE_NAME)="WI-Dept. of Public Instr.") 
	AND [B][COLOR=red](Mid([CMPS_CDS_SITE_CALL_JOURNAL]![ENTRYTEXT],1,19)="PROBLEM - CUSTOMER")[/color][/B]
	AND ((CMPS_CDS_SITE_CALL_LOG.CALLSTATUS)="OPEN") 
	AND ((CMPS_CPM_PHASE.PHASE_NAME)="WKCE-CRT FALL 2005") 
	AND ((CMPS_CDS_SITE_CALL_LOG.CAUSE)="ACCOUNTABILITY"));

I have tried several variations on this and it will not return a value, and I know there are at least 7 records
that have the search value in it. I am using Access 2002 (10.6501.6714) SP3 to query an Oracle 9i database.

Some of the variations are as follows:

(Mid([CMPS_CDS_SITE_CALL_JOURNAL]![ENTRYTEXT],9,12)=" - CUSTOMER")
(Left([CMPS_CDS_SITE_CALL_JOURNAL]![ENTRYTEXT],19)="PROBLEM - CUSTOMER")
(Mid([CMPS_CDS_SITE_CALL_JOURNAL]![ENTRYTEXT],1,)="PROBLEM - CUSTOMER")

And not one of them returns a darn thing! Any ideas? I need this query to drive a series of reports.

One by one the penguins steal my sanity!

 

[Golom]

Could it be because

Len("PROBLEM - CUSTOMER") = 18

?

 

[PHV]

You may try either this:
AND (Left([CMPS_CDS_SITE_CALL_JOURNAL].[ENTRYTEXT],18)="PROBLEM - CUSTOMER")
Or this:
AND (CMPS_CDS_SITE_CALL_JOURNAL.ENTRYTEXT Like "PROBLEM - CUSTOMER*")

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

 

[MkIIISupra]

PHV it worked very well! Thanks!

One by one the penguins steal my sanity!