[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How do I change the format of a query field?

Hi everyone,

I am creating a simple query that takes a list of clients with additional related information. Among the fields that I am adding into my query, one field contains dates that do not have a date format.

Their current format is YYYYMMDD but Access does not recognize it as a date.

How can I use the query so that my dates become actual dates in Access(so that I can compare these dates with the actual current date)?

Thank you!
0
appc
Asked:
appc
4 Solutions
 
pcelbaCommented:
You may use some conversion, e.g.:

CDate(LEFT(YourField, 4) + "-" + Mid(YourFiled, 5, 2) + "-" + Mid(YourFiled, 7, 2))
0
 
ralmadaCommented:
Use this
select Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")
0
 
tbsgadiCommented:
Try
CDate(LEFT(Field1, 4) & "/" + Mid(Field1, 5, 2) & "/" & Right(Field1,2))

Gary
0
 
pcelbaCommented:
I would also try

DateValue(LEFT(TxtField, 4) + "." + Mid(TxtField, 5, 2) + "." + Mid(TxtField, 7, 2))
0
 
appcAuthor Commented:
Thanks a lot guys.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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