Is there a way to define data format within MS Access SQL CREATE TABLE Statement?

Question

I am using the DBI library in R to establish a connection to an MS Access DB (in this example called "db") and then using the DBI dbGetQuery() function to pass MS Access SQL query seen below. If I run this example code segment without attempting to define format it creates a new table as expected. What I cannot determine is whether or not it is possible to define the format for a data type within the same query? If it is possible, what the syntax is for defining format within the CREATE TABLE statement.

Without attempting to format:

dbGetQuery(db, "CREATE TABLE MyTable
(
  Table_ID AutoIncrement PRIMARY KEY,
  Location CHAR NOT NULL,
  Event_Date DATE NOT NULL,
  Species_Code CHAR NOT NULL,
  Length DOUBLE,
  Weight DOUBLE,
  Sex CHAR
)")

With attempting to format date, results in a syntax error:

dbGetQuery(db, "CREATE TABLE MyTable
(
  Table_ID AutoIncrement PRIMARY KEY,
  Location CHAR NOT NULL,
  Event_Date DATE NOT NULL FORMAT \"yyyy/mm/dd\",
  Species_Code CHAR NOT NULL,
  Length DOUBLE,
  Weight DOUBLE,
  Sex CHAR
)")

If you mean Format property of field, that requires VBA and DAO TableDefs stackoverflow.com/questions/56593003/…. No idea if can be translated to r code. I seldom set format in table. I format controls on form or report. — June7
– June7, Commented Feb 17, 2021 at 0:01
Thank you, both. I do mean Format property of field but being new to Access DB I didn't realize these needed to be set with VBA and DAO TableDefs I thought they might be able to be established while creating a table. I will see if this can be translated to R otherwise what @RonakShah suggested (storing it as Date/Time and formatting later in R) will be the way I handle it. — Lauren Hostert
– Lauren Hostert, Commented Feb 17, 2021 at 15:08

DenimChicken · Accepted Answer · 2021-02-17 00:09:11Z

0

To the best of my knowledge, the DATETIME field in Access defaults to mm/dd/yyyy format. I don't know if you can modify the format with a SQL command - you can change it using the table's design view.

When I want a field to have special date formatting, I use CHAR datatype (or Short Text, in Access parlance), then I use the FORMAT() function to format the date values to my liking whenever I insert a new date. This gives greater flexibility with dates.

answered Feb 17, 2021 at 0:09

DenimChicken

593 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

June7 Over a year ago

A date stored in a text field is not a true date value and date calcs will not work properly without converting. Why not use a date/time field and apply formatting with Format property on form/report? Can even use Format function on date/time field. Keep in mind Format() function results in a string.

Gustav Over a year ago

This is a very bad advice. Always store dates as Date, not text, not numbers, no exceptions.

Collectives™ on Stack Overflow

Is there a way to define data format within MS Access SQL CREATE TABLE Statement?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related