Append problem 1

[MA04]

Hi,

I have an sql statement that runs a search query that splits a field PRMF by semicoloms ";", the splitted field properties create seperate records and displayed as such on field SubString,

This query is run from a form and the user is allowed to select a choice in a combo box.

The problem is i have a command button which by a press of a button should append the users choice on form view But i am having problems with it, i want it to append that specific SubString and all other fields with that record chosen.

sql that splits field (works):

SELECT I1.ID, I1.postcode, Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS SubString, I1.STR, I1.CNT
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE I1.postcode Like [Enter Postcode:] AND
Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
and S1.num+1 < S2.num
and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0;

What i have so far is for the append; event procedure(needs changing):

Dim strSQL As String

strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT ma_dbo_uk.postcode, ma_dbo_uk.SubString, ma_dbo_uk.STR, ma_dbo_uk.CTN " & _
"FROM ma_dbo_uk LEFT JOIN [ma_enq] ON ma_dbo_uk.postcode = [ma_enq].A4 " & _
"WHERE (((ma_dbo_uk.postcode)=[Forms]![Ma_search2]![PRMF_a1]));"

DoCmd.RunSQL strSQL

Any help very much appreciated,
M-.

 

[MA04]

I think this is what i need but have a 'Compile error, syntax error' because of the semicoloms:

Dim strSQL As String

strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, Mid$(";" & I1.PRMF & ";",S1.num+1,S2.num-S1.num-1) AS SubString, I1.STR, I1.CNT " & _
"FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2 " & _
"WHERE (((Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1))=[Forms]![Ma_search2]![PRMF_a1]) AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));"

DoCmd.RunSQL strSQL

I may not need sql to show the split by the semicoloms as form view has already done that i.e. viewing SubString field on form, Can anyone help?

M-.

 

[PHV]

To avoid the syntax error:
strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) AS SubString, I1.STR, I1.CNT " & _
"FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2 " & _
"WHERE (((Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1))=[Forms]![Ma_search2]![PRMF_a1]) AND ((Mid$(';' & [I1].[PRMF] & ';',[S1].[num],1))=';') AND ((Mid$(';' & [I1].[PRMF] & ';',[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1),';'))=0));"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thanks for that, i did not realise that a small syntax problem as replacing the " for ' caused it.

I was wondering if i had the table to append to in this case ma_enq in another database say Homes_prototype, how would this append query look?

Thanks again for the help.

M-.

 

[MA04]

I have managed to resolve the problem, i have linked the table ma_enq to the current database.