# Thread: Convert Text Date using Mid Function (Access97)

1. ## 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. ## 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. ## 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
•