How do you build a query or report w/ list of parameters, separated by commas.

[DanicaDear]

This is a two part question... I'm after one solution but I've thought of two possible ways to make this work.

I want to have a rptCustomer_Inquiry_Report that will list customer information for specific, user-defined customers. They will be identified by their Customer_Number. For example, I want to be prompted to Enter Customer Numbers and then enter "10628, 10654, 11369". When I make a query and set the criteria to the parameter [Enter Customer Numbers], if I enter more than one parameter I get zero results.

One idea is to get a query working. I would just need to know how to allow the user to enter the multiple values and have the database actually give me the desired results. What kind of query would this require?

Another idea is to use a parameter in the actual report. I'm less confident this is possible and/or straightforward. I haven't found too much info on specifying a parameter in a report, but I may find this useful in other situations.

Please advise or offer opinions. I am still very new at Access so don't assume I know too much. :-)
Thanks so much.

[ADezii]

Opening a Report that has a Data Source which is based on a Single or Variable number of Parameters can be a little tricky. I created a Code template that will do just that. The User will enter either a single or multiple Comma Separated Customer IDs into a Text Box named txtCustomers. The code will parse this String, then generate an SQL Statement to be used in the WHERE Clause of the OpenReport Method. Substitute your values as required and should you have any questions, feel free to ask.

Dim varCustomerIDs As Variant
Dim intCounter As Integer
Dim strSQL As String
 
If IsNull(Me![txtCustomers]) Then Exit Sub
 
varCustomerIDs = Split(Me![txtCustomers], ",")
 
For intCounter = LBound(varCustomerIDs) To UBound(varCustomerIDs)
  strSQL = strSQL & "'" & Trim(varCustomerIDs(intCounter)) & "',"
Next
 
strSQL = "[CustomerID] IN(" & Left$(strSQL, Len(strSQL) - 1) & ")"
 
 
DoCmd.OpenReport "rptCustomers", acViewPreview, , strSQL

[DanicaDear]

I have attached my DB. I have had some pretty intelligent help from BYTES so anything you see pretty handy...probably was my idea paired up with someone else's hints and code.

My problem with answer's such as yours is that I can never figure out where to put the code. I'm getting better with things..my speed is definetely improving...but I'm still a little slow. (I'm only 3 weeks into Access.)

So, where would I put this code you refer to...and how do I call up the text box to enter the parameters? So far my parameter text boxes have been called up by MS Access where I have just entered in the parameter name and type. I think I've only entered code from forms and reports so I'm thinking this code has to attach to the properties box in the report. Specific instructions are appreciated.

If you want to look at my DB, the report I'm trying to get this parameter to run is named rptHOTSTICK_CUSTOMER_INQUIRY. The query the customer number and customer info is based from is named HOTSTICK_CUSTOMERS Query. Thanks so much for your help and time.

[NeoPa]

Danica,

This code should work for you and it would be in the Click event procedure of the Command Button that you want to trigger running the report.

If you post the procedure where you currently run the report, I'm sure either of us can tell you how to incorporate this code so that it works for you.

PS. Terminology can be very important in Access. Fields aren't controls. Understanding the terminology can be very helpful to you in understanding what we are saying (and vice-versa of course). If there's ever anything you're not sure of simply ask and we'll be pleased to enlighten.

[NeoPa]

ADezii's code should work (as I mentioned earlier) but I would do it slightly differently (This works under the same basic assumptions though. Names etc.) :

Private Sub cmdReport_Click()
    Dim strSQL As String
 
    If IsNull(Me.txtCustomers) Then Exit Sub
 
    strSQL = Replace(Trim(Me.txtCustomers) , " ,", ",")
    strSQL = Replace(strSQL, ", ", ",")
    strSQL = Replace(strSQL, ",", "','")
    strSQL = Replace("[CustomerID] IN('%S')", "%S", strSQL)
 
    Call DoCmd.OpenReport(ReportName:="rptCustomers", _
                          View:=acViewPreview, _
                          WhereCondition:=strSQL)
End Sub

This also handles errors where the operator puts unrequired spaces around any of their commas.

[ADezii]

Danica, I'm sending your Database back to you with the following changes:

1. The Database opens with a Form being displayed.
2. This Opening Form contains a Text Box with Sample Customer Numbers pre-entered. These are your Comma-Delimiter Parameters.
3. There are also 2 Command Buttons on this Form. These Command Buttons contain both my code and NeoPa's in the Button's Click() Event Procedure. There is no Door #3! (LOL)!
4. View the code in each Click() Event to see its implementation.
5. Simple code was placed in the NoData() Event of the Report. Should no Records be returned for a specific Parameter or set of Parameters, a Message Box will be displayed indicating same and the Open() Event of the Report will be cancelled.
6. Have fun with this, and should you have any further questions, either NeoPa or myself will be glad to assist.
7. Hope you don't mind me speaking for you, NeoPa.

