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

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


"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
>
>

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;

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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" <(E-Mail Removed)> wrote in message
news:181vf5a25el1n$.(E-Mail Removed)...
> 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;
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:181vf5a25el1n$.(E-Mail Removed)...
>> 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;
>>
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:181vf5a25el1n$.(E-Mail Removed)...
>>> 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;
>>>
>>> --
>>> Fred
>>> Please respond only to this newsgroup.
>>> I do not reply to personal e-mail
>>
>
>

On Fri, 23 Mar 2007 23:29:33 -0000, Paul wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:181vf5a25el1n$.(E-Mail Removed)...
>> 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;
>>
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail

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.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Paul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:181vf5a25el1n$.(E-Mail Removed)...
>>>> 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;
>>>>
>>>> --
>>>> Fred
>>>> Please respond only to this newsgroup.
>>>> I do not reply to personal e-mail
>>>
>>
>>
>

Fantastic, thanks Fred! I can sleep easy now!

And thanks all you guys too,
Paul


"fredg" <(E-Mail Removed)> wrote in message
news:ekj7ld6c977q$.(E-Mail Removed)...
> On Fri, 23 Mar 2007 23:29:33 -0000, Paul wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:181vf5a25el1n$.(E-Mail Removed)...
>>> 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;
>>>
>>> --
>>> Fred
>>> Please respond only to this newsgroup.
>>> I do not reply to personal e-mail
>
> 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.
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

fgfg
"Paul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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