Populating SQL String Using VBA in a MS Access Application

Question

I have quite a complex requirement where I need to populate a SELECT query with parameters coming from a list box in Access. All seems to be working fine except for the SELECT part of the query, In short, I need the VBA code to populate the following in my Access query:

SELECT TRIM(ACCOUNT_NO)&","&UNITS FROM GRV_OFFICE

I have the following in my VBA code:

strSQL = "SELECT TRIM(ACCOUNT_NO)" & "," & "UNITS FROM GRV_OFFICE"

But for some reason it keeps populating it as follows in Access:

SELECT Trim(ACCOUNT_NO) AS Expr1, GRV_OFFICE.UNITS

The reason I am using trim is because this is eventually exported from the database to populate an import into another application. Without the trim there are unnecessary spaces which are messing with the subsequent import process.

So the ultimate result of this query should be the ACCOUNT_NO with a comma and then the number of units in one field.

I find this really frustrating as this should be the easy part of the project.

Any assistance will be appreciated.

Shivang Gupta · Accepted Answer · 2020-03-08 08:04:01Z

1

You need to use concatenation to get two column fields together.

Below code may work:

 strSQL = "SELECT TRIM(ACCOUNT_NO) + ' ' + UNITS as newColumn FROM [GRV_OFFICE];"

Reference : https://www.mssqltips.com/sqlservertip/2985/concatenate-sql-server-columns-into-a-string-with-concat/

edited Mar 8, 2020 at 8:04

answered Mar 8, 2020 at 7:56

Shivang Gupta

3474 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gdekoker Over a year ago

Thanks, that pointed me in the right direction. The final solution was: strSQL = "SELECT TRIM(ACCOUNT_NO) & ',' & UNITS as newColumn FROM [GRV_OFFICE]"

Collectives™ on Stack Overflow

Populating SQL String Using VBA in a MS Access Application

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related