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
25 3980
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: -
WHERE DATEDIFF(Day, [memberdate], GETDATE()) < 31
-
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"'")
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 -
set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND DATEDIFF(Day, [memberdate], GETDATE()) < 31"
-
-
Here is the updated query -
set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND DATEDIFF(Day, [memberdate], GETDATE()) < 31"
-
-
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.
NeoPa 32,556
Expert Mod 16PB
In place of GETDATE() use Date().
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 -
set rscheck = my_conn.Execute ("Select * from member where email='" _
-
& email & "' and password='" & password & _
-
"' AND DATEDIFF(Day, [memberdate], DATE()) < 31")
I believe something like this would make more sense... -
set rscheck = my_conn.Execute ("Select * from member where email='" _
-
& email & "' and password='" & password & "' AND [memberdate] >= #" _
-
& 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.
NeoPa 32,556
Expert Mod 16PB
I believe something like this would make more sense...
Definitely.
A much better way to do it. - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"AND ([memberdate]>#" & Format(Date()-31,'m/d/yyyy') & "#"))
Definitely.
A much better way to do it. - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"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') & "#"))
-----------------------------------------^
NeoPa 32,556
Expert Mod 16PB
No.
My bad.
Single-quotes are for data within the SQL string and this is not. It should be double-quotes.
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: - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"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") & "#"))
----------------------------------------------------------------------------------^
I think your closing brackets should be inside the quotes. - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))"
I think your closing brackets should be inside the quotes. - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"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
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.
New thought occurs... try this... - set rscheck = my_conn.Execute ( _
-
"SELECT * " & _
-
"FROM member " & _
-
"WHERE (([email]='" & email & "') " & _
-
"AND ([password]='" & password & "') " & _
-
"AND ([memberdate]>#" & Format(Date()-31,"m/d/yyyy") & "#))")
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
NeoPa 32,556
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.
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.
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) -
-
set my_conn= Server.CreateObject("ADODB.Connection")
-
set rs = server.CreateObject("ADODB.RecordSet")
-
my_Conn.Open ConnString
-
-
set rscheck = my_conn.Execute ("Select * from member where email='" & email & "' and password='" & password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)
-
-
-
-
if rscheck.eof then
-
Response.redirect invalid_page
-
'invalid password and redirect to login page
-
else
-
session("login") = "yes"
-
session("name") = rscheck("name")
-
'session("...") = rscheck("...")
-
-
' add session ..if you want to keep profile of your user.
-
-
Response.redirect valid_login
-
' password accept and go to memberpage.asp
-
-
end if
-
-
RScheck.close
-
rs.Close
-
my_conn.close
-
set my_conn = nothing
-
end if
Thanks for your help.
NeoPa 32,556
Expert Mod 16PB
Did you look at Killer's post?
It had what you needed.
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: -
-
Dim my_conn As ADODB.Connection
-
Dim rs As New ADODB.Recordset
-
Dim ConnString As String
-
Dim strSQL As String
-
-
ConnString = "xxxxxxxxx"
-
my_Conn.Open ConnString
-
-
strSQL = ("Select * from member where email='" & email & "' and password='" & _
-
password & "' AND memberdate BETWEEN " & DATE() & " AND " & DATE() - 31)
-
-
Set rs = my_conn.Open strSQL, my_conn, adOpenDynamic, adLockOptimistic
-
-
If rs.eof then
-
Response.redirect invalid_page
-
'invalid password and redirect to login page
-
else
-
session("login") = "yes"
-
session("name") = rs("name")
-
'session("...") = rs("...")
-
-
' add session ..if you want to keep profile of your user.
-
-
Response.redirect valid_login
-
' password accept and go to memberpage.asp
-
-
End If
-
-
rs.Close
-
Set rs = Nothing
-
my_conn.close
-
set my_conn = nothing
-
-
end if
-
NeoPa 32,556
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.
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
Do'h!
Never though of that. Good Catch.
Mary
As per NeoPa's suggestion change this line of code to: -
-
strSQL = ("Select * from member where email='" & email & "' and password='" & _
-
password & "' AND memberdate>#" & DATE() - 31 & "#")
-
-
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.
As per NeoPa's suggestion change this line of code to: -
-
strSQL = ("Select * from member where email='" & email & "' and password='" & _
-
password & "' AND memberdate>#" & DATE() - 31 & "#")
-
-
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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:
...
|
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...
|
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...
|
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....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |