473,320 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date as Text issues

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
Expand|Select|Wrap|Line Numbers
  1. ORDER BY test03.[Last Name], test03.[First Name],test03.Date, test03.Time; 
  2.  
This is what I would like to try to do.
Expand|Select|Wrap|Line Numbers
  1. ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.Date), test03.Time;
  2.  
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
Nov 20 '07 #1
12 2469
FishVal
2,653 Expert 2GB
Hi, Ken.

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

Expand|Select|Wrap|Line Numbers
  1. ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.[Date]) + CDate(test03.[Time]);
  2.  
BTW, Date and Time are reserved words. It is better to enclose them in square brackets.

Regards,
Fish
Nov 20 '07 #2
Hi, Ken.

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

Expand|Select|Wrap|Line Numbers
  1. ORDER BY test03.[Last Name], test03.[First Name],CDate(test03.[Date]) + CDate(test03.[Time]);
  2.  
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
Dec 19 '07 #3
FishVal
2,653 Expert 2GB
Not a problem.
Good luck.
Dec 19 '07 #4
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.
Dec 19 '07 #5
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.
Dec 19 '07 #6
FishVal
2,653 Expert 2GB
Try to use Nz() function.
e.g.
CDate(Nz([Date],0))+CDate(Nz([Time],0))
Dec 19 '07 #7
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.
Dec 19 '07 #8
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
Dec 19 '07 #9
FishVal
2,653 Expert 2GB
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.
Dec 19 '07 #10
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
Dec 19 '07 #11
FishVal
2,653 Expert 2GB
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. :)
Dec 19 '07 #12
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
Dec 20 '07 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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...
26
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...
11
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
2
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...
7
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, ...
6
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...
4
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)...
4
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...
9
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...
2
patjones
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...
0
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...
0
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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)...
0
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...
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.