August 30, 2008 03:21am pdt

06.30.2008 at 11:02PM PDT, ID: 23529195

	[x] Attachment Details

Use of Split function in SQL statement

Asked by angie_angie in SQL Query Syntax

Tags: MS Access, VBA

Hi Experts,

Can I use Split function in SQL statement?

I have values in myTblB.aFldInB as a combination of words and numbers with brackets. E.g. "abcdef (123456)". I want to trim the numbers together with the brackets and put only the words into myTblA.aFldInA. So I used the split function in my query with VBA codes like this:

sGetSQL = "UPDATE myTblA INNER JOIN myTblB ON Cstr(myTblA.caseNo) = Mid(myTblB.caseID) " _
& "SET myTblA.aFldInA = Split(myTblB.aFldInB, '(')(0) " _

But error ocurs saying: Invalid use of '.','!', or '()'. in query expression 'Split(myTblB.aFldInB, '(')(0). I tried out the same use of Split function outside SQL like this:

myWords = Split("abcde (1234)", "(")(0)
Debug.Print myWords

and it works fine. Can anyone help to explain why? How to do it correctly?

This is URGENT! Thank you very much!!Start Free Trial

Keywords: Use of Split function in SQL statement

	Title
1	T-SQL Update Inner Join Syntax
2	SQL Query not working from VBA
3	Limitations of debug.print in VBA
4	MySQL: How to INNER JOIN a COUNT…
5	SQL Inner Join Problem
6	update statement with inner join

Loading Advertisement...

20080716-EE-VQP-32 - Hierarchy / EE_QW_2_20070628

1. angelIII 844,660
2. ee_rlee 458,453
3. capricorn1 352,496
4. chapmandew 335,152
5. mark_wills 326,930
6. aneeshatti... 189,721
7. matthews... 187,246
8. Thomasian 170,076
9. jinesh_ka... 167,181
10. sdstuber 147,127
11. jimhorn 128,700
12. peter57r 119,405
13. harfang 111,183
14. frankytee 108,644
15. DatabaseMX 105,597
16. hielo 94,350
17. TextReport 84,195
18. GRayL 82,934
19. boag2000 81,858
20. cactus_d... 77,446
21. ScottPletc... 77,436
22. dqmq 70,815
23. LSMCons... 62,900
24. dtodd 62,424
25. DanielWilson 61,003

1. angelIII 1,589,993
2. capricorn1 686,608
3. matthews... 477,157
4. ee_rlee 458,453
5. aneeshatti... 400,428
6. chapmandew 335,152
7. mark_wills 326,930
8. harfang 221,963
9. peter57r 219,434
10. imitchie 215,310
11. DatabaseMX 214,669
12. jimhorn 209,220
13. dqmq 194,080
14. GRayL 188,765
15. jinesh_ka... 183,470
16. sdstuber 179,625
17. Thomasian 170,076
18. frankytee 158,892
19. Lowfatsp... 148,233
20. mbizup 140,947
21. ScottPletc... 130,541
22. DanielWilson 120,378
23. MikeToole 118,033
24. derekkromm 114,235
25. appari 113,378

Use of Split function in SQL statement

Asked by angie_angie in SQL Query Syntax

Tags: MS Access, VBA

About this solution