By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Query Database where my date field is less than 31 days

P: 8
I am new to database queries. I am using access, trying to query my database where the field memberdate is less than 31 days old. I thought maybe the DateDiff function would work but I'm not sure how to use it correctly.

I would appreciate any help. Thanks
Nov 15 '06 #1
Share this Question
Share on Google+
25 Replies


100+
P: 1,646
I am new to database queries. I am using access, trying to query my database where the field memberdate is less than 31 days old. I thought maybe the DateDiff function would work but I'm not sure how to use it correctly.

I would appreciate any help. Thanks
Try this:
Expand|Select|Wrap|Line Numbers
  1. WHERE DATEDIFF(Day, [memberdate], GETDATE()) < 31
  2.  
Nov 15 '06 #2

P: 8
gwb
Sorry I guess i should have expanded on this a bit more. Thank you for your reply by the way. I am using this to login to a section on a website using asp. my sql statement to select only the records that meets the criteria is:

set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate='"DATEDIFF(Day, [memberdate], GETDATE() < 31"'")
Nov 15 '06 #3

100+
P: 1,646
Sorry I guess i should have expanded on this a bit more. Thank you for your reply by the way. I am using this to login to a section on a website using asp. my sql statement to select only the records that meets the criteria is:

set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate='"DATEDIFF(Day, [memberdate], GETDATE() < 31"'")
Here is the updated query
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND DATEDIFF(Day, [memberdate], GETDATE()) < 31"
  2.  
  3.  
Nov 15 '06 #4

P: 8
gwb
Here is the updated query
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND DATEDIFF(Day, [memberdate], GETDATE()) < 31"
  2.  
  3.  
I copied and pasted the way your code example was and then I noticed no closing parentheses so I also tried adding the closing parentheses, neither way worked it gave error below

Received This Error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GETDATE' in expression.
Nov 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
In place of GETDATE() use Date().
Nov 16 '06 #6

Expert 5K+
P: 8,434
In place of GETDATE() use Date().
A minor point (unless the data volume is huge, in which case it could be a major point).

It would make more sense, efficiency-wise, to work out the Date once then use the result in the WHERE clause, rather than forcing Access to calculate DateDiff for every record and then check the results.

In other words, rather than
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ("Select * from member where email='" _
  2. & email & "' and password='" & password & _
  3. "' AND DATEDIFF(Day, [memberdate], DATE()) < 31")
I believe something like this would make more sense...
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ("Select * from member where email='" _
  2. & email & "' and password='" & password & "' AND  [memberdate] >= #" _
  3. & DateAdd("d", -31, Date) & "#")
Note, I'm not certain about the syntax with the #'s around the date - may need to play with this, I always get it mixed up.
Nov 16 '06 #7

NeoPa
Expert Mod 15k+
P: 31,494
I believe something like this would make more sense...
Definitely.
A much better way to do it.

Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,'m/d/yyyy') & "#"))
Nov 16 '06 #8

P: 8
gwb
Definitely.
A much better way to do it.

Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,'m/d/yyyy') & "#"))

Thanks for the help! In the m/d/yyyy format it doesn't like the single quotes it seems
Now receiving this error:

Microsoft VBScript compilation error '800a03ea'

Syntax error

/verifypassword.asp, line 212

"AND ([memberdate]>#" & Format(Date()-31,'m/d/yyyy') & "#"))
-----------------------------------------^
Nov 16 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
No.
My bad.
Single-quotes are for data within the SQL string and this is not. It should be double-quotes.
Nov 16 '06 #10

P: 8
gwb
No.
My bad.
Single-quotes are for data within the SQL string and this is not. It should be double-quotes.
Here is revised code with double quotes:
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#"))
Generates this error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/verifypassword.asp, line 212

"AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#"))
----------------------------------------------------------------------------------^
Nov 16 '06 #11

Expert 5K+
P: 8,434
I think your closing brackets should be inside the quotes.
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))"
Nov 16 '06 #12

P: 8
gwb
I think your closing brackets should be inside the quotes.
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))"

Now receiving this error. Tried moving the last parentheses outside the quote that gave me the second error listed.

