More Details on a Query PLEASE

  • Thread starter Thread starter Wally Steadman
  • Start date Start date
W

Wally Steadman

Will try to keep this short and sweet in hopes folks will read this and be
able to help:

I have a DB with a table of log entries. The table is made up of the
following fields with example data


Date Time From To
Issue Remarks Initials
10/01/03 13:45 Node A Node B
Link Out Restart Router WFS
10/01/03 14:30 Node A Node C
Link Out Replace Router WFS
10/01/03 15:00 Node B Node C
Link In Monitor WFS
10/02/03 7:30 Node C Node D
Link Out Restart Router WFS



I have a form with multiple unbound combo boxes. Two of which are the FROM
and TO and each Combo Box has the different Nodes. I am using queries in
queries to weed out specific data, So I have the first query that looks at
the DATE and then a second query that looks at the results of the first
query and looks at TIME and it drills down through each field so that I can
build a form that users can query. Where I am stuck is when I get to the
FROM and TO Fields. I have each of these queries set to FORM!From or
FORM!From is Null and the same for the TO query. So when I run the FROM
query, if i put no criteria it returns all. But note in table above, the
FROM and the TO both have entries for NODE C but NODE C is never in the FROM
and TO at the same time so when I do the Query and want to see any record
with NODE C, either in the FROM or the TO, I get no results (where i want to
get the bottom 3 in the example) because it pulls the results from the FROM
Query, but then it only looks at those results for the TO query and
therefore there are no records. I also need to be able to have the FROM and
TO look at specifics for Example if I want to see all entries FROM NODE A to
NODE B (which would only return the 1st Record from above) then I need that
functionality also. I have tried Union queries and such, but not sure of
the proper syntax needed for this type of query. When I get this figured
out, my DB will be ready for users to use so any help as soon as possible
would be greatly appreciated. Thanks In Advance



I have added some of the sql statements that I have used and been
unsuccessful:

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) AND
(([Log Sheet].To)=[Forms]![testing]![Combo4])) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Forms]![testing]![Combo4]) Is
Null));

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Log
Sheet].To)=[Forms]![testing]![Combo4])) OR (([Forms]![testing]![Combo4]) Is
Null));


Wally Steadman
GI in Iraq


--
Walter Steadman
CW2, USA
124th Signal Battalion
Network Management Tech
(e-mail address removed)
 
My initial observation is that you're not grouping the logical operators in
your queries. You have Logic1 OR Logic2 AND Logic3 OR Logic4.

Without parentheses, ACCESS will decide how to do the operator groupings,
and I'm sure it's not doing what you want. Try this:

(Logic1 OR Logic2) AND (Logic3 OR Logic4)

In other words, use ( ) to group the logic tests together whose total result
is to be used for the subsequent test.

--
Ken Snell
<MS ACCESS MVP>


Wally Steadman said:
Will try to keep this short and sweet in hopes folks will read this and be
able to help:

I have a DB with a table of log entries. The table is made up of the
following fields with example data


Date Time From To
Issue Remarks Initials
10/01/03 13:45 Node A Node B
Link Out Restart Router WFS
10/01/03 14:30 Node A Node C
Link Out Replace Router WFS
10/01/03 15:00 Node B Node C
Link In Monitor WFS
10/02/03 7:30 Node C Node D
Link Out Restart Router WFS



I have a form with multiple unbound combo boxes. Two of which are the FROM
and TO and each Combo Box has the different Nodes. I am using queries in
queries to weed out specific data, So I have the first query that looks at
the DATE and then a second query that looks at the results of the first
query and looks at TIME and it drills down through each field so that I can
build a form that users can query. Where I am stuck is when I get to the
FROM and TO Fields. I have each of these queries set to FORM!From or
FORM!From is Null and the same for the TO query. So when I run the FROM
query, if i put no criteria it returns all. But note in table above, the
FROM and the TO both have entries for NODE C but NODE C is never in the FROM
and TO at the same time so when I do the Query and want to see any record
with NODE C, either in the FROM or the TO, I get no results (where i want to
get the bottom 3 in the example) because it pulls the results from the FROM
Query, but then it only looks at those results for the TO query and
therefore there are no records. I also need to be able to have the FROM and
TO look at specifics for Example if I want to see all entries FROM NODE A to
NODE B (which would only return the 1st Record from above) then I need that
functionality also. I have tried Union queries and such, but not sure of
the proper syntax needed for this type of query. When I get this figured
out, my DB will be ready for users to use so any help as soon as possible
would be greatly appreciated. Thanks In Advance



