To SQL Expert: VBA Function that Calls SQL Query

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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
 
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
Click to expand...

Thanks Harald...
 
Thanks Harald...
Click to expand...

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