|
| |
|
|
travismp
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
 |
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
Posts: 6271
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: easy fix. Need to add OR IS NULL - 5/6/2008 12:32:32
Try this:
AND ([::Operator::]='Yes' OR [::Operator::]='')
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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
Posts: 6271
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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
Posts: 6271
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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.
| |
|
|
|
travismp
Posts: 215
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
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
Posts: 407
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 