Access / SQL query

[RobinDiederen]

I'm trying to calculate, from a SQL statement, the count of days to a persons birthday.

I'm trying the following:
strSQL = "SELECT * FROM Employees WHERE (DateDiff('d', Date, (DateSerial(Mon th([DateOfBirth]),Day([DateOfBirth]),Year(Date))) BETWEEN 0 AND 5"

The [DateOfBirth] is a field in the Employees table. Yet, I always receive a 3061 runtime error... anybody got any idea what is wrong? Yes, there are records in the table satisfying the asked conditions.

Many many thanks in advance!

[tshvw]

Hello,

well I don't know the answer but I always make a Query in DesignView in MSAccess and when it executes okay then I look at the SQL syntax that this query is using by selecting the 'SQL-view' option in this designview query.

regards,

Henk

[CaptainD]

I'm trying to calculate, from a SQL statement, the count of days to a persons birthday.

I'm trying the following:
strSQL = "SELECT * FROM Employees WHERE (DateDiff('d', Date, (DateSerial(Mon th([DateOfBirth]),Day([DateOfBirth]),Year(Date))) BETWEEN 0 AND 5"

The [DateOfBirth] is a field in the Employees table. Yet, I always receive a 3061 runtime error... anybody got any idea what is wrong? Yes, there are records in the table satisfying the asked conditions.

Many many thanks in advance!

The datediff function uses 3 values separated by commas, you have more then that by the way you have the last date values entered. My guess is, that is what is throwing your error.

Why not just use "WHERE DateDiff("d",Fo rmat(DateOfBirt h, "mmddyyyy") , Date) Between 0 and 5

[RobinDiederen]

The datediff function uses 3 values separated by commas, you have more then that by the way you have the last date values entered. My guess is, that is what is throwing your error.

Why not just use "WHERE DateDiff("d",Fo rmat(DateOfBirt h, "mmddyyyy") , Date) Between 0 and 5

Thanks, I'm still tinkering with this though..

I'm currently trying

SQL = "SELECT * FROM Employees WHERE DateDiff('d', Format([DoB], 'General Date'), Format(Date, 'General Date') ) BETWEEN 0 AND 5;"
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

But that keeps reporting a (Excpected: 1) runtime error. Any clues?

[patcullen]

Thanks, I'm still tinkering with this though..

I'm currently trying

SQL = "SELECT * FROM Employees WHERE DateDiff('d', Format([DoB], 'General Date'), Format(Date, 'General Date') ) BETWEEN 0 AND 5;"
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

But that keeps reporting a (Excpected: 1) runtime error. Any clues?

My guess is you're trying to find the employees with a birthday in the next five days... Try this:

SELECT * FROM Employees WHERE DateDiff('d', Format(now(), 'General Date'), Format([DoB], 'General Date') ) BETWEEN 0 AND 5;

This worked for me :)

Patrick Cullen, ZA

[willakawill]

My guess is you're trying to find the employees with a birthday in the next five days... Try this:

SELECT * FROM Employees WHERE DateDiff('d', Format(now(), 'General Date'), Format([DoB], 'General Date') ) BETWEEN 0 AND 5;

This worked for me :)

Patrick Cullen, ZA

Am I missing something obvious here? Surely this would return the number of days between the date they were born and now. i.e. more than 7,000 days. Unless, of course, you are looking for 5 day old employees :)

[willakawill]

Thanks, I'm still tinkering with this though..

I'm currently trying

SQL = "SELECT * FROM Employees WHERE DateDiff('d', Format([DoB], 'General Date'), Format(Date, 'General Date') ) BETWEEN 0 AND 5;"
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

But that keeps reporting a (Excpected: 1) runtime error. Any clues?

This gives me 4

DateOfBirth = #11/11/2006#
    MsgBox DateDiff("d", Now, DateSerial(Year(Date), Month(DateOfBirth), Day(DateOfBirth)))

It seems that you may have entered the DateSerial parameters in the wrong order.

[RobinDiederen]

This gives me 4

DateOfBirth = #11/11/2006#
    MsgBox DateDiff("d", Now, DateSerial(Year(Date), Month(DateOfBirth), Day(DateOfBirth)))

It seems that you may have entered the DateSerial parameters in the wrong order.

Whatever I try, I can't get it to work.

For example, I try:
[code
SQL = "SELECT * FROM Werknemers WHERE DateDiff('d', Format(DateSeri al(Year(Now),Mo nth(Geboortedat um),Day(Geboort eDatum)), 'Short Date'), Format(Date, 'Short Date') ) BETWEEN 0 AND 5;"
[/code]

This keeps on giving error 3601... while I can't find any problem.. any clues?

[Andrew Thackray]

try this

SQL = "SELECT * FROM Werknemers WHERE month(now) = month(DateOfBirth) and day(DateOfBirth) - day(now) > -1 and  day(DateOfBirth) - day(now) < 6