|
| |
**SOLVED**how to NOT show records that are over 1 month old??
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
kwfields
Posts: 62
Joined: 10/11/2005
Status: offline
 |
**SOLVED**how to NOT show records that are over 1 month... - 11/15/2005 12:01:33
I use access DB and I am trying to make the results form not show any records over 1 month old. I am using my "Shipdate" column for a reference so that anything that has shipped will not show if it is over 1 month old.
here is my SQL:
fp_sQry="SELECT * FROM JobStat WHERE CustID = "&Session("Userid")
I have read several books on SQL and havent found what I'm looking for yet.
any help would be appreciated.
I also have searched this forum but havent found anything that will work for what I need.
Thank you,
Kwfields
< Message edited by kwfields -- 11/17/2005 10:14:40 >
|
|
|
|
dpf
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 12:04:58
quote:
FROM JobStat WHERE CustID = "&Session("Userid") does this user have multiple records that would pull in the search - some more the 30 days and some less and that is what you are trying to filter? if so you would use the AND to add that additional factor
(note this isnt correct syntax im giving - just s thought) where user id = session AND where date is less than 30 days. does that help at all?
_____________________________
Dan
| |
|
|
|
kwfields
Posts: 62
Joined: 10/11/2005
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 12:12:43
that is exactly the scenerio i am looking for.
I'm looking for help with the syntax.
I have one more book to look in but it is at home.
| |
|
|
|
ou812
Posts: 1601
Joined: 1/5/2002
From: San Diego
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 13:44:22
Maybe something like this:
"SELECT * FROM JobStat WHERE CustID = " & Session('Userid') & " and shipdate >= " & date()-30
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
kwfields
Posts: 62
Joined: 10/11/2005
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 14:20:18
quote:
& " and shipdate >= " & date()-30
this still shows records over 30 days old.
But i get no errors.
FYI my shipdate is in the DATE format and not TEXT.
< Message edited by kwfields -- 11/15/2005 14:33:08 >
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 15:55:03
quote:
& date()-30
How about:
DateAdd("d",-30,Date())
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/15/2005 16:54:57
Or try if that doesn't work maybe (DATE()-30)
| |
|
|
|
kwfields
Posts: 62
Joined: 10/11/2005
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/17/2005 8:51:11
I have tried both statements below and neither work.
I put in a test job with a ship date of 9/15/05 well over 30 days of todays date.
"SELECT * FROM JobStat WHERE CustID = " & Session('Userid') & " and shipdate >= " & date()-30
fp_sQry="SELECT * FROM JobStat WHERE CustID = "&Session("Userid") &"and shipdate >=" &(DATE()-30)
fp_sQry="SELECT * FROM JobStat WHERE CustID = "&Session("Userid") &"and shipdate >=" &DateAdd("d",-30,Date())
I have also tried changing the "d" to "m" and the interval to -1 still no good.
I feel I'm losing this battle, but I am learning alot in the process.
any more suggestions???
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/17/2005 9:31:52
quote:
fp_sQry="SELECT * FROM JobStat WHERE CustID = "&Session("Userid") &"and shipdate >=" &DateAdd("d",-30,Date())
Try this one:
fp_sQry="SELECT * FROM JobStat WHERE (CustID = "&Session("Userid") &") and (shipdate >= #" &DateAdd("d",-30,Date()) & "#)"
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/17/2005 9:58:32
quote:
Could you explain the "#" ?? what does it do or mean??
Generally with databases you need to use "delimiters" when sending data. With Access the delimiters are:
Number = (no delimiters)
Text = '
Date/Time = #
so a typical DRW insert might look like:
"INSERT INTO myTable (myNumberField,myTextField,myDateField) VALUES (::myNumberField::,'::myTextField::',#::myDateField::#)"
That's with Access. SQL Server and mySQL use apostrophes for Date/Time delimiters as well as text.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
kwfields
Posts: 62
Joined: 10/11/2005
Status: offline
|
RE: how to NOT show records that are over 1 month old?? - 11/17/2005 10:12:53
AHHHHH,,
Thanks for the information. I understand now.
Once again this forum has been a great help to me.
Thanks ,
Ken
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
