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.
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. |
|
|
|
|
|
|
|