Type Mismatch error

[andrewf10]

I'm trying to write a SQL statement on a form called frmForm1 but when I run it, I get a Type Mismatch error message. All the relevant fields are set to text on their tables so I'm out of ideas. Can someone please help?
Many thanks


DoCmd.RunSQL "SELECT tblCollateralPurchasingGroups.[Purchasing Group] FROM tblCollateralPurchasingGroups WHERE (((tblCollateralPurchasingGroups.Material)=[Forms]![frmForm1]![COMPONENT 1]));"

 

[Mile-O]

DoCmd.RunSQL "SELECT tblCollateralPurchasingGroups.[Purchasing Group] FROM tblCollateralPurchasingGroups WHERE tblCollateralPurchasingGroups.Material) = """ &[Forms]![frmForm1]![COMPONENT 1] & """;"

 

[andrewf10]

Thanks for the suggestions but still the same problem. Strange

 

[Oldsoftboss]

Is the error with the sql statement or in another part of your sub ?

 

[andrewf10]

Yes its with the SQL statement

 

[Mile-O]

What sort of control is: [COMPONENT 1]?

 

[Mile-O]

DoCmd.RunSQL "SELECT [Purchasing Group] FROM tblCollateralPurchasingGroups WHERE Material = """ & [Forms]![frmForm1]![COMPONENT 1] & """;"

 

[andrewf10]

It's a textbox

 

[Mile-O]

Sorry, first day back at work today and a little tired.
Just spotted the problem.

DoCmd.RunSQL is used to run an SQL statement that performs an action.

Just create a new query and put your SQL into it and then use:

DoCmd.OpenQuery "Queryname"

 

[andrewf10]

But I have 30 other component fields, such as [Component 1], [Component 2] etc

My plan was to use the same SQL statement with a few tweaks.

 

[Mile-O]

But I have 30 other component fields, such as [Component 1], [Component 2] etc

My plan was to use the same SQL statement with a few tweaks.

More fool you for not normalising your data.


And what happens if you need 40 components?

That's why we build databases down and not across.

 

[andrewf10]

So there's no way of getting what I have to work? I inherited this mess

 

[Mile-O]

THere is a longwinded way but it would mean that every time you wanted to make a change to your database you'd have to go through your database fixing changes to your tables, queries, forms, reports, macros, and modules. A nightmare scenario when it is, as a whole, unnecessary when done properly.

You can learn how to build a query using a QueryDef object - I'm sure I've put an example into the Sample Databases forum - and allow this query to pick the relevant field.

I'm not an advocate of this however and would still suggest getting a proper database table structure before even thinking about forms and queries and reports.