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:

'SQL Query String
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 " & _
        "FROM ((daily_report_items INNER JOIN daily_report_name ON daily_report_items.cmn_id = daily_report_name.cmn_id) INNER JOIN org_param_value ON daily_report_items.cmi_param = org_param_value.param_id) INNER JOIN org_parameter ON org_param_value.param_id = org_parameter.param_id " & _
        "WHERE (((daily_report_name.cmn_id) = 1414) And ((daily_report_items.cmi_active) = 1) And ((daily_report_items.cmi_status) = 1) And ((org_param_value.param_status) = 1) And ((org_param_value.param_effective_date) >= '" & RptDate & "')) " & _
        "GROUP BY 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;"

[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?

[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]

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

[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)

[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

[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.

"SELECT Format([org_param_value].[param_effective_date],0) + [org_parameter].[param_id] AS [LookupValue]

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

[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.

"SELECT Format([org_param_value].[param_effective_date],0) + [org_parameter].[param_id] AS [LookupValue]

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]

I've run this directly in the db:

SELECT org_param_value.param_effective_date + org_parameter.param_id as Lookup_Value, 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 
FROM ((daily_report_items INNER JOIN daily_report_name ON daily_report_items.cmn_id = daily_report_name.cmn_id) INNER JOIN org_param_value ON daily_report_items.cmi_param = org_param_value.param_id) INNER JOIN org_parameter ON org_param_value.param_id = org_parameter.param_id 
WHERE (((daily_report_name.cmn_id) = 1414) And ((daily_report_items.cmi_active) = 1) And ((daily_report_items.cmi_status) = 1) And ((org_param_value.param_status) = 1) And ((org_param_value.param_effective_date) >= '2015/12/07')) 
GROUP BY 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

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.

[KML1976]

This is what i'm trying to achieve.
thanks

query output.png

[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......