Data Type mismatch

[nuttychick]

Hi - Hope someone can help - I'm stuck!
I have an inherited database thats about 10 years old written in Access 97.

There is a cmd button that is importing data from an excel sheet into a table.
One of the fields [TBCS Code] being imported is a two digit number - always starting with 0.

The field [TBCS Code] was orgionally a text field. Changes in the orgional data source meant that this figure is no longer available as text. I have needed to change this to a numeric field in the db with a format of 00.

The import works fine now and I have the TBCS Field showing the correct figures such as 01 or 04.

However when running the following code elsewhere in the database it gives the error Datatype mismatch in criteria expression.
I'm assuming its the where clause - but I'm not that experienced with code like this, I haven't got clue what all the & mean!

Is it possible to re-write this code to manage with this new format, or do I need to start at the begining!?

Thanks

MySQL = "SELECT studies.study_id, studies.Feasibility_code, studies.study_title, Actuals.[TBCS Code], Actuals.[Year/Month], tblBusinessCalendar.BusinessCalendarID, Actuals.Actual "
MySQL = MySQL + "FROM (studies INNER JOIN Actuals ON studies.Feasibility_code = Actuals.[Study Code]) INNER JOIN tblBusinessCalendar ON Actuals.[Year/Month] = tblBusinessCalendar.BusinessCalendarMonthYear "
[COLOR=DarkOrchid]MySQL = MySQL + "WHERE ((studies.study_id = " + Trim([Forms]![Study_Main].[Study_id]) + ") And ((Actuals.[TBCS Code] >= '" & "01" & "' And Actuals.[TBCS Code] <= '" & "07" & "'))) "[/COLOR]
MySQL = MySQL + "ORDER BY studies.Feasibility_code; "

 

[KenHigg]

To start with, you really need to decide if this should be a numeric or a text string. If you intend to do any calculations with the field, then you should make it numeric, if not, then make it a string in the database in which are going to import it into.

Having said that, if you make it a numeric, the 0 in 04 makes no sense and should be eliminated.

???

 

[nuttychick]

Thanks Ken.

I'm going to take another look and try some different things based on what you have said!

Thanks again

 

[nuttychick]

Re tihink - HELP!

OK been back to the drawing board.

Because there is existing data in the table which needs to remain. I need to import this as a text field so that I can include the 0 before number - otherwise the numbers are not recognised later on.

This is the code that imports the info from excel.

How can I force it to import with a 0 in front of the TBCS code?

Private Sub LoadActualsDataButton_Click() On Error GoTo Err_LoadActualsDataButton_Click

' This procedure performs a two file match between the Actuals table (the Master file) and ' The Actuals spreadsheet file (the Transaction file).
'
' Keys : Study Code|Work Package|Period
'
' If the Master key < Transaction key then
'       Read the next Master record.
' If the Transaction key > Master key then
'       Add the transaction record to the Master file
'       Read the next Transaction record.
' If the Master key = Transaction key then
'       Update the value on the Master record with the value on the Transaction record
'       Read the next Master Record
'       Read the next Transaction record.
'
' End of File processing
'       At End of File on the Master file, set the Master key to "ZZZZZZ"
'       At End of File on the Transaction file, set the Transaction key to "ZZZZZZ"
'       Continue processing until both keys are equal to "ZZZZZZ"


Dim MyDB As Database, MySQL As String, MySet As Recordset Dim appExcel As Excel.application Dim MyFiles As String Dim MasterKey As String, TransactionKey As String

Set MyDB = CurrentDb()
Set appExcel = CreateObject("Excel.Application")

' Set up the transaction file (Actual Data Spreadsheet)

MyFiles = appExcel.GetOpenFilename("Excel Files(*.xls),*.xls", , "Open Actuals Spreadsheet") If MyFiles = "False" Then Exit Sub

appExcel.Workbooks.Open FileName:=MyFiles, ReadOnly:=True appExcel.Visible = False

' Check that this is a genuine Actual spreadsheet On Error Resume Next Let Err.Number = 0 appExcel.Sheets("Sheet1").Range("B1").Select
If Err.Number = 9 Then
   MsgBox "This is not a valid Actuals Spreadsheet."
    appExcel.Quit
   Exit Sub
End If

If appExcel.ActiveCell <> " Extracted Actuals Data" Then
    MsgBox "This is not a valid Actuals Spreadsheet."
    appExcel.Quit
    Exit Sub
Else
    appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
    TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2) End If appExcel.Visible = True

' Set up the Master File (Actual Table)

MySQL = "SELECT Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month], Actuals.Actual "
MySQL = MySQL + "From Actuals "
MySQL = MySQL + "ORDER BY Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month]; "
Set MySet = MyDB.OpenRecordset(MySQL)
If MySet.EOF Then
    MasterKey = "ZZZZZZ"
Else
    MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month] End If

Do Until TransactionKey = "ZZZZZZ"
    If MasterKey < TransactionKey Then
        ' Read the next master record
        MySet.MoveNext
        MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
        GoTo Next_Loop
    End If
    If MasterKey > TransactionKey Then
        ' Add a new record from the Transaction to the Master
        MySet.AddNew
        MySet![Study Code] = appExcel.ActiveCell
        [COLOR=Purple]MySet![TBCS Code] = appExcel.ActiveCell.OffSet(0, 1)[/COLOR]
        MySet![Year/Month] = appExcel.ActiveCell.OffSet(0, 2)
        MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
        MySet.Update
'        MySet.Requery
        appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
        TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
        GoTo Next_Loop
    End If
    ' Keys are equal so update the Master with the Transaction value
    MySet.Edit
    MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
    MySet.Update
'    GoTo Next_Loop
    appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
    TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
    MySet.MoveNext
    MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
Next_Loop:
Loop

Exit_LoadActualsDataButton_Click:
    Exit Sub

Err_LoadActualsDataButton_Click:
    MsgBox "An has occured." & vbCrLf & vbCrLf & _
    "Error number: " & Err.Number & vbCrLf & vbCrLf & _
    "Description: " & Err.Description
    Resume Exit_LoadActualsDataButton_Click
    
' = Mid(ActiveCell, 1, (Len(ActiveCell) - 1)))

End Sub

 

[KenHigg]

I hate to nag, but...

Earlier you said you had the import working fine, with the leading '0'. Can you go back to whatever you were doing before?

Also, If I follow correctly, somewhere in a criteria in a query, you need to get records where this fld is greater to or maybe less than a numeric value...

Is this correct?

 

[workmad3]

MySql = MySql & "Whatever"
not
MySQL = MySql + "Whatever"

Also, a >= operator makes no sense when comparing a text field, so you should probably have

Actuals.[TBCS Code] >= " & "01" & " And Actuals.[TBCS Code] <= " & "07" & "))) "
without the additional single quotes

 

[KenHigg]

WorkMad3 - You may be close but your proposed line of code still looks flawed...

I think she still needs to val() the 'TBCS Code' fld or convert it to a numeric... And maybe use a 'between'...

 

[nuttychick]

Fixed it!..but.....

Hi guys,

Had prepared a big reply to you, but then I went back to try something suggested by my father... in the macro that converts the spreadsheet and generates the TBCS code, I added a ' to the begining of the two digit result. e.g. if the current code = 12 then the TBCS Code needs to ='01

The ' is not displayed and the import to access ignores the ' I get my two digit number.

All the code works! YEAY!

But you guys could certainly help with another issue around this! After I have done this import access freezes and timesout...

Is there some sort of 'end' statement that needs to be included? It used to work with the old excel import. Any ideas what could cause this?