How to get needed records for report

[Tony Girgenti]

Hello.

We have a database used just for reporting with status records that have a
date and workstation fields as in:
TempStatusRecNo TIMESTAMP WORKSTATION JOBNO
------------------- --------------- ------------------- --------
2091705 4/22/2004 6:58:32 AM #4- IN WORK 2803
2091703 4/22/2004 6:55:39 AM #2- RECEIVED 2803
2092176 4/28/2004 1:04:06 PM #5- COMPLETED 2803
2092401 5/3/2004 11:14:34 AM #7- INVOICED 2803
2091704 4/22/2004 6:55:47 AM #2- RECEIVED 2803

It gives me the records with the right timestamp, but only the
WORKSTATION="#2- RECEIVED"
I want to be able to select all of the job records that match a set of
criteria from a form. The form will ask for a date range which i will use
to select based on the "#2-RECEIVED" record timestamp but i want all of the
records for that JOBNO.

Here is the reports underlying query:
SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Here is my where statement for the "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere" statement:
"[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[TempStatus].WORKSTATION = "#2- RECEIVED" AND [TempStatus].TIMESTAMP Between
#04/22/2004# And #04/23/2004#

Any help would be greatly appreciated.

Thanks,
Tony

 

[Guest]

Replace [TempStatus].WORKSTATION = "#2- RECEIVED" AND
with [TempStatus].JOBNO = "2803" AND

 

[Van T. Dinh]

What do you want to do if 2 or more JobNos has at least one Record
satisfying your selection criteria?

 

[Tony Girgenti]

Karl and Van. Thanks for your replies.

I need to be able to find the record that matches the entered date or date
range matching the "#2- RECEIVED" TIMESTAMP, but once it is found, then i
need all of the records with the same JOBNO as the found "#2- RECEIVED"
record.

In other words, when using the date matches, use the TIMESTAMP from the
record that has a "#2- RECEIVED" in the WORKSTATION column, then get all of
the records that match the JOBNO of that found "#2- RECEIVED" record.

I hope that explains it.

Thanks,
Tony

Replace [TempStatus].WORKSTATION = "#2- RECEIVED" AND
with [TempStatus].JOBNO = "2803" AND

Hello.

We have a database used just for reporting with status records that have
a
date and workstation fields as in:
TempStatusRecNo TIMESTAMP WORKSTATION JOBNO
------------------- --------------- -------------------
--------
2091705 4/22/2004 6:58:32 AM #4- IN WORK 2803
2091703 4/22/2004 6:55:39 AM #2- RECEIVED 2803
2092176 4/28/2004 1:04:06 PM #5- COMPLETED 2803
2092401 5/3/2004 11:14:34 AM #7- INVOICED 2803
2091704 4/22/2004 6:55:47 AM #2- RECEIVED 2803

It gives me the records with the right timestamp, but only the
WORKSTATION="#2- RECEIVED"
I want to be able to select all of the job records that match a set of
criteria from a form. The form will ask for a date range which i will
use
to select based on the "#2-RECEIVED" record timestamp but i want all of
the
records for that JOBNO.

Here is the reports underlying query:
SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Here is my where statement for the "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere" statement:
"[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[TempStatus].WORKSTATION = "#2- RECEIVED" AND [TempStatus].TIMESTAMP
Between
#04/22/2004# And #04/23/2004#

Any help would be greatly appreciated.

Thanks,
Tony

 

[Tony Girgenti]

Van.

It doesn't matter. As long as one of the WORKSTATION="#2- RECEIVED" and the
TIMESTAMP for that record matches the date range, then i want all of the
records with that same JOBNO.

Thanks,
Tony

What do you want to do if 2 or more JobNos has at least one Record
satisfying your selection criteria?

--
HTH
Van T. Dinh
MVP (Access)

Hello.

We have a database used just for reporting with status records that have
a date and workstation fields as in:
TempStatusRecNo TIMESTAMP WORKSTATION JOBNO
------------------- --------------- -------------------
--------
2091705 4/22/2004 6:58:32 AM #4- IN WORK 2803
2091703 4/22/2004 6:55:39 AM #2- RECEIVED 2803
2092176 4/28/2004 1:04:06 PM #5- COMPLETED 2803
2092401 5/3/2004 11:14:34 AM #7- INVOICED 2803
2091704 4/22/2004 6:55:47 AM #2- RECEIVED 2803