Error with both parentheses inside quote
Microsoft VBScript compilation error '800a03ee'

Expected ')'

/verifypassword.asp, line 212

"AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))"
-----------------------------------------------------------------------------------^

Second error with parentheses outside quote


Microsoft VBScript runtime error '800a000d'

Type mismatch: 'Format'

/verifypassword.asp, line 207
Nov 16 '06 #13

Expert 5K+
P: 8,434
Now receiving this error. ...
You're not talking to SQL Server or something, are you? These errors look very odd to me. However, I'm not familiar with VBscript so that may be why.

Perhaps it doesn't have a Format function? I suppose you could try using Format$ just to see what happens. Also, is it possible that you need to add a semicolon on the end of the SQL string, as happens in Access?

I suppose you could try leaving out all of the parentheses, too. They're probably not really necessary, but Access tends to throw in lots of them.
Nov 16 '06 #14

Expert 5K+
P: 8,434
New thought occurs... try this...
Expand|Select|Wrap|Line Numbers
  1. set rscheck = my_conn.Execute ( _
  2. "SELECT * " & _
  3. "FROM member " & _
  4. "WHERE (([email]='" & email & "') " & _
  5.   "AND ([password]='" & password & "') " & _
  6.   "AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))")
Nov 16 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
You're not talking to SQL Server or something, are you? These errors look very odd to me. However, I'm not familiar with VBscript so that may be why.

Perhaps it doesn't have a Format function? I suppose you could try using Format$ just to see what happens. Also, is it possible that you need to add a semicolon on the end of the SQL string, as happens in Access?

I suppose you could try leaving out all of the parentheses, too. They're probably not really necessary, but Access tends to throw in lots of them.
OK Back to your original query ...

set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate='"DATEDIFF(Day, [memberdate], GETDATE() < 31"'")
Change this to ...

set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)

Any problems let me know.

Mary
Nov 16 '06 #16

NeoPa
Expert Mod 15k+
P: 31,494
I'm sorry to say that my originally posted 'Try this' version had THREE errors in it.
Firstly, the quotes inside the Format should have been dbl-quotes.
Secondly, the ending ')' of the string should have been inside the string.
Thirdly, I completely forgot to close out the ')' for the My_Conn.Execute function call.

Luckily, Killer spotted each of these three in turn and his last post should definitely (?) be correct.
I suspect that Mary's code (without the tags tsk tsk) would probably work also, but the form originally posted by Killer DOES have the merit claimed, in that it processes the calculation of '31 days ago' just the once.
Nov 17 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm sorry to say that my originally posted 'Try this' version had THREE errors in it.
Firstly, the quotes inside the Format should have been dbl-quotes.
Secondly, the ending ')' of the string should have been inside the string.
Thirdly, I completely forgot to close out the ')' for the My_Conn.Execute function call.

Luckily, Killer spotted each of these three in turn and his last post should definitely (?) be correct.
I suspect that Mary's code (without the tags tsk tsk) would probably work also, but the form originally posted by Killer DOES have the merit claimed, in that it processes the calculation of '31 days ago' just the once.
Taking sides again, Adrian.



Power corrupts, absolute power corrupts absolutely.

I know you're right. One of these days my sql statements will consider preformance, one of these days.....

BTW, I think that other thread got sorted. No thanks to me.



Mary

P.S.

Tags .. I know, I know ... shame on me.

Nov 17 '06 #18

P: 8
gwb
OK Back to your original query ...



Change this to ...

set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)

Any problems let me know.

