Microsoft Query Help

  • Thread starter Thread starter Dave Y
  • Start date Start date
D

Dave Y

Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
 
Dave,

I just figured this out for myself last week because I got no answer here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos
 
Hey Carlos,

Thank you for your quick reply. Unfortunately, I cannot
access the value field because there is no graphical
representation of the tables for my query. I tried
to "show tables" and also to "add criteria" but the sub-
options in the drop dowm menus are grayed out. I have my
main query wrapped within a sub-query plus I am using a
union within the main query which may be why I can't get a
graphical showing of the tables. I'm hoping to come up
with a way of using a specified date from the spreadsheet
with some form of code, whether it be SQL or maybe VBA.
Maybe I'll just try to start over from scratch and see if
I can come up with a way to see the tables that I have to
use. Thanks again for your reply; if you any more
suggestions or solutions I would like to hear them.

Dave Y
-----Original Message-----
Dave,

I just figured this out for myself last week because I got no answer here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...


.
Click to expand...
 
Dave,

The SQL looks like this in my query:

SELECT sotran.sono, sotran.opno, sotran.code, sotran.t_type, sotran.t_date,
sotran.t_time, sotran.part_grade, sotran.qty, sotran.amount,
sotran.timestamp, sotran.labor_post
FROM sotran sotran
WHERE (sotran.sono=?)
ORDER BY sotran.t_date, sotran.t_time

The question mark in the WHERE line is the parameter. I guess you could try
that.

Carlos

Dave Y said:
Hey Carlos,

Thank you for your quick reply. Unfortunately, I cannot
access the value field because there is no graphical
representation of the tables for my query. I tried
to "show tables" and also to "add criteria" but the sub-
options in the drop dowm menus are grayed out. I have my
main query wrapped within a sub-query plus I am using a
union within the main query which may be why I can't get a
graphical showing of the tables. I'm hoping to come up
with a way of using a specified date from the spreadsheet
with some form of code, whether it be SQL or maybe VBA.
Maybe I'll just try to start over from scratch and see if
I can come up with a way to see the tables that I have to
use. Thanks again for your reply; if you any more
suggestions or solutions I would like to hear them.

Dave Y
-----Original Message-----
Dave,

I just figured this out for myself last week because I got no answer here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...


.
Click to expand...
Click to expand...
 
Carlos, you rule! I've been trying to figure out how to do this FOREVER!
Thanks so much for the tip!

CarlosAntenna said:
Dave,

I just figured this out for myself last week because I got no answer here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Dave Y said:
Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...
Click to expand...
 
You're welcome, Patti. Glad I could help.

Carlos

Patti said:
Carlos, you rule! I've been trying to figure out how to do this FOREVER!
Thanks so much for the tip!

CarlosAntenna said:
Dave,

I just figured this out for myself last week because I got no answer
here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt
here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the
context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Dave Y said:
Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...
Click to expand...
Click to expand...
 
Carlos thanks a million I was trying to get this right for ages, the weird
pard is that is so simple...

Thanks a million

Antonio Ramos

CarlosAntenna said:
Dave,

I just figured this out for myself last week because I got no answer here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Dave Y said:
Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...
Click to expand...
 
Antonio

You might look here

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Antonio Ramos said:
Carlos thanks a million I was trying to get this right for ages, the weird
pard is that is so simple...

Thanks a million

Antonio Ramos

CarlosAntenna said:
Dave,

I just figured this out for myself last week because I got no answer
here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt
here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the
context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Dave Y said:
Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...
Click to expand...
Click to expand...
 
Great page Nick. Where were you when I was looking for this a couple of
months ago? <VBG>

Carlos

Nick Hodge said:
Antonio

You might look here

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Antonio Ramos said:
Carlos thanks a million I was trying to get this right for ages, the
weird
pard is that is so simple...

Thanks a million

Antonio Ramos

CarlosAntenna said:
Dave,

I just figured this out for myself last week because I got no answer
here.
Maybe I just didn't ask the right question.

In MS Query go into the "Value" field right below the "Criteria Field".
Enter a pair of square brackets with a prompt in between [your prompt
here].
Then when the query runs a box pops up asking for the value.

If you want it to refer to a cell in the spreadsheet you can right click
somewhere in your retrieved data then choose "Parameters" from the
context
menu. Select "Get Value from Cell" and enter the cell reference.

Carlos

Hello,

I have a Microsoft Query that connects to an Oracle DB and
bring the data results into an Excel spreadsheet. The
query runs fine and brings in the correct data, but my
issue is this; currently the SQL in the query is set to
bring in the data from SYSDATE-1. How can I change things
so that the user can type the desired date in a cell (for
example Cell A1)in the Excel spreadsheet and then click
Refresh Data to bring in the data from that date? There is
no graphical representation for this query so is there a
way for me to reference the cell the date is entered into
in my SQL code? Any help will be greatly appreciated,
thank you.

Dave Y
Click to expand...
Click to expand...
Click to expand...
 
Back
Top