Convert Text Date using Mid Function (Access97)

Topic

New Reply

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

Exp1:DateValue([Pay date]) gives me #Error for ddmmyyyy records

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote