Query Help

[rajeshsarin]

SELECT Payment.Pay_ID, Client.Client_No, Nz(Fname)+" "+Nz(LName) AS [Client Name], Address, Payment.PDate, Payment.Purpose, Payment.Method, Payment.Notes, Payment.Amount, Received_By, Nz(Payment.Purpose)+" - "+Nz(Payment.Notes) AS [Report Field]
FROM Payment, Client
WHERE Client.Client_No=Payment.Client_No And Payment.Pay_ID= ([Enter Payment No For Which Receipt Is Required]);
When I run this query, the prompt is "Enter Payment No For Which Receipt Is Required", however then when my user tries to enter multiple payment nuumbers seperated by commas the query doesnt return any result.

If the user enter just one payment number, then the query returns results matching that particular payment number.

Basically I am using the result of the query to generate a payment receipt. At present if a client has two payments, I end up generating two different receipts and hence two different print outs (basically want to save paper).

 

[wilpeter]

Have you tried separate parameters for each Receipt (Pay_ID) with the "OR" clause?

 

[rajeshsarin]

Have you tried separate parameters for each Receipt (Pay_ID) with the "OR" clause?

The problem is that some clients may just have one receipt ( as they may have made only one payment) whereas another may have 5 or 6 receipts (as they have made multiple payments).

I was hoping that when the prompt box comes up, my users can simply type in multiple receipt numbers seperated by a comma.

Someting like "And Payment.Pay_ID IN (2058, 1986);

The query works, if I put in actual values, however if I instead use a prompt box, and users input numbers seperated by commas, it returns a blank result .

Pay_ID is an autonumber field and hence number only.

 

[wilpeter]

If you view your existing SQL query in Design Mode, does your parameter not treat it as 2058 and 1986 - in which case no record would match both IDs. I'm not well enough versed in code (VBA nor SQL) to write it. I use Design view and simply view the result in SQL. By placing [Enter first Receipt No] on the criteria row and [Enter a second Receipt No] on the OR row, it seems to work to include both records. I just wanted to see if you had tried it. I do know that in Design View, if you put the criteria row as 2058, 1986 it will not work.

 

[jal]

I'm guessing you should create a table called UserTypedPmt_IDs with one column called Pay_ID and bind it to a subform visible to the user (he can type his receipt numbers into the table as displayed on the subform). then you might be able to join this table to other tables (I'm not sure as I don't use subforms much).

...FROM Payment, Client, UserTypedPay_IDs
WHERE Client.Client_No=Payment.Client_No
And Payment.Pay_ID= UserTypedPay_IDs.Pay_ID

 

[stopher]

I'm guessing you should create a table called UserTypedPmt_IDs with one column called Pay_ID and bind it to a subform visible to the user (he can type his receipt numbers into the table as displayed on the subform). then you might be able to join this table to other tables (I'm not sure as I don't use subforms much).

...FROM Payment, Client, UserTypedPay_IDs
WHERE Client.Client_No=Payment.Client_No
And Payment.Pay_ID= UserTypedPay_IDs.Pay_ID

I like this idea too. It will need an event to empty the table at the appropriate moment e.g. On Current.

A similar approach would be to populate a list box with all the relevant Pay_ID's and the user can select one or more then click a button to print. This would need some code though to create a custom query or criteria to run the report.

Chris

 

[wilpeter]

Chris, from the earlier poster's notes, I'm gathering he already has a method that produces a receipt for one item and simply wants to expand it to allow for two or more. My assumption was that a number field wouldn't accept (2058, 1234) as selection criteria, and tried to direct him towards the 2058 or 1234 criteria. Assuming no two Clients access the database simultaneously, a Make-Table Query might take the Client's code as the criteria for step one, producing a second form populated with the resulting receipt numbers. Inevitably though, multiple choices would be made and they would be treated as "or" criteria. Is that logical?

 

[jal]

I'm going to upload a sample.

 

[jal]

Attachment: Open form1 in the sample. The user can type his Pay_IDs into the bottom subform and then click the "Results" button. The results will display in the top subform.

In this sample I only joined two tables but it would probably work just as well if you put three tables in the JOIN query.

type in Pay_Ids ranging from 1 to 8 (my sample data has only those values).

 

[wilpeter]

Since you have made the Pay_ID a Text field with Duplicates OK, I'm wondering if that was intentional. Normally it would be unique to avoid duplicate receipts. How are the Pay_ID 'numbers' assigned?

 

[jal]

My upload was just a sketch to get him moving in one direction or another. I wasn't suggesting he set up his tables in the same way I did. I was just showing him the general idea of how to use subforms to attack this problem.

 

[rajeshsarin]

Reply: Query Help

Thanks for all your replies,

I am attaching a screen shot of the system to give you a better idea of my problem.

As you can see there is a print invoice button, which the users press to then enter the payment number to print invoice.

I want to enable the users to enter multiple payment numbers in the same box (if multiple payments exist for the client).

Alternatively let the users select the payment number (maybe via tick box, or highlighting the payment number lines with a cursor) and then press the print invoice button - I always wanted to use this approach, but I am VB
Dyslexic .
Regards

 

[jal]

Re: Reply: Query Help

Thanks for all your replies,

I am attaching a screen shot of the system to give you a better idea of my problem.

As you can see there is a print invoice button, which the users press to then enter the payment number to print invoice.

I want to enable the users to enter multiple payment numbers in the same box (if multiple payments exist for the client).

Alternatively let the users select the payment number (maybe via tick box, or highlighting the payment number lines with a cursor) and then press the print invoice button - I always wanted to use this approach, but I am VB
Dyslexic .
Regards

I use Access 2003, but I still think my sample is relevant to your question -and it doesn't take much code. I only used one line of VBA code to produce that sample, everything else was selected from wizards. It allows the user to type in multiple pay_IDs. Any reason not to follow my example?