NZ related question

[dominicgingras]

I have a database desing for a transport company picking up people in different place like a taxi. Sometimes they schedule a pickup and at the sametime they schedule the return of that person:

Private Sub Retour_Click()

On Error GoTo Err_Retour_Click

Dim IntersectionArrivee As String
Dim AppartementArrivee As String
Dim NoCiviqueArrivee As String
Dim RueArrivee As String
Dim VilleArrivee As String
Dim ProvinceArrivee As String
Dim IntersectionDepart As String
Dim AppartementDepart As String
Dim NoCiviqueDepart As String
Dim RueDepart As String
Dim VilleDepart As String
Dim ProvinceDepart As String
Dim Établissement As Integer
Dim Client As Integer


IntersectionArrivee = Me.IntersectionArrivee
AppartementArrivee = Me.AppartementArrivee
NoCiviqueArrivee = Me.NoCiviqueArrivee
RueArrivee = Me.RueArrivee
VilleArrivee = Me.VilleArrivee
ProvinceArrivee = Me.ProvinceArrivee
IntersectionDepart = Me.IntersectionDepart
AppartementDepart = Me.AppartementDepart
NoCiviqueDepart = Me.NoCiviqueDepart
RueDepart = Me.RueDepart
VilleDepart = Me.VilleDepart
ProvinceDepart = Me.ProvinceDepart
Établissement = Me.EtablissementId
Client = Me.ClientID


    DoCmd.GoToRecord , , acNewRec
    
Me.IntersectionArrivee = IntersectionDepart
Me.NoCiviqueArrivee = NoCiviqueDepart
Me.AppartementArrivee = AppartementDepart
Me.RueArrivee = RueDepart
Me.VilleArrivee = VilleDepart
Me.ProvinceArrivee = ProvinceDepart
Me.IntersectionDepart = IntersectionArrivee
Me.NoCiviqueDepart = NoCiviqueArrivee
Me.AppartementDepart = AppartementArrivee
Me.RueDepart = RueArrivee
Me.VilleDepart = VilleArrivee
Me.ProvinceDepart = ProvinceArrivee
Me.RechCode = Client
Me.Établissement = Nz(Établissement, 0)

DoCmd.OpenForm "CalendarPopupRetour"

Exit_Retour_Click:
    Exit Sub

Err_Retour_Click:
    MsgBox Err.Description
    Resume Exit_Retour_Click
    
End Sub

You can see how my code is working:

take the departure field, save them in variables, and then create a new record, invert the departure and the arrival, and finaly ask for a time in a form.

My problem is the folowing, some time some field are empty sometimes, like etablissement. So I tried to use Nz so I dont get a Null error but its does not work, Any idea?

 

[dhookom]

etablissement can't be null with your code since you dim it as a String. You would need to use:
Dim Établissement As Variant

Duane
MS Access MVP

 

[dominicgingras]

Thank you! Its working