To SQL Expert: VBA Function that Calls SQL Query

[Mike]

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

 

[Harald Staff]

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]

Hi Mike I'm quite sure I don't understand your question. But anyway, thiswill 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" <[email protected]> skrev i melding news:[email protected]... > 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: Howshould 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...

 

[Andrew Taylor]

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,"'","''") & "'"