It gives me the records with the right timestamp, but only the
WORKSTATION="#2- RECEIVED"
I want to be able to select all of the job records that match a set of
criteria from a form. The form will ask for a date range which i will
use to select based on the "#2-RECEIVED" record timestamp but i want all
of the records for that JOBNO.

Here is the reports underlying query:
SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER, INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Here is my where statement for the "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere" statement:
"[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[TempStatus].WORKSTATION = "#2- RECEIVED" AND [TempStatus].TIMESTAMP
Between #04/22/2004# And #04/23/2004#

Any help would be greatly appreciated.

Thanks,
Tony

 

[Guest]

Van.

I'm not sure that i answered your exact question. If 2 or more JOBNO's has
at least one record that satisfies the selection criteria, the report groups
the records by JOBNO. So ,if two or more JOBNO's are selected they will
reported under their specific job.

I hope that explains it.

Thanks,
Tony

What do you want to do if 2 or more JobNos has at least one Record
satisfying your selection criteria?

--
HTH
Van T. Dinh
MVP (Access)

Hello.

We have a database used just for reporting with status records that have a
date and workstation fields as in:
TempStatusRecNo TIMESTAMP WORKSTATION JOBNO
------------------- --------------- ------------------- --------
2091705 4/22/2004 6:58:32 AM #4- IN WORK 2803
2091703 4/22/2004 6:55:39 AM #2- RECEIVED 2803
2092176 4/28/2004 1:04:06 PM #5- COMPLETED 2803
2092401 5/3/2004 11:14:34 AM #7- INVOICED 2803
2091704 4/22/2004 6:55:47 AM #2- RECEIVED 2803

It gives me the records with the right timestamp, but only the
WORKSTATION="#2- RECEIVED"
I want to be able to select all of the job records that match a set of
criteria from a form. The form will ask for a date range which i will use
to select based on the "#2-RECEIVED" record timestamp but i want all of
the records for that JOBNO.

Here is the reports underlying query:
SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER, INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Here is my where statement for the "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere" statement:
"[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[TempStatus].WORKSTATION = "#2- RECEIVED" AND [TempStatus].TIMESTAMP
Between #04/22/2004# And #04/23/2004#

Any help would be greatly appreciated.

Thanks,
Tony

 

[Van T. Dinh]

My question was that if somehow, there are 2 JobNos matching your criteria,
do you still want only ALL Records belong to just one JobNo or you want ALL
Records for both JobNos?

If you only want all Records belonging to just one JobNo, how do you decide
which one to hit and which one to miss?

 

[Guest]

Van.

There is so much data in this database that it would be highly impropbable
that it would NOT find more than 2 JOBNO's matching my criteria. I am
expecting it to find more than 2 JOBNO's matching the criteria.

I wish i could send you my Access database so that you could see how it
works. It groups the records by JOBNO. It displays all of the records in
TIMESTAMP order for one JOBNO.

My problem is that i want to find the JOBNO that matches the entered date
range to the TIMESTAMP for the record that has "#2- RECEIVED" in WORKSTATION.
Then, it should find all of the records for that JOBNO. If it does not find
a "#2- RECEIVED" WORKSTATION, then that JOBNO will not show on the report.

Thanks,
Tony

 

[Van T. Dinh]

That's better...

I think you need to use an IN Clause with a SubQuery. However, I am not
100% sure whether you can use the In Clause in the "wherecondition" argument
of the OpenReport method but logically, you should be able to.

Try the following "wherecondition" argument:

[JOBNO] IN
(
SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)
)

If the above doesn't work, try using it in your RecordSource Query and then
call your Report without the "wherecondition" argument (since you already
set the filtering criteria in the RecordSource)

Note: I have not checked the SQL String you used as the RecordSource.

 

[Guest]

Van.

When itry this in my strWhere, it gives me a syntax error and won't execute.

When i put it into my RecordSoure Query, I can't save it. It gives me
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.

Thanks,
Tony

That's better...

I think you need to use an IN Clause with a SubQuery. However, I am not
100% sure whether you can use the In Clause in the "wherecondition" argument
of the OpenReport method but logically, you should be able to.

Try the following "wherecondition" argument:

[JOBNO] IN
(
SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)
)

If the above doesn't work, try using it in your RecordSource Query and then
call your Report without the "wherecondition" argument (since you already
set the filtering criteria in the RecordSource)

Note: I have not checked the SQL String you used as the RecordSource.

--
HTH
Van T. Dinh
MVP (Access)

Van.

