Using table to build query not working as designed 1

[MkIIISupra]

Hello all!

I have an Access DB that is using several Oracle tables to query against for some in-house reports. The table that I am using as a criteria selector has 3 non-changeable (data content cannot be modified by the user) drop menus that are used to allow the user the ability to select 3 items for their contract.

tblContractSelction
SITE_NAME : drop menu / VISIBLE TO USER
PHASE_NAME : drop menu / VISIBLE TO USER
ENTRYTEXT : drop menu / VISIBLE TO USER
ENTRYTEXT2 : Populated by case statement that is driven by ENTRYTEXT / NOT VISIBLE TO USER

How the form works is the user will select each item needed as follows:

Select SITE_NAME from SITE_NAME menu – identifies the contract site needed
Select PHASE_NAME from PHASE_NAME menu – identifies the appropriate phase of the selected SITE_NAME
Select ENTRYTEXT from ENTRYTEXT menu – afterUpdate will take selection and create a Like statement to be used in the query below.

Once all 3 criteria are selected then a function button executes the query which is tied to this table for variable input. Problem is I cannot seem to get the Like statement to work. What I am trying to do is a first line Like match on the FIELD ENTRYTEXT in the Oracle DB, but I get a NULL return with all the combinations I have tried. The area that is bolded in the SQL code below is the area I am having an issue with. I have tried several combinations and none have resulted in a return. Below the code are some examples of the other code I have tried so far.

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_CPM_ENROLLMENT_SITE INNER JOIN CMPS_CPM_PHASE ON CMPS_CPM_ENROLLMENT_SITE.PHASE_SYS_ID = CMPS_CPM_PHASE.PHASE_SYS_ID,
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) ON tblContractSelector.CALLSTATUS = CMPS_CDS_SITE_CALL_LOG.CALLSTATUS

WHERE 
(((CMPS_CPM_ENROLLMENT_SITE.SITE_NAME)=[tblContractSelector]![SITE_NAME]) AND 
((CMPS_CDS_SITE_CALL_JOURNAL.ENTRYTEXT) [B]Like [tblContractSelector]![ENTRYTEXT2][/B]) AND ((CMPS_CDS_SITE_CALL_LOG.CALLSTATUS)="OPEN") AND 
((CMPS_CPM_PHASE.PHASE_NAME)=[tblContractSelector]![PHASE_NAME]) AND 
((CMPS_CDS_SITE_CALL_LOG.CAUSE)="ACCOUNTABILITY"));

Example:
Like “[tblContractSelector]![ENTRYTEXT2]*”
Like “*[tblContractSelector]![ENTRYTEXT2]”
Like “*[tblContractSelector]![ENTRYTEXT2]*”

So how do I set up an Access query using a table to give selection criteria that can be used with a Like switch?

One by one the penguins steal my sanity!

 

[PHV]

And what is the value of [tblContractSelector]![ENTRYTEXT2] ?

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

 

[dhookom]

Try:
Like "*" & [tblContractSelector]![ENTRYTEXT2] & "*"

This is if you are using Access and not a pass-through query.

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]

 

[Remou]

Would this have anything to do with % as wildcard?

 

[MkIIISupra]

And what is the value of [tblContractSelector]![ENTRYTEXT2] ? the value = “PRO* - *”

If I use the query with this --> “PRO* - *” value then I get 36 returns, as I expect but when I use the field I get nothing and I get no errors.

One by one the penguins steal my sanity!

 

[MkIIISupra]

Would this have anything to do with % as wildcard? - Yes in Oracle % is a wild card that can be used with SQL. I need the FIELD to have the Access equivilant of a wild card which is *.

ENTRYTEXT has a full name or qualifier that the user is expecting, ENTRYTEXT2 will be loaded with the "Wild Card" variant of ENTRYTEXT so as to account for possible spelling errors. If I hard code the queries they work, but I have 20 contracts that would each need 28 separate queries to account for the variations allowed. I can do it but time to write, test and validate each one is not what I have redily available.

So I am hoping to simplify the process by having one query that can accomodate the multiple combinations / wild cards all with a simple form that when all criteria are met the appropriate report is generated.

One by one the penguins steal my sanity!

 

[PHV]

Get rid of the quotes in ENTRYTEXT2

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

 

[MkIIISupra]

Get rid of the quotes in ENTRYTEXT2

Okay so far so good! Thank you! I will try some of the other 28 combinations and let you all know either way!!!

One by one the penguins steal my sanity!

 

[MkIIISupra]

Happy to report that all variations are working fine! Again thank you!!!

One by one the penguins steal my sanity!