473,289 Members | 1,959 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Query Database where my date field is less than 31 days

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
25 3977
willakawill
1,646 1GB
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
gwb
8
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
willakawill
1,646 1GB
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
gwb
8
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
32,554 Expert Mod 16PB
In place of GETDATE() use Date().
Nov 16 '06 #6
Killer42
8,435 Expert 8TB
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
32,554 Expert Mod 16PB
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
gwb
8
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
32,554 Expert Mod 16PB
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
gwb
8
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
Killer42
8,435 Expert 8TB
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
gwb
8
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
14,534 Expert Mod 8TB
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
32,554 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
gwb
8
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
32,554 Expert Mod 16PB
Did you look at Killer's post?
It had what you needed.
Nov 17 '06 #20
MMcCarthy
14,534 Expert Mod 8TB
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
32,554 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
gwb
8
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
14,534 Expert Mod 8TB
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

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

Similar topics

14
by: deko | last post by:
This runs, but does not narrow to current week. suggestions appreciated! SELECT lngEid, dtmApptDate, Subject, Appt_ID FROM qry002 WHERE (dtmApptDate BETWEEN DateAdd("d",-weekday()+2,) And...
2
by: Willem | last post by:
Hi there, I'm sort of new with doing much record manipulation with queries. Up till now I've been programming VBA and doing record looping to get my results. This works fine but tends to get...
2
by: Richard New to Access | last post by:
For the Past few days I have been trying to create Query, and everyone I have asked have been obtuse with the help. I am a new to access and still learning, so it is probably that stops me from...
4
by: C White | last post by:
Hi I am having problems with running a query that does the following there are 5 fields in a table that the query is based on, the first four are simple enough and all that happens is that the...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
11
by: Andy_Khosravi | last post by:
My problem: I'm having trouble with a query taking much too long to run; a query without any criteria evaluating only 650 records takes over 300 seconds to run (over the network. On local drive...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
21
by: jennwilson | last post by:
Ok - So, I am back. I would like to count the number of times a specific record appears in a field from one table in my query and then use that value in the same query to calculate an average....
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.