Prev: crosstab for day of week
Next: How to make a query sort randomly each time it is run?
From: dar on 26 Apr 2010 16:03
Hello Dorian-
Is there any spaces? When I run it, it pops up a warning that I have a
syntas error

"Dorian" wrote:

> SELECT * FROM Customers where PolicyExpirationDate >= Date() ORDER BY
> PolicyExpirationDate
> This will list the future expiration dates but ignore those that have
> already passed.
> Assumes 'PolicyExpirationDate' is a date/time column.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "dar" wrote:
>
> > I would like to run a qry that will show me when my insurance policyies
> > expire. Some expire in 30 days others in 90.
> >
> > Table: Customers
> > Fields:
> > CustomerName
> > ContactName
> > PolicyNumber
> > PolicyExpirationDate
From: dar on 27 Apr 2010 11:08
John am I doing something wrong?

I entered test data in the field of PolicyExpirationDate with the below
criterion, it returns a query with everything I entered. I entered random
dates that were less than 7 days and more than 7 days.

"John W. Vinson" wrote:

> On Mon, 26 Apr 2010 10:36:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:
>
> >The PolicyExpirationDate will have a date of when the Policy will expire.
> >The policy may expire in August or November. i want to run a qry that will
> >show me up coming policies that will expire at any given time.
>
> Then create a query with an appropriate criterion on the PolicyExpirationDate.
> For instance, if you want to find which policies expire between today and a
> week from today, use a criterion
>
> >= Date() AND <= Date() + 7
>
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 27 Apr 2010 12:45
On Tue, 27 Apr 2010 08:08:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:

>John am I doing something wrong?
>
>I entered test data in the field of PolicyExpirationDate with the below
>criterion, it returns a query with everything I entered. I entered random
>dates that were less than 7 days and more than 7 days.

Please open the query in SQL view and post the entire SQL string here.

Is PolicyExpirationDate in fact a Date/Time field, or is it perhaps Text
(which looks like a date but won't search chronologically)?
--

John W. Vinson [MVP]
From: dar on 27 Apr 2010 13:30
PolicyExpirationDate is set to Date/Time

SELECT tblCustomer2.CustomerName, tblCustomer2.ContactName,
tblCustomer2.ContactNumber, tblCustomer2.PolicyNumber, tblCustomer2.Carrier,
tblCustomer2.Premium, tblCustomer2.PolicyExpirationDate,
tblCustomer2.PolicyType
FROM tblCustomer2
WHERE (((tblCustomer2.PolicyExpirationDate)>=Date() And
(tblCustomer2.PolicyExpirationDate)<=Date()+7));



..

"John W. Vinson" wrote:

> On Tue, 27 Apr 2010 08:08:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:
>
> >John am I doing something wrong?
> >
> >I entered test data in the field of PolicyExpirationDate with the below
> >criterion, it returns a query with everything I entered. I entered random
> >dates that were less than 7 days and more than 7 days.
>
> Please open the query in SQL view and post the entire SQL string here.
>
> Is PolicyExpirationDate in fact a Date/Time field, or is it perhaps Text
> (which looks like a date but won't search chronologically)?
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 27 Apr 2010 15:01
On Tue, 27 Apr 2010 10:30:02 -0700, dar <dar(a)discussions.microsoft.com> wrote:

>PolicyExpirationDate is set to Date/Time
>
>SELECT tblCustomer2.CustomerName, tblCustomer2.ContactName,
>tblCustomer2.ContactNumber, tblCustomer2.PolicyNumber, tblCustomer2.Carrier,
>tblCustomer2.Premium, tblCustomer2.PolicyExpirationDate,
>tblCustomer2.PolicyType
>FROM tblCustomer2
>WHERE (((tblCustomer2.PolicyExpirationDate)>=Date() And
>(tblCustomer2.PolicyExpirationDate)<=Date()+7));

If you run that query today that should find only records with
PolicyExpirationDate in the range from #4/27/2010# to #5/4/2010#.

If it doesn't please post a few rows of your data indicating the dates that
violate these conditions. That's wierd.
--

John W. Vinson [MVP]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: crosstab for day of week
Next: How to make a query sort randomly each time it is run?