Annoying syntax error (missing operator)

P

Paul

Anyone know why I'm getting this syntax error?


Syntax error (missing operator) in query expression 'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date() And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I just
can't see whats wrong with it.


Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date() And
Date() + 7;

Cheers,
Paul
 
G

Guest

I think your just missing a parenthesis for the year - Year(Date()).

DateSerial(Year(Date()), Month([DOB]), Day([DOB])) AS Birthday FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) = Between Date() And
Date() + 7;

HTH
Bryan
 
F

fredg

Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression 'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date() And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date() And
Date() + 7;

Cheers,
Paul
Click to expand...

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
...... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
 
P

Paul

Thanks for pointing out the mistakes but after copying your corrected code
it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?


Cheers,
Paul




fredg said:
Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I
just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

Cheers,
Paul
Click to expand...

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
 
J

Jeff Boyce

Paul

Here's a simple test...

Count the number of left parentheses in your statement.

Count the number of right parentheses in your statement.

Do the counts match? (hint: they HAVE to!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul said:
Thanks for pointing out the mistakes but after copying your corrected code
it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?


Cheers,
Paul




fredg said:
Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I
just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

Cheers,
Paul
Click to expand...

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
Click to expand...
 
P

Paul

Thanks Jeff,
It 'compiles' now but its giving me all 12 records from my Clients table,
records it is meant to show and records its not meant to show.

Example John Smith's date of birth is 21/03/1954 and his birthday shows up
on the query as being 21/03/2007. Although that would be the correct, I
don't want to display birthdays that have passed. The query is only
supposed to show people who's birthdays either today or in the next 7 days,
not days that have passed!
Any ideas?

I used this code (right number of parenthesis' this time!)
SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between Date() And
Date()) + 7;



Cheers,
Paul


Jeff Boyce said:
Paul

Here's a simple test...

Count the number of left parentheses in your statement.

Count the number of right parentheses in your statement.

Do the counts match? (hint: they HAVE to!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul said:
Thanks for pointing out the mistakes but after copying your corrected
code it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?


Cheers,
Paul




fredg said:
On Fri, 23 Mar 2007 22:08:19 -0000, Paul wrote:

Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I
just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

Cheers,
Paul

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
Click to expand...
Click to expand...
 
F

fredg

Thanks for pointing out the mistakes but after copying your corrected code
it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?

Cheers,
Paul

fredg said:
Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I
just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

Cheers,
Paul
Click to expand...

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
Click to expand...

You left off another closing parentheses in the Where clause.
You had 5 "(" but only 4 ")".

Try again.
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

You have to count opening and closing parenthesis, so that the open (
minus the closing ) = 0.
 
D

Douglas J. Steele

You sure Date() is returning the correct date on your machine?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Paul said:
Thanks Jeff,
It 'compiles' now but its giving me all 12 records from my Clients table,
records it is meant to show and records its not meant to show.

Example John Smith's date of birth is 21/03/1954 and his birthday shows up
on the query as being 21/03/2007. Although that would be the correct, I
don't want to display birthdays that have passed. The query is only
supposed to show people who's birthdays either today or in the next 7
days, not days that have passed!
Any ideas?

I used this code (right number of parenthesis' this time!)
SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between Date() And
Date()) + 7;



Cheers,
Paul


Jeff Boyce said:
Paul

Here's a simple test...

Count the number of left parentheses in your statement.

Count the number of right parentheses in your statement.

Do the counts match? (hint: they HAVE to!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul said:
Thanks for pointing out the mistakes but after copying your corrected
code it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?


Cheers,
Paul




On Fri, 23 Mar 2007 22:08:19 -0000, Paul wrote:

Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between
Date() And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days.
I just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between
Date() And
Date() + 7;

Cheers,
Paul

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
Click to expand...
Click to expand...
 
P

Paul

Fantastic, thanks Fred! I can sleep easy now!

And thanks all you guys too,
Paul


fredg said:
Thanks for pointing out the mistakes but after copying your corrected
code
it still doesn't work!
I get this message:

Syntax error in query expression 'DateSerial(Year(Date()), Month([DOB]),
Day([DOB]) Between Date() And Date() + 7;

What could be wrong!
My DOB field is of the ShortDate format (dd/mm/yyyy) if that makes a
difference?

Cheers,
Paul

fredg said:
On Fri, 23 Mar 2007 22:08:19 -0000, Paul wrote:

Anyone know why I'm getting this syntax error?

Syntax error (missing operator) in query expression
'DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

The idea is to display people who have birthdays in the next 7 days. I
just
can't see whats wrong with it.

Full code:
SELECT Clients.FirstName, Clients, Surname, DateSerial(Year(Date(),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date(), Month([DOB]), Day([DOB]) = Between Date()
And
Date() + 7;

Cheers,
Paul

1) You left off closing parenthesis around the Year() function in
DateSerial(Year(Date().... BOTH TIMES.
Should be DateSerial(Year(Date()),etc.
2) If you use the Between operator you do not use the =.
3) This comma (Select .... Clients, Surname) should be a dot:
..... Clients.Surname etc.

Try:

SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between
Date() And Date() + 7;
Click to expand...
Click to expand...

You left off another closing parentheses in the Where clause.
You had 5 "(" but only 4 ")".

Try again.
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

You have to count opening and closing parenthesis, so that the open (
minus the closing ) = 0.
Click to expand...
 
M

Matthias Klaey

Paul said:
Thanks Jeff,
It 'compiles' now but its giving me all 12 records from my Clients table,
records it is meant to show and records its not meant to show.

Example John Smith's date of birth is 21/03/1954 and his birthday shows up
on the query as being 21/03/2007. Although that would be the correct, I
don't want to display birthdays that have passed. The query is only
supposed to show people who's birthdays either today or in the next 7 days,
not days that have passed!
Any ideas?

I used this code (right number of parenthesis' this time!)
SELECT Clients.FirstName, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB]) Between Date() And
Date()) + 7;
Click to expand...

Try:

SELECT Clients.Firstname, Clients.Surname, DateSerial(Year(Date()),
Month([DOB]), Day([DOB])) AS Birthday
FROM Clients
WHERE DateSerial(Year(Date()), Month([DOB]), Day([DOB])) Between
Date() And Date()+7;

[...]


(parentheses parentheses (()((()()() ))) ;-)

HTH
Matthias Kläy
 

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

Current Date in Query 12
Still stumped - can't get a date (range). 6
Stumped with an upcoming birthdays query 16
Need help with Between dates on birthday calculation 9
Easy birthdays question... 1
Query By Month and Year - Access 2007 2
Query by Month and Year (Repost) 2
Filter criteria for a conditional serialdate field in a query 2

Top