**SOLVED**how to NOT show records that are over 1 month old?? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

kwfields -> **SOLVED**how to NOT show records that are over 1 month old?? (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



dpf -> 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?



kwfields -> 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 -> 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




kwfields -> 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.



rdouglass -> 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?



jgeatty -> 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 -> 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 -> 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()) & "#)"



kwfields -> RE: how to NOT show records that are over 1 month old?? (11/17/2005 9:45:12)

quote:

fp_sQry="SELECT * FROM JobStat WHERE (CustID = "&Session("Userid") &") and (shipdate >= #" &DateAdd("d",-30,Date()) & "#)"


That works!!

Thanks, I was really close to what you came up with reading the MSDN,

I am one of those guys who have to have knowledge beat into him but once learned it is there forever[:D]

Could you explain the "#" ?? what does it do or mean?? May be a dumb question but I just dont understand that portion of the code.

Thanks,

Ken



rdouglass -> 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?



kwfields -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125