SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL WHERE clause in EXCEL - HELP!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryEL
Starting Member

USA
5 Posts

Posted - 07/29/2013 :  11:36:46  Show Profile  Reply with Quote
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);"

Edited by - GaryEL on 07/31/2013 11:36:43

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 07/29/2013 :  12:21:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

GaryEL
Starting Member

USA
5 Posts

Posted - 07/29/2013 :  12:49:00  Show Profile  Reply with Quote
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)????
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 07/29/2013 :  12:52:55  Show Profile  Reply with Quote
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
Go to Top of Page

GaryEL
Starting Member

USA
5 Posts

Posted - 07/29/2013 :  13:32:16  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 07/29/2013 :  15:22:08  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000