SQL WHERE clause in EXCEL - HELP!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL WHERE clause in EXCEL - HELP!!

Author

Topic

GaryEL
Starting Member

5 Posts

Posted - 2013-07-29 : 11:36:46

Still having a problem with my SQL WHERE clause…

I have three variables on an Excel form that connects to a Teradata database. The first variable is a date format (DateworkedF and DateworkedT) the other two are text fields. (StatusX and ErrorTypeX)

I want to be able to search on any or all of these fields. (If the field is blank return all values)

Can’t figure out the syntax ???

[code]
Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _
"FROM UD402.JD_MCP_MASTER WHERE " & _
"(DATE_WORKED >= #" & DateworkedF & "# Or #" & DateworkedF & "# IS NULL)" & _
"AND (DATE_WORKED <= #" & DateworkedT & "# Or #" & DateworkedT & "# IS NULL)" & _
"AND (STATUS = '" & StatusX & "' OR '" & StatusX & "' IS NULL)" & _
"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL);"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-29 : 12:21:17

Brackets # are not date separators in SQL Server. Date separators in SQL Server are ', just as strings.

Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _
"FROM UD402.JD_MCP_MASTER WHERE " & _
"(DATE_WORKED >= '" & DateworkedF & "' Or '" & DateworkedF & "' IS NULL)" & _
"AND (DATE_WORKED <= '" & DateworkedT & "' Or '" & DateworkedT & "' IS NULL)" & _
"AND (STATUS = '" & StatusX & "' OR '" & StatusX & "' IS NULL)" & _
"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL);"

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

GaryEL
Starting Member

5 Posts

Posted - 2013-07-29 : 12:49:00

Still not working correctly.....
Unable to use IS NULL if i don't want to filter on that field.
And also having a problem with from date and to date (DateworkedF and DateworkedT)????

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 12:52:55

quote:
Originally posted by GaryEL

Still not working correctly.....
Unable to use IS NULL if i don't want to filter on that field.
And also having a problem with from date and to date (DateworkedF and DateworkedT)????

This depends on what is in your DateWorkedF and DateWorkedT text boxes, and what gets sent to the server. If you have a blank in the DateWorkedF text box, does it send a null to the database, or does it send an empty string? If it sends anull, the query Swepeso posted should work. If it is sending an empty string, try the query I posted in the other thread where you had asked the question http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187167

GaryEL
Starting Member

5 Posts

Posted - 2013-07-29 : 13:32:16

Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _
"FROM UD402.JD_MCP_MASTER WHERE " & _
"(DATE_WORKED >= '" & DateworkedF & "' AND DATE_WORKED <= '" & DateworkedT & "' )" & _
"AND (STATUS = '" & StatusX & "' or '" & StatusX & "' IS NULL) " & _
"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL );"

OK….. This is what I’m doing:
On opening the form I set all fields to NULL.

I then enter the following values into the form fields:

DateworkedF = “1/1/2013”
DateworkedT = 1/30/2013”
Error_TypeX = “CBR”
StatusX = “Reported”

Click the button to run the code….
And get the following message:

Run-time error….
A Character string failed conversion to a numeric value.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 15:22:08

quote:
Originally posted by GaryEL

Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _
"FROM UD402.JD_MCP_MASTER WHERE " & _
"(DATE_WORKED >= '" & DateworkedF & "' AND DATE_WORKED <= '" & DateworkedT & "' )" & _
"AND (STATUS = '" & StatusX & "' or '" & StatusX & "' IS NULL) " & _
"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL );"

OK….. This is what I’m doing:
On opening the form I set all fields to NULL.

I then enter the following values into the form fields:

DateworkedF = “1/1/2013”
DateworkedT = 1/30/2013”
Error_TypeX = “CBR”
StatusX = “Reported”

Click the button to run the code….
And get the following message:

Run-time error….
A Character string failed conversion to a numeric value.

The fact that one of the dates is in quotes and the other is not in quotes is giving me pause. That could be the problem, but I don't know.

The easiest way to debug this is to run your VB code, put a break point immediately after it constructs the Query string, copy that string from text visualizer and run it from a SQL Server Management Studio Query window. If it gives you an error (and I am expecting that it will), you need to figure out why it is giving an error. If you need help with figuring out what needs to be changed, post that string.

Also, I am expecting that the data types of DateWoredF and DateWorkedT are datetime or date (or one of the other datetime types), Error_TypeX and StatusX are character types. IF that is not the case, that would be the cause of the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources