---all works find under SQL...The SQL statement i run in QA is as follows:

SELECT amount, transactiondate, accountnumber
FROM fullCC
WHERE amount = 192.11 AND transactiondate BETWEEN '10/10/02' AND '10/30/02' AND lastfour = '4000'

-----

The code in my access form is writing the SQL statement exactly as the one used in QA, only it appears to be timing out in the form. Therefore, I'm thinking a stored procedure for that SQL statement may be a good alternative...

I just don't know if a stored procedure is possible since I am getting the values from a text box in a form and don't know how to exactly structure it in SQL to where it sees the values from the text box.

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

It's telling that ccdbusr does not exist???? In the Users Section, it is clearly there...Keep in mind this is a Local Machine where the Access Front End Resides on the same machine as the SQL Server....

This is ugly, but it does work. Call the SP passing any combo of Amount, LastFour, or (Start/End) Dates. The defaults in the SP should be adjusted to cover any possible dates if none are specifically given.

If @Amount Is Not Null and @LastFour Is Not Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Not Null and @LastFour Is Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate

If @Amount Is Null and @LastFour Is Not Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Null and @LastFour Is Null
SELECT amount, transactiondate, accountnumber FROM fullCC

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

If @Amount Is Not Null and @LastFour Is Not Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Not Null and @LastFour Is Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate

If @Amount Is Null and @LastFour Is Not Null
SELECT amount, transactiondate, accountnumber FROM fullCC
WHERE transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Null and @LastFour Is Null
SELECT amount, transactiondate, accountnumber FROM fullCC

basically in the command button on click event i had:

DoCmd.openstoredprocedure ("sp_PartCC") '&txtAmount.Value&' AND '&txt.PartialCCNbr&' AND &'txtFromDate.Value&' AND &'txtToDate.Value&'

I did that thinking that the order of the variables in the stored procedure that it would pass the parameters from the text boxes to the stored procedure...Instead the stored procedure runs when clicking on it, but I get a prompt for Amount, LastFour, StartDate,EndDate...ignoring the textbox values...

The RowSource in the Form's List Box properties is pointing to the view from the Database table.

>>Do I need to include the cn to connect to the server<<
Do be candid I do not know. Since it is used in the Command object you will need something like it, perhaps MS Access has something that will return the current database. But this question is best posted in the MS Access Topic Area.

If @Amount Is Not Null and @LastFour Is Not Null
SELECT * FROM viewfullCC
WHERE amount = @Amount AND date BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour
Else If @Amount Is Not Null and @LastFour Is Null
SELECT * FROM viewfullCC
WHERE amount = @Amount AND date BETWEEN @StartDate AND @EndDate
Else If @Amount Is Null and @LastFour Is Not Null
SELECT * FROM viewfullCC
WHERE date BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour
Else If @Amount Is Null and @LastFour Is Null
SELECT * FROM viewfullCC
WHERE date BETWEEN @StartDate AND @EndDate
----------

The problems that I'm running into now/still:

1) I still cannot append the parameters from the textbox values to the variables in the stored procedure

2) If I run the procedure separately and manually type in a value on the pop up dialog box, the procedure runs fine.

3) I am trying to bind the listbox (lstFullCC) to the recordset to see if the actuall btnSearch_Click event is working or not
in opening/running the stored procedure passing the parameters to it.

I am increasing the points as I am really appreciative of the help and learning little by little.

note to lludden: I had to ad the Else IF to the procedure, otherwise it would run all the if when I only wanted to see which
condition it met and run that.