Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I seperate Date and Time text data within an access field (Split or remove)

Posted on 2004-04-08
12
Medium Priority
?
305 Views
Last Modified: 2011-09-20
Help.  I need to break a specific field in a database up.  

My DB file has about 10 fields such as first,last,phone,address, etc. My date field contains both the date and time in the string. For example "1/1/2004 1:00am".  It contains about 400,000 records.  I would like it to read "1/1/2004", removing or separating the "1:00am" portions.

I need to separate the date from the time within that particular "Date" field.  I will then de dupe by date.  (I am presently unable to dedupe the file because the 'time' portion of the "Date" field causes the entire field to be unique).

How can I spit the field into 2 fields or remove the 'time' portion entirely?

I am an access novice so please write your instructions with patience and detail in mind.

Thanks!
0
Comment
Question by:wermy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 11

Expert Comment

by:phileoca
ID: 10786987
in the query, create a field

Field:Left([tablename]![fieldname],InStr(1,[tablename]![fieldname]," ")-1)

that should return just the date portion, without losing the value in the record.

although u cod then do an update query and update
Yourfield to equal the new value in Field, but i would just use the new field as the value u need.

KT
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10786989
Greetings wermy!

  DateOnly = Datevalue([DateTimeField])
  TimeOnly = TimeValue([DateTimeField])

I would recommend you KEEP the values together for storage and split the values as needed to test conditions
Otherwise you Double your storage requirements for that element.

What is the purpose of splitting this one field, and why are you depending on a Date to be part of a unique key?

regards
jack
0
 

Author Comment

by:wermy
ID: 10787032
KT,
I am about to try what you asked.


Jack,
The file is part of a sweepstakes.  Only one entry per day was allowed.  I need to remove any subsequent entries for the same calendar day.  I can't get the data creator to modify the data.  I never needed the time.  They added it without permission.

Wermy
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:wermy
ID: 10787047
KT Where do I place your code within the query?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10787105
UPDATE query the field

  UPDATE TableName SET MyDateField = DateValue([MyDateField]);

this will stripp the time seqment clear and leave date only...

change the names as needed ...

-j-

ps:  saw "Only one entry per day was allowed" and panic'd thinking I would have to come back to the question tomorrow to post the above suggestion..
{phew!}
0
 

Author Comment

by:wermy
ID: 10787178
J,

I guess i'll let my ignorance show.  

I have within theQuery1:Update window

~~~~~~~~
       Field:  Entry_date
      Table: Sweepstakes
Update to:
    Critera:
           or:
~~~~~~~~

Does your "UPDATE TableName SET MyDateField = DateValue([MyDateField]);"
 belong in the "update to:" position in the query?
0
 
LVL 32

Accepted Solution

by:
jadedata earned 1000 total points
ID: 10787202
update to:  DateValue([Entry_Date])
0
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 200 total points
ID: 10787302
Hi wermy,

Besides what Jack suggested,
in your update query, use this to update your field:
Format([MyDateField],"MM/DD/YY")

I Hope this helps

jaffer
0
 

Author Comment

by:wermy
ID: 10791363
Thanks!

I was able to split it as Jack suggested.

Jaeffer I'm giving you 50 points for the added educational value!

I can't thank you all enough.

Wermy
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10791593
That a fair and creative way of doing it wermy!
Thanx!
-j
0
 

Author Comment

by:wermy
ID: 10791731
So you noticed that it was 50 additional points to the original value.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10792080
i did, indeed... :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question