Search code examples
vbaexcelms-access-2016

convert an excel number to a short text in access VBA

Hi I have a table in access that gets its data from excel like so

Public Sub ImportExcel(fileName As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "JobNumber", fileName, False, "K2:K2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DateTable", fileName, False, "C3:C3"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Supplier", fileName, False, "C5:C5"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Graphic", fileName, False, "C6:C6"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Shift", fileName, False, "L1:L1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Size", fileName, False, "L3:L3"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "RollInfo", fileName, False, "B10:F102"
Call CheckNullValues
Call AddLookUps
Call AddPressData
Call deleteZigZagTables
End Sub

I'm have problems with the converting the value of C6 in excel to text in access what I'm currently doing is this

Public Sub AddPressData()
Dim SQLstring As String
Dim GraphicString As String
Dim rst As Recordset

SQLstring = "SELECT F1 FROM GRAPHIC;"
Set rst = CurrentDb.OpenRecordset(SQLstring)
If Not IsNumeric(rst.Fields(0).Value) Then
    SQLstring = "INSERT INTO Press ( MasterRollNumber, JobNumber, DateDay, Shift, Supplier, Graphic, [Size], [Linear Measure], [Pallet Number] ) SELECT RollInfo.F1, JobNumber.F1, DateTable.F1, Shift.F1, Supplier.F1, Graphic.F1, (Size.F1), RollInfo.F3, RollInfo.F4 FROM RollInfo, JobNumber, DateTable, Shift, Supplier, Graphic, [Size] WHERE NOT ISNULL(RollInfo.F1);"
    CurrentDb.Execute SQLstring, dbFailOnError
Else
    SQLstring = "INSERT INTO Press ( MasterRollNumber, JobNumber, DateDay, Shift, Supplier, Graphic, [Size], [Linear Measure], [Pallet Number] ) SELECT RollInfo.F1, JobNumber.F1, DateTable.F1, Shift.F1, Supplier.F1, CSTR(Graphic.F1), (Size.F1), RollInfo.F3, RollInfo.F4 FROM RollInfo, JobNumber, DateTable, Shift, Supplier, Graphic, [Size] WHERE NOT ISNULL(RollInfo.F1);"
    CurrentDb.Execute SQLstring, dbFailOnError
End If
End Sub

Most of the time graphic is a string but every once and a while it can be a number and when I run the code when its a number it will just leave the field blank for each record.

If Anyone has any suggestions that will be a big help Thank you

Solution

  • There are many ways to do this, but this is the way I would do it because then you wont need the If statement at all.

     Graphic.F1 & ''

    When you concatenate a string with a number, the result is a string.

    CSTR is a VBA function, and you are trying to use it in a SQL statement.