SQL String in Excel VBA

Hi,

I am importing data from our SQL server in to Excel, it works fine:

Please Login or Register  to view this content.

Once it is imported to the Excel sheet I then (in VBA) concatenate the two fields org_param_value.param_effective_date and org_parameter.param_id in to the blank 'Lookup' column I have at the beginning of the SQL.

I want to be able to do this without the extra vba to concatenate both field. I have tried: SELECT CDbl([org_param_value].[param_effective_date]) & [org_parameter].[param_id],

this doesn't work, I get an error: SQL Syntax - ([org_param_value].[param_effective_date]) & [org_parameter].[param_id], near line 1.

Using this in Access works.

any help appreciated.

thanks
Kerry

What are the data types of [org_param_value].[param_effective_date]) & [org_parameter].[param_id] and how should it look when concatenated? Could you give some examples?

If you just want to chain them together you can do this.

Please Login or Register  to view this content.

Hi,
I need the date converting to a serial number (as per Excel) and then concatenating. So Access will output the query like this:

LookupValue param_effective_date param_id
42498205561 08-May-16 205561
42499205562 09-May-16 205562
42499205563 09-May-16 205563
42499205564 09-May-16 205564

Need to replicate this in the sql in VBA.

Any ideas?

many thanks
Kerry

Originally Posted by Kyle123

What are the data types of [org_param_value].[param_effective_date]) & [org_parameter].[param_id] and how should it look when concatenated? Could you give some examples?

[org_param_value].[param_effective_date] is Date/Time and [org_parameter].[param_id] is a number Long Integer

Can anyone help out with this, it's driving me crazy?!

Hi KML,
In case you have not figured it out yet this should do the Trick

Please Login or Register  to view this content.

If your workbook has the 1904 option activated you need to change the Date in the SQL accordingly

Greets Loki