Search results for query: *

Hi PHV thanks so much for pointing me in the right direction. This is what I ended up with and it worked. I have learnt another piece of the vba sql puzzle thanks to you PHV :D strSql = "Insert into parts (ID, part_number) values (" & ID & ", '" & strpart(i) & "')" If str_part(i) <> "A" And...

how do I include a where clause in the sql 'insert into' statement: strSql = "Insert into parts (ID, part_number) values (" & ID & ", '" & strpart(i) & "')" the where clause I want to include is str_part <> "A" and str_part <> "B" and str_part is not null thanks in advance maggsz

Hi dhookom I tried your suggestion and yes it works great. :-) thanks maggsz

Hi dhookom I will try your suggestion when I am at work tomorrow thanks :-) maggsz

Hi MajP Yes your corrections worked perfect. :-) Before I had read your reply I had changed color to strColor but still no luck. Once I read your reply and included the null code it worked. For me it has highlighted the importance of capturing nulls. Thanks maggsz

thank you Golom, dhookom and MajP. I am still learning and developing my vba skills so your help keeps me developing and finetuning my vba. maggsz

Hi Golom sorry I did put the trim in the wrong place. It was not till I saw my reply after I submitted it that I noticed where the trim should have been, near the top as in: SELECT EmployeeID, Trim$(Mid("," & E.Children & ",", S1.N + 1 , S2.N - S1.N - 1)) AS Child FROM...

Hi Golom I still get the leading space. I am not sure if I put the trim$ in the right place. SELECT EmployeeID, Mid("," & E.Children & ",", S1.N + 1 , S2.N - S1.N - 1) AS Child FROM tblEmployees AS E, qryInt2 AS S1, qryInt2 AS S2 WHERE Mid$("," & E.Children & "," ,S1.N...

Hi Golom thanks for your reply. Yes it works a treat. However as I have spaces after the comma these are included in the results of the last query. Is there a way to remove the space? Trim maybe? And if so how could I include this so that it would not affect a color with spaces in between such...

Hi MajP I am getting an error with: for i = lbound(colors) to ubound(colors) there is a type mismatch. I am not sure why. colours is a text field which from my understanding gets split into the strQualifications array which is a string. What am I missing? Maggsz

Hi dhookom With the intSection I am not sure how to get this into the function. Maggsz

Hi dhookom thanks for your reply. This looks an interesting variation. Looking at the code where does "intSection" come from Maggz

Hi MajP thanks for your reply. I will try this out now and let you know how I go :-) Maggz

Is the split function the way to go? maggsz

simplified here is what I have: ID colors 1 white, black, red 2 red, orange 3 blue Here is what I want to achieve: ID colors 1 white 1 black 1 red 2 red 2 orange 3 blue I have an Access 2007 table with some IDs having more than one color. There is no limit to the...