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
12 2469
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
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
Not a problem.
Good luck.
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.
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.
Try to use Nz() function.
e.g.
CDate(Nz([Date],0))+CDate(Nz([Time],0))
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.
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
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.
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
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.2007")
<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. :)
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.2007")
<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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Kenickney |
last post by:
I use ASP/Javascript. I pull a date from a recordset field like this.
var theDate = new Date()
theDate = rs.Fields.Item("date").value
Now I want to apply the method toLocaleString(). There are...
|
by: sgershon |
last post by:
Hi.
I know this is should be a simple question.
I know server-side web-programming, and never needed to use client-side
scripting... until now :)
I have done so far a little number of scripts...
|
by: Dixie |
last post by:
How can I programatically, take some Date/Time fields present in a table in
the current database and change their type to text?
dixie
|
by: Derek Vincent |
last post by:
What must I do to overcome a problem with my dates becoming formatted as
"2/22/2525 12:00:00 AM" in the datagrid? I want to handle all dates as
short string of format "2/22/2525." Otherwise when I...
|
by: mr.nimz |
last post by:
hello,
this is antenio. recently i've come to a problem. i got a way through
it, somehow, still it left me in a curious state, so i'm posting it
here, if i can get an answer from some techy,
...
|
by: Aussie Rules |
last post by:
Hi,
I have a datepicker that show a calender. The user picks a date and the
time component is always 00:00.
I then have a drop down that provides a list of times, (10:00, 11:00 etc),
and I...
|
by: sck10 |
last post by:
Hello,
I created an ashx file for my rss feed (rss.ashx). I notice on the site
(http://rss.msnbc.msn.com/id/3032091/device/rss/rss.xml) that the date
(Yesterday, October 30, 2006, 3:02:37 PM)...
|
by: keithsimpson3973 |
last post by:
Please forgive me for being so stupid. I have searched this site and many others for what should be a simple thing. I have a vb 6 form with a textbox that I input a date into. I can't set the format...
|
by: Kenevel |
last post by:
Hi everyone,
Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling...
|
by: patjones |
last post by:
Good afternoon all,
This is one of those seemingly simple problems. I've searched through many posts here, and many people have date and time issues, but not quite what mine is.
I've got a...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |