What is wrong with this query - using "like"? 1

[gwoman]

Hey There ...

This is my select query:

SELECT ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_ID, ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME, ETLCORE_CIF_OBJT_CTNR.CRE_ID, ETLCORE_CIF_OBJT_CTNR.CRE_DTTM, ETLCORE_CIF_OBJT_CTNR.LST_MDFD_ID, ETLCORE_CIF_OBJT_CTNR.LST_MDFD_DTTM, ETLCORE_CIF_OBJT_CTNR.DEL_INDC
FROM ETLCORE_CIF_OBJT_CTNR
WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like "Forms!250_Object_Container_frm.txtCIF_OBJT_CTNR%"));

The results are no records. I have narrowed it down to my WHERE clause and have tried the follow scenarios:

WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like "Forms![250_Object_Container_frm].[txtCIF_OBJT_CTN]%"))

Results in no records

WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like "Forms![250_Object_Container_frm].[txtCIF_OBJT_CTN]"))

This gives me the 1 record that matches the value of txtCIF_OBJT_CTN (which in this particular case is "NEW"


WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like "NEW%"))

This last WHERE clause gives me the correct set of records... all the records starting with "NEW"

My question is this: Why doesn't the first or second WHERE clause work if technically they are the same value???

Thanks much!

gwoman

 

[dhookom]

You shouldn't have the reference to "Forms!250_Object_Container_frm.txtCIF_OBJT_CTNR%" inside quotes. The SQL view should look like:
SELECT CIF_OBJT_CTNR_ID, CIF_OBJT_CTNR_NME, CRE_ID, CRE_DTTM, LST_MDFD_ID, LST_MDFD_DTTM, DEL_INDC
FROM ETLCORE_CIF_OBJT_CTNR
WHERE CIF_OBJT_CTNR_NME Like Forms!250_Object_Container_frm.txtCIF_OBJT_CTNR & "%";

This assumes that you want to use "%" as a wild card. In Access, we generally use "*".

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]

 

[gwoman]

Thanks!

Yes ... I was using "%" as a wildcard ... I tried both ways ... using "%" and "*"... and I removed the quotes from the form refernce. I got a syntax error ... indicaitng that I had a missing operator.

gwoman

 

[dhookom]

If you are using Access and this is the sql view of a query, try:
SELECT ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_ID, ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME, ETLCORE_CIF_OBJT_CTNR.CRE_ID, ETLCORE_CIF_OBJT_CTNR.CRE_DTTM, ETLCORE_CIF_OBJT_CTNR.LST_MDFD_ID, ETLCORE_CIF_OBJT_CTNR.LST_MDFD_DTTM, ETLCORE_CIF_OBJT_CTNR.DEL_INDC
FROM ETLCORE_CIF_OBJT_CTNR
WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like Forms!250_Object_Container_frm.txtCIF_OBJT_CTNR & "*"));

If you are setting this in code or somewhere else then let us know.

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]

 

[gwoman]

Thanks ... using the current SQL statement in Access I get all records... I have also tried to set it in code behind a button click event which was to no avail.
I fully welcome any other suggestions you may have!
Thanks ... gwoman

 

[dhookom]

What's wrong with getting all records? Did you have a value in the text box on the form? If not, you might get all records.

If you attempt to use this sql in code, you might need to replace the double-quotes with single. We would need to see your code to be sure.

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]

 

[gwoman]

My intention was to capture the text that had been typed in the text box 250_Object_Container_frm.txtCIF_OBJT_CTNR_NME ... and select all the records that started with that text. My example above indicated that I had tested the query using the actual text (i.e. "NEW*") and got the result I expected. However when 250_Object_Container_frm.txtCIF_OBJT_CTNR_NME was inserted in place of the actual text is when the results were wrong.
Thanks!

 

[gwoman]

Thanks dhookom ...

I tried your where clause again ...

WHERE (((ETLCORE_CIF_OBJT_CTNR.CIF_OBJT_CTNR_NME) Like Forms!250_Object_Container_frm.txtCIF_OBJT_CTNR_NME & "*"));

and it worked ... not sure what happened the first time!

Thanks ... have a good one!

gwoman