Improve company productivity with a Business Account.Sign Up

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

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

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
nblassen
Asked:
nblassen
  • 2
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
Rey Obrero (Capricorn1)Commented:
just use datevalue()

update tablex
set [datefield]=datevalue([datefield])
0
 
Patrick MatthewsCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
nblassenAuthor Commented:
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
 
nblassenAuthor Commented:
The data type is Text.
0
 
Rey Obrero (Capricorn1)Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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