0

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
)")
June7
  • 19,874
  • 8
  • 24
  • 34
Lauren Hostert
  • 63
  • 3
  • 8
  • 2
    If you mean Format property of field, that requires VBA and DAO TableDefs https://stackoverflow.com/questions/56593003/create-table-with-access-vba-and-format-fields. No idea if can be translated to r code. I seldom set format in table. I format controls on form or report. – June7 Feb 17 '21 at 00:01
  • You can format after getting data into R. – Ronak Shah Feb 17 '21 at 03:19
  • 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 Feb 17 '21 at 15:08

1 Answers1

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.

DenimChicken
  • 59
  • 3
  • 3
    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. – June7 Feb 17 '21 at 00:13
  • 3
    This is a very bad advice. Always store dates as _Date_, not text, not numbers, no exceptions. – Gustav Feb 17 '21 at 11:11