I'm not sure that i answered your exact question. If 2 or more JOBNO's
has
at least one record that satisfies the selection criteria, the report
groups
the records by JOBNO. So ,if two or more JOBNO's are selected they will
reported under their specific job.

I hope that explains it.

Thanks,
Tony

 

[Van T. Dinh]

If you use it as the RecordSource of the Report, you need to combine with
your Select SQL like:

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
WHERE [JOBNO] IN
(
SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)
)
ORDER BY TempStatus.TIMESTAMP;

I haven't checked your part of the SQL. I am not sure about the use of
DISTINCT here.

 

[Tony Girgenti]

Hi Van.

If i do it that way, will i be able to refer to Form fields ? Also, how
will i handle it if they only put in one date ? In my coding, i have it so
that if SIDEMARK is not entered on the form, then the StrWhere string does
not contain: AND [SIDEMARK] = "MARGARGEL"

"BLAIR HOUSE 10023", "MARGAREL", 04/22/2004 and 04/24/2004 are all form
fields.

If i do it the way you suggest, i am restricted to only one form of the
select statement.

Thanks,
Tony

If you use it as the RecordSource of the Report, you need to combine with
your Select SQL like:

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
WHERE [JOBNO] IN
(
SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)
)
ORDER BY TempStatus.TIMESTAMP;

I haven't checked your part of the SQL. I am not sure about the use of
DISTINCT here.

--
HTH
Van T. Dinh
MVP (Access)

Van.

When itry this in my strWhere, it gives me a syntax error and won't
execute.

When i put it into my RecordSoure Query, I can't save it. It gives me
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT',
or 'UPDATE'.

Thanks,
Tony

 

[Van T. Dinh]

Test the SQL String first with explicit values replacing the Parameters to
make sure the Query works.

You can then simply use the references to the Controls on the Form as
Parameters in your Query / SubQuery. When you run the Report (using the
Query as the DataSource) and if the Form is open, Access will find the
values in the Controls on the Form and substitute the references to the
Controls in the Query with the found values.

 

[Tony Girgenti]

When i put your sub query in and try to open the form, i get:
"The specified field '[JOBNO]' could refer to more than one table listed in
the FROM clause of your SQL statement".

Thanks,
Tony

Test the SQL String first with explicit values replacing the Parameters to
make sure the Query works.

You can then simply use the references to the Controls on the Form as
Parameters in your Query / SubQuery. When you run the Report (using the
Query as the DataSource) and if the Form is open, Access will find the
values in the Controls on the Form and substitute the references to the
Controls in the Query with the found values.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van.

If i do it that way, will i be able to refer to Form fields ? Also, how
will i handle it if they only put in one date ? In my coding, i have it
so that if SIDEMARK is not entered on the form, then the StrWhere string
does not contain: AND [SIDEMARK] = "MARGARGEL"

"BLAIR HOUSE 10023", "MARGAREL", 04/22/2004 and 04/24/2004 are all form
fields.

If i do it the way you suggest, i am restricted to only one form of the
select statement.

Thanks,
Tony

 

[Van T. Dinh]

You got the Field JOBNO in more than one Table. Try:

....
WHERE TempStatus.[JOBNO] IN
....


--
HTH
Van T. Dinh
MVP (Access)

When i put your sub query in and try to open the form, i get:
"The specified field '[JOBNO]' could refer to more than one table listed
in the FROM clause of your SQL statement".

Thanks,
Tony

Test the SQL String first with explicit values replacing the Parameters
to make sure the Query works.

You can then simply use the references to the Controls on the Form as
Parameters in your Query / SubQuery. When you run the Report (using the
Query as the DataSource) and if the Form is open, Access will find the
values in the Controls on the Form and substitute the references to the
Controls in the Query with the found values.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van.

If i do it that way, will i be able to refer to Form fields ? Also, how
will i handle it if they only put in one date ? In my coding, i have it
so that if SIDEMARK is not entered on the form, then the StrWhere string
does not contain: AND [SIDEMARK] = "MARGARGEL"

"BLAIR HOUSE 10023", "MARGAREL", 04/22/2004 and 04/24/2004 are all form
fields.

If i do it the way you suggest, i am restricted to only one form of the
select statement.

Thanks,
Tony

 

[Tony Girgenti]

Van.

When i do this and run the report, it hangs for a really long time and gives
no records.

Tony

You got the Field JOBNO in more than one Table. Try:

...
WHERE TempStatus.[JOBNO] IN
...


--
HTH
Van T. Dinh
MVP (Access)

