MS QUERY excel > Parameters not allowed in non graphical queries

[withoutaclue]

HI

I’m running a complex query in Excel>ms query, however as it is complex, (is using 2 ODBC's) it wont allow me to use parameters.

Therefore is there a way around it as i need to enter in 2 date parameters.

I have looked for table .fields > today()
or table .fields > now()
or table .fields Sysdate()

But they just don’t work, does any one know what function names to use, or a way around this.

I don’t want to move away from excel,

Any help would be great

 

[SkipVought]

withoutaclue,

I use this technique on a regular basis. HOWEVER, it does require VBA using your macro recorder and an minor amount of customizing.

Turn on your macro recorder and EDIT your query -- in and out! Turn off the recorder.

Display the VB Editor (alt+F11) to view your recorded code. It looks more complicated than it is.

The place that you need to edit is in the .CommandText string. This is the SQL code that the graphical interface generates. It looks something like this...

    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=A010PROD;PWD=;DBQ=A010PROD;DBA=R;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MD" _
        ), Array("I=F;CSR=F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = Array( _
        "SELECT MFG_ORDER_INFO.PART_ID, MFG_ORDER_INFO.LPCT, MFG_ORDER_INFO.PST" & Chr(13) & "" & Chr(10) & "FROM FPRPTSAR.MFG_ORDER_INFO MFG_ORDER_INFO" & Chr(13) & "" & Chr(10) & "WHERE (MFG_ORDER_INFO.LPCT<sysdate) AND (MFG_ORDER_INFO.PST>sysdate) AND (MFG_ORDER_INFO.PART_ID='123')")
        .Refresh BackgroundQuery:=False
    End With

The syntax of the SQL depends on the DATABASE that you are accessing. I am accessing an ORACEL database -- hence sysdate. If it's MS Access, then it's Date().

What database? What other parameters -- just the current date?



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]