istext ???

[p-rat]

I have this code that I'm trying to fix. The data type on a field
(Field_Ticket_Number) was changed from integer on the back end to
nvarchar. I'm a beginner and am trying to figure out how to make this
code work now with a string instead of number:


Private Sub dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate
(Cancel As Integer)
Dim lngField_Ticket_Number As Long, strCriteria As String
If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
lngField_Ticket_Number =
Me.dbo_Field_Ticket_Header_Field_Ticket_Number
strCriteria = "[Field_Ticket_Number] = " &
lngField_Ticket_Number
If SearchTableByCriteria("dbo_Field_Ticket_Header",
strCriteria) Then
MsgBox "That ticket number already exists in the database.
Please enter another ticket number.", vbExclamation
Cancel = True
End If
Else
MsgBox "INVALID TICKET NUMBER.", vbExclamation
Cancel = True
End If

End Sub

 

[Clifford Bass]

Hi,

Try changing your strCriteria = line to this:

strCriteria = "[Field_Ticket_Number] = """ & lngField_Ticket_Number & """"

This assumes that there are no leading zeroes. If there are then try:

strCriteria = "[Field_Ticket_Number] = """ &
Format$(lngField_Ticket_Number, "000000") & """"

Substituting the appropriate number of zeroes to get the right total
number of digits being stored.

Hope that helps,

Clifford Bass

 

[p-rat]

When making this change to the code I have above it gives me the
INVALID TICKET NUMBER error. Would you know why this is happening?
Thanks for your response and help on this.

Hi,

     Try changing your strCriteria = line to this:

strCriteria = "[Field_Ticket_Number] =  """ & lngField_Ticket_Number & """"

     This assumes that there are no leading zeroes.  If there arethen try:

strCriteria = "[Field_Ticket_Number] =  """ &
Format$(lngField_Ticket_Number, "000000") & """"

     Substituting the appropriate number of zeroes to get the righttotal
number of digits being stored.

            Hope that helps,

                 Clifford Bass

I have this code that I'm trying to fix. The data type on a field
(Field_Ticket_Number) was changed from integer on the back end to
nvarchar. I'm a beginner and am trying to figure out how to make this
code work now with a string instead of number:

Private Sub dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate
(Cancel As Integer)
Dim lngField_Ticket_Number As Long, strCriteria As String
    If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
        lngField_Ticket_Number =
Me.dbo_Field_Ticket_Header_Field_Ticket_Number
        strCriteria = "[Field_Ticket_Number] =  " &
lngField_Ticket_Number
        If SearchTableByCriteria("dbo_Field_Ticket_Header",
strCriteria) Then
            MsgBox "That ticket number already exists in the database.
Please enter another ticket number.", vbExclamation
             Cancel = True
        End If
    Else
        MsgBox "INVALID TICKET NUMBER.", vbExclamation
        Cancel = True
    End If

End Sub- Hide quoted text -

- Show quoted text -

 

[Clifford Bass]

Hi,

That would indicate that the ticket number is not numeric. Try
changing the error message line to this so you can actually see what it is
seeing it as:

MsgBox "INVALID TICKET NUMBER (" &
Me.dbo_Field_Ticket_Header_Field_Ticket_Number & ").", vbExclamation

Clifford Bass

 

[p-rat]

Yes, I'm sorry, but I need this field to be text such as 'A5453-1',
etc. It's seeing what I'm typing in but comes back as invalid. This
field was changed on the back-end to NVARCHAR and front-end to TEXT.
So I don't know what else needs to be changed in the code I originally
posted. Thanks.

 

[Clifford Bass]

Hi,

That is a critical bit on information; that the reason for changing it
to text was to do those new formats. However, I probably could have guessed
that. Better to have it stated explicitely. Anyway, in that case, just
delete the If IsNumeric line and the Else through End If lines. Keep the
change to the strCriteria = line.

Of course, if your back end is well designed (i.e. unique index on
Field_Ticket_Number), you probably could eliminate this part entirely and let
the back end complain about a pre-existing ticket number. Perhaps a little
less tidy for the user.

Clifford Bass