Link to home
Start Free TrialLog in
Avatar of Jeff
JeffFlag for United States of America

asked on

iSeries DB2 SQL - Request user input

Is there a way to trigger a prompt for input from a user in DB2 SQL? Currently, I am running Pass-Through Queries using Microsoft Access as a front end and on some of my queries I need the flexibility to have users input information such as dates for processing the query. Could this possibly be performed using VBA in Access?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

1. use parameters in your query, in the design view of the query, Click on Parameters from the ribbon and fill out the Query Parameters

the query will look something like this
PARAMETERS [Enter Date] DateTime;
SELECT .....

2. you can use VBA codes and a form to enter the dates and modify the SQL Statement using QueryDef
Avatar of Jeff

ASKER

It doesn't appear to work for Pass-Through queries. The Parameters option is grayed out.
you can use option 2.
how is your VBA coding?
post the SQL statement of your pass thru query
Avatar of Jeff

ASKER

I've never written VBA for Access. Years ago, I wrote a lot of VBA in Outlook so I am jumping to the conclusion that I can find my way through Access and pick it up.  For simplicity here is a basic query. Instead of hard coding customer number, beginning date, and end date I would like to be prompted.

select cusnum, code, sum(quantity) as cases
from tblinvoicing
where cusnum = 874  and invdtes > 20160501 and hhidtes < 20160731
group by cusnum, code
order by code

Open in new window

In the past, I've used an Access form to input the selection criteria, built the select-statement or stored-procedure-call in VBA, and sent it to DB2 via ODBC. It's been years since I've done it, but I know it does work.

HTH,
DaveSlash
try this first

select cusnum, code, sum(quantity) as cases
from tblinvoicing
where cusnum = [Enter Customer Number]  and invdtes > [Enter Date1] and hhidtes < [Enter Date2]
group by cusnum, code
order by code
Avatar of Jeff

ASKER

SQL0104: Token [ was not valid
If memory serves, I believe you put question-marks for the parameters you want prompted.

e.g.
select cusnum, 
       code, 
       sum(quantity) as cases
  from tblinvoicing
 where cusnum = ?
   and invdtes > ?
   and hhidtes < ?
 group by cusnum, code
 order by code
;

Open in new window


HTH
Avatar of Jeff

ASKER

Dave. ODBC Call failed: Wrong number of parameters.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeff

ASKER

Awesome! That worked great! Thank you!