Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to Resolve the Type Mismatch Error in Sql Query using VBA Macros

Posted on 2010-08-25
3
Medium Priority
?
455 Views
Last Modified: 2013-11-26
Hi, I am populating a Data from  MDB using VBA Macros. I am Facing a problem with the SQL Statement ERROR : Type Mismatch. But If i run the same code in Access Sql View , Its working properly .
How to solve This problem i attach my file for your clarification please guide me.
Thanks In Advance
test.zip
0
Comment
Question by:whspider
  • 2
3 Comments
 
LVL 2

Expert Comment

by:gordontm
ID: 33520359
Hi!
If you remove the two columns product.SKU and NORTH_2010.vles from the SELECT (as per the code attached), then the Type Mismatch problem goes away, so I recommend you use this is a starting point to solve the problem.


.CommandText = Array("select product.ProductID from product,Depot,Zones,NORTH_2010 where NORTH_2010.PrductID=product.ProductID and NORTH_2010.Mnth='January' and NORTH_2010.Type='PRE' and Zones.ID=NORTH_2010.ZoneID and Depot.DeptId=NORTH_2010.DepotID order by Depot.DepotName")

Open in new window

0
 

Author Comment

by:whspider
ID: 33520464
Thanks for your Comment, But Those two fields are mandatory fields,my doubt is why it shows error for that two fields . Is there is any other way to solve this error without removing the fields name
0
 
LVL 2

Accepted Solution

by:
gordontm earned 2000 total points
ID: 33520501
OK. Try this instead

Replace this line

.CommandText = Array("select product...")

with

.CommandText = "select product..."

This worked for me when I tried it.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question