I have added some of the sql statements that I have used and been
unsuccessful:

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) AND
(([Log Sheet].To)=[Forms]![testing]![Combo4])) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Forms]![testing]![Combo4]) Is
Null));

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Log
Sheet].To)=[Forms]![testing]![Combo4])) OR (([Forms]![testing]![Combo4]) Is
Null));


Wally Steadman
GI in Iraq


--
Walter Steadman
CW2, USA
124th Signal Battalion
Network Management Tech
(e-mail address removed)
Click to expand...
 
Hi Wally,

When you're trying to do something this complex, a more productive
approach in the long run is to write VBA code that reads the values in
the comboboxes (or other controls) on the search form, and constructs
the SQL statement for the query that's needed. This gives much more
power and flexibility than you can achieve by using control values as
parameters in a fixed query.

Having created the SQL statement, you'd use it as the RecordSource of
another form (or a subform) or a report.

The best way to go is to start with the query design grid and use
specific values for the dates, times, etc. rather than attempting to get
the values from controls on a form. When you get this one query working
as it should, switch to SQL view to see what you need to achieve in VBA,
and start working out how to build it up in a string variable.

Usually, you can have the body of the SQL as something like this

Const SQL_1 = "SELECT [Log Sheet].* FROM [Log Sheet] WHERE "

and then just concentrate on assembling the WHERE clause. Use VBA
If..Then constructs to handle the cases where various combos contain
nulls or real values, and SQL BETWEEN or IN ( ) constructs for things
like Node A to Node C.

By the way, life is a lot simpler if you don't use spaces in table names
or field names. It saves a lot of thought about [ ] !


Will try to keep this short and sweet in hopes folks will read this and be
able to help:

I have a DB with a table of log entries. The table is made up of the
following fields with example data


Date Time From To
Issue Remarks Initials
10/01/03 13:45 Node A Node B
Link Out Restart Router WFS
10/01/03 14:30 Node A Node C
Link Out Replace Router WFS
10/01/03 15:00 Node B Node C
Link In Monitor WFS
10/02/03 7:30 Node C Node D
Link Out Restart Router WFS



I have a form with multiple unbound combo boxes. Two of which are the FROM
and TO and each Combo Box has the different Nodes. I am using queries in
queries to weed out specific data, So I have the first query that looks at
the DATE and then a second query that looks at the results of the first
query and looks at TIME and it drills down through each field so that I can
build a form that users can query. Where I am stuck is when I get to the
FROM and TO Fields. I have each of these queries set to FORM!From or
FORM!From is Null and the same for the TO query. So when I run the FROM
query, if i put no criteria it returns all. But note in table above, the
FROM and the TO both have entries for NODE C but NODE C is never in the FROM
and TO at the same time so when I do the Query and want to see any record
with NODE C, either in the FROM or the TO, I get no results (where i want to
get the bottom 3 in the example) because it pulls the results from the FROM
Query, but then it only looks at those results for the TO query and
therefore there are no records. I also need to be able to have the FROM and
TO look at specifics for Example if I want to see all entries FROM NODE A to
NODE B (which would only return the 1st Record from above) then I need that
functionality also. I have tried Union queries and such, but not sure of
the proper syntax needed for this type of query. When I get this figured
out, my DB will be ready for users to use so any help as soon as possible
would be greatly appreciated. Thanks In Advance



I have added some of the sql statements that I have used and been
unsuccessful:

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) AND
(([Log Sheet].To)=[Forms]![testing]![Combo4])) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Forms]![testing]![Combo4]) Is
Null));

SELECT [Log Sheet].*, [Log Sheet].From, [Log Sheet].To
FROM [Log Sheet] WHERE ((([Log Sheet].From)=[Forms]![testing]![Combo2]) OR
((([Forms]![testing]![Combo2]) Is Null) AND (([Log
Sheet].To)=[Forms]![testing]![Combo4])) OR (([Forms]![testing]![Combo4]) Is
Null));


Wally Steadman
GI in Iraq
Click to expand...
 
Back
Top