SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Getting records from last week or month

    I'm using MS Access (yeah, silly, I know) to store news from a site, I have a Date/Time field which I would like to use to get records from the last week or month but I can seem to put together a query that can pull this off

    Anyone got any suggestions ?

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I'm assuming you know how to use SQL to rtrieve records from access, you're just having problems building the WHERE part of the SQL?
    Code:
    function DateToAccess(strOldValue)
    
    	'strOldValue=DateAdd("yyyy", -1, dMyDate)
    
    	strMonth=month(strOldValue)
    	strDay=day(strOldValue)
    	strYear=year(strOldValue)
    
    	strResult="#" & strMonth & "/" & strDay & "/" & strYear & "#"
    
    	DateToAccess = strResult
    end function
    So your SQL should look something like

    Code:
    Select * from TABLE where DATEFIELD Between #1/5/2000# And #2/5/2001#
    You get the date values by using the function I provided:
    Code:
    strStartDate = DateToAccess("12 / 31 / 02")
    strEndDate = DateToAccess("01 / 31 / 03")
    And plug the return values into the SQL string.

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I have some more functions you can use for access which really speed the coding up.

    Code:
    'returns date from one Month ago
    function LastMonth()
    	dMyDate=DateAdd("m", -1, date)
    	LastMonth=global_MakeDBFriendly(dMyDate)
    end function
    
    'returns date from one week ago
    function LastWeek()
    	dMyDate=DateAdd("d", -7, date)
    	LastWeek=global_MakeDBFriendly(dMyDate)
    end function
    
    'returns date from three days ago
    function LastThreeDays()
    	dMyDate=DateAdd("d", -3, date)
    	LastThreeDays=global_MakeDBFriendly(dMyDate)
    end function
    
    'returns Todays Date
    function TodaysDate()
    	TodaysDate=global_MakeDBFriendly(Date)
    end function
    
    
    ' This function calculates the first day of a month, given a date.
    function FirstOfMonth( dteDate )
          FirstOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)
    end function
    
    ' This function calculates the last day of a month, given a date.
    function LastOfMonth( dteDate )
          LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
    end function

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And the function referenced above is here too

    Code:
    	function global_MakeDBFriendly(strOldValue)
    
    		strMonth=month(strOldValue)
    		strDay=day(strOldValue)
    		strYear=year(strOldValue)
    		strResult="#" & strMonth & "/" & strDay & "/" & strYear & "#"
    
    		global_MakeDBFriendly = strResult
    	end function
    Which is the same as the function in my first post, just a different name.

    [Edited the code to make the date Format access comatible]
    Last edited by asterix; Aug 7, 2003 at 04:23.

  5. #5
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sweeeeeeet thanks a lot dude. I'll definately be printing that out for reference


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •