code is broke - need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

code is broke - need help

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-22 : 20:29:54

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 SQL Server 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

ANY help with this would be appreciated. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-12-23 : 08:17:46

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

You need to delimit string literals with quotes, unlike numeric values, in your SQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-23 : 09:45:50

I made this change and get the error message 'Invalid Ticket Number'. I need this field to accept typed in values such as A54-1 or 12345-1A. Thanks for your help and expertise in this matter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources