[AC-2007] Casse : savoir si valeur de champ commence par majuscule ? [Résolu]

[Gregorx]

Bonjour,

Tout est dans le titre ! ^^

Il ne me reste plus qu'à résoudre ce petit problème pour en finir avec mon bazar...
Pour ce faire, je dois supprimer des valeurs de champs (strings) SI ET SEULEMENT SI elles contiennent une majuscule (si non, le champ intervient dans la suite des procédures).

Ma question est donc :

Comment savoir si champA CONTIENT ou COMMENCE par une majuscule ?

J'ai (bien ?) cherché, ici et ailleurs, mais sans résultat sur la moindre piste pour ça...

Merci beaucoup, pour votre aide et la mine qu'est ce site !

[pc75]

Bonjour,

Comment savoir si champA CONTIENT une majuscule ?

Code :

1
2
3
4
5
6
 
for i = 1 to len(champA)
   if mid(champA(i, 1) >= chr(65) and mid(champA(i, 1) <= chr(90) then
      Contient = true
   end if
next i

Comment savoir si champA COMMENCE par une majuscule ?

Code :

1
2
3
4
 
if left(champA, 1) >= chr(65) and left(champA, 1) <= chr(90) then
   Commence = true
end if

[Gregorx]

Merci pour votre réponse...qui me confirme une énième fois que sans bases, on ne va pas bien loin !

Cependant, j'ai beau intégrer votre proposition dans un SQL (on fait ce qu'on peut ! ^^), il semble ne pas faire la différence entre min- et maj-uscules...

Pourriez-vous me confirmer si la syntaxe est bonne, ou si je me suis planté quelque part svp ?

Merci encore !

Code :

SQL1 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET PLANTAE_LRR_UNMATCHED_TOTAL.OK5 = '' WHERE Left([PLANTAE_LRR_UNMATCHED_TOTAL].[OK5],1) >= chr(65) And Left([PLANTAE_LRR_UNMATCHED_TOTAL].[OK5],1) <= chr(90) ;"

[lelensois16]

Bjr ,


Essayez la syntaxe suivante :

Code :

SQL1 = " UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET PLANTAE_LRR_UNMATCHED_TOTAL.OK5 = '' WHERE Left([PLANTAE_LRR_UNMATCHED_TOTAL].[OK5],1) >= " & chr(65) & "  And Left([PLANTAE_LRR_UNMATCHED_TOTAL].[OK5],1) <= " & chr(90) & " ;"

Ensuite , faire attention aux valeurs vides , faut voir s'il faut utiliser les propriétés Empty etc ...

Bien cdt

RM

[Gregorx]

malheureusement sans résultat... :'(
Une autre idée svp ?

[Gregorx]

Pour ceux que le sujet intéressera peut-être un jour :

A défaut d'une solution propre (et en une ligne !) telle que proposée par les bonnes âmes venues à ma rescousse, j'ai finalement créé 26 QRY avec 26 variables...
Pas jojo, ais l'important c'est que ça marche !

Si ça peut aider, code ci-après.

Merci encore.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
Private Sub MAJ_OK15()
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
Dim SQL6 As String
Dim SQL7 As String
Dim SQL8 As String
Dim SQL9 As String
Dim SQL10 As String
Dim SQL11 As String
Dim SQL12 As String
Dim SQL13 As String
Dim SQL14 As String
Dim SQL15 As String
Dim SQL16 As String
Dim SQL17 As String
Dim SQL18 As String
Dim SQL19 As String
Dim SQL20 As String
Dim SQL21 As String
Dim SQL22 As String
Dim SQL23 As String
Dim SQL24 As String
Dim SQL25 As String
Dim SQL26 As String
Dim SQL27 As String
 
Dim champ As String
Dim chr As String
champ = "OK15"
 
chr1 = "chr(65)"
chr2 = "chr(66)"
chr3 = "chr(67)"
chr4 = "chr(68)"
chr5 = "chr(69)"
chr6 = "chr(70)"
chr7 = "chr(71)"
chr8 = "chr(72)"
chr9 = "chr(73)"
chr10 = "chr(74)"
chr11 = "chr(75)"
chr12 = "chr(76)"
chr13 = "chr(77)"
chr14 = "chr(78)"
chr15 = "chr(79)"
chr16 = "chr(80)"
chr17 = "chr(81)"
chr18 = "chr(82)"
chr19 = "chr(83)"
chr20 = "chr(84)"
chr21 = "chr(85)"
chr22 = "chr(86)"
chr23 = "chr(87)"
chr24 = "chr(88)"
chr25 = "chr(89)"
chr26 = "chr(90)"
 
SQL1 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr1 & ", 0) ;"
SQL2 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr2 & ", 0) ;"
SQL3 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr3 & ", 0) ;"
SQL4 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr4 & ", 0) ;"
SQL5 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr5 & ", 0) ;"
SQL6 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr6 & ", 0) ;"
SQL7 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr7 & ", 0) ;"
SQL8 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr8 & ", 0) ;"
SQL9 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr9 & ", 0) ;"
SQL10 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr10 & ", 0) ;"
SQL11 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr11 & ", 0) ;"
SQL12 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr12 & ", 0) ;"
SQL13 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr13 & ", 0) ;"
SQL14 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr14 & ", 0) ;"
SQL15 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr15 & ", 0) ;"
SQL16 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr16 & ", 0) ;"
SQL17 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr17 & ", 0) ;"
SQL18 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr18 & ", 0) ;"
SQL19 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr19 & ", 0) ;"
SQL20 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr20 & ", 0) ;"
SQL21 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr21 & ", 0) ;"
SQL22 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr22 & ", 0) ;"
SQL23 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr23 & ", 0) ;"
SQL24 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr24 & ", 0) ;"
SQL25 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr25 & ", 0) ;"
SQL26 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE InStr(1, PLANTAE_LRR_UNMATCHED_TOTAL." & champ & "," & chr26 & ", 0) ;"
SQL27 = "UPDATE PLANTAE_LRR_UNMATCHED_TOTAL SET " & champ & " = '' WHERE PLANTAE_LRR_UNMATCHED_TOTAL." & champ & " = 'ex' Or PLANTAE_LRR_UNMATCHED_TOTAL." & champ & " = 'subsp' Or PLANTAE_LRR_UNMATCHED_TOTAL." & champ & " = 'de' ;"
 
DoCmd.SetWarnings False
 
DoCmd.RunSQL (SQL1)
DoCmd.RunSQL (SQL2)
DoCmd.RunSQL (SQL3)
DoCmd.RunSQL (SQL4)
DoCmd.RunSQL (SQL5)
DoCmd.RunSQL (SQL6)
DoCmd.RunSQL (SQL7)
DoCmd.RunSQL (SQL8)
DoCmd.RunSQL (SQL9)
DoCmd.RunSQL (SQL10)
DoCmd.RunSQL (SQL11)
DoCmd.RunSQL (SQL12)
DoCmd.RunSQL (SQL13)
DoCmd.RunSQL (SQL14)
DoCmd.RunSQL (SQL15)
DoCmd.RunSQL (SQL16)
DoCmd.RunSQL (SQL17)
DoCmd.RunSQL (SQL18)
DoCmd.RunSQL (SQL19)
DoCmd.RunSQL (SQL20)
DoCmd.RunSQL (SQL21)
DoCmd.RunSQL (SQL22)
DoCmd.RunSQL (SQL23)
DoCmd.RunSQL (SQL24)
DoCmd.RunSQL (SQL25)
DoCmd.RunSQL (SQL26)
DoCmd.RunSQL (SQL27)
 
End Sub

[micniv]

Bonsoir,

En une requete, ceci devrait le faire :

Code :

 ... WHERE strcomp(left(" & champ & ",1),Ucase(left(" & champ & ",1)),vbBinaryCompare) = 0 ;"