[NeoPa]

Absolutely not my friend. You always do such a thorough job. You go more extra miles than anyone I know.

BTW Do me a favour would you. Tell me whether you see the link to Choose as best answer against every post bar the original in this thread?

I expect Danica does, as she's the OP. I know I do, as I can see them. Do you?

[ADezii]

Yes NeoPa, I do see the Link.

[NeoPa]

Thanks. That's a liitle weird though. I'll have to chat with KUB about this sometime soon. Cheers.

[DanicaDear]

Believe it or not....I got this working! (I hope you're proud of me NeoPa!!) :-) ADezii, thank you for putting the extra time to show me exactly how that worked. I was able to figure it all out, but I did use NeoPa's code. She has helped me a LOT lately and so I just used her code to be consistent. LOL.

One question, when doing a report, I notice when the report is based off a couple different queries, if one customer doesn't have data in a particular query they will not show up in the report. For example, customer number 10654 is enetered into the form, it says no records available. This is because they have no orders yet in the table HOTSTICK_ORDERS. However, we can get customer info before they have an order...and we might still want to view the customer in this report. I saw this happen in another report when I didn't have an account number put in...it just left the customer off the report. How can I prevent this?

[ADezii]

@DanicaDear
You can accomplish this by changing the Join Properties of the Tables involved in the underlying Record Source for your Report. Now, all Customers will be included regardless of whether or not they have actually placed an Order. Download the New Attachment. and view the Join Properties of the Tables involved in the Query for the Report. If you are not sure as to how to do this, let us know.

[DanicaDear]

Well, flip!! I tried so hard to figure this out without having to come back and ask.... I am viewing my relationships and join properties between tables and other queries in query design view. However, I don't see any changes from the DB you uploaded and my original. I know *you* did it right because it's working in your report. Can you be more specific about what I should look at? I think this is worth my effort to learn. It will occur in many more reports than just this one.
Thanks, Danica PS. I will be gone until Monday so please stick around til then. LOL. ;-)

[ADezii]

@DanicaDear

1. Single, Left-Click on the Join Line between Tables in your Query - the Line will now be thicker and Bold (it is selected).
2. Right-Click on the Join Line to Open the Join Properties Dialog Box.
3. The answer to your Question should now be obvious.

[NeoPa]

@DanicaDear
I am proud of you Danica. You've come on leaps and bounds, and I like working on your questions generally as your attitude is so healthy.

I'm not entirely sure why I gave the impression of being a NeoMa - but possibly you didn't pick up on the reference in the name ;) I'll take it as a compliment though. I'm sure it was so intended :)
@DanicaDear
Check out SQL JOINs.

[DanicaDear]

It's a sad Monday morning here in Alabama. I STILL cannot figure out what you did on the join properties. At this point I'm going to be very specific. I opened qryCUSTOMER_INQUIRY. In design view, I clicked SHOW TABLES and chose HOTSTICK_ORDERS and HOTSTICK_ORDER_DETAILS. I looked at the join properties like you told me to. (I was in fact doing it the same way last week.) What I am unable to find is what you changed. You said it should be obvious to me so I'm thinking I am either in the wrong query or looking at the wrong table relationships. All of the ones I see in your modified DB have the same settings as mine...yet I know you did something magical because when you run YOUR report the desired results appear.

(Recall, start the report from frmPassCustomerNumbersToReport, customer 10654 is the magic parameter.)

This time, I have attached my real unmodified DB. I haven't been just taking the DBs you attach, I have been doing the exercises in my own DB (to learn how). Could you please help me find what I need to do to make this work? I know you have given, but I have not received. LOL. ;-)

