conversion failed when converting varchar value to int

[dodyryda]

Hi hoping someone may be able to help..

I'm getting this error on a sql select statement

conversion failed when converting varchar '433salisburydelivernjhnj' value to int my sql skills are somewhat basic.. here's what i have

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim s As String
s = Me![ID] & Me![BR] & Me![JO] & Me![CO] & Me![SI]
rs.Open "SELECT [ID] & [BRANCH] & [JOB TYPE] & [COMPANY NAME]& [SITE LOCATION] FROM JobSpec WHERE ((([ID] & [BRANCH] & [JOB TYPE] & [COMPANY NAME] & [SITE LOCATION])='" & s & "'));", CurrentProject.Connection, adOpenDynamic
If rs.RecordCount <> 0 Then
MsgBox "This Job appears to have already been Added. Use edit entered Job to Amend the found Job."
Else
Set rs = Me.Recordset.Clone
If IsNull(Me![BR]) Or IsNull(Me![JO]) Or IsNull(Me![CO]) Or IsNull(Me![SI]) Or IsNull(Me![EQ]) Then
MsgBox "You Must Enter Branch,Job Type, Company Name, Site Location and Equipment Field to add a job"
Else
rs.AddNew
rs![ID] = Me![ID]
rs![Branch] = Me![BR]
rs![Deliver Time] = Me![DE]
rs![Job Type] = Me![JO]
rs![Company Name] = Me![CO]
rs![Site Location] = Me![SI]
rs![Equipment] = Me![EQ]
rs![Purchase Goods] = Me![PU]
rs![Driver] = Me![DR]
rs.Update
Command50_Click_Exit:
MsgBox "Job Added"

'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If

End If

End If

 

[DCrake]

Where abouts in your code are attempting to convert the string to Integer?

Anyway You cannot convert "123ABC" to Integer. You would first have to strip out all the non numeric characters first.

 

[dodyryda]

Hi DC..

thank for the prompt response.. I don't want to convert '123string' to integer... I just want to select record based on comparision of my text boxes [ID] [BR] [JO] [CO] [SI] and the fields in my table [ID] [BRANCH] [JOB TYPE] [COMPANY NAME] [SITE LOCATION] My [ID] field is an integer though but is needed in the comparison..

 

[dodyryda]

Hi DC..

Thanks for the prompt response.. further...

I do not wish to convert the '123XXX' string to integer but to simply compare it against my fields in the database ie. if [ID] & [BR] & [JO] & [CO] & [SI] = [ID] & [BRANCH] & [JOB TYPE] & [COMPANY NAME]& [SITE LOCATION] then ....

My ID field is an integer which i believe is causing a problem in the select statement but is required to complete the comparision..

 

[dodyryda]

sorry 4 the double post i keep getting white screen on post of quick reply!

 

[LPurvis]

