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

x
?
Solved

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

Posted on 2011-02-25
6
Medium Priority
?
251 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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