(As a side question, if I hadn't named my reports to coincide with my queries, I would have trouble figuring out which query a report was based on. How could you determine it otherwise?)

[ADezii]

@NeoPa
Sorry NePa, but I also thought that you were a she! (LOL)!

[ADezii]

View the Attachment, Danica, and all should be clear.

[NeoPa]

@DanicaDear
Although relationship definitions can help Access to determine the default joins between two related tables, the actual joins themselves are entirely under your control within the QueryDef definition. If none already exists, you can drag a field from one table and drop it on the related field in the other to join two fields. This can be done with up to ten field pairs.

When two tables are joined in a QueryDef they will have lines connecting each of the fields to be linked in one table, with each of the fields to be linked in the other. Each of these connecting lines should reflect the same type of join or Access will get shirty with you.

When you double-click on any of the lines you will see the Join Properties box. This shows the tables and the fields involved. The left indicates the table and field that you dragged from, and the right indicates those that you dropped on (if you did it that way).

Below that are three Join Type options. For Access to ignore any result sets where the same data doesn't exist in both input tables select a Type 1. If you want either of the tables to show their data regardless of whether there is matching data in the other, then select either Type 2 or Type 3 depending on which you require. See SQL JOINs for more help on this point.
@DanicaDear
Using Polish Notation (I think it's called) each object has a 3-letter code which identifies the type of object it is.

tblName ==> Table
qryName ==> Query
frmName ==> Form
rptName ==> Report
modName ==> Module

[NeoPa]

@ADezii
Don't give me that!! We spoke on the phone once some while ago. I may not be Barry White, but I doubt you'd have mistaken me for a wo-man.

[ADezii]

@NeoPa
Gotcha, didn't I? (LOL)!

[DanicaDear]

@ADezii
It's because NeoPa is so nice and patient..... All this has cracked me up.

I hope you have not been offended that I thought you were a she. I will admit I have been reading your posts in a female voice with a British accent... LOL.

What does NeoPa mean, anyway? :-)

[DanicaDear]

@NeoPa

I think you may have misunderstood my question here....allow me to try again. I used qryCUSTOMER_INQUIRY to create rptCUSTOMER_INQUIRY. Forget about the Polish (or Hungarian?) notation....if I didn't name them both CUSTOMER_INQUIRY...then how would I know which query a report was based from? (Say in case I needed to add a control/field to the query to then add to the report. Is there another intelligent way to tell?)

[ChipR]

I believe you would have to check the data source defined in the report's properties or code.

[ajalwaysus]

@DanicaDear
This is the greatest post I have ever read! Can't stop Laughing. LOL!!!

Sorry, absolutely no offense intended. =)
-AJ

P.S. I'm a "He" just as an FYI.

[DanicaDear]

ADezii,
I have looked at that at least 20 times. If it makes any difference, you explained this in a receivable way the very first time. Thank you for the attachment. I am attaching my own word document which may explain why I have been so confused. The DB that works correctly, the one you sent me, doesn't have a join line between HOTSTICK_CUSTOMERS Query and HOTSTICK_ORDERS Query, the same two queries shown connected in the WORD file you sent me. I was definetely expecting a radio button in the 2 or 3 position clicked (not the 1st position), but I could never find one. Then I thought, well if that's how it's supposed to be, I'll just do it like that in my own. But then that didn't explain how your report was working properly. Take a look at my attached file and please tell me if I'm making any sense.
I'm sorry for dragging out a simple issue here but I really do want to understand what I'm not doing correctly. Thanks!

[DanicaDear]

Oops...I don't think it attached last time.
Danica

[NeoPa]

@DanicaDear
Not in the least. I felt sure you meant it as a compliment anyway and it brought a big grin to my face.

I certainly do have a British accent (as you would say), but probably down an octave or two from my female countryfolk ;)
@DanicaDear
NeoPa comes from my gaming days. When I joined a server that my son (who called himself N30 (Neo)) was playing on I used this to indicate I was his dad (Pa). That way I got a bit of slack until I'd warmed up a little. N30 was always very good at games (well - I used to beat him regularly until he started getting pubertised) so people put up with me if that meant they got to play with N30.
@ADezii
Darn it! I just put it down to a senior moment there. Forgetting who you were talking to and all that nonsense :D

[NeoPa]

@ajalwaysus
s l a p !!!

[ADezii]

@DanicaDear
Simply stated, the Data Source for your Report cannot consist of three interrelated Tables and produce the results you are requesting. The Customers and Orders Tables must comprise a single Query with ALL Records from the Customer side (1). This Query can now be joined to the Details Table in another Query in order to produce the desired results. If you do not construct the Data Source for your Report this way and still try to include ALL Records from Customers, you will get an Ambiguous Outer Join Error. The Attachment indicates how it should be done.

[DanicaDear]

WOOOHOOOO!! I have found it now!!!!!!!!!!!!!!! Thanks so much for your patience and continued support. I will try to make this work for me now. :-)

[DanicaDear]

I am so embarrased. That was the easiest thing I've ever done so far. Sometimes just finding out where to look is 99% of the problem. I was looking in the queries themselves. Thanks to you and a hint from ChipR, I found the correct place in the property of the report itself. Thanks again. This problem is SOLVED! :-)