0

I have an MS Access database of fishes. I also have a search form in MS Access where you can search for any species of fish, and the results will be displayed in a separate form. My database is getting bigger and I decided to covert it to postgresql and link the postgresql database to MS Access via ODBC so I can still use my old forms.

When I was still using the onl access database, my search form isn't case sensitive. For example, either I search for "Gadus morhua" or "gadus morhua", it will display the same information about the species "Gadus morhua". But since I converted my database to PostgreSQL, whenever I search for "gadus morhua" it will say "no record found" because of PostgreSQL's case sensitivity.

One solution I came across with is to convert all my varchars to citext. The problem is I have like 40+ tables with ~400 varchars and I'm not that familiar with how citext can affect all of my data. Is there a way to disable case sensitivity in MS Access when searching through forms?

I'm using MS Access 2019 and PostgreSQL 13

Many thanks

Martin Paulo Nevado
  • 1
  • This cannot be solved in Access, only in the database (or perhaps in the ODBC driver). Search for case-insensitive collation with PostgreSQL. – Andre Jan 25 '22 at 15:10
  • from the Access side, a quick fix is to cast both sides of the query to lowercase using LCase. Something like "SELECT LinkedTableColumn FROM LinkedTable WHERE LCase(LinkedTabledColumn) as lowercasecolumn = LCase('Gadus Morhua') " – mazoula Jan 27 '22 at 05:52

0 Answers0