Compile error: Type mismatch on CreateQueryDef

[SomeDude]

I've got a database I'm using to replace MS Money. I've got some qdf queries to insert data to a table, but two particular qdf's give me a "Compile error: type mismatch". I'm getting the error on the "Set qdf_qry_Ins_forecast_Transfers_To = db.CreateQueryDef(" and "Set qdf_qry_Ins_forecast_TransfersFrom = db.CreateQueryDef(" statements, with ".CreateQueryDef" highlighted by the VBA debugger.

All of the other qdf's have the same .CreateQueryDef and the process runs fine if I turn the problem qdf's to comment blocks. Can anyone tell me what I'm doing wrong here?

Option Compare Database

Private Sub Update_tbl_Bank_forecast_Click()

'-----------------------------------------------
'   Dim section
'-----------------------------------------------

    Dim db As DAO.Database
    Dim qdf_qry_Del_tbl_Bank_forecast As QueryDef
    Dim qdf_qry_1stApp_forecast_pending As QueryDef
    Dim qdf_qry_1stApp_forecast_Bills_and_Deposits As QueryDef
    Dim qdf_qry_1stApp_forecast_Transfers_To As QueryDef
    Dim qdf_qry_1stApp_forecast_Transfers_From As QueryDef
    Dim qdf_qry_Ins_forecast_Bills_and_Deposits As QueryDef
    Dim qdf_qry_Ins_forecast_Transfers_To As QueryDef 'See this qdf in the Set section
    Dim qdf_qry_Ins_forecast_TransfersFrom As QueryDef 'See this qdf in the Set section
    
    
