SQL Statement

[Tim Manos]

Hello all,

I'm trying to retrieve an ADO Recordset form an Access database and I'm
using the following SQL statement:

SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001# )
WHERE First LIKE 'A%'

which is obviously wrong. How can I modify this statement to get the
disirable result?

Thanks in advance

Tim

[Colin Moore]

Hi Tim,

Instead of this:

SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
)
WHERE First LIKE 'A%'

try this:

SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
) AND
First LIKE 'A%'

Your problem was that you had two where clauses, you can only have one where
clause but it can have many conditions

Hope that helps

Colin Moore
IS Dept.
Ext 188




"Tim Manos" <timmanos@t-online.de> wrote:
>Hello all,
>
>I'm trying to retrieve an ADO Recordset form an Access database and I'm
>using the following SQL statement:
>
>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
)
>WHERE First LIKE 'A%'
>
>which is obviously wrong. How can I modify this statement to get the
>disirable result?
>
>Thanks in advance
>
>Tim
>
>

[Arthur Wood]

Tim,
Why do think thet the SQL is "obviously wrong"?

It looks fine to me, as the Outer WHERE applies to the Main part of the Query,
and the inner where only applies to the SubQuery.

Of course, you could simplify the whole thin as:

SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
AND First LIKE 'A%')

which would then apply the filter on First in the inner query, and only return
the names that you appear to require.

Arthur Wood


"Tim Manos" <timmanos@t-online.de> wrote:
>Hello all,
>
>I'm trying to retrieve an ADO Recordset form an Access database and I'm
>using the following SQL statement:
>
>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
)
>WHERE First LIKE 'A%'
>
>which is obviously wrong. How can I modify this statement to get the
>disirable result?
>
>Thanks in advance
>
>Tim
>
>

[Colin Moore]

Sorry Tim I didn't see the inner query

Colin
"Arthur Wood" <wooda@saic-trsc.com> wrote:
>
>Tim,
> Why do think thet the SQL is "obviously wrong"?
>
>It looks fine to me, as the Outer WHERE applies to the Main part of the
Query,
>and the inner where only applies to the SubQuery.
>
>Of course, you could simplify the whole thin as:
>
>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
>AND First LIKE 'A%')
>
>which would then apply the filter on First in the inner query, and only
return
>the names that you appear to require.
>
>Arthur Wood
>
>
>"Tim Manos" <timmanos@t-online.de> wrote:
>>Hello all,
>>
>>I'm trying to retrieve an ADO Recordset form an Access database and I'm
>>using the following SQL statement:
>>
>>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
>)
>>WHERE First LIKE 'A%'
>>
>>which is obviously wrong. How can I modify this statement to get the
>>disirable result?
>>
>>Thanks in advance
>>
>>Tim
>>
>>
>

[Rohit Wason]

>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
)
>WHERE First LIKE 'A%'


Tim,
It is not clear from your post what is the 'desirable' result. But you can
actually combine the two WHERE clauses to get the result expected from the
query you have written. So even this:

SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
FROM CustomerAddresses
WHERE TimeStamp BETWEEN #7/10/2001# AND
#7/13/2001# AND
First LIKE 'A%'

will work in the same manner, unless you want some different results.
Cheers,
Rohit

[Sue Harsevoort]

If you combined the both where statements in the inner query could you just
eleminate the outer query all together and just have:

SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
AND First LIKE 'A%'

since as far as I can tell the outer query is just selecting all the records
from then inner query.

Sue

"Arthur Wood" <wooda@saic-trsc.com> wrote in message
news:3b4f10b5$1@news.devx.com...
>
> Tim,
> Why do think thet the SQL is "obviously wrong"?
>
> It looks fine to me, as the Outer WHERE applies to the Main part of the
Query,
> and the inner where only applies to the SubQuery.
>
> Of course, you could simplify the whole thin as:
>
> SELECT * FROM ( SELECT
First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
> >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND
#7/13/2001#
> AND First LIKE 'A%')
>
> which would then apply the filter on First in the inner query, and only
return
> the names that you appear to require.
>
> Arthur Wood
>
>
> "Tim Manos" <timmanos@t-online.de> wrote:
> >Hello all,
> >
> >I'm trying to retrieve an ADO Recordset form an Access database and I'm
> >using the following SQL statement:
> >
> >SELECT * FROM ( SELECT
First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
> >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND
#7/13/2001#
> )
> >WHERE First LIKE 'A%'
> >
> >which is obviously wrong. How can I modify this statement to get the
> >disirable result?
> >
> >Thanks in advance
> >
> >Tim
> >
> >
>