Microsoft Excel/Access tips

Détails: 24 mars 2025; 5624

Index de l'article

MS Excel/Access is enought supported, so I would list only specific cases which took me more than 1 minute to solve.

Excel

Find "?"

~?

AltGr+2é x2 then delete

Find `\n`

Ctrl+J

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)

TitleCase:UCASE(LEFT([country];1))&LCASE(RIGHT([country];LEN([country])-1))

Remove accents from plain text in a query

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([First name];"è";"e");"ú";"u");"í";"i");"c";"c");"á";"a");"é";"e");"ö";"o");"ç";"c");"s";"s");"ü";"u");"ó";"o");"ñ";"n");"ë";"e");"æ";"ae");"à";"a");"ã";"a");"ô";"o")

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],"è","&egrave;"),"ú","&uacute;"),"í","&iacute;"),"ć","&cacute;"),"á","&aacute;"),"é","&eacute;"),"ö","&ouml;"),"ç","&ccedil;"),"ş","&scedil;"),"ü","&uuml;"),"ó","&oacute;"),"ñ","&ntilde;"),"ë","&euml;"),"æ","&aelig;"),"à","&agrave;"),"ã","&atilde;"),"'","&apos;"),"ô","&ocirc;")

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.

Search a field at the end of another field

IIf(Right([field_left]; Len(field_right)) = [field_right]; "Yes"; "No")

Menu