'-----------------------------------------------
'   Set section
'-----------------------------------------------

    
    Set db = CurrentDb()
    
    Set qdf_qry_Del_tbl_Bank_forecast = db.CreateQueryDef("", _
        "DELETE tbl_Bank_forecast.* " & _
        "FROM tbl_Bank_forecast ")
        
    Set qdf_qry_1stApp_forecast_pending = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Bank_pending.[Account Number], tbl_Bank_pending.[Transaction Date], tbl_Bank_pending.[Transaction Amount], tbl_Bank_pending.[Transaction Description] " & _
        "FROM tbl_Bank_pending ")
        
    Set qdf_qry_1stApp_forecast_Bills_and_Deposits = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT [tbl_Scheduler_Bills_&_Deposits].AcctNumber, [tbl_Scheduler_Bills_&_Deposits].NextDate, [tbl_Scheduler_Bills_&_Deposits].Amount, [tbl_Scheduler_Bills_&_Deposits].TransactionName " & _
        "FROM [tbl_Scheduler_Bills_&_Deposits] " & _
        "WHERE [tbl_Scheduler_Bills_&_Deposits].NextDate <=#" & [Forms]![frm_HomePage]![ToDate] & "#")
        
    Set qdf_qry_1stApp_forecast_Transfers_To = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Scheduler_Transfers.ToAcct, tbl_Scheduler_Transfers.NextDate, tbl_Scheduler_Transfers.Amount, tbl_Scheduler_Transfers.TransactionName " & _
        "FROM tbl_Scheduler_Transfers " & _
        "WHERE tbl_Scheduler_Transfers.NextDate <=#" & [Forms]![frm_HomePage]![ToDate] & "#")
        
    Set qdf_qry_1stApp_forecast_Transfers_From = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Scheduler_Transfers.FromAcct, tbl_Scheduler_Transfers.NextDate, -tbl_Scheduler_Transfers.Amount AS Amount, tbl_Scheduler_Transfers.TransactionName " & _
        "FROM tbl_Scheduler_Transfers " & _
        "WHERE tbl_Scheduler_Transfers.NextDate <=#" & [Forms]![frm_HomePage]![ToDate] & "#")
    
    Set qdf_qry_Ins_forecast_Bills_and_Deposits = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT [tbl_Scheduler_Bills_&_Deposits].AcctNumber, DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].FrequencyDays,[qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) AS NextDate, [tbl_Scheduler_Bills_&_Deposits].Amount, [tbl_Scheduler_Bills_&_Deposits].TransactionName " & _
        "FROM [tbl_Scheduler_Bills_&_Deposits] INNER JOIN [qry_Last_Forecasted_Bills_&_Deposits] ON [tbl_Scheduler_Bills_&_Deposits].TransactionName = [qry_Last_Forecasted_Bills_&_Deposits].[Transaction Description] " & _
        "WHERE DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].[FrequencyDays],[qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) <=#" & [Forms]![frm_HomePage]![ToDate] & "#")

                                            'The following two ".CreateQueryDef"s gets caught with a "compile error: type mismatch"?
    Set qdf_qry_Ins_forecast_Transfers_To = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Scheduler_Transfers.FromAcct, DateAdd('d',tbl_Scheduler_Transfers.Frequency,qry_Last_Forecasted_Transfers.[MaxOfTransaction Date]) AS NextDate, -tbl_Scheduler_Transfers.Amount AS Amount, tbl_Scheduler_Transfers.TransactionName " & _
        "FROM tbl_Scheduler_Transfers INNER JOIN qry_Last_Forecasted_Transfers ON (tbl_Scheduler_Transfers.Amount = qry_Last_Forecasted_Transfers.[Transaction Amount]) AND (tbl_Scheduler_Transfers.TransactionName = qry_Last_Forecasted_Transfers.[Transaction Description]) " & _
        "WHERE DateAdd('d',[tbl_Scheduler_Transfers].[Frequency],[qry_Last_Forecasted_Transfers].[MaxOfTransaction Date]) <=#" & [Forms]![frm_HomePage]![ToDate] & "#") & _
        "GROUP BY tbl_Scheduler_Transfers.FromAcct, DateAdd('d',tbl_Scheduler_Transfers.Frequency,qry_Last_Forecasted_Transfers.[MaxOfTransaction Date]), -tbl_Scheduler_Transfers.Amount, tbl_Scheduler_Transfers.TransactionName "

    Set qdf_qry_Ins_forecast_TransfersFrom = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Scheduler_Transfers.ToAcct, DateAdd('d',tbl_Scheduler_Transfers.Frequency,qry_Last_Forecasted_Transfers.[MaxOfTransaction Date]) AS NextDate, tbl_Scheduler_Transfers.Amount AS Amount, tbl_Scheduler_Transfers.TransactionName " & _
        "FROM tbl_Scheduler_Transfers INNER JOIN qry_Last_Forecasted_Transfers ON (tbl_Scheduler_Transfers.Amount = qry_Last_Forecasted_Transfers.[Transaction Amount]) AND (tbl_Scheduler_Transfers.TransactionName = qry_Last_Forecasted_Transfers.[Transaction Description]) " & _
        "WHERE DateAdd('d',[tbl_Scheduler_Transfers].[Frequency],[qry_Last_Forecasted_Transfers].[MaxOfTransaction Date]) <=#" & [Forms]![frm_HomePage]![ToDate] & "#") & _
        "GROUP BY tbl_Scheduler_Transfers.ToAcct, DateAdd('d',tbl_Scheduler_Transfers.Frequency,qry_Last_Forecasted_Transfers.[MaxOfTransaction Date]), tbl_Scheduler_Transfers.Amount, tbl_Scheduler_Transfers.TransactionName "


'-----------------------------------------------
'   Process section
'-----------------------------------------------


    If IsDate(Forms!frm_HomePage!FromDate) And IsDate(Forms!frm_HomePage!ToDate) Then 'Checks the frm_HomePage for proper From and To dates
        qdf_qry_Del_tbl_Bank_forecast.Execute 'Cleans up tbl_Bank_forecast
        qdf_qry_1stApp_forecast_pending.Execute 'Inserts pending transactions to tbl_Bank_forecast
        qdf_qry_1stApp_forecast_Bills_and_Deposits.Execute 'Inserts 1st bills & deposits on which to base all further bills & deposits
        qdf_qry_1stApp_forecast_Transfers_To.Execute 'Inserts 1st transfers to on which to base all further transfers to
        qdf_qry_1stApp_forecast_Transfers_From.Execute 'Inserts 1st transfers from on which to base all further transfers from
        Do
            qdf_qry_Ins_forecast_Bills_and_Deposits.Execute
        Loop Until qdf_qry_Ins_forecast_Bills_and_Deposits.RecordsAffected = 0 'Loops the insert transactions queries until the queries are empty
        Do
            qdf_qry_Ins_forecast_Transfers_To.Execute
        Loop Until qdf_qry_Ins_forecast_Transfers_To.RecordsAffected = 0 'Loops the insert transactions queries until the queries are empty
        Do
            qdf_qry_Ins_forecast_TransfersFrom.Execute
        Loop Until qdf_qry_Ins_forecast_TransfersFrom.RecordsAffected = 0 'Loops the insert transactions queries until the queries are empty
        DoCmd.OpenTable "tbl_Bank_forecast", acViewNormal, acEdit 'Opens tbl_Bank_forecast so I can see the progress...will get deleted
    Else: MsgBox "Invalid From or To Date", vbOKOnly, "PC Load Letter" 'Displays message box informing user of invalid To and From dates
    End If