When i put your sub query in and try to open the form, i get:
"The specified field '[JOBNO]' could refer to more than one table listed
in the FROM clause of your SQL statement".

Thanks,
Tony

Test the SQL String first with explicit values replacing the Parameters
to make sure the Query works.

You can then simply use the references to the Controls on the Form as
Parameters in your Query / SubQuery. When you run the Report (using the
Query as the DataSource) and if the Form is open, Access will find the
values in the Controls on the Form and substitute the references to the
Controls in the Query with the found values.

--
HTH
Van T. Dinh
MVP (Access)



Hi Van.

If i do it that way, will i be able to refer to Form fields ? Also,
how will i handle it if they only put in one date ? In my coding, i
have it so that if SIDEMARK is not entered on the form, then the
StrWhere string does not contain: AND [SIDEMARK] = "MARGARGEL"

"BLAIR HOUSE 10023", "MARGAREL", 04/22/2004 and 04/24/2004 are all form
fields.

If i do it the way you suggest, i am restricted to only one form of the
select statement.

Thanks,
Tony

 

[Van T. Dinh]

I wwrote before that I have some doubt about the the DISTINCT key word you
use in the SELECT clause ...

Firstly, try the SubQuery I posted independently as a separate Query, i.e.
with SQL String

SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)


Does that return the JOBNO(s) you want at reasonable speed?

If it does, my suggestion is correct and you will need to check your part of
the SQL String. My suggestion is to try the main Query without the keyword
DISTINCT. I don't think you actually need it there and DISTINCT can
sometimes take a fair while to execute, especially if you have a lot of
Fields in the selection list.

 

[Guest]

Hi Van. Thanks for all your help.

I tried what you suggested. First tried the SQL string and it ran fast, but
did not return any records.

Then i removed "DISTINCT" from the Reports Query and got the same results.

Here is the Reports Quesry i used:
"SELECT TempStatus.WORKSTATION, TempStatus.TIMESTAMP, TempStatus.JOBNO,
[PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET, [PENDING
ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;"

And here is what my query string looks like:
"SELECT DISTINCT TS.JOBNO FROM ([PENDING ORDERS] AS PO LEFT JOIN TempStatus
AS TS ON PO.JOBNO=TS.JOBNO) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB
NO] WHERE (PO.[COMPANY NAME] = "BLAIR HOUSE 10023" ) AND ( PO.[SIDEMARK] =
"MARGARGEL" ) AND ( TS.WORKSTATION = "#2- RECEIVED" ) AND ( TS.[TIMESTAMP]
BETWEEN #04/22/2004# AND #04/23/2004#)"

Thanks,
Tony

I wwrote before that I have some doubt about the the DISTINCT key word you
use in the SELECT clause ...

Firstly, try the SubQuery I posted independently as a separate Query, i.e.
with SQL String

SELECT DISTINCT TS.JOBNO
FROM ( [PENDING ORDERS] AS PO LEFT JOIN
TempStatus AS TS ON PO.JOBNO=TS.JOBNO
) LEFT JOIN INSTALL AS INST ON PO.JOBNO=INST.[JOB NO]
WHERE ( PO.[COMPANY NAME] = "BLAIR HOUSE 10023" )
AND ( PO.[SIDEMARK] = "MARGARGEL" )
AND ( TS.WORKSTATION = "#2- RECEIVED")
AND ( TS.[TIMESTAMP] BETWEEN #04/22/2004# AND #04/23/2004#)


Does that return the JOBNO(s) you want at reasonable speed?

If it does, my suggestion is correct and you will need to check your part of
the SQL String. My suggestion is to try the main Query without the keyword
DISTINCT. I don't think you actually need it there and DISTINCT can
sometimes take a fair while to execute, especially if you have a lot of
Fields in the selection list.

--
HTH
Van T. Dinh
MVP (Access)

Van.

When i do this and run the report, it hangs for a really long time and
gives no records.

Tony

 

[Van T. Dinh]

Don't worry about the main Query as yet. The SubQuery needs to work first
before the main Query is going to work.

* Are you sure that there are at least one JOBNO that satisfies the
selection criteria in the SubQuery (being tested as a stand-alone Query)?

As you can see, the selection criteria are simple and if JET cannot find any
JOBNO, it means that no Record meets the selection criteria. You may need to
check the Joins, too, as I simply copied the FROM clause from your original
Query to the SQL for the SubQuery.

* Try temporarily removing one selection condition at a time and see if you
find any JOBNO. If you do, check the condition you removed and see what's
wrong.