Data Type Mismatch???

[kwokv616]

I have got problems runnign this SQL it says its got Data type mismatch...
what does it mean and why is this happening??
Thank you!!

Sqlstr = ""
Sqlstr = Sqlstr & "SELECT IL4010DA.Pnumber, IL4010DA.Code, ([ValDate]-Day([ValDate])+Day([Pyd])) AS fdate, Sum(IIf([IL4010DF]![Fund_code]='BALANC',[Fund_bal],0)) AS Balance, Sum(IIf([IL4010DF]![Fund_code]='CASH',[Fund_bal],0)) AS Cash, Sum(IIf([IL4010DF]![Fund_code]='GLOBAL',[Fund_bal],0)) AS Global, Sum(IIf([IL4010DF]![Fund_code]='GROWTH',[Fund_bal],0)) AS Growth, Sum(IIf([IL4010DF]![Fund_code]='FUND',[Fund_bal],0)) AS Fund, IL4010DF.Fund_code "
Sqlstr = Sqlstr & "INTO TFundPart1 "
Sqlstr = Sqlstr & "FROM ValuationDate INNER JOIN (IL4010DA LEFT JOIN IL4010DF ON IL4010DA.Pnumber = IL4010DF.Pnumber) ON ValuationDate.Pnumber = IL4010DA.Pnumber "
Sqlstr = Sqlstr & "GROUP BY IL4010DA.Pnumber, IL4010DA.Code, ([ValDate]-Day([ValDate])+Day([Pyd])), IL4010DF.Fund_code "
Sqlstr = Sqlstr & "HAVING (((IL4010DA.Code)='FP' Or (IL4010DA.Code)='FB' Or (IL4010DA.Code)='FFPV' Or (IL4010DA.Code)='FFPT'));"
DoCmd.RunSQL Sqlstr

 

[Banana]

Two things to know:

1) Data type mismatch usually means that you're trying to do something that doesn't mesh together... like adding a 1 with "1" (e.g. a string with a character code for a one, rather than an actual integer value of 1). Normally, VBA try to help you and implicitly converts datatypes whenever possible but sometime it can't be done, especially if you're trying to put a integer in a function expecting a string for example.

2) The error message actually isn't descriptive of what happened. I believe the real problem is that you're trying to use a RunSQL method, which is good only for action queries (e.g. INSERT INTO, UPDATE, DELETE FROM), upon a SELECT query (not an action query).

What are you trying to do with that SQL?

 

[kwokv616]

Im trying to make a new table with selected columns and selected records.
A simpler version maybe:

SELECT Table.* INTO Newtable FROM Table GROUP BY Column1 HAVING Column1 = "Criteria"

Im typing out the whole thing because I dont know where in the code it has gone wrong....

 

[Banana]

Why not just use the query builder?

It can be used to create a Make-Table query (to do so, right-click on the gray area after you've selected the tables) and navigate to Action Query -> Make Table Query.

HTH.

 

[kwokv616]

I used the query builder to create the query, then copy and pasted the whole SQL into VBA.
I need it in VBA because i need a series of actions to be done on the click of a button.

I copy and pasted the whole thing so it shouldnt be wrong...that is why i am so confused now...><

 

[boblarson]

Why not just save the query as a saved query and call it from VBA during your series of Actions?

 

[kwokv616]

But I dont know how to make the output into a new table.

using SQL its INTO TableName

Can i do the same when running a saved query?

thanks =)

 

[Banana]

If you want to dynamically name the table at run time, you want to create a "parameter query".

In query builder, right click the gray area to get the same context menu and select "Parameter" then enter as many parameters as you need and identify the data type for each.

SQL would probably look like this:

PARAMETERS MyTableName TEXT
SELECT INTO [MyTableNAME] FROM ....

I'm not sure if parameter can be used for the table name, but give it a try and see if it works.


In general, I usually avoid using VBA to build SQL, and instead build Access objects then access it via VBA. This gives Access a chance to optimize the queries at compile time rather than run time among other thing.

HTH.