Query SQL

N

Nick T

Hi,
I have a customer details database & i want a query which when opened will
just show my customers whos date of birth is next month:

This is my current SQL:

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY Format$([Client Details].DateofBirth,'mmmm');

Is there some code i can add, so that eg. if i open the query in Janurary,
it shows me customers whos birthday is in February etc.

Also, i cant seem to sort them by month, ie, Jan, Feb, March, Apr..... if i
set to sort assending, it simply does it alphabetically.

Any help would be great.

Thanks
 
W

Wayne-I-M

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth
FROM [Client Details]
WHERE (((Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]))=Year(Date())*12+DatePart("m",Date())+1))
ORDER BY Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]);


In the date of birth column put this
Year(Date())*12+DatePart("m",Date())+1
and you get the above sql - if you sort ascending

good luck
 
D

Douglas J. Steele

Why introduce Year?

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY DatePart("m", [Client Details].DateofBirth);

or, if you want them sorted in order through the month

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY Format$([Client Details].DateofBirth,'mmdd');


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Wayne-I-M said:
SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town,
[Client
Details].PostalCode, [Client Details].DateofBirth
FROM [Client Details]
WHERE (((Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]))=Year(Date())*12+DatePart("m",Date())+1))
ORDER BY Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]);


In the date of birth column put this
Year(Date())*12+DatePart("m",Date())+1
and you get the above sql - if you sort ascending

good luck





--
Wayne
Manchester, England.



Nick T said:
Hi,
I have a customer details database & i want a query which when opened
will
just show my customers whos date of birth is next month:

This is my current SQL:

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town,
[Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY Format$([Client Details].DateofBirth,'mmmm');

Is there some code i can add, so that eg. if i open the query in
Janurary,
it shows me customers whos birthday is in February etc.

Also, i cant seem to sort them by month, ie, Jan, Feb, March, Apr..... if
i
set to sort assending, it simply does it alphabetically.

Any help would be great.

Thanks
Click to expand...
Click to expand...
 
K

Ken Sheridan

For next month's birthdays:

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
WHERE MONTH(DateofBirth) = MONTH(DATEADD("m",1,DATE()));

To sort all clients by birthday month:

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY MONTH(DateofBirth);

The Month function returns a vale from 1 to 12 so the months will sort
correctly.

Ken Sheridan
Stafford, England
 
W

Wayne-I-M

Oh yeah - ooops

but maybe it a pre-natal ward in a hospital
Hmmmmm doubt it though :)


--
Wayne
Manchester, England.



Douglas J. Steele said:
Why introduce Year?

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY DatePart("m", [Client Details].DateofBirth);

or, if you want them sorted in order through the month

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town, [Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY Format$([Client Details].DateofBirth,'mmdd');


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Wayne-I-M said:
SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town,
[Client
Details].PostalCode, [Client Details].DateofBirth
FROM [Client Details]
WHERE (((Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]))=Year(Date())*12+DatePart("m",Date())+1))
ORDER BY Year([Client Details]![DateofBirth])*12+DatePart("m",[Client
Details]![DateofBirth]);


In the date of birth column put this
Year(Date())*12+DatePart("m",Date())+1
and you get the above sql - if you sort ascending

good luck





--
Wayne
Manchester, England.



Nick T said:
Hi,
I have a customer details database & i want a query which when opened
will
just show my customers whos date of birth is next month:

This is my current SQL:

SELECT [Client Details].CustomerID, [Client Details].FirstName, [Client
Details].LastName, [Client Details].Address, [Client Details].Town,
[Client
Details].PostalCode, [Client Details].DateofBirth, Format$([Client
Details].DateofBirth,'mmmm') AS [DateofBirth By Month]
FROM [Client Details]
ORDER BY Format$([Client Details].DateofBirth,'mmmm');

Is there some code i can add, so that eg. if i open the query in
Janurary,
it shows me customers whos birthday is in February etc.

Also, i cant seem to sort them by month, ie, Jan, Feb, March, Apr..... if
i
set to sort assending, it simply does it alphabetically.

Any help would be great.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 and Dates 1
sorting a query 7
How do you sort 2 date fields in assending order (query or report) 1
query look up(?) 4
Combining 3 queries 2
calc date of birth in query 3
Getting only the last transaction for each customer 2
send object based on query 1

Top