Converting a text date to normal date in a query

[Duncs]

I have inherited a database with the following structure:

tblMovements
fldName Text
fldDate Text
fldStatus Text

So, some example date looks like:

John Smith 20080423 In
Paul Webb 20071124 Discharged
Jack Brown 20080423 In
John Smith 20080516 Out
Paul Webb 20080101 On Loan
John Smith 20080801 Discharged

What I want to do, is display the names and the latest status / date
for each person. For example:

John Smith Discharged 20080801
Paul Webb On Loan 20080101
Jack Brown In 20080423

However, since the dates are held as text—don’t ask why, as I don’t
know—I can’t use the ‘Max’ or ‘Last’ functions in a query to get the
latest date.

Can someone help?

TIA

Duncs

 

[Dale Fye]

Actually, given the way your fldDate is organized (yyyymmdd), you should be
able to use MAX, but I wouldn't use Last.

To convert the string to a date try:

dateserial(Left(strDate,4), Mid(strDate,5,2), Mid(strDate, 7))

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Forgot to answer your other question.

You should still be able to do the following:

SELECT yourTable.*
FROM yourTable
INNER JOIN (SELECT fldName, MAX(fldDate) as MaxDate
FROM yourTable
GROUP BY fldName) as Temp
ON yourTable.fldName = Temp.fldName
AND yourTable.fldDate = Temp.Maxdate

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Hey Dale,
Wanna see a unique way to do that?
Try this just for fun:

CDate(Format("20080525","0000/00/00"))

 

[Dale Fye]

Dave, I had tried:

Format("20080930", "yyyy/mm/dd")

But that didn't work. Never thought about using zeros. Thanks, Dave. I'll
but that one in my kit bag.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John W. Vinson]

I have inherited a database with the following structure:

tblMovements
fldName Text
fldDate Text
fldStatus Text

So, some example date looks like:

John Smith 20080423 In
Paul Webb 20071124 Discharged
Jack Brown 20080423 In
John Smith 20080516 Out
Paul Webb 20080101 On Loan
John Smith 20080801 Discharged

What I want to do, is display the names and the latest status / date
for each person. For example:

John Smith Discharged 20080801
Paul Webb On Loan 20080101
Jack Brown In 20080423

However, since the dates are held as text—don’t ask why, as I don’t
know—I can’t use the ‘Max’ or ‘Last’ functions in a query to get the
latest date.

Your problem is not (specifically) that the field isn't date/time; as a
yyyymmdd date, it at least will sort chronologically. Create a Totals query on
the table, Group By fldName and select Max of fldData; create another query
joining your table to this query by fldName and fldDate.

Question: do you have other fields identifying people? If not, what will you
do if you have two different people who happen to both be named Fred Brown? I
know three such people right here in the little town of Parma.

 

[John W. Vinson]

I have inherited a database with the following structure:

tblMovements
fldName Text
fldDate Text
fldStatus Text

So, some example date looks like:

John Smith 20080423 In
Paul Webb 20071124 Discharged
Jack Brown 20080423 In
John Smith 20080516 Out
Paul Webb 20080101 On Loan
John Smith 20080801 Discharged

Should have added... to create a Date/Time field from your text date, you can
add a date/time field and run an Update query updating it to

CDate(Format(fldDate, "@@@@-@@-@@"))

 

[Rick Brandt]

Dave, I had tried:

Format("20080930", "yyyy/mm/dd")

But that didn't work. Never thought about using zeros. Thanks,
Dave. I'll but that one in my kit bag.

Format(SomeValue, "yyyy/mm/dd")

....would have worked if SomeValue were already a DateTime. In your case it
was not.