Complex Query 1

[MA04]

Hi,

I have a form that performs searches and allows the user to append the record being viewed by pressing a command button.

I have 2 tables:
1, ma_enq with fields A1, A2, A3, A4 and name.
2, ma_dbo_uk with fields PRMF, Strd, Str, Locdd, Locd, PTN, and CTN.

The field PRMF is split by semicoloms "'" and a field SubString is created which holds the splitted fields as seperate records.

I want to be able to append more than one field into another divided by commas "," so it will look like format:

A1 A2
PRMF(SubString) Locdd
Strd Locd
Str
A3 A4
PTN Postcode
CNT

I want the apend query to do a conditional append for fields A1,A2,A3 that are going to hold more than one field if neccesary. If the field Strd is not empty add a comma before it "," and after it if Str is not empty, but if they are empty exclude the comma so:

SubString Strd Str
whsmith Pent Park Gladstone Road

A1
Whsmith, Pent Park, Gladstone Road

I want to do the same for the other fields like A2 and A3 that can hold more than one field.

At present my search query looks like:
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;

and my append query like:

Private Sub Command18_Click()
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
End Sub

Hope this makes senes if not i would be happy to explain further, thanks in advance.

M-.

 

[PHV]

You may try something like this if the fields are NULL:
strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, " & _
"Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & Nz(', '+I1.Strd) & Nz(', '+I1.Str), " & _
"I1.Locdd & Nz(', '+I1.Locd), I1.PTN & Nz(', '+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));"
If the fields are empty or blank another way:
...
"Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & IIf(Trim(I1.Strd & '')='','',','+I1.Strd) & IIf(Trim(I1.Str & '')='','',', '+I1.Str), " & _
...

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, But I can't seem to get my search syntax correct for the above append query that you proposed.

M-.

 

[MA04]

Hi,

I have tried to get my search query up to scratch but still have problems, at present i have managed to get 2 fields joining with an expression but it does not take into account if their are null values in the second field and if there are it just places a comma "," not null as i want it and also i cannot seem to join fields with my substring this is my search query:

SELECT I1.postcode, Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1) AS SubString, [I1].[Locdd] & ", " & [Locd] AS Expr1, [I1].[PTN] & ", " & [CNT] AS Expr2
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));

 

[PHV]

My suggestion was this:
I1.Locdd & Nz(', '+I1.Locd)
Instead of this:
[I1].[Locdd] & ", " & [Locd]

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

 

[MA04]

I get a syntax error if i use the example above for my query search which is seperate form the append query, the above works for my append but have problems with my query search, here it is:

SELECT I1.postcode, Mid$(";" & I1.PRMF & ";",S1.num+1,S2.num-S1.num-1) & Nz(', '+I1.Strd) & Nz(', '+I1.Str), I1.Locdd & Nz(', '+I1.Locd), I1.PTN & Nz(', '+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));

Thanks in advance.

 

[PHV]

You may try this:
SELECT I1.postcode, Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1) AS SubString, [I1].[Locdd] & IIf(IsNull([Locd]),"",", " & [Locd]) AS Expr1, [I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT]) AS Expr2
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));

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

 

[MA04]

The above does not show the other fields that i want to add with the substring and it does not take into account if the first field is null it places a comma "," before a field if the previous is null.

Thanks again.

 

[MA04]

This is a clearer image of the above search query:

postcode
SS3 9BW
SubString
Mossbank Primary School(Fields Strd and STR are not entered)
Expr1
, Steventon
Expr2
, Sussex

The query inputs null if both fields are null but not if one field is.

 

[PHV]

And this ?
[I1].[Locdd] & IIf(IsNull([Locd]),"",IIf(IsNull([Locdd]),"",", ") & [Locd]) AS Expr1,
[I1].[PTN] & IIf(IsNull([CNT]),"",IIf(IsNull([PTN]),"",", ") & [CNT]) AS Expr2


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

 

[MA04]

This seems to multiply the 2nd field in Expr1 by 3 for instance:

postcode SubString
SS3 9BW Post Office
Expr1
, SteventonSteventonSteventon
Expr2
, Sussex

I still have the problem that if the first field is null the comma is shown in both Expression fields and i need to somehow display the other fields alongside the substring the append query works and does this, but the append does not work if a record has any null values is there a way of adapting the append sql to the search:

strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, " & _
"Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & Nz(', '+I1.Strd) & Nz(', '+I1.Str), " & _
"I1.Locdd & Nz(', '+I1.Locd), I1.PTN & Nz(', '+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));"

 

[MA04]

The problem is the code

[I1].[Locdd] & IIf(IsNull([Locd]),"",", " & [Locd]) AS Expr1, [I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT]) AS Expr2

which does exactly the same as

I1.Locdd & Nz(', '+I1.Locd), I1.PTN & Nz(', '+I1.CNT)

Both do not get rid of the comma for the first field if it is null, but works if the 2nd field is null and if no fields are null this is how it looks:

Only problem is if first field is missing:
,Locd

Is there a way to get rid of comma, Thanks again in advance,

M-.

 

[MA04]

I Have managed to resolve the problem with the commas, but i am having problems with the first field. i want to use the iff statement in the first part but i am having difficulties getting it to work as it also splits a field.

This is what my query looks like now:

SELECT I1.postcode, Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1) AS SubString, IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),"",", " & [Locd])) AS Expr1, IIF(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr2
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));

I want to take this statement from above:
Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1) AS SubString,

and add fields [I1].[Strd] and [I1].[STR] to it and add the IIF statement to it like the other ones above.

Any help very much appreciated,

M-.