Query date criteria

[davoakey]

How do I put criteria in to get the the birthdays in the next 30 from a date of birth field

 

[SJMcAbney]

Add a calculated field:

Birthdays: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

Add the criteria for this field:

Between Date() And DateAdd("d",30,Date())

Stewart J. McAbney | Talk History

 

[OhioSteve]

Another alternative would be to run an update query on the first workday of each year and whenever you hire an employee. This solution breaks one of the main rules of relational DB design (not to hardcode calculated values). However, I don't think that the consequences would be too bad in this case. After all, the employees can't change this value!

This update query would take date date from DOB and populate the date field birthdate.

UPDATE Table1
SET Table1.birthdays = Month([DOB]) & "/" & Day([DOB]) & "/" & Year(Date());

 

[SJMcAbney]

OhiSteve, the problem isn't to update anything.

Also, concatenation is slower than using designated functions hence there's no need to use Month([DOB]) & "/" & Day([DOB]) & "/" & Year(Date()) when the DateSerial() function already accommodates this.

Stewart J. McAbney | Talk History

 

[davoakey]

Hi Stewart
I pasted the criteria you gave me into the query criteria line, but it did not produce a result, the birth date I used was 1/2/1933, and it did not find it.
Dave

 

[SJMcAbney]

Did you add the calculated field?

Stewart J. McAbney | Talk History

 

[davoakey]

Sorry
I thought it was either so I only put criterea in date field

 

[OhioSteve]

Your calculations will execute much faster then mine. However, my update query only runs once each year. And after it executes, you have a normal date field that is very easy to use.

 

[dhookom]

I agree with Stewart. There should be no reason to store another value and date expression should use date functions and not rely on Access' ability to convert from date to text to date.

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]

 

[SJMcAbney]

---I thought it was either so I only put criterea in date field---

You create the new field and add the criteria to this new field.

i.e in SQL

SELECT DateSerial(Year(Date()), Month([DOB]), Day([DOB])) As Birthday
FROM MyTable
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB])) Between Date() And DateAdd("d",30,Date());

---Your calculations will execute much faster then mine.---

Yes.

---However, my update query only runs once each year. And after it executes, you have a normal date field that is very easy to use.---

And the need for an UPDATE query is...?





Stewart J. McAbney | Talk History

 

[davoakey]

I put the experssion in a calculating field, changing the DOB the name of the date of birth field, and then put the criteria in the criteria for the calculating field, and got a message mis match
REgards
Dave

 

[SJMcAbney]

Is your date of birth field a date field or a text field?

Stewart J. McAbney | Talk History

 

[davoakey]

A Date field

 

[dhookom]

davoakey,
How about posting your sql view so that we know what you are doing?

Is your date field ever Null?

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]

 

[davoakey]

No I do not think they are null as they are date fields, but a lot do not have data, and they might be null if that is possible in a date field.
I have put sample dates in some fields to try to get them to show up in a report.
How do I past a copy of my sql view in this programne.
This is the first time I have used it
Regards
Dave

 

[OhioSteve]

Using the mouse, select the text in SQL view and right-click. Then choose "copy". Now the text is on the clipboard. You can paste it anywhere.

 

[davoakey]

Hi Yes I did that but the paste is not avialible in this window, I am going to bed now, will check again in the morning, thanks for you patience

 

[dhookom]

Set the criteria under your DOB field to
Is Not Null


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]

 

[davoakey]

hi
All the other fields must be null as the only one that showed with the is not null cirteria was the ones with dates in them.
but how does that help
cheers
Dave

 

[dhookom]

Dave,
If your DOB field is Null then this expression will give you an error [Red]DateSerial(Year(Date()), Month([DOB]), Day([DOB]))[/red]

What is your current SQL and what error message do you get when you run the query?

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]