To SQL Expert: VBA Function that Calls SQL Query
 

Hi everyone,

I have a VBA function like this:

Function Total(VarName1, VarTable, VarName2, VarValue1, VarName3, VarValue2)

Inside the function, I have a query of the type:
Total = "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 = 'VarValue2' "

The function will be used as Add-ins in Excel. Note the words that start with "Var..". In a cell, I would type: "=Total(..,..,..,..,..,..)"

My question: How should I define Var.. ones in "Function Total(...)" and "Total = " so those values are typed inside the excel cell, and then scrolled down?

Thanks,
Mike

To SQL Expert: VBA Function that Calls SQL Query
 

Hi Mike

I'm quite sure I don't understand your question. But anyway, this will never
work:

"Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 =
'VarValue2' "

Everything between this pair " " is treated as literally this, and your
search for text 'VarValue1' will probably return nothing. Notation with
variables should read:

"Select VarName1 From VarTable Where VarName2 = '" & VarValue1 & "' AND
VarName3 = '" & VarValue2 & "'"

Best wishes Harald


"Mike" skrev i melding
...
Hi everyone,

I have a VBA function like this:

Function Total(VarName1, VarTable, VarName2, VarValue1, VarName3,
VarValue2)

Inside the function, I have a query of the type:
Total = "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND
VarName3 = 'VarValue2' "

The function will be used as Add-ins in Excel. Note the words that start
with "Var..". In a cell, I would type: "=Total(..,..,..,..,..,..)"

My question: How should I define Var.. ones in "Function Total(...)" and
"Total = " so those values are typed inside the excel cell, and then
scrolled down?

Thanks,
Mike

To SQL Expert: VBA Function that Calls SQL Query
 

On Wednesday, November 20, 2013 5:32:36 PM UTC-5, Harald Staff wrote:
Hi Mike I'm quite sure I don't understand your question. But anyway, this will never work: "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 = 'VarValue2' " Everything between this pair " " is treated as literally this, and your search for text 'VarValue1' will probably return nothing. Notation with variables should read: "Select VarName1 From VarTable Where VarName2 = '" & VarValue1 & "' AND VarName3 = '" & VarValue2 & "'" Best wishes Harald "Mike" skrev i melding ... Hi everyone, I have a VBA function like this: Function Total(VarName1, VarTable, VarName2, VarValue1, VarName3, VarValue2) Inside the function, I have a query of the type: Total = "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 = 'VarValue2' " The function will be used as Add-ins in Excel. Note the words that start with "Var..". In a cell, I would type: "=Total(..,..,..,..,..,..)" My question: How should I define Var.. ones in "Function Total(...)" and "Total = " so those values are typed inside the excel cell, and then scrolled down? Thanks, Mike

Thanks Harald...

To SQL Expert: VBA Function that Calls SQL Query
 

On Thursday, 21 November 2013 14:19:20 UTC, Mike wrote:
On Wednesday, November 20, 2013 5:32:36 PM UTC-5, Harald Staff wrote:

Hi Mike I'm quite sure I don't understand your question. But anyway, this will never work: "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 = 'VarValue2' " Everything between this pair " " is treated as literally this, and your search for text 'VarValue1' will probably return nothing. Notation with variables should read: "Select VarName1 From VarTable Where VarName2 = '" & VarValue1 & "' AND VarName3 = '" & VarValue2 & "'" Best wishes Harald "Mike" skrev i melding ... Hi everyone, I have a VBA function like this: Function Total(VarName1, VarTable, VarName2, VarValue1, VarName3, VarValue2) Inside the function, I have a query of the type: Total = "Select VarName1 From VarTable Where VarName2 = 'VarValue1' AND VarName3 = 'VarValue2' " The function will be used as Add-ins in Excel. Note the words that start with "Var..".. In a cell, I would type: "=Total(..,..,..,..,..,..)" My question: How should I define Var.. ones in "Function Total(...)" and "Total = " so those values are typed inside the excel cell, and then scrolled down? Thanks, Mike



Thanks Harald...

You should also make sure you deal with the possibility of quotes within
the VarValues (to prevent errors and SQL injection attacks), so:

"Select VarName1 From VarTable Where VarName2 = '" & Replace(VarValue1,"'","''") & "' AND
VarName3 = '" & Replace(VarValue2,"'","''") & "'"