Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003

    Unanswered: to get range of values from a date converted string

    First, Iam converting text field 0302-112345 to date field where 0302 is yymm. I need to select range of values , when the user enters 0302 in first field and 0305 in the second I need to get values in between these parameters. Any help is appreciated

    PARAMETERS[Date to summerise] short, [Date to summerise1] short;
    SELECT tblStock.StockID, tblStock.Description, tblStock.UnitOfMeasureID,
    Format(CDate("00" & Mid(tblPattern.BaleNo,3,2) &
    Mid(tblPattern.BaleNo,1,2)),'yymm') AS Expr1,
    tblPattern.Pattern, tblPattern.Dye, tblPattern.QuantityReceived,
    tblPattern.Quantity, tblPattern.JobID, tblPattern.SBN, tblPattern.Notation,
    tblPattern.LocationID, tblPattern.ActualCost, [Quantity]*[ActualCost] AS
    Total, zz_RepPar.StockStatusID, tblPattern.USCCategoryID,
    FROM tblStock RIGHT JOIN ((zz_RepPar LEFT JOIN tlkpStockStatus ON
    zz_RepPar.StockStatusID = tlkpStockStatus.StockStatusID) LEFT JOIN
    tblPattern ON zz_RepPar.StockStatusID = tblPattern.StockStatusID) ON
    tblStock.StockID = tblPattern.StockID
    WHERE Format(CDate("00" & Mid((tblPattern.BaleNo),3,2) &
    Mid((tblPattern.BaleNo),1,2)),'yymm') >= [Date to summerise] And
    Format(CDate("00" & Mid((tblPattern.BaleNo),3,2) &
    Mid((tblPattern.BaleNo),1,2)),'yymm') <=
    [Date to summerise1] AND ((tblPattern.Quantity)<>0);

  2. #2
    Join Date
    May 2003
    New York, NY

    Re: to get range of values from a date converted string

    I assume you want to select records for a particular month so why not just let the user type in a valid date for the month that contains the data that they need then create the dates that you need. Example:

    The User types into Field1 = 03/23/03

    StartDate = Dateserial(Year(Field1),Month(Field1),1)
    'This statement will take the year and the month return the first
    day of the month i.e. 03/01/03

    EndDate = (DateAdd("M",1,[StartDate])-1)
    'This will add one month to the start date then it will subtract one day
    which will be the last day of the month

    You can use the StartDate and the EndDate for your query.

    if you need data from another time period (i.e. previous year you can use the DateAdd function to create these values:

    PreviousStartDate = DateAdd("yyyy",-1,[StartDate])
    PreviousEndDate = DateAdd("yyyy",-1,[EndDate])

    I hope this helps!


Posting Permissions

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