VBA to make numeric value short text

Good morning everyone,

I'm getting Data type mismatch in criteria expression on a VBA that I'm using, I'm not the author, it was working fine until upgrade from office 2013 to 365.

The code:

Function UpdateLocalCurrencyAmount()
On Error GoTo Err_UpdateLocalCurrencyAmount

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sql As String

Dim RRTerCode As String
Dim RRMonth As String
Dim RRServiceType As String
Dim CB_Amount As Double



Set dbs = CurrentDb

sql = "SELECT [Financial Reporting - Recharge Records].[Territory-code], [Financial Reporting - Recharge Records].[Process Month (YYMM)], [Financial Reporting - Recharge Records].[Service Type] " & _
"FROM [Financial Reporting - Recharge Records] " & _
"ORDER BY [Financial Reporting - Recharge Records].[Territory-code], [Financial Reporting - Recharge Records].[Process Month (YYMM)], [Financial Reporting - Recharge Records].[Service Type];"

Set qdf = dbs.CreateQueryDef("", sql)
Set rst = qdf.OpenRecordset


If rst.EOF = False Then
    
    Do Until rst.EOF

        CB_Amount = -1
        
        RRTerCode = rst.Fields("Territory-code")
        RRMonth = rst.Fields("Process Month (YYMM)")
        RRServiceType = Nz(rst.Fields("Service Type"), "")

        
        'first lookup if a fixed amount was set at the period in question
        
        sql = "SELECT Mtbl_02_Recharge_FixedAmounts.[Fixed amount for all Recharges (Provide in local currency)] " & _
                "FROM Mtbl_02_Recharge_FixedAmounts " & _
                "WHERE (((Mtbl_02_Recharge_FixedAmounts.TerrCode_FA)='" & RRTerCode & "') AND ((Mtbl_02_Recharge_FixedAmounts.[Service Type])='" & RRServiceType & "') AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM First month of New Amount])<=" & RRMonth & ") AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM Last month of Fixed Amount])>=" & RRMonth & "));"

Using debug.pring I know that second sql string results in this:

SELECT Mtbl_02_Recharge_FixedAmounts.[Fixed amount for all Recharges (Provide in local currency)] FROM Mtbl_02_Recharge_FixedAmounts WHERE (((Mtbl_02_Recharge_FixedAmounts.TerrCode_FA)='602') AND ((Mtbl_02_Recharge_FixedAmounts.[Service Type])='RL') AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM First month of New Amount])<=1904) AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM Last month of Fixed Amount])>=1904));

If my understanding is correct the error comes from 1904 value that is numeric in sql while Mtbl_02_Recharge_FixedAmounts.[YYMM Last month of Fixed Amount] is stored as short text.
I tried to modify like this:

        sql = "SELECT Mtbl_02_Recharge_FixedAmounts.[Fixed amount for all Recharges (Provide in local currency)] " & _
                "FROM Mtbl_02_Recharge_FixedAmounts " & _
                "WHERE (((Mtbl_02_Recharge_FixedAmounts.TerrCode_FA)='" & RRTerCode & "') AND ((Mtbl_02_Recharge_FixedAmounts.[Service Type])='" & RRServiceType & "') AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM First month of New Amount])<='" & RRMonth & "') AND ((Mtbl_02_Recharge_FixedAmounts.[YYMM Last month of Fixed Amount])>='" & RRMonth & "'));"

But it makes no difference. I'm not so good with VBA in access, I don't think I can solve it on my own, please help!

Hi Marcinrd,

Since RRMonth is already declared as string, but if the source data is numeric then the problem could be at the expression : RRMonth = rst.Fields("Process Month (YYMM)").
My guess is the value stored in Process Month (YYMM) field may be a numeric value. You may try to use function CStr() to convert numeric data type to string data type.

Please try to change RRMonth = rst.Fields("Process Month (YYMM)") to RRMonth = CStr(rst.Fields("Process Month (YYMM)")).
Another way is you may try to run in debug mode, so it will show the line that caused an error.

Thank you chocolate_kitten! It put me on right track
CStr(rst.Fields("Process Month (YYMM)")) alone didn't work but combined with my first attempt of adding ' in sql those two methods together finally gave the right result!

congratulations!