I have a user login form where the user can log in. There is also a hidden field that is adding to the database when the user has logged in with the <%=NOW()%> function.

Now I want to create a SQL query where I can filter the date and times out. The date is formatted like this: "1-5-2005 18:00:00". I cannot get my code to work. It works perfectly if I only put the date in the database instead of Now() but I want also to filter on time.

I have tried several things like #date# and changing the Access database objectes.. but I can't get it to work. Does anybody has an idea how to solve this? The basis thing is: I want to select a field from a recordset what has been filled by the NOW() function.

Thanks in advance!

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>

Hi Rene

What is the datatype of the field in the db?

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
What is the datatype of the field in the db?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

The datatype is an Access Date Time field with the standard form notation in Dutch: "4-5-2005 10:11:26" (the fourth of May). I want to select an exact date and time but cannot get it to work.

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>

Hi Rene

This will give me all records with the hour of 16:00 regardless of date, this might get you going in the right direction. Post back if you need something else.

SELECT Table1.col_date
FROM Table1
WHERE DATEPART("h", Table1.col_date) = '16';



Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

I also tried the DATEPART function of SQL and tried to make a whole date with different DATEPARTs. But it is somewhat stupid that I have to slove the problem by making a very long SQL DATEPART string <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>

Hi Rene

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>I also tried the DATEPART function of SQL and tried to make a whole date with different DATEPARTs. But it is somewhat stupid that I have to slove the problem by making a very long SQL DATEPART string <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Seems about right!

Although I think Access support separate Date and Time fields, SQL doesn't, so I wonder if it would be better to split the date and time out into different columns.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
...it would be better to split the date and time out into different columns.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Yes, I thought the same! And when are also thinking on this solution I will re-build my code and database layout! Thanks for your help!!

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>