Pulling dates query 1

[cheyenne2002]

I have an existing database (so I can't start over) and it contains a field where the members birthday was input. It is a text field and the dates are enter 9/28/1955.

I need to create a query that will allow me to pull out all the September birthdays when I run the query in August so they can get cards. Is there an easy way to do this?

I hate putting all 700 + members into an Excel file just to sort them.

Any suggestion greatly appreciate.
Sharon

 

[jrbarnett]

select *
from yourtable
where month (datefield) = 9

for september; to get it to pick the one after the current month:

select *
from yourtable
where month (datefield) = (month (date())+1)

John

 

[cheyenne2002]

Unfortunately that did not work. Here is the code after I created it in the Design View.

SELECT Members.[Contact Name], Members.[Company Name], Members.[My Birthday]
FROM Members
WHERE (((Members.[My Birthday])=9));

My field [My Birthday] is a text field and the birthdays are entered 9/28/1955 or 7/22/1972. Querying for 9 will not pull the Sept birthdays because there are no fields with just 9.

If I had created this I would have had a separate field for the month, but unfortunately I'm stuck with what I have. Can't make changes to it.

Who knows maybe this is something that can't be done given the constraints I'm stuck with.

Sharon

 

[dhookom]

Are all the dates legitimate?

SELECT Members.[Contact Name], Members.[Company Name], Members.[My Birthday]
FROM Members
WHERE Month(CDate([My Birthday]))=9;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cheyenne2002]

dhookom,

What do you mean by "Are all the date legitimate?"

I tried the new code and I get an error message. "Invalid use of Null"

Is it because some of the members have not shared their date of birth with us so the field is blank?

Sharon

 

[jrbarnett]

Yes - that would cause it. Change query to:

SELECT Members.[Contact Name], Members.[Company Name], Members.[My Birthday]
FROM Members
WHERE [My Birthday] Is Not Null And Month(CDate([My Birthday]))=9

John

 

[cheyenne2002]

jrbarnett,

Thank you. I edited the code but I still get the same error message.

Sharon

 

[dhookom]

Try:

SELECT [Contact Name], [Company Name], [My Birthday]
FROM Members
WHERE [My Birthday] Is Not Null And 
Month(CDate(Nz([My Birthday],#1/1/1900#)))=9

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cheyenne2002]

Thank you all!!!

It works like a charm

Sharon

 

[PHV]

A simpler way:

SELECT [Contact Name], [Company Name], [My Birthday]
FROM Members
WHERE Month(CDate(Nz([My Birthday],#1/1/1900#)))=9

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Hi PH,
You been on holiday?

My concern with your SQL is when the query is for January. If My Birthday is null then it will match "=1"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]