Mary
This code seems to process through the query but it sends me back to my login page. Just to make sure I am not messing this up, I need this statement to search the records in my access database and if the email and password match what is in the database and the memberdate (date member signed up) is not more than 31 days old, the member is granted access to the members area, if not the member is redirected back to the login page To make sure I am giving enough information I am including the code that is before and after the sql statement. (Note: The code worked when I was just checking for email and password as confirming the members account)
Expand|Select|Wrap|Line Numbers
  1.  
  2. set my_conn= Server.CreateObject("ADODB.Connection")
  3.         set rs = server.CreateObject("ADODB.RecordSet")
  4.         my_Conn.Open ConnString        
  5.  
  6.         set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)
  7.  
  8.  
  9.  
  10.         if rscheck.eof then
  11.             Response.redirect invalid_page
  12.             'invalid password and redirect to login page
  13.         else            
  14.         session("login") = "yes"
  15.         session("name") = rscheck("name")
  16.         'session("...") = rscheck("...")
  17.  
  18.         ' add session ..if you want to keep profile of your user.
  19.  
  20.         Response.redirect valid_login
  21.         ' password accept and go to memberpage.asp
  22.  
  23.         end if
  24.  
  25.         RScheck.close
  26.         rs.Close
  27.         my_conn.close
  28.         set my_conn = nothing
  29. end if
Thanks for your help.
Nov 17 '06 #19

NeoPa
Expert Mod 15k+
P: 31,494
Did you look at Killer's post?
It had what you needed.
Nov 17 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
I would usually use DAO, so if anyone notices any mistakes in this let me know.

You were confusing your code with additions and changes. I think this will simplify it for you. And as far as I'm aware you should be opening rather than executing the select statement as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim my_conn As ADODB.Connection
  3. Dim rs As New ADODB.Recordset
  4. Dim ConnString As String
  5. Dim strSQL As String
  6.  
  7.   ConnString = "xxxxxxxxx"
  8.   my_Conn.Open ConnString
  9.  
  10.   strSQL = ("Select * from member where email='" & email & "' and password='" & _
  11.        password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)
  12.  
  13.   Set rs = my_conn.Open strSQL, my_conn, adOpenDynamic, adLockOptimistic 
  14.  
  15.   If rs.eof then
  16.     Response.redirect invalid_page
  17.     'invalid password and redirect to login page
  18.   else            
  19.     session("login") = "yes"
  20.     session("name") = rs("name")
  21.     'session("...") = rs("...")
  22.  
  23.     ' add session ..if you want to keep profile of your user.
  24.  
  25.     Response.redirect valid_login
  26.     ' password accept and go to memberpage.asp
  27.  
  28.   End If
  29.  
  30.   rs.Close
  31.   Set rs = Nothing
  32.   my_conn.close
  33.   set my_conn = nothing
  34.  
  35. end if
  36.  
Nov 17 '06 #21

NeoPa
Expert Mod 15k+
P: 31,494
You need delimiters (#) around your date (which should be formatted as m/d/y) for it to work.
You can use between but as the second date is today (Date) anyway you may as well say > Date()-31.
Nov 18 '06 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
You need delimiters (#) around your date (which should be formatted as m/d/y) for it to work.
You can use between but as the second date is today (Date) anyway you may as well say > Date()-31.
Do'h!

Never though of that. Good Catch.

Mary
Nov 18 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Do'h!

Never though of that. Good Catch.

Mary
As per NeoPa's suggestion change this line of code to:

Expand|Select|Wrap|Line Numbers
  1.  
  2. strSQL = ("Select * from member where email='" & email & "' and password='" & _
  3.        password & "' AND memberdate>#" & DATE() - 31 & "#")
  4.  
  5.  
Just one thing. It does assume no memberdate is after today's date. If there is stick with the Between statement and just add the # characters arround the dates.
Nov 18 '06 #24

P: 8
gwb
As per NeoPa's suggestion change this line of code to:

Expand|Select|Wrap|Line Numbers
  1.  
  2. strSQL = ("Select * from member where email='" & email & "' and password='" & _
  3.        password & "' AND memberdate>#" & DATE() - 31 & "#")
  4.  
  5.  
Just one thing. It does assume no memberdate is after today's date. If there is stick with the Between statement and just add the # characters arround the dates.
That was the fix!!! Thank You All Very Much! I hope with time I'll be able to help out someone like you fine folks have helped me.

Thanks again to everyone who helped.
Nov 19 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
That was the fix!!! Thank You All Very Much! I hope with time I'll be able to help out someone like you fine folks have helped me.

Thanks again to everyone who helped.
You're welcome.

Mary
Nov 19 '06 #26

Post your reply

Sign in to post your reply or Sign up for a free account.