SQL in VBA - Concatenating 2 fields

[KML1976]

Hi,
I have the following but it keeps throwing up an error, all works fine in other queries where i'm not trying to concatenate 2 fields.
What is wrong?

strSQL = "SELECT Format([org_param_value].[param_effective_date],0) & [org_parameter].[param_id] AS [LookupValue], org_param_value.param_effective_date, org_parameter.param_id, org_parameter.param_daily_name, org_param_value.param_value, org_param_value.param_rag, daily_report_items.cmi_parent, daily_report_name.cmn_id " & _

Thanks in advance.

Kerry

[JBeaucaire]

Shouldn't that first & be a + instead?

[KML1976]

That hasn't worked, I get the message:
vba_sql_error.png

My full query is:

Please Login or Register  to view this content.

[JBeaucaire]

Independently what is an example of those two values and the expected concatenation result?

[Norie]

Kerry

Have you tried executing the SQL statement directly in the database?

[gbeats101]

Shouldnt the formatting part be enclosed in quotation marks

Format([org_param_value].[param_effective_date],'0') (try double quotes if this doesnt work, just be sure you double them up when creating the string)

[Norie]

gbeats101

FORMAT is a MySQL function and as far as I can recall it doesn't need the arguments to be enclosed in quotes.

[gbeats101]

This is incorrect, it is a required syntax to use quotation...

EDIT: just tested it on msql server management studio and it returns an error in the second parameter if you dont use quotation

[Norie]

The OP is working with a MySQL database not a MSSQL database - look at the error message in post #3.

http://dev.mysql.com/doc/refman/5.7/...unction_format

[gbeats101]

Ah ok, im sorry, this function for mysql seems to format numbers with x decimal digits, in this case (i cannot test this but) it seems that the OP is trying to format a date with 0 decimal figures........is this going to be the problem?

Format([org_param_value].[param_effective_date],0)

[Norie]

No problem.

When I first saw the code myself I thought the OP was trying to use the VBA Format function.

Just looking at the error message I think the problem lies/starts somewhere here.

Please Login or Register  to view this content.

I'd test it but I don't really have time to set up something to test it with.

[KML1976]

Kerry

Have you tried executing the SQL statement directly in the database?

I am unable to do this, we can only link and query in Access or via VBA.

[KML1976]

This is what i'm trying to achieve.
thanks

query output.png

[KML1976]

No problem.

When I first saw the code myself I thought the OP was trying to use the VBA Format function.

Just looking at the error message I think the problem lies/starts somewhere here.

Please Login or Register  to view this content.

I'd test it but I don't really have time to set up something to test it with.

I've tried using + instead of & and that doesn't work.

[KML1976]

Shouldnt the formatting part be enclosed in quotation marks

Format([org_param_value].[param_effective_date],'0') (try double quotes if this doesnt work, just be sure you double them up when creating the string)

I've tried '0' and double quotes too (doubling them up) and i'm getting the same error

[KML1976]

OK, i've been allowed to install HeidiSQL so i'm going to give it a go there.

[KML1976]

I've run this directly in the db:

Please Login or Register  to view this content.

and it is returning this:
db query output.png
The lookup value is incorrect, I think it's actually adding the 2 values together instead of just concatenating them.

[Norie]

What happens if you use & instead of + to concatenate?

If that doesn't work try using the CONCAT function.

[gbeats101]

have a look at this, it may (or may not) be the problem

Link

like i mentioned earlier i noticed your using FORMAT() on what appears to be a date field......