Datediff Function and VBA

[lopiner]

First of all, hi to everyone here on the forum and thanks in advance for any help.

I'm having troubles with a query that works fine on the Query section of Access but prompts an error when executed in VBA. The error is:

Run-time error '3141'
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

And the code is as following:

TimesInvestedQuery = "SELECT datediff('m',Min(AUX_Funds_Performance_Invested.Data),Max(AUX_Funds_Performance_Invested.Data)) + 1 - Count(AUX_Funds_Performance_Invested.Data) AS Diff" & _
                     "FROM (SELECT AUX_Funds_Performance_Invested.Fund, AUX_Funds_Performance_Invested.Data" & _
                     "FROM AUX_Funds_Performance_Invested" & _
                     "WHERE AUX_Funds_Performance_Invested.Fund='" & fundo & "');"
Set TimesInvested = CurrentDb.OpenRecordset(TimesInvestedQuery)

Can anyone point me a possible reason for this please. Im new to Access so something simple may be the reason.
Thanks in advanced.

 

[datAdrenaline]

You need to embedd spaces ...

The value of TimeInvestedQuery will look like this when VBA evaluates it:

... Count(AUX_Funds_Performance_Invested.Data) AS DiffFROM (SELECT ...

So .. you need to put the spaces in your expression:

TimesInvestedQuery = "SELECT datediff('m',Min(AUX_Funds_Performance_Invested.Data),Max(AUX_Funds_Performance_Invested.Data)) + 1 - Count(AUX_Funds_Performance_Invested.Data) AS Diff" & _
                     [COLOR=seagreen]" FROM[/COLOR] (SELECT AUX_Funds_Performance_Invested.Fund, AUX_Funds_Performance_Invested.Data" & _
                     [COLOR=seagreen]" FROM[/COLOR] AUX_Funds_Performance_Invested" & _
                     [COLOR=seagreen]" WHERE[/COLOR] AUX_Funds_Performance_Invested.Fund='" & fundo & "');"

 

[lopiner]

datAdrenaline

Thank you very much, the problem is solved, that was it.

Regards