if one field is null

[ally]

Hello

I am trying to make a function work which would enable me to do a
mailing.
My fields are

Table: Clients
RefClient
Titre
NomFamille
Prénom
Societé/Entreprise
ContactPersonne
Adresse
Code
Ville

Pays
all text except the first one Auto Entier long

My function is

Function Adresses(pvarNoAdr As Variant, Optional pfSansNom As Boolean)
As Variant

Dim RefClient As Long

If Not TablesOpen Then OpenTables
Adresses = Null
If IsNull(pvarNoAdr) Then Exit Function
RefClient = CLng(pvarNoAdr)
With Clients
.Seek "=", RefClient
If .NoMatch Then Exit Function
If Not pfSansNom Then
Adresse = ! Titre + vbCrLf & ! Prénom + " " & ! NomFamille
Else
Adresse = "?"
End If
Adresse = Adresse _
& vbCrLf + ! Société/Entreprise +!ContactPersonne
& vbCrLf + ! Adresse _
& vbCrLf +!Pays + "-"_& ! CodePostal & " " + ! Ville


If pfSansNom Then _
Adresse = Mid(Adresse, 4)
End With

End Function

And it does not work !

Could somebody please help me correct this code.
I do not understand a lot of VBA I am just trying to convert some code
found somewhere else
I thought it right to make a report with a text field
And to put this function in a module.

Thank you in advance

ally

 

[John Vinson]

Could somebody please help me correct this code.
I do not understand a lot of VBA I am just trying to convert some code
found somewhere else
I thought it right to make a report with a text field
And to put this function in a module.

Why? No code is needed AT ALL. You can use an expression in a Query.

You don't say what's incorrect or what problem you're having, though I
can see several things (the disconnected ! for one); but try a
calculated field

AdresseShow: Adresse & Chr(13) & Chr(10) & ([Société/Entreprise] +
[ContactPersonne] + Chr(13) & Chr(10)) & [Adresse] & Chr(13) & Chr(10)
& [Pays] & "-" & [CodePostal] & " " & [Ville]

Your code is inserting Adresse twice, once before the
[Société/Entreprise] and once after - I don't know if that is what you
intend.

The important issues (in query or code): any fieldname containing
special characters such as / or blank must be delimited by square
brackets; and the & and + operators both concatenate strings, but
treat NULL differently. + will "propagate nulls" so if either
[Société/Entreprise] or [ContactPersonne] is NULL, the entire
parenthetical expression will be NULL (and you won't get an extra
line).

If you're doing this for a Report, it's easier to just use bound
textboxes on the report with their Can Shrink and Can Grow properties
set.

John W. Vinson[MVP]

 

[ally]

sorry .had problems with my internet

just wanted to thank you for your help
I made a calculated field
and everything works ok
thanks again
ally

Could somebody please help me correct this code.
I do not understand a lot of VBA I am just trying to convert some code
found somewhere else
I thought it right to make a report with a text field
And to put this function in a module.

Why? No code is needed AT ALL. You can use an expression in a Query.

You don't say what's incorrect or what problem you're having, though I
can see several things (the disconnected ! for one); but try a
calculated field

AdresseShow: Adresse & Chr(13) & Chr(10) & ([Société/Entreprise] +
[ContactPersonne] + Chr(13) & Chr(10)) & [Adresse] & Chr(13) & Chr(10)
& [Pays] & "-" & [CodePostal] & " " & [Ville]

Your code is inserting Adresse twice, once before the
[Société/Entreprise] and once after - I don't know if that is what you
intend.

The important issues (in query or code): any fieldname containing
special characters such as / or blank must be delimited by square
brackets; and the & and + operators both concatenate strings, but
treat NULL differently. + will "propagate nulls" so if either
[Société/Entreprise] or [ContactPersonne] is NULL, the entire
parenthetical expression will be NULL (and you won't get an extra
line).

If you're doing this for a Report, it's easier to just use bound
textboxes on the report with their Can Shrink and Can Grow properties
set.

John W. Vinson[MVP]