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]

Hey Chris,

Thanks again for all of the assistance.

I am currently going to try the ODBC route, and I will probably try and write the parameters myself using ADO if that doesn't work. I will update the post as soon as I have tested these options.

Thanks for the time, have a great day.

Cheers,
Chris

 

[Fazza]

currently it is like

WHERE 1 and 2 or 3 and 2

please use either
WHERE (1 and 2) or (3 and 2)

or,
WHERE (1 or 3) AND 2

OK?

 

[Norie]

I think you are missing some parentheses.

This is your current criteria:

("TableName"."Assigned Group" Like '%CSC')
AND
("TableName"."Last Resolved Date">=? And "TableName"."Last Resolved Date"<=?)
OR
("TableName"."Assigned Group" Like 'COM%')
AND
("TableName"."Last Resolved Date">=? And "TableName"."Last Resolved Date"<=?)

which is basically saying this:

Exp1 AND Exp2 OR Exp3 AND Exp4

I think you might want this:

(Exp1 AND Exp2) OR (Exp3 AND Exp4)

 

[chardy]

I have attempted using both of your described SQL parameters and they both return results that are outside of the group criteria.

Even when I submit the SQL directly(using 0 graphical criteria - pure SQL) such as:

(Exp1 AND Exp2) OR (Exp3 AND Exp4)

MS query removes these parentheses and passes the parameters along as separate. I have tried changing them several times but with no such luck. I am attempting 1 or 2 other suggestions as we speak. I will send a message with an update asap.

Thanks again for the help everyone.

 

[Norie]

Is there anyway you can test the query outside of MS Query?

Have you tried the query with hard-coded values for the dates?

What in words is the criteria?

Is it this?

Return all records where the assigned group begins with COM or ends with CSC where the last resolved date is between start date and end date.

That's basically what Chris posted earlier, I posted the same but not as concise.

 

[chardy]

Norie,

I cannot run the query outside of MS Query because I do not have access. I have successfully run this query for 2 different worksheets using the exact same structure. However, the difference is that instead of having 2 LIKE criteria, I only use one. This is an example of these queries that I am using:

SELECT "TableName"."Submit Date", "TableName"."Incident Number", "TableName".Impact, "TableName"."Submit Date", "TableName"."Last Resolved Date", "TableName".Description, "TableName".Resolution, "TableName"."Closure Product Model/Version", "TableName"."Product Model/Version"
FROM "TableName" "TableName"
WHERE ("TableName"."Assigned Group" Like '%IPFR%') AND ("TableName"."Last Resolved Date">=? And "TableName"."Last Resolved Date"<=?)

I have used 2 of these queries already in 2 other worksheets in the same workbook. They work terrific and do exactly what I require from them.

When one of our users click's refresh on that specific table in excel, they are prompted to input the "starting date" and the "ending date" to determine which results will be pulled with an assigned group of '%IPFR%'.

As soon as I tried to implement 2 different LIKE parameters, it went haywire on the group parameters and began to yield results from multiple groups(randoms - practically all other groups).

There are 2 other applications which allow me to access the same data, however to do not pull directly into excel, and therefore cannot be automated. I also have not figured out a way to pass dynamic date parameters through these 2 interfaces, only static date windows.

The main purpose of this whole ordeal is that I am trying to alleviate my colleagues from having to manually pull this data every week and basically copy and paste it into a "metrics" tracking tool in excel.

But to answer your other questions, yes I have many queries that work with hard coded dates. Also, you are completely correct with the verbal description of my query parameters. Please let me know if I can clarify anything else and I will update ASAP.

 

[Norie]

Well it could be because of the 2 LIKES, thats one reason that I separated things out.

Have you tried this SQL with hard-coded dates?

 

[chardy]

Hello Norie,

I have just come out of a meeting and I must get on the road to make my long commute home. I will extensively test asap using static dates to obtain more information for you.

Appreciate the continued support!

Cheers,
Chris

 

[Norie]

Chris

Have you considered using something like ADO to get the data?

 

[Fazza]

Hi, Chris

It seems like the query is generally good except the parameters are not doing what you want. If staying with the query table approach, the next step up in controlling the result is to use VBA to explicity set the SQL. It should be simple enough - just assign the SQL (text) to the query table object, and refresh the query table. In Excel 2003, this is simply,

with your_worksheet_reference.querytables(1)
  .sql = "SELECT whatever FROM wherever WHERE your criteria"
  .refresh backgroundquery:=false
end with

With later versions the object model is different; I am unfamiliar; maybe

with your_worksheet_reference.listobjects.querytables(1)
  .sql = "SELECT whatever FROM wherever WHERE your criteria"
  .refresh backgroundquery:=false
end with

OK?

regards