End Sub

 

[JHB]

You have a "-" before "-tbl_Scheduler_Transfers.Amount AS Amount"

     Set qdf_qry_1stApp_forecast_Transfers_From = db.CreateQueryDef("", _
        "INSERT INTO tbl_Bank_forecast ( [Account Number], [Transaction Date], [Transaction Amount], [Transaction Description] ) " & _
        "SELECT tbl_Scheduler_Transfers.FromAcct, tbl_Scheduler_Transfers.NextDate, [SIZE=4][B][COLOR=Red]-[/COLOR][/B][/SIZE]tbl_Scheduler_Transfers.Amount AS Amount, tbl_Scheduler_Transfers.TransactionName " & _
        "FROM tbl_Scheduler_Transfers " & _
        "WHERE tbl_Scheduler_Transfers.NextDate <=#" & [Forms]![frm_HomePage]![ToDate] & "#")

The other error you get, check if NextDate in table tbl_Scheduler_Transfers is a date type field.

 

[namliam]

Holy unreadable code, batman...

Readable code is maintainable code....

    Set qdf_qry_Ins_forecast_Bills_and_Deposits = Db.CreateQueryDef("", _
        " INSERT INTO tbl_Bank_forecast ( [Account Number]                           , [Transaction Date]                                                                                                                     , [Transaction Amount]                   , [Transaction Description] ) " & _
        " SELECT                          [tbl_Scheduler_Bills_&_Deposits].AcctNumber, DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].FrequencyDays, [qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) AS NextDate, [tbl_Scheduler_Bills_&_Deposits].Amount, [tbl_Scheduler_Bills_&_Deposits].TransactionName " & _
        " FROM       [tbl_Scheduler_Bills_&_Deposits] " & _
        " INNER JOIN [qry_Last_Forecasted_Bills_&_Deposits] ON [tbl_Scheduler_Bills_&_Deposits].TransactionName = [qry_Last_Forecasted_Bills_&_Deposits].[Transaction Description] " & _
        " WHERE DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].[FrequencyDays],[qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) <=#" & [Forms]![frm_HomePage]![ToDate] & "#")

If you do that you will probably find your problems, failing that you can debug.print your actually created SQL and execute it in an actual query you usually get a more legible error

 

[RainLover]

A suggestion.

You should always use "Option Explicit" at the beginning of every Module.

 

[RainLover]

Holy unreadable code, batman...

Readable code is maintainable code....

    Set qdf_qry_Ins_forecast_Bills_and_Deposits = Db.CreateQueryDef("", _
        " INSERT INTO tbl_Bank_forecast ( [Account Number]                           , [Transaction Date]                                                                                                                     , [Transaction Amount]                   , [Transaction Description] ) " & _
        " SELECT                          [tbl_Scheduler_Bills_&_Deposits].AcctNumber, DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].FrequencyDays, [qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) AS NextDate, [tbl_Scheduler_Bills_&_Deposits].Amount, [tbl_Scheduler_Bills_&_Deposits].TransactionName " & _
        " FROM       [tbl_Scheduler_Bills_&_Deposits] " & _
        " INNER JOIN [qry_Last_Forecasted_Bills_&_Deposits] ON [tbl_Scheduler_Bills_&_Deposits].TransactionName = [qry_Last_Forecasted_Bills_&_Deposits].[Transaction Description] " & _
        " WHERE DateAdd('d',[tbl_Scheduler_Bills_&_Deposits].[FrequencyDays],[qry_Last_Forecasted_Bills_&_Deposits].[MaxOfTransaction Date]) <=#" & [Forms]![frm_HomePage]![ToDate] & "#")

If you do that you will probably find your problems, failing that you can debug.print your actually created SQL and execute it in an actual query you usually get a more legible error

I agree with your statement that the code should be more readable.

One should be able to read each line of code without scrolling to the right and Left.