still that darn SQL

[mission2java_78]

I posted this a while back and its still getting me ....
I have a table of tasks with several fields. In this table i have two particular fields : TaskSource, and InsertBeforeTask.

I want to sql all the records BUT I dont want any record with
TaskSource="N\A"
InsertBeforeTask=0

Meaning If I had a task where one of these conditions was true but the other was false I'd still want it. But if these conditions are true for both I dont want it...
Ive tried combinations of ands and ors with no luck.

Can anyone give me a hand:

"SELECT * FROM tblTasks WHERE RecordID= " & recordID & " AND TaskOrder >= " & taskOrder _
& " _______________ ORDER BY TaskOrder DESC"

I am stuck _____fill in the blanks_____________ at that portion. I tried AND but it didnt work.

Anyone with ideas?

Jon

 

[scottfarcus]

I'm sure there is a more proper way to do this, but this should work as a temporary solution.

Create an expression in the query "Combine" which combines TaskSource and InsertBeforeTask. Then use that expression to filter your results.

SELECT tblTask.TaskNo, tblTask.TaskSource, tblTask.InsertBeforeTask, [TaskSource] & [InsertBeforeTask] AS Combine
FROM tblTask
WHERE ((([TaskSource] & [InsertBeforeTask])<>"N/A0"));


Hope this helps.

 

[mission2java_78]

Query combine? See I really dont want to use a query or any function call because this is where the main problem begins...its quite a long story but basically I need a SQL stmnt like so:

Set rs6 = Nothing
                    rs6.Open "SELECT * FROM qryDepTask WHERE RecordID= " & recordID & " AND Completed=False AND TaskOrder >= " & taskOrder _
                                    & " ORDER BY TaskOrder DESC", AccessConnect, , , adCmdText
                    
                    If rs6.BOF Then
                        'do nothing
                    Else
                        While Not rs6.EOF
                            rs5.CursorType = adOpenKeyset
                            rs5.LockType = adLockOptimistic
                
                            rs5.Open "SELECT * FROM qryDepTask WHERE RecordID= " & recordID & " AND Completed=False AND TaskOrder < " & taskOrder _

but rather than qryDepTask I need to use my table...but I need to ensure I dont have any records where:
TaskSource="N\A"
And InsertTaskBefore=0

Jon

 

[chrismcbride]

Or you could make a query that returns all the records where ...
TaskSource<>"N\A" AND
InsertBeforeTask>0
Then create you next query based on this query and apply other criteria.

Chris

 

[mission2java_78]

The problem with this is that both conditions must be true...
you had mentioned > 0 for inserttaskbefore...
well this is a problem because many of the tasks I WANT or WANT to include have inserttaskbefore= 0...BUT their tasksource <> "N\A"

I DONT want any records where BOTH of these conditions meet
TaskSource="N\A"
InsertTaskBefore=0


If I had a record with
TaskSource="ME"
InsertTaskBefore=0

I would WANT this record

If I had another record with
TaskSource="N\A"
InsertTaskBefore=1230

Then I would want this record...

the only records I dont want is when
TaskSource="N\A"
InsertTaskBefore=0

Jon

 

[mission2java_78]

Hah I got it...
SELECT * FROM tblTasks WHERE (TaskSource<> 'N\A' AND InsertTaskBefore=0) OR (TaskSource='N\A' AND InsertTaskBefore>0);

What a moron I am
Thanks team
Jon

 

[John.Woody]

If you created a query or sql to return the records you don't want
TaskSource<>"N\A" AND InsertBeforeTask>0

You could then create a second sql where the records are <> the records in the first sql.

Would that work?

 

[chrismcbride]

Sorry Jon, did not read your question correctly.....

Try this

SELECT tblTask.*, tblTask.record_id
FROM tblTask
WHERE (((tblTask.record_id) Not In (SELECT tblTask.record_id
FROM tblTask
WHERE (((tblTask.task_source)="N/A") AND ((tblTask.insert_before_task)=0))));

Chris

 

[chrismcbride]

Sorry, stupic smilies keep buggering up the example...

SELECT tblTask.*, tblTask.record_id
FROM tblTask
WHERE (((tblTask.record_id) Not In (SELECT tblTask.record_id
FROM tblTask
WHERE (((tblTask.task_source)="N/A") AND ((tblTask.insert_before_task)=0))));

 

[RV]

Hey mission,

AND, are the conditions making more sence now OR...

Greetings,

RV