Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Text Date using Mid Function (Access97)

    I am back with as crazy as ever problem.
    I have Linked table that has [Pay date] column that is Text datatype.
    It is also contains mm/dd/yyyy and mmddyyy format enties.
    I wrote Select Query where [Pay Date] should be converted to a yy/mm/yyyy format where I am using formula
    IIf(InStr([Pay Date],'/')>0,DateValue([Pay Date]),DateSerial(Right([Pay Date],4),Mid([Pay Date],4,2),Left([Pay Date],2)))

    SELECT [master-data-charges1].[Pay Date]
    FROM [master-data-charges1]
    GROUP BY [master-data-charges1].[Pay Date]
    HAVING ((([master-data-charges1].[Pay Date])=IIf(InStr([Pay Date],'/')>0,DateValue([Pay Date]),DateSerial(Right([Pay Date],4),Mid([Pay Date],4,2),Left([Pay Date],2)))));

    and

    SELECT [master-data-charges1].[Pay Date]
    FROM [master-data-charges1]
    WHERE ((([master-data-charges1].[Pay Date])=IIf(InStr([Pay Date],'/')>0,DateValue([Pay Date]),DateSerial(Right([Pay Date],4),Mid([Pay Date],4,2),Left([Pay Date],2)))))
    GROUP BY [master-data-charges1].[Pay Date];

    Those queries will omit mmddyyyy records alltogeather .

    What am I doing wrong this time?
    Thanks

    Exp1ateValue([Pay date]) gives me #Error for ddmmyyyy records

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Text Date using Mid Function (Access97)

    It doesn't make sense to place the converted date value in the criteria. I also don't understand why you created a group by query. Finally, the formula

    DateSerial(Right([Pay Date],4),Mid([Pay Date],4,2),Left([Pay Date],2))

    is wrong in two places: it assumes ddmmyyyy format, and the middle part should be Mid([Pay Date],3,2). Here is the SQL for a query that returns a date value:

    SELECT IIf(InStr([Pay Date],'/')>0,DateValue([Pay Date]),DateSerial(Right([Pay Date],4),Left([Pay Date],2),Mid([Pay Date],3,2))) AS PayDate
    FROM [master-data-charges1];

    It would be a good idea to get a beginner's book on Access and study it.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Text Date using Mid Function (Access97)

    Thanks again and I do have a book. I am stumbling upon things sometimes and can't see an exit.
    Trust me I've created whole Reporting System so far that will allow to choose and pick Reports and zip those and send to recipients on the list...and it does, but but sometimes I am lost and Thanks a lot and good weekends to all.

Posting Permissions

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