1

I am trying to write in Access an If statement that changes the value of a form value depending on the fact that another value doesn't contain the character "*".

So far, I have this :

If Forms![Saisie commande panier]![Qualité].Value = "CA" _
Or Forms![Saisie commande panier]![Qualité].Value = "Salarié" _
And Not Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation].contains "*" Then
Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Remise] = "30"

Which, of course, is not working. Do you have any solution for that ?

Knowing that this form is a spreadsheet. NOT LIKE doesn't work, <> doesn't work either... There must be a simple function, right ?

Thank you for your help, Flo

Florence S
  • 23
  • 5
  • 2
    It is `InStr` that searches a text value. – Gustav Jun 18 '21 at 14:08
  • ` If Forms![Saisie commande panier]![Qualité].Value = "CA" _ Or Forms![Saisie commande panier]![Qualité].Value = "Salarié" _ And InStr(Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation], "*") = False Then Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Remise] = "30" End If ` **Says the type is not compatible. Also tried with** ` AND Not InStr(Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation], "*") Then ` – Florence S Jun 18 '21 at 14:15

1 Answers1

3

Use Like: If Not someControl Like "*[*]*" Then
Note that the not is before the expression in VBA, while in SQL you could use not like.

Regarding your comment, I suspect the problem is in Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation]. You probably miss a Form somewhere. See this one or this one.
It's probably something like
Forms![Saisie commande panier]![SF_Panier].Form![SF_Détail_panier].Form![Designation]
By the way, if your code is in the main form, you could write it
Me![SF_Panier].Form![SF_Détail_panier].Form![Designation]

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • `If Forms![Saisie commande panier]![Qualité].Value = "CA" _ Or Forms![Saisie commande panier]![Qualité].Value = "Salarié" _ And Not Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation] Like "*[*]*" Then Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Remise] = "30" End If` It says that the type is not compatible :( – Florence S Jun 18 '21 at 14:19
  • Error might be somewhere else, like in `Remise = 30` (no quotes) – iDevlop Jun 18 '21 at 14:23
  • No it stops at the line before, right on "And not Form!... Like... " – Florence S Jun 18 '21 at 14:28
  • @FlorenceS see updated answer. Is your code in form [Saisie commande panier] ? – iDevlop Jun 18 '21 at 14:35
  • Following what you said `If Forms![Saisie commande panier]![Qualité].Value = "CA" _ Or Forms![Saisie commande panier]![Qualité].Value = "Salarié" _ And Not Me![SF_Détail_panier].Form.[Designation] Like "*[*]*" Then Me![SF_Détail_panier].Form.[Remise] = "30" End If` Still not working. Knowing my button is on [SF_Panier] (really useful link however, thx) – Florence S Jun 18 '21 at 14:59
  • 1
    If you use your immediate window, you can evaluate each component of your IF statement separately to find exactly which part is causing the problem. https://stackoverflow.com/questions/794255/how-do-you-use-the-immediate-window-in-visual-studio E.g. `?Not Forms![Saisie commande panier]![SF_Panier]![SF_Détail_panier]![Designation] Like "*[*]*"` – Joe Jun 18 '21 at 15:08
  • Well, no more error, but nothing happens. I tried with Not like... and InStr. I think I'm just not gonna do it... – Florence S Jun 18 '21 at 16:19
  • C'est en forgeant qu'on devient fort en jurons 8-) – iDevlop Jun 18 '21 at 17:17