easy fix. Need to add OR IS NULL

easy fix. Need to add OR IS NULL (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

travismp -> easy fix. Need to add OR IS NULL (5/6/2008 12:06:07)
AND [::Operator::]=Yes How can I change this to add operator = YES or IsNull I thought this would do it but it does not AND ([::Operator::]=Yes OR IsNull(Operator)) what am I missing? It is a drop down box on a search form if it matters.

BeTheBall -> RE: easy fix. Need to add OR IS NULL (5/6/2008 12:32:32)
Try this: AND ([::Operator::]='Yes' OR [::Operator::]='')

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 12:45:37)
Nope. Database Results Error Description: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Number: -2147217913 (0x80040E07) Source: Microsoft OLE DB Provider for ODBC Drivers One or more form fields were empty. You should provide default values for all form fields that are used in the query.

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 12:48:20)

Here is my full line of code if it helps.

fp_sQry="SELECT * FROM tbl_MAIN WHERE (Contractors LIKE '::Contractors::%' AND City LIKE '%::City::%' AND State LIKE '%::State::%' And (PlanStatus LIKE '%::PlanStatus::%' OR IsNull(PlanStatus)) And (PlanStatus_SeaRiver LIKE '%::PlanStatus_SeaRiver::%' OR IsNull(PlanStatus_SeaRiver)) AND ([::Operator::]='Yes' OR [::Operator::]='')) ORDER BY Contractors ASC"

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 12:54:47)
Also could it be something with my drop down as well? I have "Select One" as my top drop down with no value selected.

BeTheBall -> RE: easy fix. Need to add OR IS NULL (5/6/2008 14:06:09)
Do you have a link? What does the full search form look like?

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 14:10:19)
No it is behind a secure log in section. I can PM you the entire code... would that work or would that not work?

BeTheBall -> RE: easy fix. Need to add OR IS NULL (5/6/2008 15:37:55)
Explain a bit more about the field Operator. It is the name of the dropdown, not a field in the database?

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 15:42:01)
Operator is the name of the drop down. There are currently 12 selections. Those 12 come from my DB. This allows them to search the different divisions for a more detailed view. I added a 13th option called "View All" which has a null value so if they want to search for all locations then they can just leave that as the default. Operator is the name of the drop down control.

Spooky -> RE: easy fix. Need to add OR IS NULL (5/6/2008 16:25:53)
But "view all' cant be null? The 'operator' drop down must supply a value as this is required as a column name in the SQL string. To do a view all, you would need to eliminate that part of the search from the criteria if "view all" was selected

travismp -> RE: easy fix. Need to add OR IS NULL (5/6/2008 16:42:16)
OK. I wanted to add an option to "View All" and have no value assigned to it. So if they selected that it would technically be a null value. Sounds like my logic cannot be done. I guess my users will HAVE to select a division to see the results then. Thanks.

DesiMcK -> RE: easy fix. Need to add OR IS NULL (5/6/2008 17:28:38)
Can you not have a several SQL statement for the View All scenario. So rather than have fp_sQry="SELECT * FROM tbl_MAIN WHERE (Contractors LIKE '::Contractors::%' AND City LIKE '%::City::%' AND State LIKE '%::State::%' And (PlanStatus LIKE '%::PlanStatus::%' OR IsNull(PlanStatus)) And (PlanStatus_SeaRiver LIKE '%::PlanStatus_SeaRiver::%' OR IsNull(PlanStatus_SeaRiver)) AND ([::Operator::]='Yes' OR [::Operator::]='')) ORDER BY Contractors ASC" Try something like if Request("PlanStatus") = 'All' Then fp_sQry="SELECT * FROM tbl_MAIN WHERE (Contractors LIKE '::Contractors::%' AND City LIKE '%::City::%' AND State LIKE '%::State::%') And (PlanStatus_SeaRiver LIKE '%::PlanStatus_SeaRiver::%' OR IsNull(PlanStatus_SeaRiver)) AND ([::Operator::]='Yes' OR [::Operator::]='')) ORDER BY Contractors ASC" End If This why you can customise the sql WHERE clause. Does this help? Desi

Page: [1]

4.589844E-02