Run-time error '13' Type mismatch on query in VBA 1

[djfeldspiff]

Good afternoon everyone,

I'm struggling with the following snippet of code. All goes well until the RunSQL line. At that point, I receive the Run-time error '13' Type mismatch message. The query is to make a new table from linked tables from an AS400. I'm using Access 2000. I can get the query to execute from the query designer, but when I attempt to use a value from a form in VBA, it fails. Any direction or assistance is greatly appreciated.

Dim dbCurr As DAO.Database
Dim ctlBDate As Variant
Dim ctlEDate As Variant

Set dbCurr = CurrentDb
ctlBDate = Me!BeginDate
ctlEDate = Me!Enddate

DoCmd.SetWarnings False

If (Forms![Date Options]!FrameDate = Val(1)) Then
If ctlBDate > Date Or IsNull([ctlBDate]) Then
MsgBox "Null or Invalid date(s). Please enter valid date parameters", vbCritical, "Process Status"
Else
If ctlBDate = Date Then
MsgBox "WRONG", vbCritical, "Process Status"
Else
DoCmd.RunSQL "SELECT SM_ACTIVITY.ACT_ACT_DT, SM_ACTIVITY.ACT_ACT_I, SM_ACTIVITY.ACT_CUST_NUM, SM_INVOICE.I_ORD_NUM, SM_INVOICE.I_INV_NUM, SM_INVOICE.I_PAY_METH_I, SM_ORDER_CC.OC_CC_I, SM_INVOICE.I_NET_AMT, SM_INVOICE.I_TAX_AMT, SM_INVOICE.I_MISC_AMT, SM_INVOICE.I_SHIP_AMT, SM_INVOICE.I_TOT_AMT, SM_INVOICE.I_PP_AMT, SM_INVOICE.I_CHRG_AMT, SM_INVOICE.I_CCC_AMT, SM_INVOICE.I_CPN_AMT, SM_INVOICE.I_CR_APLD_AMT, SM_INVOICE.I_GC_AMT, SM_ORDERS.O_PP_F, SM_ORDERS.O_PP_AMT, SM_ORDERS.O_CCC_F, SM_INVOICE.I_BAL_I, SM_INVOICE.I_BAL_AMT, SM_ORDERS.O_CC_PREDEP_AMT, SM_INVOICE.I_SLSMN_CD" & _
"INTO POSOrders" & _
"FROM ((SM_ACTIVITY INNER JOIN SM_INVOICE ON SM_ACTIVITY.ACT_ACT_NUM = SM_INVOICE.I_INV_NUM) LEFT JOIN SM_ORDER_CC ON SM_INVOICE.I_ORD_NUM = SM_ORDER_CC.OC_ORD_NUM) INNER JOIN SM_ORDERS ON SM_INVOICE.I_ORD_NUM = SM_ORDERS.O_ORD_NUM" & _
"WHERE (((SM_ACTIVITY.ACT_ACT_DT) = #" & ctlBDate & "#)) AND ((SM_INVOICE.I_ORD_METH_I)=" * ")" & _
"ORDER BY SM_ACTIVITY.ACT_ACT_DT;"
End If
End If
Else

 

[RoyVidar]

I don't know excactly where the fault might be, but I notice some things about the sql statement

1 - every time you concatenate the text, theres no space, ex:

[tt]...SM_INVOICE.I_SLSMN_CD" & _
"INTO POSOrders" & _...[/tt]

will produce:

[tt]...M_INVOICE.I_SLSMN_CDINTO POSOrders...[/tt]

- which I'm not entirely sure how jet will interpret, but probably not how it is intended - so - add spaces

2 The wildchard
[tt](SM_INVOICE.I_ORD_METH_I)=" * ")" & _[/tt]

This should probably be
[tt](SM_INVOICE.I_ORD_METH_I)='*')" & _[/tt]
or
[tt](SM_INVOICE.I_ORD_METH_I) like '*')" & _[/tt]

- i e single quotes for text wildchard, and perhaps the Like Keyword?

3 - the date
When the regional date settins are different from the US date format, one need to format the date to an US recognizable format, for instance:

[tt]...ACT_ACT_DT) = #" & format(ctlBDate,"yyyy-mm-dd") & "#)) AND[/tt]

One hint - use debug.print <the whole sql string>, hit ctrl+g and study the sql in the immidiate pane - or try copy/paste it to the SQL view of the QBE (where it then should work)...

Roy-Vidar

 

[djfeldspiff]

Hi Roy,

I added the spaces at the end of the concatenated text, then modifided the =" * " to ='*' and the query executed. The "*" is actually the text in the field indicating a particular record type. Have a star!