date question -MSAccess97

  • Thread starter Thread starter micfly
  • Start date Start date
M

micfly

I have a query that prints contacts address labels for a selected months
birthdays. What code would I add and where so it would only go back, say, 30
months. I'm stumped, thanks - here's the code:

SELECT Contacts.Birthdate, Contacts.FirstName, Contacts.LastName,
Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts
WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
UNION SELECT tblCoBuyer.Birthdate2, tblCoBuyer.FirstName,
tblCoBuyer.LastName, Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts INNER JOIN tblCoBuyer ON Contacts.ID = tblCoBuyer.ID
WHERE (((Month([Birthdate2]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No);
 
How do you defined going back 30 months? All I see there are birthdates: are
you saying you only want to select those records where the person is 30
months old?

If there's another date field that you haven't shown, you could use
something like:

WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
AND (DateDiff("m", [OtherDateField], Date) < 30)
 
I have a query that prints contacts address labels for a selected months
birthdays. What code would I add and where so it would only go back, say, 30
months
Click to expand...

Back 30 months on... what field?

The criterion would be
DateAdd("m", -30, Date())
Click to expand...

to get all values of a datefield more recent than 30 months ago today.
 
Sorry, what I mean is I only want recent customers that I have sold going
back 30 months by thier sold date (which is the "Date" field I don't even
have in this code). Someone else helped me with this way back when and now
my customers have grown so much I can't mail out that many. Anyway, I
didn't realize the Date field was missing when I posted this and I assume
that is the one that needs the correct criteria? Thanks for helping. Can you
suggest how to do this? Where would the Date field go here and how to go
back 30 months from that?
How do you defined going back 30 months? All I see there are birthdates: are
you saying you only want to select those records where the person is 30
months old?

If there's another date field that you haven't shown, you could use
something like:

WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
AND (DateDiff("m", [OtherDateField], Date) < 30)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



micfly said:
I have a query that prints contacts address labels for a selected months
birthdays. What code would I add and where so it would only go back,
Click to expand...
say,
30
months. I'm stumped, thanks - here's the code:

SELECT Contacts.Birthdate, Contacts.FirstName, Contacts.LastName,
Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts
WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
UNION SELECT tblCoBuyer.Birthdate2, tblCoBuyer.FirstName,
tblCoBuyer.LastName, Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts INNER JOIN tblCoBuyer ON Contacts.ID = tblCoBuyer.ID
WHERE (((Month([Birthdate2]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No);
Click to expand...
Click to expand...
 
Sorry, what I mean is I only want recent customers that I have sold going
back 30 months by thier sold date (which is the "Date" field I don't even
have in this code). Someone else helped me with this way back when and now
my customers have grown so much I can't mail out that many. Anyway, I
didn't realize the Date field was missing when I posted this and I assume
that is the one that needs the correct criteria? Thanks for helping. Can you
suggest how to do this? Where would the Date field go here and how to go
back 30 months from that?
How do you defined going back 30 months? All I see there are birthdates: are
you saying you only want to select those records where the person is 30
months old?

If there's another date field that you haven't shown, you could use
something like:

WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
AND (DateDiff("m", [OtherDateField], Date) < 30)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



micfly said:
I have a query that prints contacts address labels for a selected months
birthdays. What code would I add and where so it would only go back,
Click to expand...
say,
30
months. I'm stumped, thanks - here's the code:

SELECT Contacts.Birthdate, Contacts.FirstName, Contacts.LastName,
Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts
WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No)
UNION SELECT tblCoBuyer.Birthdate2, tblCoBuyer.FirstName,
tblCoBuyer.LastName, Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts INNER JOIN tblCoBuyer ON Contacts.ID = tblCoBuyer.ID
WHERE (((Month([Birthdate2]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No);
Click to expand...
Click to expand...
 
This code will run but it is still showing contacts that have a Date older
than 30 months - not their birthday date but the (Date) they were entered in
the database. What's wrong?

SELECT Contacts.Birthdate, Contacts.FirstName, Contacts.LastName,
Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts
WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No) AND (DateDiff("m", [Date], Date) < 30)
UNION SELECT tblCoBuyer.Birthdate2, tblCoBuyer.FirstName,
tblCoBuyer.LastName, Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts INNER JOIN tblCoBuyer ON Contacts.ID = tblCoBuyer.ID
WHERE (((Month([Birthdate2]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No) AND (DateDiff("m", [Date], Date) < 30);
 
Dear Mic:

I having to guess at what you mean:

(DateDiff("m", [Date], Date) < 30)

There is essentially no difference between [Date] and Date. You're
comparing something to the same thing, and the DateDiff would be 0 in
all cases.

Do you mean "today's date" for the second Date parameter. This is a
function you can call. You MUST include parens for it to refer to the
function, however. Try this:

(DateDiff("m", [Date], Date()) < 30)

Because of ambiguity in the term "Date" it is best not to name any
columns with this name (or any other reserved word or function name).
Use BirthDate, DueDate, etc.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
That did it, thanks for all the help!
Mickey

Tom Ellison said:
Dear Mic:

I having to guess at what you mean:

(DateDiff("m", [Date], Date) < 30)

There is essentially no difference between [Date] and Date. You're
comparing something to the same thing, and the DateDiff would be 0 in
all cases.

Do you mean "today's date" for the second Date parameter. This is a
function you can call. You MUST include parens for it to refer to the
function, however. Try this:

(DateDiff("m", [Date], Date()) < 30)

Because of ambiguity in the term "Date" it is best not to name any
columns with this name (or any other reserved word or function name).
Use BirthDate, DueDate, etc.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This code will run but it is still showing contacts that have a Date older
than 30 months - not their birthday date but the (Date) they were entered in
the database. What's wrong?

SELECT Contacts.Birthdate, Contacts.FirstName, Contacts.LastName,
Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts
WHERE (((Month([Birthdate]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No) AND (DateDiff("m", [Date], Date) < 30)
UNION SELECT tblCoBuyer.Birthdate2, tblCoBuyer.FirstName,
tblCoBuyer.LastName, Contacts.Address,
Contacts.City, Contacts.State, Contacts.ZipCode
FROM Contacts INNER JOIN tblCoBuyer ON Contacts.ID = tblCoBuyer.ID
WHERE (((Month([Birthdate2]))=[? Enter 1 for Jan, 2 for Feb, etc])) AND
((Contacts.[Bad Address])=No) AND (DateDiff("m", [Date], Date) < 30);
Click to expand...
Click to expand...
 
Back
Top