Results 1 to 9 of 9
  1. #1
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    how to convert date period in single column in rows

    Dear experts,



    to convert date period in rows which function i can use in access like 01-01-13 to 15-01-13 this fifteen days period how can i get in query

    in rows.

    01-01-13
    02-01-13
    03-01-13
    04-01-13
    05-01-13
    .....
    .....

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,553
    What are you trying to do in plain English?
    Provide a sample if that helps.

  3. #3
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    i have two columns datefrom and dateto 01-01-13 to 15-01-13 this date period i have in table in columns. in query i want to get result as below

    01-01-13
    02-01-13
    03-01-13
    04-01-13 upto 15-01-13 it will be fifteen rows i think now it will be clear. my question was which function i have to use to get this result in access database

  4. #4
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    please have a look on my request

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,553
    ???I'm looking. What am I suppose to see???
    i think now it will be clear
    Not clear to me.

    Show us all the fields in the table and what fields you want in the result.

  6. #6
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    my fields are below.
    ID , DATEFROM, DATETO
    23 , 01-01-12 , 15-01-12

    result should come as below
    23 , 01-01-12
    23, 02-01-12
    23, 03-01-12
    23, 04-01-12
    .......upto......
    23, 15-01-12

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,553
    Here is a sample routine to show each Date between DateFrom and DateTo

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : waqas
    ' Author    : Jack
    ' Date      : 27/02/2013
    ' Purpose   : To display all Dates between DateFrom until and including DateTo
    '
    'OP https://www.accessforums.net/queries/how-convert-date-period-single-column-rows-32787.html#post161423
    '---------------------------------------------------------------------------------------
    '
    Sub waqas()
              Dim DateFrom As Date
              Dim DateTo As Date
              Dim DateToShow As Date
    10       On Error GoTo waqas_Error
             '  Date format depends on your settings
    20        DateFrom = #1/1/2012#   'sample dateFrom
    30        DateTo = #1/15/2012#    'sample DateTo
    40        DateToShow = DateSerial(Year(DateFrom), Month(DateFrom), Day(DateFrom))
              ' Debug.Print DateToShow
    50        While DateToShow <= DateTo
    60            Debug.Print DateToShow
    70            DateToShow = DateAdd("d", 1, DateToShow)
    80        Wend
    
    90       On Error GoTo 0
    100      Exit Sub
    
    waqas_Error:
    
    110       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure waqas of Module AWF_Related"
    End Sub

  8. #8
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    thanks fine,

    but i want to do by query.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,553

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Alert on Existing Date Period
    By waqas in forum Access
    Replies: 5
    Last Post: 02-03-2013, 10:38 PM
  2. Convert multiple rows to columns?
    By NelsonKauley in forum Programming
    Replies: 2
    Last Post: 04-04-2012, 04:59 PM
  3. Replies: 6
    Last Post: 01-13-2012, 09:17 AM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Getting Tax Period from current date
    By crxftw in forum Forms
    Replies: 3
    Last Post: 07-09-2011, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums