How do I paste in an SQL code into my excel vba code?

[daveyboy23]

Hi guys,

Hopefully someone can help.

 

[Norie]

In the code you posted you have defined the SQL, in a string variable.

You might have defined a function with the same name as the variable elsewhere in your code, I don't know.

As for replacing + with &, that's a general thing and has nothing to do with the problem.

You can keep using + if you want, it'll work most of the time but like I said it can cause problems.

 

[RoryA]

qry_CPI_Logged is a function that requires two arguments to be supplied - you aren't supplying any.

 

[daveyboy23]

Thanks for the input, I have sorted out the SQL string now, but in terms of arguments, presumably because the SQL string contains a date from and to is the reason for the need to enter an argument?

 

[Norie]

A string doesn't require arguments, but you will need to give values to any variables you want to be part of the string.

 

[RoryA]

The way your last posted version is written, it only uses the first parameter so you could remove the table one.

 

[daveyboy23]

Thank you, as you said, tbl not necessary

but now I have another issue, the macro runs (yay)! but it's displaying hundreds more results than it should it do

I have even got rid of the inner join, which I know, will make zero difference

The sql string is this....qry_CPI_Logged = _
"SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] " _
+ "FROM tblCPILOgged " _
+ "WHERE (((tblCPILogged.[Logged Date])>=#" + CStr(convert_date(wc)) + "# And (tblCPILogged.[Logged Date])<#" + CStr(convert_date(wc + 7)) + "#));"

The sql in db is this SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date]
FROM tblCPILogged
WHERE TblCPILogged.[Logged Date] >=#6/23/2014# AND TblCPILogged.[Logged Date] <#6/29/2014#;

 

[RoryA]

Without the data it's pretty difficult to say what's going on. Are any of the dates returned outside your date range?

 

[daveyboy23]

Thank you all for your help, I have learned something.
it's done