Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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

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
wermy
Asked:
wermy
2 Solutions
 
phileocaCommented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
wermyAuthor Commented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
wermyAuthor Commented:
KT Where do I place your code within the query?
0
 
jadedataMS Access Systems CreatorCommented:
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
 
wermyAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
update to:  DateValue([Entry_Date])
0
 
jjafferrCommented:
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
 
wermyAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
That a fair and creative way of doing it wermy!
Thanx!
-j
0
 
wermyAuthor Commented:
So you noticed that it was 50 additional points to the original value.
0
 
jadedataMS Access Systems CreatorCommented:
i did, indeed... :)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now