MINI CORSO DI MICROSOFT ACCESS - parte 4°

FUNZIONI VBA E QUERY

Le funzioni disponibili in Visual Basic for Application sono utilizzabili anche all'interno dei comandi SQL. I comandi così creati non risultano compatibili con lo standard (SQL ANSI), accettato dalla gran parte dei DB SERVER .
Elenchiamo qui di seguito le funzioni VBA più importanti.

Funzioni per la manipolazione delle stringhe

ASC(Argomento)

La funzione ASC restituisce il codice ascii del carattere passato come Argomento.

ASC("A") ==> 65

In MySQL il comando equivalente è:

ASCII(Lettera)
esempio:
ASCII('A') ==> 65
ASCII('B') ==> 66

CHR(Argomento)

La funzione CHR restituisce il carattere il cui codice ascii è passato come Argomento.

CHR(65) ==> "A"

In MySQL il comando equivalente è:

CHAR(CodiceAscii1,CodiceAscii2, ...)
esempio:
CHAR(65,66) ==> "AB"

LEFT(Argomento, N)

La funzione LEFT vuole 2 argomenti. Il 1° è una stringa (esempio "Ciao") mentre il 2° (N) è un intero. LEFT restituisce i primi N caratteri della stringa passata come 1° argomento.

Left("Questa è una frase",4) ==> "Ques"

In MySQL la funzione è la stessa

RIGHT(Argomento, N)

La funzione RIGHT vuole 2 argomenti. Il 1° è una stringa (esempio "Ciao") mentre il secondo (N) è un intero . RIGHT restituisce gli ultimi N caratteri della stringa passata come 1° argomento.

Right("Questa è una frase",4) ==> "rase"

In MySQL la funzione è la stessa

LEN(Argomento)

La funzione LEN restituisce il numero di caratteri contenuti nella stringa passata come Argomento (spazi compresi).

len("Questa è una frase ") ==> 19

In MySQL il comando equivalente è:

LENGTH(Argomento)/CHAR_LENGTH(Argomento)
esempio:
CHAR_LENGTH("Ciao") ==> 4

MID(Argomento, I, N)

La funzione MID restituisce, partendo dall'I-esimo carattere, N caratteri estratti dalla stringa passata come 1° argomento.

mid("Questa è una frase", 3, 8) ==> "esta è u"

Quando ometto il 3° argomento la funzione MID estrae tutti i caratteri del 1° parametro a partire dalla lettera di posizione I-esima (compresa).

mid("Questa è una frase", 10) ==> "una frase"
mid("Questa è una frase",len("Questa è una frase")-1)==>"se"

Attenzione: La posizione di un carattere in una stringa va calcolata tenendo presente che la numerazione parte da 1 e non da 0 come solitamente avviene in linguaggi come il javascript,C o php.

In MySQL il comando equivalente è:

MID(Str, pos, len)/SUBSTRING(Str, pos, len)
esempio:
SUBSTRING("Ciao",3,1) ==> "a"
SUBSTRING("Ciao",3) ==> "ao"

INSTR(StringaDoveCerco, StringaCercata)

Restituisce la posizione della prima occorrenza di una stringa (StringaCercata) all'interno di un'altra (StringaDoveCerco). Se non trova niente restituisce 0.

Instr("Questa è una frase fraintesa", "fra") ==> 14
Instr("Questa è una frase", "ciao") ==> 0

In MySQL il comando equivalente è:

LOCATE(StringaCercata,DoveCerco)
si noti che gli argomenti sono ribaltati - esempio:
LOCATE("ANDATO","Sono andato a casa") ==> 6

Esempio: l'utilizzo combinato delle funzioni INSTR, MID, LEFT consente di separare il cognome dal nome quando sono registrati assieme in un unico campo:

Nominativo="Sechi Marco"
Left(Nominativo,Instr(Nominativo," ")-1) ==> "Sechi"
Mid(Nominativo, Instr(Nominativo," ")+1) ==> "Marco"

Applicando l'esempio all'SQL ottengo:

SELECT Nominativo,
Left(Nominativo,Instr(Nominativo," ")-1) AS COGNOME,
Mid(Nominativo, Instr(Nominativo," ")+1) AS NOME
FROM Studenti

la cui esecuzione produce questo risultato:

