Newbie: Query problem

[tkila]

I am new to access and asp, and have tried to make a query to one existing access database in our web portal.
While trying to get in the database "Users" and the Query "bursdag" (That I made - to get BirthDay (Date/time) and UserName).

I am trying to get the BirthDay day and month and compare them with todays so it prints the UserName and a birthday greeting...

This is what I got so far:

<%
dag = day(now)
mnd = month(now)

OpenDB sConnUsers
sSQL = &quot;SELECT * FROM bursdag WHERE BirthDay = &quot; & formatdatetime((now),vbshortdate)

rs.Open sSQL,,,adCmdTable
if rs.eof or rs.bof then
response.write &quot;Ingen har bursdag i dag<br>&quot;
end if

do while not rs.eof and not rs.bof
hvem = rs(&quot;UserName&quot
dagen = rs(&quot;BirthDate&quot

If ((day(dagen) = day(dag)) AND (month(dagen)) = month(mnd)) then
Response.write(&quot; & hvem & &quot;&quot;har bursdag &quot;&quot; & BirthDay>&quot
end if

rs.movenext
loop
rs.close


%>


This is the error I get: (We have someone with birthday today..)

Microsoft JET Database Engine error '80040e14'

Syntax error in number in query expression 'BirthDay = 03.09.2002'.

 

[Tarwn]

Try this as your SQL statement:

sSQL = "SELECT * FROM bursdag WHERE BirthDay = #" & formatdatetime((now),vbshortdate) & "#"

Access prefers dates to be hash delimited when accessing/comparing date/time fields.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[Gorkem]

or if that doesn't work, try single quotes around the date.. ie:

... WHERE Birthday = '&quot; & formatdatetime((now), vbshortdate) & &quot;'&quot;


Cheers,

G.

 

[tkila]

Tried both with no luck.

It might be the scripting that has the fault, because I am terrible in asp....

 

[Gorkem]

Is there any reason why you are reformating the date? SQL and Access both can take full text, long and short dates and convert it itself.

For instance.. If I were to pass January 15, 2002 or 01/15/02 or 15-Jan-2002, SQL would understand that all to be the same as its own date format -> 01/15/02.

Is your date field set to handle dates?

From the error message that you have posted, it seems as though its expecting a number. You should really change that to a DATE/TIME field.

Hope this solves the problem.

Cheers,

Gorkem.

 

[Tarwn]

Using hashes for dates is pretty standard with access, but one problem you may be having is that when you are comparing dates it is also comparing the years, so unless someone was born today it won't work as a comparison. Try breaking it down into month and day only. You can get the current month and day from ASP by using:
Day(Now)
Month(Now)
So you could make a query like so:

sSQL = "SELECT * FROM bursdag WHERE Month(BirthDay) = " & Month(Now) & " Day(BirthDay) = " & Day(Now)

Now you could do the entire thing in your sql statement, the reason I concatenated from the ASP instead is so that you could allow someone to enter a month and year and then use those entries instead. I apologize for not noticing the year inconsistency above, my mistake.
If you have any more problems, post the full error message so we can give it another shot
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[tkila]

Tank you for good ideas, this is what I got now, but still not working:

FYI: The dates are stored in Date/Time format, like 04.09.2002 (DD.MM.YYYY)


<%

OpenDB sConnUsers
sSQL = &quot;SELECT * FROM bursdag WHERE Month(BirthDay) = &quot; & Month(Now) & &quot; Day(BirthDay) = &quot; & Day(Now)
rs.Open sSQL,,,adCmdTable
if rs.eof or rs.bof then
response.write &quot;Ingen har bursdag i dag<br>&quot;
end if



do while not rs.eof and not rs.bof
hvem = rs(&quot;UserName&quot
dagen = rs(&quot;BirthDate&quot
Response.write(&quot; & hvem & &quot;&quot;har bursdag &quot;&quot; & dagen&quot

rs.movenext
loop
rs.close



%>


Error message:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'Month(BirthDay) = 9 Day(BirthDay) = 4'.

Any help/comments are apprisiated?

 

[Gorkem]

Your SQL statement should be changed to reflect the following:

sSQL = &quot;SELECT * FROM bursdag WHERE Month(BirthDay) = &quot; & Month(Now) & &quot; AND Day(BirthDay) = &quot; & Day(Now)

 

[Tarwn]

Eh, Sorry, my mistake, I must have been typing to fast
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[tkila]

Wow, tanx guys, its up and running.
I couldnt have done it without your help.