Where clause in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David McKnight - 20 Dec 2005 16:56 GMT

I would like to use a where clause in a query such that query would only
select Aug dates , but I'm not sure how the statement would be constructed.

Something like:

stSql= SELECT [table A] WHERE [DATE]=8/*/****

Signature

David McKnight

Reply to this Message

Allen Browne - 20 Dec 2005 17:00 GMT

Try the Month() function:
strSql= "SELECT [table A].* FROM [table A] WHERE Month([table A].[DATE])
= 8;"

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I would like to use a where clause in a query such that query would only
> select Aug dates , but I'm not sure how the statement would be
[quoted text clipped - 3 lines]
>
> stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

Klatuu - 20 Dec 2005 17:07 GMT

stSql= "SELECT [table A] WHERE Month([DATE]) = 8;"

Don't name anything date. Date is a reserved word in Access and doing this
can cause unexpected results.

> I would like to use a where clause in a query such that query would only
> select Aug dates , but I'm not sure how the statement would be constructed.
>
> Something like:
>
> stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

David McKnight - 20 Dec 2005 19:10 GMT

Anything!? I have several tables and thus queries that have a [Date] field -
is that Okay? It seems to work, because I have tens if not hundreds of
reference too these date Fields.

Signature

David McKnight

> stSql= "SELECT [table A] WHERE Month([DATE]) = 8;"
>
[quoted text clipped - 7 lines]
> >
> > stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

Rob Oldfield - 21 Dec 2005 00:22 GMT

Yup. It's a bad move. e.g. http://support.microsoft.com/kb/209187/

There are various utilities which will search a db and change everything for
you - but I've never had to use one so can't really recommend a particular
one. Anyone?

> Anything!? I have several tables and thus queries that have a [Date] field -
> is that Okay? It seems to work, because I have tens if not hundreds of
[quoted text clipped - 11 lines]
> > >
> > > stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

Douglas J. Steele - 21 Dec 2005 01:05 GMT

The ones I know are:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/

I've used Find & Replace for years.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Yup. It's a bad move. e.g. http://support.microsoft.com/kb/209187/
>
[quoted text clipped - 22 lines]
>> > >
>> > > stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

David McKnight - 21 Dec 2005 02:01 GMT

I'll give these a try on a backup copy of my database - I'm interested to
see impact on performance since [data] is such a critical pat of my databse.

thanks

Signature

David McKnight

> The ones I know are:
>
[quoted text clipped - 30 lines]
> >> > >
> >> > > stSql= SELECT [table A] WHERE [DATE]=8/*/****

Reply to this Message

Allen Browne - 21 Dec 2005 02:23 GMT

David, altering the field name will not improve the performance of your
database.

What it does is avoid the cases where Access misinterprets what you meant.
Date is a reserved word in VBA (for the system date), so there are cases
where Access will use today's date instead of the value in the field, and
your progam will appear to be giving wrong results.

Renaming the field to InvoiceDate or OrderDate or whatever prevents that
ambiguity.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'll give these a try on a backup copy of my database - I'm interested to
> see impact on performance since [data] is such a critical pat of my
[quoted text clipped - 23 lines]
>> >> is that Okay? It seems to work, because I have tens if not hundreds of
>> >> reference too these date Fields.

Reply to this Message