Jet will perform data type conversions implicitly (as will the expression service and VBA). You refer to CurrentProject.Connection. Is this an ADP file and this is actually a SQL Server BE you're hitting?
That would explain the conversion problems (T-SQL doesn't offer impicit conversions - and will probably return an error message about the attempt to convert the wrong one anyway just to confuse you - sods law lol).

You need to perform the conversions youself.

rs.Open "SELECT CONVERT(varChar,[ID]) + [BRANCH] + [JOB TYPE] + [COMPANY NAME] + [SITE LOCATION] FROM JobSpec WHERE CONVERT(varChar,[ID]) + [BRANCH] + [JOB TYPE] + [COMPANY NAME] + [SITE LOCATION]='" & s & "'", CurrentProject.Connection, adOpenDynamic

Of course - there were other syntax problems in there too - so I'm not convinced at all that this is the case.

Cheers.

 

[dodyryda]

Thanks LPurvis ..

yes is an adp hitting sql backend.. I had this working as an access db but have a user defined function that i need to specify as query criteria and found out tis not supported.. typical!.. so having to convert to adp and sql db.... nice one thanks will give this a go when back on line with the db..

 

[LPurvis]

No problem but umm... whoa - having to convert?
Are you talking about a SQL Server UDF that you wanted to use in a query request but couldn't through linked tables?
There are other options still through an MDB/ACCDB for accessing server data.
You don't have to move over for that alone.

 

[dodyryda]

.well I'd rather not convert as the database is built and runs ok.. the only problem i had, is i need to access the data outside of access [program called datapoint by presentation point] and a number of my queries are using a user defined function as criteria... as I understand it the ole driver does not support this but sql driver will... so was attempting to convert the db over.. not sure if i can get away with linked tables

 

[LPurvis]

Hi again.

Are you saying that your queries are using Access (VBA) functions - and that's not ideal for linked tables (are you experiencing bad performance - what does the actual query look like?)
Or are you saying that you want to use a SQL Server UDF in your query.

Access can't use VBA functions directly against with server data (in whatever mode, as ADO connections will generally be using the OLEDB provider and ADP - as it uses the same OLEDB provider to establish a connection. I don't know what you mean by the "SQL" driver - the drivers are either OLEDB or ODBC - in each case it's the appropriate SQL Server driver) An MDB on linked tables can - but only by local filtering (exactly what you want to avoid).

If you have a UDF on the server and want to use that in your queries - an ADP and connections in code from an MDB (using ADO and the OLEDB provider) can use those server UDF's in queries. Any T-SQL based syntax works - because it is executed on the server.

Indeed - if the results don't need to be updatable (e.g. reporting of list returning) then you can use passthroughs (which use ODBC connections the same as linked tables do) to execute such server based queries.

If you post the queries and functions in question - perhaps more exact suggestions could be made?

Cheers.

 

[dodyryda]

Thanks .. Leigh

I have only this working on my .mdb at the mo, haven't created the server function yet.. The function and query I have for the .mdb are shown below. I need to re-create this if possible on my sql server.. The results of the query do not need to be updateable.

Function GetBusinessDay(FromDate As Date, Offset As Long, Optional WorkDays As String = "23456", _
    Optional ZeroOffsetBehavior As Long = 1, Optional HolidayTblName As String = "", _
    Optional HolidayDateField As String = "")
    
    Dim DaysCounter As Long
    Dim TestDate As Date
    Dim Holidays As String
    Dim rs As ADODB.Recordset
        
    FromDate = DateValue(FromDate)
    
    If HolidayTblName <> "" Then
        If Left(HolidayTblName, 1) <> "[" Then HolidayTblName = "[" & HolidayTblName & "]"
        If Left(HolidayDateField, 1) <> "[" Then HolidayDateField = "[" & HolidayDateField & "]"
        Set rs = New ADODB.Recordset
        rs.Open "SELECT " & HolidayDateField & " FROM " & HolidayTblName, CurrentProject.Connection
        Do Until rs.EOF
            Holidays = Holidays & Format(rs.Fields(0).Value, "|yyyy-mm-dd|")
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    End If
    
    Select Case Offset
        Case 0
            If InStr(1, WorkDays, Weekday(FromDate)) > 0 And _
                InStr(1, Holidays, Format(FromDate, "|yyyy-mm-dd|")) = 0 Then
                GetBusinessDay = FromDate
            ElseIf ZeroOffsetBehavior = 0 Then
                GetBusinessDay = FromDate
            ElseIf ZeroOffsetBehavior < 0 Then
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", -1, TestDate)
                Loop
                GetBusinessDay = TestDate
            Else
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", 1, TestDate)
                Loop
                GetBusinessDay = TestDate
            End If
        Case Is > 0
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", 1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter + 1
                End If
            Loop
            GetBusinessDay = TestDate
        Case Else
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", -1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter - 1
                End If
            Loop
            GetBusinessDay = TestDate
    End Select
        
End Function

example query using next business day

SELECT JobSpec.[Company Name], JobSpec.Equipment
FROM Calendar INNER JOIN JobSpec ON Calendar.ID = JobSpec.ID
WHERE (((Calendar.[Job Date])=GetBusinessDay(Date(),1,"23456",1,"Holidays","Holiday Dates")) AND ((JobSpec.Branch)="Salisbury") AND ((JobSpec.[Job Type])="DELIVER"));

 

[LPurvis]

Indeed - that VBA UDF will be of no use to you at all in an ADP.
(Less than it would be in an MDB!)
If you have already or can convert that VBA function into a T-SQL UDF then you can use it in a T-SQL query through an ADP or from an MDB via code (a recordset).

Alas I don't have anything like the time to convert it. (And it wouldn't be a real question anyway "convert this"
There will be SQL Server equivalent functions out there for you to use instead (and tweak) - it's just a "business days" function and that's pretty common.

Cheers.

 

[dodyryda]

cool.. thanks for you help though leigh.... i'll have a good look around for something already out there.. sure there must be someone that has done something similar...

 

[LPurvis]

Indeed.
Here's a potential head-start in the search.
SQL Server Central - calculatingworkdays

Cheers.