Trim(stringa) LTrim(stringa) RTrim(stringa)

Restituisce l'argomento (di tipo stringa) privo degli spazi iniziali e finali.

Trim(" Questa è bella ") ==> "Questa è bella"

La funzione LTrim elimina dalla stringa passata come argomento gli spazi iniziali.

LTrim(" Questa è bella ") ==> "Questa è bella "

La funzione RTrim elimina dalla stringa passata come argomento gli spazi finali.

RTrim(" Questa è bella ") ==> " Questa è bella"

In MySQL queste funzioni sono identiche

Ucase(Argomento) - Lcase(Argomento)

Ucase restituisce la stringa passata come Argomento in caratteri maiuscoli.

Lcase restituisce la stringa passata come Argomento in minuscolo.

Esempio: combinando le funzioni Ucase e Lcase possiamo trasformare il contenuto di un campo in modo che venga scritto tutto in minuscolo esclusa la prima lettera (tipico dei nomi propri):

Nome="mArCo"
Ucase(left(Nome,1))+lcase(mid(Nome,2)) ==>"Marco"

In MySQL queste funzioni sono identiche - esistono comunque dei sinonimi: Lower e Upper

SPACE(N)

Restituisce una stringa composta da N spazi.

Space(5) ==> " "

In MySQL ia funzione è identica.

STRING(N, Carattere)

Restituisce una stringa composta da N repliche del Carattere passato come 2° argomento.

String(5, "*") ==> "*****"

In MySQL il comando equivalente è:

REPEAT(Stringa,NrVolte)
si noti che accetta anche stringhe composte da più caratteri e che gli argomenti sono ribaltati rispetto alla funzione di ACCESS- esempio:
REPEAT("Ciao",3) ==> "CiaoCiaoCiao"

REPLACE(Stringa, StringaCercata, NewStringa)

Restituisce la stringa ottenuta sostituendo nel 1° argomento tutte le occorrenze della sottostringa StringaCercata con la stringa NewStringa passata come 3° argomento

Replace("Ciao sono Marco", "o", "*") ==> Cia* s*n* Marc*"

In MySQL questa funzione è identica.

La funzione REPLACE non esiste nella versione 97. Può essere comunque implementata mediante VBA. La funzione MyReplace(), presentata in seguito (basata sulle funzioni: INSTR, LEFT, MID, LEN), svolge la stesso compito di REPLACE:

Function MYREPLACE(ByVal StringaOriginale As String, _
                       StringaDaRimpiazzare As String, _
                       StringaNuova As String) As String

Dim StringaFinale As String
Dim ParteSinistra As String
Dim ParteDestra As String
Dim DoveELaStringaDaRimpiazzare As Long

   StringaFinale = ""
   DoveELaStringaDaRimpiazzare = InStr(StringaOriginale, StringaDaRimpiazzare)
   While DoveELaStringaDaRimpiazzare > 0
      ParteSinistra = Left(StringaOriginale, DoveELaStringaDaRimpiazzare - 1)
      ParteDestra = Mid(StringaOriginale, DoveELaStringaDaRimpiazzare + Len(StringaDaRimpiazzare))
      StringaFinale = StringaFinale + ParteSinistra + StringaNuova
      StringaOriginale = ParteDestra
      DoveELaStringaDaRimpiazzare = InStr(StringaOriginale, StringaDaRimpiazzare)
   Wend
   MYREPLACE = StringaFinale + StringaOriginale

End Function

Funzioni di conversione

Cstr(Argomento)

Converte l'Argomento passato in stringa

Cstr(1.002) ==> "1,002"

Cstr(#11/26/2005#) ==> "26/11/2005"

Cstr(True) ==> "Vero"

In MySQL esiste la funzione CONVERT(Argomento, Tipo) ma è pressochè inutile poichè a seconda del contesto la conversione avviene automaticamente:

Esempio:
CONCAT('Ciao a tutti e ',2) ==> 'Ciao a tutti e 2'
CONCAT('Oggi è ',CURDATE()) ==> 'Oggi è 2003-01-31'

CDbl(Argomento)

Converte, se possibile, la stringa passata come Argomento in double (numero in precisione doppia) altrimenti genera un errore

CDbl("1,002") ==> 1.002

CDbl("A") ==> ERRORE: Tipo non corrispondente

In MySQL non esiste un comando equivalente poichè la conversione avviene in modo automatico:

Esempio:
'1.21'+0.2 ==> 1.41

CLng(Argomento)

Converte l'Argomento, se è possibile, in un long (numero intero lungo a 32 bit).

CLng(1.002) ==> 1

CLng(#12/31/1899#) ==> 1

In presenza di decimali arrotonda per difetto o per eccesso all'unità più vicina

CLng("1,51") ==> 2

CLng(1.49) ==> 1

In MySQL il comando equivalente è:

ROUND(Argomento)
esempio:
ROUND("1.49") ==> 1
ROUND(1.51) ==> 2

Fix(Numero)

Elimina la parte decimale del Numero passato come argomento, senza arrotondare

Fix(1.51) ==> 1

Fix(1.49) ==> 1

In MySQL il comando equivalente è:

TRUNCATE(Numero,Decimali)/FLOOR(Numero)
si noti che Fix equivale a Truncate(numero,0) - esempio:
TRUNCATE(1.49,0) ==> 1
FLOOR(1.51) ==> 1

CDate(Argomento)

Converte l'argomento passato, se è possibile, in data/ora

CDate(1) ==> #12/31/1899#

CDate("01/01/2005") ==> #1/1/2005 0.00#

In MySQL il comando equivalente esiste dalla versione 4.1.1 ed è:

STR_TO_DATE(Argomento,MascheraFormato)
La MascheraFormato segue le stesse regole di composizione della funzione DATE_FORMAT () - esempio:
STR_TO_DATE('31.03.2003 09.20','%d.%m.%Y %H.%i') -->
'2003-03-31 09:20:00'

STR_TO_DATE('32.03.2003 09.20','%d.%m.%Y %H.%i') --> NULL

Si ricordi che le date in mysql sono stringhe dove la data è scritta in maniera ribaltata ovvero anno, mese e giorno.

Format(Valore, MascheraFormato)

Trasforma il primo parametro Valore secondo le specifiche indicate in MascheraFormato. Nella tabella sottostante sono elencate le specifiche relative a formati di tipo data/ora

Maschera di formato		Esempio	Risultato	Descrizione
In SQL	Nel generatore di query	Esempio	Risultato	Descrizione
"d"	"g"	format(#12/02/2005 12.00.23#,"d")	2	Visualizza il giorno come numero senza zero iniziale (1 – 31).
"dd"	"gg"	format(#12/02/2005 12.00.23#,"dd")	02	Visualizza il giorno come numero con zero iniziale (01 – 31).
"ddd"	"ggg"	format(#12/02/2005 12.00.23#,"ddd")	ven	Visualizza il giorno abbreviato (dom – sab).
"dddd"	"gggg"	format(#12/02/2005 12.00.23#,"dddd")	venerdì	Visualizza il giorno per esteso (domenica – sabato)
"w"	"i"	format(#12/02/2005 12.00.23#,"w")	6	Visualizza il giorno della settimana come numero (1 per domenica-7 per sabato).
"ww"	"ii"	format(#12/02/2005 12.00.23#,"ww")	49	Visualizza la settimana di un anno come numero
"m"		format(#1/15/2005 12.00.23#,"m")	1	Visualizza il mese come numero senza zero iniziale (1 – 12). Se m segue immediatamente h o hh, viene visualizzato il valore relativo ai minuti anziché il mese.
"mm"		format(#1/15/2005 12.00.23#,"mm")	01	Visualizza il mese come numero con zero iniziale (01 – 12). Se m segue immediatamente h o hh, viene visualizzato il valore relativo ai minuti anziché il mese.
"mmm"		format(#1/15/2005 12.00.23#,"mmm")	gen	Visualizza il mese abbreviato (gen – dic).
"mmmm"		format(#1/15/2005 12.00.23#,"mmmm")	gennaio	Visualizza il mese con il relativo nome per esteso (gennaio – dicembre).
"q"		format(#12/02/2005 12.00.23#,"q")	4	Visualizza il trimestre dell'anno come numero (1 – 4).
y	"a"	format(#12/02/2005 12.00.23#,"y")	336	Visualizza il giorno dell'anno come numero (1 – 366).
yy	"aa"	format(#12/02/2005 12.00.23#,"yy")	05	Visualizza l'anno come numero di due cifre (00 – 99).
yyyy	"aaaa"	format(#12/02/2005 12.00.23#,"yyyy")	2005	Visualizza l'anno come numero di quattro cifre (100 – 9999).
h		format(#12/02/2005 01.02.03#,"h")	1	Visualizza l'ora come numero senza zero iniziale (0 – 23).
hh		format(#12/02/2005 01.02.03#,"hh")	01	Visualizza l'ora come numero con zero iniziale (00 – 23).
n		format(#12/02/2005 01.02.03#,"n")	2	Visualizza i minuti come numero senza zero iniziale (0 – 59).
nn		format(#12/02/2005 01.02.03#,"nn")	02	Visualizza i minuti come numero con zero iniziale (00 – 59).
s		format(#12/02/2005 01.02.03#,"s")	3	Visualizza i secondi come numero senza zero iniziale (0 – 59).
ss		format(#12/02/2005 01.02.03#,"ss")	03	Visualizza i secondi come numero con zero iniziale (00 – 59)

I singoli elementi presentati della tabella precedente possono essere combinati tra loro come nell'esempio seguente

format(#12/02/2005 01.02.03#,"dddd, d mmmm yyyy \o\r\e hh.nn.ss") ==> "venerdì, 2 dicembre 2005 ore 01.02.03"

In MySQL il comando equivalente per il tipo data/ora è:

DATE_FORMAT(Data,MascheraFormato)
esempio:
DATE_FORMAT('2006-03-01 19:59:08','%W, %d/%m/%Y %T - %j° gg %u° settimana') ==> "Wednesday, 01/03/2006 19:59:08 - 060° gg 09° settimana"
la maschera di formato si basa su queste regole:

`%M`	Nome mese (`January`..`December`)
`%W`	Giorno della settimana (`Sunday`..`Saturday`)
`%D`	giorno del mese con suffisso inglese (`1st`, `2nd`, `3rd`, etc.)
`%Y`	Anno a 4 cifre
`%y`	Anno a 2 cifre
`%a`	Giorno della settimana abbreviato (`Sun`..`Sat`)
`%d`	Giorno del mese con zero iniziale (`01, 02,` ..`31`)
`%e`	Giorno del mese senza zero iniziale (`1,2,`..`31`)
`%m`	Numero mese con zero iniziale (`01`..`12`)
`%c`	Numero mese con zero iniziale (`1`..`12`)
`%b`	Nome mese abbreviato (`Jan`..`Dec`)
`%j`	Giorno dell'anno (`001`..`366`)
`%H`	Ore (`00,01,`..`23`)
`%k`	Ore (`0`,1,..`23`)
`%h`	Ore (`01`..`12`)
`%l`	Ore (`1`..`12`)
`%i`	Minuti, numeric (`00`..`59`)
`%r`	Orario, 12-hour (`hh:mm:ss [AP]M`)
`%T`	Orario, 24-hour (`hh:mm:ss`)
`%S`	Secondi con zero iniziale (`00`..`59`)
`%s`	Secondi senza zero iniziale (`00`..`59`)
`%p`	`AM` o `PM`
`%w`	Numero giorno della settimana (`0`=Sunday..`6`=Saturday)
`%U`	Nr. Settimana (`0`..`52`), dove la domenica è il primo giorno della settimana
`%u`	Nr. Settimana (`0`..`52`), dove il lunedì è il primo giorno della settimana
`%%`	il carattere `%'.

Nella tabella seguente sono elencati alcuni esempi di specifiche relative a formati di tipo numerico

Maschera di formato		Esempio	Risultato	Descrizione
In SQL	Nel generatore di query	Esempio	Risultato	Descrizione
"#,##0.00"	"#.##0,00"	format(0.009,""#,##0.00"")	0,01	Si osservi: un numero senza parte intera viene visualizzato con lo 0 iniziale.
"#,###.00"	"#.###,00"	format(1000.014,""#,##0.00"")	1.000,01	Se il numero di cifre decimali è superiore a quello indicato nella maschera di formato allora il numero viene arrotondato per difetto o eccesso a seconda.
"#,###.00"	"#.###,00"	format(0.009,""#,###.00"")	,01	Si osservi: un numero senza parte intera viene visualizzato senza lo 0 iniziale
00000		Format(1,"00000")	00001	Segnaposto di cifra. Visualizza una cifra o uno zero. Se l'espressione contiene una cifra nella posizione in cui compare lo 0 nella stringa del formato, tale cifra verrà visualizzata; in caso contrario in tale posizione verrà visualizzato uno zero.
"#,##0 \L\i\r\e"	"#.##0 \L\i\r\e"	format(1000.014,""#,##0.00 \L\i\r\e")	1000 Lire	Se voglio scrivere delle frasi per evitare di confonderle con le sigle dellla maschera di formato ogni lettera va scritta facendola precedere con uno \

In MySQL il comando equivalente è:

FORMAT(Numero,nr Cifre decimali)
esempio:
FORMAT(12332.123456, 4) ==> '12,332.1235'
FORMAT(12332.1, 4) ==> '12,332.1000'

Nella successiva tabella sono elencati alcuni esempi di specifiche relative a valori di tipo stringa

Maschera di formato	Esempio	Risultato	Descrizione
>	format("ciao",">")	"CIAO"	Applica le maiuscole. Visualizza tutti i caratteri in maiuscolo.
<	format("ciao","<")	"ciao"	Applica le minuscole. Visualizza tutti i caratteri in minuscolo.
@	format("ciao","@@@@@@")	"ciao "	Segnaposto di carattere. Visualizza un carattere o uno spazio. Se la stringa include un carattere nella posizione in cui compare @ nella stringa di formato, tale carattere verrà visualizzato, in caso contrario in tale posizione verrà visualizzato uno spazio. I segnaposto vengono riempiti da destra a sinistra, a meno che non vi sia un punto esclamativo (!) nella stringa di formato.
!@	format("ciao","!@@@@@@")	" ciao"

In MySQL i risultati ottenuti con le maschere di formato @ e !@ possono essere replicati con le funzioni LPAD e RPAD:

esempio:
LPAD("ciao",6," ") ==> " ciao"
RPAD("Ciao",6," ") ==> "ciao "

Funzioni di test o logiche

IIF(Condizione, ValoreSeVero, ValoreSeFalso)

Restituisce il 2° parametro se la Condizione è vera altrimenti il 3° parametro.

In SQL posso scrivere ad esempio:

SELECT Nominativo, iif(Voto>=6,'Promosso', 'Bocciato') AS Esito FROM Studenti

In MySQL il comando equivalente è identico:

NZ(Argomento, ValoreSeArgomentoNullo)

Restituisce il 2° parametro ValoreSeArgomentoNullo se il primo Argomento vale Null altrimenti ritorna il valore di Argomento. Corrisponde alla seguente sequenza VBA:

iif(IsNull(Argomento), ValoreSeArgomentoNullo, Argomento)

In SQL posso scrivere ad esempio:

SELECT Nominativo, Nz(Voto,"Non Valutato") AS Esito
FROM Studenti LEFT JOIN Interrogazioni
ON Studenti.IdStudente=Interrogazioni.IdStudente

In Access 1997 la funzione non era presente per cui occorreva utilizzare in sostituzione la funzione iif:

SELECT Nominativo,
iif(IsNull(Voto),"Non Valutato",Voto) AS Esito
FROM Studenti LEFT JOIN Interrogazioni
ON Studenti.IdStudente=Interrogazioni.IdStudente

In MySQL il comando equivalente è:

IFNULL(argomento,valore restituito)
esempio:
IFNULL(2/0,"Orrore!!")

ISNULL(Argomento)

Restituisce Vero se l'argomento passato come parametro è Null. Equivale all'espressione: Argomento is Null. Una variabile è posta a Null quando non contiene dati validi.

In MySQL il comando equivalente è identico. Al posto di true restituisce 1 se l'argomento è nullo.

ISNULL(argomento)
esempio:
ISNULL(1) ==> 0
ISNULL(2/0) ==> 1

ISNUMERIC(Argomento)

Restituisce Vero se il parametro passato è trasformabile in un numero altrimenti Falso.

IsNumeric("1,51") ==> True

IsNumeric("1a,51") ==> False

ISDATE(Argomento)

Restituisce vero se il parametro passato è trasformabile in un tipo data/ora altrimenti falso.

IsDate("31/02/2005") ==> False

IsDate("31/12/2005") ==> True

IsDate(1) ==> False

In MySQL il comando non esiste ma può essere implementato con questa diseguaglianza:

(IFNULL(STR_TO_DATE(argomento,'%d.%m.%y'),'E')<>'E')

Si noti che l'argomento deve rispettare la maschera di formato e pertanto nell'esempio la data va scritta in questo modo: giorno mese e anno separati dal punto.

Funzioni di tipo cronologico

DATE()

Restituisce la data di oggi

In MySQL il comando equivalente è:

CURDATE()
esempio:
CURDATE() ==> '2005-12-29'
CURDATE()+0 ==> 20051229

NOW()

Restituisce la data e l'ora di questo istante.

In MySQL il comando equivalente è:

NOW()/SYSDATE
esempio:
NOW() ==> "2005-12-29 23:02:23"

NOW()+0 ==> "20051229230223"

TIME()

Restituisce l'ora di questo istante

In MySQL il comando equivalente è:

CURTIME()
esempio:
CURTIME() ==> "23:02:23"
CURTIME()+0 ==> 230223

YEAR(Argomento)

Restituisce l'anno della data passata come Argomento. Ad esempio la query sottostante restituisce l'elenco degli studenti nati nel 1989.

SELECT Nominativo FROM Studenti WHERE Year(NatoIl)=1989

In MySQL il comando equivalente è:

YEAR(Argomento)
esempio:
YEAR("2005-12-01") ==> 2005
YEAR("Ciao") ==> Null

MONTH(Argomento)

Restituisce il mese (in numero) della data passata come Argomento. La query successiva restituisce l'elenco degli studenti nati in dicembre o gennaio di un qualsiasi anno

SELECT * FROM Studenti WHERE Month(NatoIl)=1 or Month(NatoIl)=12

Invece la query successiva restituisce l'elenco degli studenti nati l'ultimo giorno del mese:

SELECT * FROM Studenti WHERE Month(NatoIl)<>Month(NatoIl+1)

In MySQL il comando equivalente è:

MONTH(Argomento)
esempio:
MONTH("2005-12-01") ==> 12

DAY(Argomento)

Restituisce il giorno del mese (in numero) della data passata come Argomento. La query sottostante restituisce l'elenco dei nominativi degli studenti nati il 29 febbraio di un qualsiasi anno bisestile.

SELECT * FROM Studenti WHERE Day(NatoIl)=29 AND Month(NatoIl)=2

Lo stesso risultato poteva essere ottenuto con questa query:

SELECT * FROM Studenti WHERE Format(NatoIl,'ddmm')="2902"

In MySQL il comando equivalente è:

DAYOFMONTH(Argomento)
esempio:
DAYOFMONTH("2005-12-01") ==> 1

WEEKDAY(Argomento)

Restituisce il giorno della settimana (in numero) della data passata come Argomento. (1 ==> domenica, 2 ==> lunedì etc.). La query sottostante restituisce l'elenco dei nominativi degli studenti nati di domenica

SELECT Nominativo FROM Studenti WHERE WeekDay(NatoIl)=1

Lo stesso risultato poteva essere ottenuto con questa query:

SELECT Nominativo FROM Studenti WHERE Format(NatoIl,'dddd')="domenica"

In MySQL il comando equivalente è:

DAYOFWEEK(Argomento)/WEEKDAY(Argomento)
(1--> domenica, 2 --> Lunedì, ...) esempio:
DAYOFWEEK("2005-12-01") ==> 5 (giovedì)

HOUR(Argomento)

Restituisce l'ora dell'istante passato come Argomento.

In MySQL il comando equivalente è:

HOUR(Argomento)
esempio:
HOUR("10:01:32") ==> 10

MINUTE(Argomento)

Restituisce i minuti dell'istante passato come Argomento.

In MySQL il comando equivalente è:

MINUTE(Argomento)
esempio:
MINUTE("10:01:32") ==> 1

SECOND(Argomento)

Restituisce i secondi dell'istante passato come Argomento.

In MySQL il comando equivalente è:

SECOND(Argomento)
esempio:
SECOND("10:01:32") ==> 32