While I'm at it, what about passing an array to a VBScript?

[mightycpa]

Hi,

Sorry to be so verbose.

I'm trying to create a multipurpose Access Query Macro. I want to call this macro from other macros, and pass the sql along, like this:

Macro>select.scp /whereclause=array

I want to do this because of two reasons.

1) I want my select macro to be able to handle any number of SQL statements
2) I want my calling macro to be able to pass dynamic whereclause info

I find that one challenge is to pass the where clause information. The SQL in Access is written like this:

SQLBASE=SELECT time.* FROM [time] where time.serial = '12345';
or
SQLBASE=SELECT time.* FROM [time] where time.serial = "12345";

these quote and double quote characters give rise to VBScript compilation errors when passed through VBEval>Query(%SQLBASE%)

Of course, I could pass three variables,

SQLBASE= SELECT time.* FROM [time] where
WHRECLS1=time.serial
WHRECLS2=12345

VBEval>Query(%SQLBASE%,%WHRECLS1%,%WHRECLS2%)

and concatenate the whole shootin' match in VBScript, but that doesn't help me if I need something like

SQLBASE=SELECT time.* FROM [time] where time.serial = '12345' and weekend = '01/15/2004';

because I'm not passing all the variables I'd need.

To handle this, I'd probably pass value pairs, like:

time.serial,12345
weekend,01/15/2004
whatever,whatnot

and concatenate them together in VB, but to do that, I need to either know how many there are (not multipurpose) or to pass an array....

Has anybody done something like this?

I'm at the point where I'm thinking about writing my own little VBS "Separate" code to do the job. Please help me avoid that.

Tx,

George

[mightycpa]

Impatience has produced the following:


//This separates value pairs passed as a string from MS to VBScript
//Syntax of string:
//All value pairs end in delim1
//All value pairs are separated by delim2
//End result is my whereclause

VBSTART
Dim ArrVar(99,99)

Function VBSeparate(mystring,delim1,delim2)
'MsgBox mystring & delim1 & delim2
i=0
While w=0
x=InStr(mystring,delim1)
If x 0 Then
piece=Left(mystring,x-1)
y=InStr(piece,delim2)
If y=0 Then
MsgBox "Error in variable"
End if
ArrVar(i,1)=Left(piece,y-1)
ArrVar(i,2)=Mid(piece,y+1)
i=i+1
Else
w=1
End if
mystring=Mid(mystring,x+1)
Wend

For h=0 to (i-1)
'Msgbox ArrVar(h,1) & " : " & ArrVar(h,2)
whereclause= whereclause & " and " & ArrVar(h,1) & " = '" & ArrVar(h,2)& "'"
Next
VBSeparate=Mid(whereclause,5)
'Msgbox whereclause
End Function

Function Query(SQL,WHRCLS,D1,D2)

whereclause = VBSeparate(WHRCLS,D1,D2)
MySQL = SQL & whereclause
MsgBox MySQL

'go ahead and execute the query...
'this code is re-usable
'and supports a dynamic where clause built in the code

'MacroScheduler + VBS is Awesome!

End Function


VBEND
Let>BaseSQL="Select * from time where"
Let>StringThing="one|uno~two|dos~three|tres~four|cuatro~"
Let>d1="~"
Let>d2="|"
VBEval>Query(%BaseSQL%,%StringThing%,%d1%,%d2%),retval