SQL Type mismatch error

[MkIIISupra]

Working environment:
System WinXP Pro 5.1.2600 Service Pack 1 Build 2600
Access 2002 (10.6501.6714) SP3
Oracle 9i

I am getting a Data Mismatch error and I have identified where. But searching these forums as well as the MS
Access help files has not rendered the answer yet.

Below is the SQL I am working with. The areas that are in BOLD are the data types for each field, so
you can see them as well. Below the main SQL code are the two areas that I think are causing the problem. I
cannot figure out how to change TYPE so I can execute the query below. This is a functioning query within an
Oracle 9i and Crystal Reports configuration.

Because of the cost of Crystal Reports licensing, I have been tasked to create an Access report system to do
all but the most advanced functions of Crystal... this type mismatch is just the latest hiccup in my process!

SELECT 
	CMPS_CDS_SITE_CALL_LOG.CUSTTYPE, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.TRACKER, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.CALLSTATUS, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.SHORTDESC, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.CLOSEDDATE, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.CAUSE, [B]TEXT[/B]
	CMPS_CDS_SITE_CALL_LOG.CALLDESC, [B]MEMO[/B]
	CMPS_CDS_SITE_CALL_LOG.RECVDDATE, [B]TEXT[/B]
	CMPS_CPM_ENROLLMENT_SITE.SITE_NAME, [B]TEXT[/B]
	CMPS_CPM_ENROLLMENT_SITE.SITE_NBR [B]TEXT[/B]

FROM 
	tblContractSelector INNER JOIN 
	(((CMPS_CDS_SITE_CALL_JOURNAL INNER JOIN 
	CMPS_CDS_SITE_CALL_LOG ON 
	CMPS_CDS_SITE_CALL_JOURNAL.CALLID {[B]TEXT[/B]} = CMPS_CDS_SITE_CALL_LOG.CALLID {[B]TEXT[/B]}) INNER JOIN 
	CMPS_CPM_ENROLLMENT_SITE ON 
	CMPS_CDS_SITE_CALL_LOG.CUSTID {[B]TEXT[/B]}= CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID {[B]NUMBER[/B]}) INNER JOIN 
	(CMPS_CPM_CONTRACT INNER JOIN 
	CMPS_CPM_PHASE ON CMPS_CPM_CONTRACT.CNTR_SYS_ID {[B]NUMBER[/B]} = CMPS_CPM_PHASE.CNTR_SYS_ID {[B]NUMBER[/B]}) ON 
	CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID {[B]NUMBER[/B]} = CMPS_CPM_PHASE.PHASE_SYS_ID {[B]NUMBER[/B]}) ON 
	tblContractSelector.PHASE_SYS_ID = CMPS_CPM_PHASE.PHASE_SYS_ID

WHERE 
	(((CMPS_CDS_SITE_CALL_LOG.CUSTID {[B]TEXT[/B]})=[CMPS_ENROLLMENT_SITE]![SITE_SYS_ID] {[B]NUMBER[/B]}) AND 
	((CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID {[B]NUMBER[/B]})=[CMPS_CPM_PHASE]![PHASE_SYS_ID] {[B]NUMBER[/B]}) AND 
	((CMPS_CPM_PHASE.CNTR_SYS_ID)=[CMPS_CPM_CONTRACT]![CNTR_SYS_ID]) AND 
	((CMPS_CDS_SITE_CALL_LOG.CUSTTYPE)="COMPASS") AND 
	((CMPS_CPM_CONTRACT.CNTR_NAME)="WISCONSIN") AND 
	((CMPS_CPM_PHASE.PHASE_NAME)="WKCE-CRT FALL 2005") AND 
	((CMPS_CDS_SITE_CALL_LOG.CAUSE)="ACCOUNTABILITY") AND 
	((CMPS_CDS_SITE_CALL_LOG.CALLDESC) Like [tblContractSelector]![ENTRYTEXT2]));

Specific areas of issue:

Line 5 of FROM statement
CMPS_CPM_ENROLLMENT_SITE ON CMPS_CDS_SITE_CALL_LOG.CUSTID {TEXT}= CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID {NUMBER})

