Where string hangs up when using dates only

[Tony Girgenti]

Hello.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want: "[COMPANY NAME] =
"A V H INTERIORS 4061" AND TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus
WHERE WORKSTATION= "#2- RECEIVED" AND [TempStatus].TIMESTAMP Between
#01/01/2004# And #01/01/2054#)"

Yet if i use this string, it hangs up and i never get a report. Can
somebody explain why ?

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= "#2-
RECEIVED" AND [TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

Any help would be greatly appreciated.

Thanks,
Tony

 

[Guest]

Hello.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want: "[COMPANY NAME] =
"A V H INTERIORS 4061" AND TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus
WHERE WORKSTATION= "#2- RECEIVED" AND [TempStatus].TIMESTAMP Between
#01/01/2004# And #01/01/2054#)"

Yet if i use this string, it hangs up and i never get a report. Can
somebody explain why ?

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= "#2-
RECEIVED" AND [TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

Any help would be greatly appreciated.

Thanks,
Tony

Tony,

I'm surprised that got the first string to work. When I paste the string
into the IDE, it gives me an error.

The problem is that when you put 1 double quote (") inside a quoted string,
you break (interrupt) the string. If you want to have a string within a
string, you need to use 2 double quotes ("") or a single quote ( ' ).

The string within a string is where you have ...."#2- RECEIVED"....

Below are your strings (modified); for each where clause, there is one with
the 2 double quotes and one with the single quote: (watch for line wrap)

-----------------
strWhere = "[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In
(SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

or

strWhere = "[COMPANY NAME] = ""A V H INTERIORS 4061"" AND TempStatus.JOBNO
In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= ""#2- RECEIVED"" AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

----------

strWhere = " TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE
WORKSTATION= ""#2- RECEIVED"" AND [TempStatus].TIMESTAMP Between #01/01/2004#
And #01/01/2054#)"

or

strWhere = " TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE
WORKSTATION= '#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004#
And #01/01/2054#)"

 

[Tony Girgenti]

Hello Steve.

Here are my strings. It still does the same thing. Works OK with the first
string, but never gives a report for second string.

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2005#)"

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2-
RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #06/01/2004#)"

Thanks,
Tony

Hello.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want: "[COMPANY
NAME] =
"A V H INTERIORS 4061" AND TempStatus.JOBNO In (SELECT JOBNO FROM
TempStatus
WHERE WORKSTATION= "#2- RECEIVED" AND [TempStatus].TIMESTAMP Between
#01/01/2004# And #01/01/2054#)"

Yet if i use this string, it hangs up and i never get a report. Can
somebody explain why ?

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION=
"#2-
RECEIVED" AND [TempStatus].TIMESTAMP Between #01/01/2004# And
#01/01/2054#)"

Any help would be greatly appreciated.

Thanks,
Tony

Tony,

I'm surprised that got the first string to work. When I paste the string
into the IDE, it gives me an error.

The problem is that when you put 1 double quote (") inside a quoted
string,
you break (interrupt) the string. If you want to have a string within a
string, you need to use 2 double quotes ("") or a single quote ( ' ).

The string within a string is where you have ...."#2- RECEIVED"....

Below are your strings (modified); for each where clause, there is one
with
the 2 double quotes and one with the single quote: (watch for line wrap)

-----------------
strWhere = "[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO
In
(SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

or

strWhere = "[COMPANY NAME] = ""A V H INTERIORS 4061"" AND TempStatus.JOBNO
In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= ""#2- RECEIVED"" AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

----------

strWhere = " TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE
WORKSTATION= ""#2- RECEIVED"" AND [TempStatus].TIMESTAMP Between
#01/01/2004#
And #01/01/2054#)"

or

strWhere = " TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE
WORKSTATION= '#2- RECEIVED' AND [TempStatus].TIMESTAMP Between
#01/01/2004#
And #01/01/2054#)"

 

[Guest]

Hello Steve.

Here are my strings. It still does the same thing. Works OK with the first
string, but never gives a report for second string.

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2005#)"

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2-
RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #06/01/2004#)"

Thanks,
Tony

Tony,

Are you sure the sub query returns records?
What happens if you run just the sub query? (Are records returned?)

Have you compared the [JOBNO]s in the report with the records returned
without the strWhere clause to the [JOBNO]s when you just run the sub query?

 

[Guest]

Hi Steve.

When i run the sub query, i get 16,000 records

I can run the report without filling in any of the form fields. It returns
1500 pages.

I'm not sure of what you mean by this:

Have you compared the [JOBNO]s in the report with the records returned

without the strWhere clause to the [JOBNO]s when you just run the sub
query?<<

Thanks,
Tony

Hello Steve.

Here are my strings. It still does the same thing. Works OK with the first
string, but never gives a report for second string.

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2005#)"

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2-
RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #06/01/2004#)"

Thanks,
Tony

Tony,

Are you sure the sub query returns records?
What happens if you run just the sub query? (Are records returned?)

Have you compared the [JOBNO]s in the report with the records returned
without the strWhere clause to the [JOBNO]s when you just run the sub query?

 

[Guest]

Hi Steve.

When i run the sub query, i get 16,000 records

I can run the report without filling in any of the form fields. It returns
1500 pages.

I'm not sure of what you mean by this:

Have you compared the [JOBNO]s in the report with the records returned

without the strWhere clause to the [JOBNO]s when you just run the sub
query?<<

Thanks,
Tony

Tony,

Other than the quote problem, I see no reason why you are not getting any
records in the report using the second strWhere clause.

Would you post the SQL of the report record source ?

 

[Tony Girgenti]

Hi Steve. Thanks for helping with this problem.
Here is the Report Record Source:

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;

Hi Steve.

When i run the sub query, i get 16,000 records

I can run the report without filling in any of the form fields. It
returns
1500 pages.

I'm not sure of what you mean by this:

Have you compared the [JOBNO]s in the report with the records returned

without the strWhere clause to the [JOBNO]s when you just run the sub
query?<<

Thanks,
Tony

Tony,

Other than the quote problem, I see no reason why you are not getting any
records in the report using the second strWhere clause.

Would you post the SQL of the report record source ?

 

[Guest]

Hi Steve. Thanks for helping with this problem.
Here is the Report Record Source:

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;

Tony,

I just don't see any reason why the second WHERE clause doesn't return any
records, while the first one does return records.

If you repost your question, maybe one of the SQL gurus will have an answer

Sorry I couldn't be more help to you.

 

[TC]

Your first string has an extra test: [COMPANY NAME] = "A V H INTERIORS
4061". Your second string does not have that test, so it could be
trying to return an enormously larger number of records, depending on
the size of your tables. And this could take a very long time,
depending on how well they are indexed. And the "IN" clause can be
inefficient, you should really be using "EXISTS".

This is relatively inefficient:

select *
from T1
where T1.F1 IN
( select F2
from T2
where T2.F3 = True )

This has the same reasult, but is more efficient:

select *
from T1
where EXISTS
( select null
from T2
where T2.F2 = T1.F1
and T2.F3 = True )

HTH,
TC [MVP Access]

 

[Tony Girgenti]

Hello Steve and TC.

Do you think if i made the TIMESTAMP in the table an idexed field, that it
would help ?

Also, TC, i'm not sure of where to change my "IN" to "EXISTS" in my strings.

Thanks,
Tony