Using Microsoft Query in MS Excel 1

[Ucaihc]

I create a complex query in Microsoft Query within Excel and get the message "SQL Query can’t be represented graphically"
I think this means that I can't add a parameter criteria to the query (I need the user to select the year). Is there any way around this?

 

[barbola]

That message just means it can't take you back to the wizard. If you click OK to that message, it will open MS Query in another window. From there it looks very similar to an Access query.

You can enter your criteria using [Date] instead of an actual date, and it will prompt the user to enter the date.

When you get back into Excel (close the query screen), you can use Data > Get External Data > Parameters to specify a cell in the Excel sheet for the date parameter.

I hope that makes sense.

b.

 

[Ucaihc]

Sorry for the delay, I was away for a few days.

After I get the "represented graphically" message I got the message that I can't use paramters in this query.

I tried what you suggested, using p.program_year=[Date] in the where clause, but I get and Oracle missing expression error. Is that what you meant?

 

[barbola]

If you are putting the criteria directly into the SQL statement I beleive you need to just use a question mark.

where p.program_year=?

but I'm connect to a SQL database, not Oracle, and not sure if that makes a difference.

You should be able to see the criteria section by clicking on that E that doesn't look like an E (not sure what it's called, some greek name) LOL

b.

 

[Ucaihc]

Thank you. It works now.

 

[SkipVought]

Hi,

If it Oracle, the syntax can be different.

For Current Data, use SYSDATE rather than DATE...

p.program_year=To_Char(SYSDATE,'yyyy')

Skip,

_[red][/red]