How To Target a section of a field

[lccrews]

I have an SQL that I would like to modify to find the left 6 characters of the field I'm in and insert records similar to it. Ideally, I would like this:
Raw Material
174476 - ingredient


<------174476S1 - sub-ingredient
<------174476S2 - sub-ingredient

The problem is, I'm getting Error: Too few parameters. Expected 3.

    sSQL = "INSERT INTO tmp_Formula (BP, Item, BillType, RawMaterial, UoM) " _        
         & "SELECT [Forms]![frm_Formulation]![BP] AS BP, [Forms]![frm_Formulation]![ITEM] AS Item, " _
         & "[Forms]![frm_Formulation]![BILL TYPE] AS BillType, tbl_RawMaterial.RawMaterial, '' AS UoM " _
         & "FROM tbl_RawMaterial " _
         & "WHERE (((tbl_RawMaterial.RawMaterial) Like '" & RawMaterial & "' & 'S*'));"

Right now it reads, Like '" & RawMaterial & "' & 'S*', which would show "174476 - ingredient S". I would like it to find the first 6 characters and concatenate that with S* i.e. 174476S* would return both S1 and S2. I'm thinking it might read something like WHERE (((tbl_RawMaterial.RawMaterial) Like Left('" & RawMaterial & "',6) & 'S*'))

Can someone please teach me how to do this?

[ranman256]

select left([Raw Material],instr([Raw Material]," -")-1) as KeyNum from table

Then use this KEYNUM field to add new records :
[keynum] & "S1 - sub-ingredient"
[keynum] & "S2 - sub-ingredient"

[lccrews]

If I understand, you want me to build a query in Access to output field [Keynum] and reference that in the sSQL statement I have posted? So it would look like:

& "WHERE (((tbl_RawMaterial.RawMaterial) Like [Keynum] & 'S*'));"

The reason for S* is because different ingredients have different numbers of sub-ingredients.
The idea is to get the code to look at the current record and use that RawMaterial only. If that helps.

[lccrews]

I tried what you said to no avail. I built a query that output field [Raw Material] in qry_CurrentFormula_Pt3. However the sSQL I have written keeps giving Error: Too few parameters, expected 3. Could someone please tell me what's wrong with my sSQL?

    sSQL = "INSERT INTO tmp_Formula (BP, Item, BillType, RawMaterial, UoM) " _        
         & "SELECT [Forms]![frm_Formulation]![BP] AS BP, [Forms]![frm_Formulation]![ITEM] AS Item, " _
         & "[Forms]![frm_Formulation]![BILL TYPE] AS BillType, tbl_RawMaterial.RawMaterial, '' AS UoM " _
         & "FROM tbl_RawMaterial, qry_CurrentFormula_Pt3 " _
         & "WHERE (((tbl_RawMaterial.RawMaterial) Like [Raw Material] & 'S*'))"
    CurrentDb.Execute sSQL, dbFailOnError
    CurrentDb.TableDefs.Refresh
    Me.Refresh

What I am aiming to do is select only the raw material that is like the raw material that I'm typing in the field currently. So If I type in 174476 in field 1 only 174476S1 and any other sub ingredient for that raw material should be inserted.

[Minty]

What happens when if you try and run just the select part of your query - do you get the results you expected ?
I doubt it as you have a cross joined From statement - there is no join.

Get that working first then paste it back to run your update.

[lccrews]

I've figured out that it has something to do with me referencing forms. i.e. [Forms]![frm_Formulation]![BILL TYPE] AS BillType... Removing these lets the SQL run fine. I just am not sure how to adjust my references. The reason for these is that they put in necessary information to pull the record back up in the future.

I've also tried Eval([Forms]![frm_Formulation]![BILL TYPE]) but that didn't work either.

[Minty]

You would have to Concatenate those values back into the query so assuming those are text values this line would be;

"SELECT '" & [Forms]![frm_Formulation]![BP] & "' AS BP, '" & [Forms]![frm_Formulation]![ITEM] & "' AS Item, " _

Ditto with the others. The SQL interpreter doesn't understand the Access Forms! references hence it asking for those as parameters.

[lccrews]

On a related yet unrelated topic, the last part of the SQL doesn't work either. I have to manually hit refresh at the home bar. Any way to get around that?

    CurrentDb.TableDefs.Refresh
    Me.Refresh

[Minty]

Try a Me.Requery .
The Table defs refresh won't achieve anything in this context, remove it.

[lccrews]

You're freaking brilliant. I have added this to my notebook of troubleshooting. I'm LOVE VBA! Thank you again Minty!