Solved

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

Posted on 2011-02-25
6
242 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

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 119

Expert Comment

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

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

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 119

Accepted Solution

by:
Rey Obrero earned 500 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

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.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now