Line 1 of WHERE statement
(((CMPS_CDS_SITE_CALL_LOG.CUSTID {TEXT})=[CMPS_ENROLLMENT_SITE]![SITE_SYS_ID] {NUMBER})

Hope this is enough information! Thanks again in advance.

One by one the penguins steal my sanity!

 

[MkIIISupra]

Okay I found something but it's still not working right... the two areas in RED are
what I found, this seems to have taken care of the Type Mismatch but now an input box is created with
the following information is displayed:

Enter Parameter Value
CMPS_ENROLLMENT_SITE!SITE_ID

No matter what I do, leave it blank or enter a value I get no return but I also get no errors.

SELECT
CMPS_CDS_SITE_CALL_LOG.CUSTTYPE,
CMPS_CDS_SITE_CALL_LOG.TRACKER,
CMPS_CDS_SITE_CALL_LOG.CALLSTATUS,
CMPS_CDS_SITE_CALL_LOG.SHORTDESC,
CMPS_CDS_SITE_CALL_LOG.CLOSEDDATE,
CMPS_CDS_SITE_CALL_LOG.CAUSE,
CMPS_CDS_SITE_CALL_LOG.CALLDESC,
CMPS_CDS_SITE_CALL_LOG.RECVDDATE,
CMPS_CPM_ENROLLMENT_SITE.SITE_NAME,
CMPS_CPM_ENROLLMENT_SITE.SITE_NBR

FROM
tblContractSelector INNER JOIN
(((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) INNER JOIN
CMPS_CPM_ENROLLMENT_SITE ON
CMPS_CDS_SITE_CALL_LOG.CUSTID = STR(CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID)) INNER JOIN
(CMPS_CPM_CONTRACT INNER JOIN CMPS_CPM_PHASE ON
CMPS_CPM_CONTRACT.CNTR_SYS_ID = CMPS_CPM_PHASE.CNTR_SYS_ID) ON
CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID = CMPS_CPM_PHASE.PHASE_SYS_ID) ON
tblContractSelector.PHASE_SYS_ID = CMPS_CPM_PHASE.PHASE_SYS_ID

WHERE
(((CMPS_CDS_SITE_CALL_LOG.CUSTTYPE)="COMPASS") AND
((CMPS_CDS_SITE_CALL_LOG.CAUSE)="ACCOUNTABILITY") AND
((CMPS_CDS_SITE_CALL_LOG.CALLDESC) Like [tblContractSelector]![ENTRYTEXT2]) AND
((CMPS_CDS_SITE_CALL_LOG.CUSTID)= STR([CMPS_ENROLLMENT_SITE]![SITE_SYS_ID])) AND
((CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID)=[CMPS_CPM_PHASE]![PHASE_SYS_ID]) AND
((CMPS_CPM_PHASE.CNTR_SYS_ID)=[CMPS_CPM_CONTRACT]![CNTR_SYS_ID]) AND
((CMPS_CPM_CONTRACT.CNTR_NAME)="WISCONSIN") AND
((CMPS_CPM_PHASE.PHASE_NAME)="WKCE-CRT FALL 2005"));

One by one the penguins steal my sanity!

 

[MkIIISupra]

New error...

Microsoft Access can't represent the join expression
CMPS_CDS_SITE_CALL_LOG.CUSTID=STR(CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID)
in Design view.

* One or more fields may have been deleted or renamed.
* The name of one or more fields or tables specified on the join expression may be misspelled.
* The join may use an operator that isn't supported in Design view, such as > or <.

I am at a total loss, nothing is working and I am stumped. The query I requested help with yesterday works, sorta but it's not pulling the correct information and it's missing several records that meet the search criteria.

One by one the penguins steal my sanity!

 

[lespaul]

something like this doesn't work:

CMPS_CPM_ENROLLMENT_SITE ON CMPS_CDS_SITE_CALL_LOG.CUSTID = char(CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID)

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

 

[PHV]

Perhaps something like this ?
CMPS_CPM_ENROLLMENT_SITE ON CMPS_CDS_SITE_CALL_LOG.CUSTID = CMPS_CPM_ENROLLMENT_SITE.SITE_SYS_ID & "")

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