AND OR PROBLEM 1

[TRACEYMARYLAND]

Hi i am doing this statement

SELECT * FROM FORM_VALUES
WHERE FORM_NAME = 'CUSTOMER_COMPLAINT.12.14.2004'
AND (FIELD_TYPE = 'A' OR FIELD_TYPE = 'D' OR FIELD_TYPE = 'N')

I am getting other values from the table that do not have FORM_NAME = 'CUSTOMER_COMPLAINT.12.14.2004'

I need to select where form_name = 'CUSTOMER_COMPLAINT.12.14.2004' and the field type is A or D or N

Please advise

Thanks

 

[SQLBill]

Why not try:

SELECT * FROM FORM_VALUES
WHERE FORM_NAME = 'CUSTOMER_COMPLAINT.12.14.2004'
AND FIELD_TYPE IN ('A', 'D', 'N')

-SQLBill

 

[TRACEYMARYLAND]

You start SQLBill i knew there must be a way.....
Thank you so much......

 

[SQLBill]

You are welcome and thanks for the star!

-SQLBill

 

[TRACEYMARYLAND]

Just a little thing
i tried to put it into my ASP.....but getting error

I had this before
Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FIELD_TYPE = '" & "N" & "' OR FIELD_TYPE = '" & "D" & "' OR FIELD_TYPE = '" & "A" & "' AND FORM_NAME = '" & request("FormName") & "'" ,"FIELDNAME","FIELDNAME", request("FieldName"))

When i try to put the IN part in i getting error...

Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FORM_NAME = '" & request("FormName") & "' AND FIELD_TYPE IN '" & "N" & "' , '" & "D" & "' , '" & "A" & "'" ,"FIELDNAME","FIELDNAME", request("FieldName"))


<%
sub CreateOption(strName,strTableOrSQL,strValue,strDisplay,strSelected)

'This subroutine expects the following parameters:
' strName is the name of option to be created
' strTableNameOrSQL the name of table
' strValue is the field from the recordset that holds the value to be stored;
'strDisplay is the field from the recordset that will be displayed in the drop down;
'if a default value is to be selected, that is passed as strSelected. strJavaScript holds
'any event and script to be fired with that event.

dim objRSOption, strIsSelected, strJavaScript
set objRSOption=Server.CreateObject("ADODB.RecordSet")
objRSOption.Open strTableOrSQL,dbConn

response.write("<Select Name = '" & strName & "'" & strJavaScript & ">")
response.write(vbCrLF & vbTab & vbTab & vbTab & vbTab & vbTab & "<Option Value = ''></Option>")

Do until objRSOption.eof
if Trim(UCase(objRSOption(strValue))) = Trim(UCase(strSelected)) then
strIsSelected = " selected"
else
strIsSelected = ""
end if
Response.write(vbCrLF & vbTab & vbTab & vbTab & vbTab & vbTab & "<Option" & strIsSelected & " Value ='" & objRSOption(strValue) & "'>" & objRSOption(strDisplay) & "</Option>")
objRSOption.MoveNext
Loop
Response.write(vbCrLF & vbTab & vbTab & vbTab & vbTab & "</Select>")

objRSOption.close
set objRSOption = Nothing

end sub
%>

Must be the quotes ?

But cannot figure it out

 

[Lothario]

you are missing the parenthesis...

should look like this...

FIELD_TYPE IN ()

-L

 

[ProdAir]

Maybe I missed it but you seem to be missing the parentheses () around the list.

 

[TRACEYMARYLAND]

Hmm i put in brackets
Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FORM_NAME = '" & request("FormName") & "' AND FIELD_TYPE IN '(" & "N" & "' , '" & "D" & "' , '" & "A" & ")' " ,"FIELDNAME","FIELDNAME", request("FieldName"))

 

[SQLBill]

Gotta ask....you are using a Microsoft SQL Server database for this, correct?

-SQLBill

Posting advice: FAQ481-4875

 

[Olaf Doschke]

Maybe remove those unneeded break ups of the string and you see what you did:

...AND FIELD_TYPE IN '(N' , 'D' , 'A)'

So you put the parntheses wrong. This should work:

Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FORM_NAME = '" & request("FormName") & "' AND  FIELD_TYPE IN ('" & "N" & "'  ,  '" & "D" & "'  , '" & "A" & "') "    ,"FIELDNAME","FIELDNAME", request("FieldName"))

Bye, Olaf.

 

[TRACEYMARYLAND]

Phew 4 hours later i got it...
it was the spacing.....between......
Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FORM_NAME = '" & request("FormName") & "' AND FIELD_TYPE IN ('" & "N" & "','" & "D" & "','" & "A" & "') " ,"FIELDNAME","FIELDNAME", request("FieldName"),"")

Had to close it up............

Thanks all..........

Must of the time i do calling to stored procedures....rather then messing with ASP SQL coding at the same time but it all adds to the joy of programming

Cheers

 

[Olaf Doschke]

Hi Tracey,

shouldn't this also do?

Call CreateOption("FieldName","select distinct FieldName from FORM_VALUES WHERE FORM_NAME = '" & request("FormName") & "' AND  FIELD_TYPE IN('N','D','A')", "FIELDNAME", "FIELDNAME", request("FieldName"), "")

Why did you seperate something like FIELD_TYPE IN ('N','D','A') into parts? That made "human parsing" so hard and I'd say the spacing doesn't really matter.

Bye, Olaf.

 

[TRACEYMARYLAND]

Oh i try the bit without the break up too that makes my life easier aswell

Cheers