Access

Get the first letter from each word in upper case in a query (Title Case)

Get the first letter from each word in upper case and the remaining in lower case is easy with Excel (=PROPER), but with Access I searched for a long time ...

StrConv([field],3)

Get HTML from plain text in a query

Recover plain text from rich text in an Access query is easy (PlainText([field]), but the opposite is not!

Here an example to get the main HTML chars (not all!) from a field named First name:

firstname: Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([First name],"è","è"),"ú","ú"),"í","í"),"ć","ć"),"á","á"),"é","é"),"ö","ö"),"ç","ç"),"ş","ş"),"ü","ü"),"ó","ó"),"ñ","ñ"),"ë","ë"),"æ","æ"),"à","à"),"ã","ã"),"'","'"),"ô","ô")

I know, I should use VBA ... Later!

Get the date today

Full time (13/09/2021 12:05:15)

today: Now()

Day only (13/09/2021)

today: Date()

Some formatting (2021 09 13 or 2021-09-13 12:13:16)

today:Format(Now();"yyyy-mm-dd\ hh:nn:ss")

today:Format(Date(),"aaaa mm jj")

today:Format(Now(),"aaaa-mm-jj hh:nn:ss")

If

Use IIF:

IIf(date_submit="0000-00-00 00:00:00", Format(Now(),"yyyy-mm-dd"), date_submit)

Find field in full upper case/lower case

Do a test as it:

Test: StrComp([First name];UCase([First name]);0)=0

And filter on -1.

Search a field in another field

InStr([Tmp_customer].[email];[attendees].[LAST NAME])

And a filter >0.