• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
  • Tim Holloway
  • Carey Brown
  • salvin francis

Problem with SQL in an Access database

Ranch Hand
Posts: 447
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For years I have been using in my programs statements like "SELECT * FROM user WHERE user_name=mgr", but I just learned that nowadays I have to use "user_name='mgr'". Now I am trying to verify that I also have to use "'" for Date and Number; the following code is giving me the error
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: DATE_CREATED
I am getting the same error if I don't use "'".

Similar code works fine for strings.

Posts: 20984
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just realized that you're using MS-Access. My condolences. Don't use it for web applications.

Yes, you do have to quote dates. Otherwise SQL will usually take something like 2019-05-08 to be an arithmetic expression with a value of 2006. Which isn't at all what you were expecting. Or, if you've defined the column as a date/time object in your schema, it may just yell at you.

For numbers, you don't quote them unless the number is defined in the database schema as a text value. In which case, you do put quotes around them, because they're really just text digits and not literally a number as far as the database is concerned.
Posts: 6173
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Whether you use a single quote (') or not depends on the column type.  Everything that is no a true number is surrounded by single quotes.  What I mean by true numbers is that you can have a text column, but if you want to add the value 123 to it, you still need quotes.  Said another way, it's not the data being inserted that you concern, but the type of the column.

See https://www.w3schools.com/sql/sql_where.asp
After some pecan pie, you might want to cleanse your palatte with this tiny ad:
Enterprise-grade Excel API for Java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!