MS QUERY - Issues using multiple LIKES with DATE parameters - Makes no sense.

[chardy]

Good Afternoon,

I am currently experiencing some issues while attempting to pull back data into excel from our Oracle DB.

Basically, I am looking at MS Query and I input 2 LIKE parameters using OR. (I am using the criteria fields as opposed to SQL because I need to input questions in the prompts that I will describe below). These parameters are as follows:

LIKE '%CSC' OR 'COM%'

This works completely fine. It pulls back all groups that end with "CSC" and all groups that start with "COM." I have also tried by separating the Likes into 2 separate fields to cover the "OR" portion of the sql. It would look like this in the criteria fields:

LIKE '%CSC'
LIKE 'COM%'

With that being said, as soon as I input my date parameters it completely overrides the LIKEs that I have used to filter the groups in the first column. My date parameters are as follows:

>=[First day of the business week? YYYY-MM-DD 00:00:00] AND <=[Last day of the business week? YYYY-MM-DD 23:59:59]

With this being a date field, I receive a prompt to enter these date parameters as soon as I refresh the query (which is normal, and exactly what I wanted). However, when the data is returned they query has completely overwritten the LIKE parameters for the "groups" and it returns results from every kind of group in the DB.

I am 100% sure this structure of parameter works because I am using it successfully when I only have one "LIKE" criteria. However, the second that I input a second "LIKE" and try to use the date parameter, it completely looks past the "LIKE' criteria.

Any thoughts?

Please let me know if you require more information. Happy to supply because this is an extremely frustrating experience. I also might add, that I have checked the SQL that these parameters input and it seems to be correct as far as I can see. I have also confirmed it with one of our DBAs.... Completely stumped on this one.

Thank you for your time everyone, this is my first post on the forum.

Regards,
Chris

 

[chardy]

Fazza,

Thank you very much for this idea. Sorry it took me so long to reply but I was working offsite over the weekend while travelling and I only had access to email through my phone.

I will be testing this method today and I will update the forum as soon as I have a definitive answer for you.

Regardless though, can't thank you all enough for the continued support in this thread. I will update ASAP.

I will also try and return the favours by helping out some others in various threads later today. Have a great day everyone and thanks again.

Regards,
Chris

 

[rickolh]

Fazza,

Thank you very much for this idea. Sorry it took me so long to reply but I was working offsite over the weekend while travelling and I only had access to email through my phone.

I will be testing this method today and I will update the forum as soon as I have a definitive answer for you.

Regardless though, can't thank you all enough for the continued support in this thread. I will update ASAP.

I will also try and return the favours by helping out some others in various threads later today. Have a great day everyone and thanks again.

Regards,
Chris

Hi,

I know this is an old post but I'm having the exact same problem as you using multiple Likes with Date parameters.

for example,
If I use
Group=(A OR B) AND Date<=C
it will returns everything that meet the criteria of C but totally ignore (A OR B).

If I use
(Group=A AND date<=C) OR (Group=B AND date<=C)
it will gives an error instead citing invalid query.

Wondering if you have been able to to find an answer for this?

Cheers,
Rick