Data Type mismatch in Criteria expression 1

[DrSmyth]

Hi,

I'm trying to produce an address field concatenated from a series of other address fields. I need each concatenated element to be padded with spaces to 40 characters. So I've tried this expression:

RP Address: [S17 active space]![PRMS] & Space(40-Len([S17 active space]![PRMS])) & [S17 active space]![ST1] & Space(40-Len([S17 active space]![ST1])) & [S17 active space]![ST2] & Space(40-Len([S17 active space]![ST2])) & [S17 active space]![DIS] & Space(40-Len([S17 active space]![DIS])) & [S17 active space]![TOWN] & Space(40-Len([S17 active space]![TOWN])) & [S17 active space]![PCDE] & Space(40-Len([S17 active space]![PCDE])) & [S17 active space]!
[CTRY] & Space(40-Len([S17 active space]![CTRY]))

But i keep getting a 'Data Type mismatch in Criteria expression' error. I can't figure out why because they are all text fields and none of them are greater than 40 characters wide..
Can anybody help?

 

[JerryKlmns]

DrSmyth

I cant see any criteria in the SQL part of the statement, and because of the extra solar activity during summer period telepathetic channel is jammed. Pls post the whole SQL...

Why do you use ! and not .

 

[DrSmyth]

OK Here goes:

SELECT 3 AS type, [S17 active space]![Bene_Titl] AS [RP Title], [S17 active space]![LLPSC01U_PSC_BENE_FRNM] & Space(40-Len([S17 active space]![LLPSC01U_PSC_BENE_FRNM])) AS [RP Forename], [S17 active space]![LLPSC01U_PSC_BENE_SRNM] & Space(100-Len([S17 active space]![LLPSC01U_PSC_BENE_SRNM])) AS [RP Surname], [S17 active space]![PRMS] & [S17 active space]![ST1] & [S17 active space]![ST2] & [S17 active space]![DIS] & [S17 active space]![TOWN] & [S17 active space]![CTY] & [S17 active space]![CTRY] AS [RP Address], [S17 active space]![PRMS] & Space(40-Len([S17 active space]![PRMS])) & [S17 active space]![ST1] & Space(40-Len([S17 active space]![ST1])) & [S17 active space]![ST2] & Space(40-Len([S17 active space]![ST2])) & [S17 active space]![DIS] & Space(40-Len([S17 active space]![DIS])) & [S17 active space]![TOWN] & Space(40-Len([S17 active space]![TOWN])) & [S17 active space]![PCDE] & Space(40-Len([S17 active space]![PCDE])) & [S17 active space]![CTRY] & Space(40-Len([S17 active space]![CTRY])) AS [RP Address]
FROM [S17 active space]
GROUP BY 3, [S17 active space]![Bene_Titl], [S17 active space]![LLPSC01U_PSC_BENE_FRNM] & Space(40-Len([S17 active space]![LLPSC01U_PSC_BENE_FRNM])), [S17 active space]![LLPSC01U_PSC_BENE_SRNM] & Space(100-Len([S17 active space]![LLPSC01U_PSC_BENE_SRNM])), [S17 active space]![PRMS] & [S17 active space]![ST1] & [S17 active space]![ST2] & [S17 active space]![DIS] & [S17 active space]![TOWN] & [S17 active space]![CTY] & [S17 active space]![CTRY], [S17 active space]![PRMS] & Space(40-Len([S17 active space]![PRMS])) & [S17 active space]![ST1] & Space(40-Len([S17 active space]![ST1])) & [S17 active space]![ST2] & Space(40-Len([S17 active space]![ST2])) & [S17 active space]![DIS] & Space(40-Len([S17 active space]![DIS])) & [S17 active space]![TOWN] & Space(40-Len([S17 active space]![TOWN])) & [S17 active space]![PCDE] & Space(40-Len([S17 active space]![PCDE])) & [S17 active space]![CTRY] & Space(40-Len([S17 active space]![CTRY]));

 

[JerryKlmns]

SELECT 
	3 AS type, 
	[Bene_Titl] AS [RP Title], 
	Left([LLPSC01U_PSC_BENE_FRNM] & Space(40), 40) AS [RP Forename], 
	Left([LLPSC01U_PSC_BENE_SRNM] & Space(100), 100) AS [RP Surname], 
        [PRMS] & [ST1] & [ST2] & [DIS] & [TOWN] & [CTY] & [PCDE] & [CTRY] AS [RP Address], 
        Left([PRMS] & Space(40), 40) & Left([ST1] & Space(40), 40) & Left([ST2] & Space(40), 40) & Left([DIS] & Space(40), 40) & Left([TOWN] & Space(40), 40) & Left([PCDE] & Space(40), 40) & Left([CTRY] & Space(40), 40) AS [RP Address Spc]

FROM [S17 active space]

GROUP BY 
	3, 
	[Bene_Titl], 
	Left([LLPSC01U_PSC_BENE_FRNM] & Space(40), 40),
	Left([LLPSC01U_PSC_BENE_SRNM] & Space(100), 100), 
	[PRMS] & [ST1] & [ST2] & [DIS] & [TOWN] & [CTY] & [PCDE] & [CTRY],
        Left([PRMS] & Space(40), 40) & Left([ST1] & Space(40), 40) & Left([ST2] & Space(40), 40) & Left([DIS] & Space(40), 40) & Left([TOWN] & Space(40), 40) & Left([PCDE] & Space(40), 40) & Left([CTRY] & Space(40), 40);

 

[DrSmyth]

Works like a dream Jerry... Thanks for the help, where was I going wrong?

 

[JerryKlmns]

I don't know! As you can see I've rewritten the whole of it!