?
Solved

What query can I create  to remove some characters from a field in Access

Posted on 2011-02-25
6
Medium Priority
?
248 Views
Last Modified: 2012-05-11
Hello,

I have a field in Access that has the time and date shown like this: 1/10/2011 10:14.  I would like to remove the time and only show the date.  What query can I create to accomplish this?
0
Comment
Question by:nblassen
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34980630
If the data type is Date:


UPDATE [SomeTable]
SET [SomeColumn] = Int([SomeColumn])

Open in new window



If the data type is Text, and if the time is always at the end of the string:


UPDATE [SomeTable]
SET [SomeColumn] = Left([SomeColumn], InStrRev([SomeColumn], " ") - 1)

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34980724
just use datevalue()

update tablex
set [datefield]=datevalue([datefield])
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34980849
Rey,

In this case, Int and DateValue will yield the same result.  I recommended Int instead of DateValue because, if I remember gustav correctly, DateValue is slow as molasses :)

Patrick
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:nblassen
ID: 34980885
To matthewspatrick:
I tried the expression but it said Syntax Error in query expression.
I'm a novice Access user, so can you give me more instruction.  Thanks!
0
 

Author Comment

by:nblassen
ID: 34980889
The data type is Text.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34980906
try this

update tablex
set [datefield]=iif([datefield] is null, null,datevalue([datefield]))

or

update tablex
set [datefield]=iif([datefield] is null, "",datevalue([datefield]))
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …
Suggested Courses

765 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