Date as Text issues

[akirekab]

I am working on someone elses database. The table has a Date and Time field as Text.

They are using the following which is not working when it comes to order by, because of the fact they built the table with wrong data type. That being said here is the order by code and I am haveing problems using CDate with which I think should work one way or another.

Orig Code

ORDER BY test03.[Last Name], test03.[First Name],test03.Date, test03.Time;

This is what I would like to try to do.

ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.Date), test03.Time;

This code is located in the Property of the form used to search, in the guery build area of the Record Source.

I have read lots othe the posts on here and tried to emulate, but really would just like to understand how to get this text field to sort as needed, and understand it.
Thank you
Ken

[FishVal]

Hi, Ken.

Additional difficulty arises from that date and time are stored in different fields.
Try the following.

ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.[Date]) + CDate(test03.[Time]);

BTW, Date and Time are reserved words. It is better to enclose them in square brackets.

Regards,
Fish

[akirekab]

Hi, Ken.

Additional difficulty arises from that date and time are stored in different fields.
Try the following.

ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.[Date]) + CDate(test03.[Time]);

BTW, Date and Time are reserved words. It is better to enclose them in square brackets.

Regards,
Fish

Thank you for you effort, appreciate it. I am taking this to the bank and will let you know later if if works.
Ken

[FishVal]

Not a problem.
Good luck.

[akirekab]

Not a problem.
Good luck.

Fishval I put this in and got the following error"Type data mismatch in expression"
Now the Date and Time are in separate fields in the table , both as text.

The expression runs fine without the CDate in it, unfortunately just doesnt sort the way it is needed.

[akirekab]

Fishval I put this in and got the following error"Type data mismatch in expression"
Now the Date and Time are in separate fields in the table , both as text.

The expression runs fine without the CDate in it, unfortunately just doesnt sort the way it is needed.

Fish also I am finding as I look thru data, that some of the fields are empty, in case that would change things.

[FishVal]

Try to use Nz() function.
e.g.
CDate(Nz([Date],0))+CDate(Nz([Time],0))

[akirekab]

Try to use Nz() function.
e.g.
CDate(Nz([Date],0))+CDate(Nz([Time],0))

I will try that, but may I ask, since we are concantenating the to functions, does that mean they will only check on field, rather than both the date and time field separately.

[akirekab]

I will try that, but may I ask, since we are concantenating the to functions, does that mean they will only check on field, rather than both the date and time field separately.

I still got "data Type Mismatch in Criteria Expression" Here is full SQL

(CODE)
SELECT Not ((Len(Trim(([outside_order])))<1) Or (IsNull([outside_order]))) AS outside, test03.* FROM test03 ORDER BY test03.[Last Name], test03.[First Name], CDate(Nz(test03 .Date,0))+CDate (Nz(test03.Time ,0));
(/CODE)

Thank you

[FishVal]

Well.

I've tested it thoroughly and found that it fails with the same error if CDate() fails to recognize a value in some record as date/time.

e.g.
18:00 and 18.00 converts just fine, 18-00 raises the error
and so on.

Beside this, the expression works fine.

[akirekab]

Well.

I've tested it thoroughly and found that it fails with the same error if CDate() fails to recognize a value in some record as date/time.

e.g.
18:00 and 18.00 converts just fine, 18-00 raises the error
and so on.

Beside this, the expression works fine.

Thanks again,,,I wont bother you anymore, with this, I am going to try to go thru the data and clean the fields. My utmost fear is that I will wipe out records at some point trying to do that.

Can I assume it will react positively to 00.00.0000 or 00:00

[FishVal]

Thanks again,,,I wont bother you anymore, with this, I am going to try to go thru the data and clean the fields. My utmost fear is that I will wipe out records at some point trying to do that.

Can I assume it will react positively to 00.00.0000 or 00:00

00.00.0000 will be recognized by CDate() as time, not date
00:00 is just fine as time format

you may play around with CDate() function in VBA immediate window to figure out how does it work

Examples:
? CDate("01.01.07 ")
1:01:07 AM
? CDate("01.01.20 07")
<error raised>
? CDate("01-Jan-7")
1/1/2007
? CDate("18:00")
6:00:00 PM
? CDate("18.00")
6:00:00 PM
? CDate("1/1/1")
1/1/2001
? CDate("18-00")
<error raised>

I am going to try to go thru the data and clean the fields.

I hope you are not going to do all it manually. ;) Update queries and Replace() function can make almost all the job.

P.S. You are quite welcome to bother more. :)

[akirekab]

00.00.0000 will be recognized by CDate() as time, not date
00:00 is just fine as time format

you may play around with CDate() function in VBA immediate window to figure out how does it work

Examples:
? CDate("01.01.07 ")
1:01:07 AM
? CDate("01.01.20 07")
<error raised>
? CDate("01-Jan-7")
1/1/2007
? CDate("18:00")
6:00:00 PM
? CDate("18.00")
6:00:00 PM
? CDate("1/1/1")
1/1/2001
? CDate("18-00")
<error raised>


I hope you are not going to do all it manually. ;) Update queries and Replace() function can make almost all the job.

P.S. You are quite welcome to bother more. :)

Thank you very much. Your patience is a big help. I will study and utilize the queries and replace as needed. Have a great day, evening or whatever is is where you are....Ken Baker...Kansas City, Missouri