Need help with a multiple field query PLEASE

W

Wally Steadman

Let me apologize up front for accidentally cross posting, I initially sent
this message to the GETTINGSTARTED group, but realized that the Query group
may be the best place.

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)




--
Walter Steadman
CW2, USA
124th Signal Battalion
Network Management Tech
(e-mail address removed)
 
M

Michel Walsh

Hi,

FROM is a reserved word (and I suspect TO can be one too). Try using a
different field name, like FromNode, ToNode, or using [ ] around them:

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



Note that if neither [From] neither [To] can be null, then you can
simplify:

SELECT *
FROM [Log Sheet]
WHERE [From] = Nz( Forms!Testing!Combo2, [From] )
AND
[To] = Nz( Forms!Testing!Combo4, [To] )



since if Forms!Testing!Combo2 is null, then we will got: [From] = Nz( Null,
[From] ), which is [From] = [From]. And since [From] cannot be Null, by
assumption, [From]=[From] will always be true.



Hoping it may help,
Vanderghast, Access MVP






Wally Steadman said:
Let me apologize up front for accidentally cross posting, I initially sent
this message to the GETTINGSTARTED group, but realized that the Query group
may be the best place.

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)




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

Michel Walsh

Hi,


By the way, it is generally much better to have a single field for the
date_and_time, than having two different fields, one for the date, one for
the time. DateValue( myDateAndTimeField) and
TimeValue(myDateAndTimeField) can supply each "part", if needed.

Hoping it may help,
Vanderghast, Access MVP


Wally Steadman said:
Let me apologize up front for accidentally cross posting, I initially sent
this message to the GETTINGSTARTED group, but realized that the Query group
may be the best place.

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)




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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

More Details on a Query PLEASE 2
Multiple Parameter Crosstab query filter by form 1
Query driven by form field 1
Access Determine what query to run based on a checkbox 0
IIF in a query 3
automate a make table query 5
HELP! Convert SQL query to Access? 1
Summing time worked by an individual on a weekly basis 12

Top