Using MS Access/Access 2007

Advertisement


Question
QUESTION: Access 2007, I have an append query that I only want to append certain records at a time out of the table.  I would like to set a parameter in the append query that would work something like  Between PaymentID and PaymentID - PaymentID field is an autonumber in the table.  So when I run the append query I am only appending the PaymentIDs that I designate and not the other PaymentIDs which I would have already appended.  And I can't delete the previously appended PaymentIDs for audit purposes.  I can't seem to get the Parameter to work properly for some reason.

ANSWER: Can you post the SQL for your query. Do you want to filter for a range of IDs or only for selected ones.

Also, why are you doing this? I'm assuming that the table records payments. Why would you want these records in another table?

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: I want a parameter that will ask for between PID # and PID # (a range)

Doing this because bossman wants to approve/double check payment data entry before it goes into the Master Payments table.

Here is the Append query sql

INSERT INTO Payments ( [CONTACT ID NUMBER], [Payment Data Entered By], DatePaymentEntered, PaymentAmount, [FORM OF DONATION], CheckNumber, DateDonorWroteCheck, [AIRCRAFT TAIL NUMBER], DirectFundsTo, [SPECIFIC PROJECT DESCRIPTION], HonoreeSalutation, HonoreeFirstName, HonoreeLastName, HonoreeState, HonoreeCity, HonoreeStreet, HonoreeZip, Sal1, FirstName1st, LastName1st, Street1St, City1St, State1St, Zip1St, Sal2, FirstName2nd, LastName2nd, City2nd, Street2nd, State2nd, Zip2nd, Sal3, FirstName3nd, LastName3rd, Street3nd, City3nd, State3rd, Zip3nd, Sal4, FirstName4th, LastName4th, Street4th, City4th, State4th, Zip4th, CODE, COMMENTS, CreditCardType, [EBAY Donation], [EXP DATE], [Item Description], TotalOfPledge, [SPECIAL EVENT BOX], [PAYMENT ID], [SPECIFIC PROJECT BOX], [STOCKS-BONDS], CashFormOfDonation, Memorial, DesignationOfDonationTo, LetterSent, LetterSentBy, DateLetterSent, AuditBy, AuditDate, PaymentDate, [Membership Donation], [DONOR DONATION], [Memorial Donation], Honorarium, [TYPE OF DONATION], [Matching Gift Company Name], [Name of Event], [Date OF Event], EID, [BoD Invitee] ) IN 'F:\SQL databases\Contacts 3.6.2010_be.accdb'
SELECT [DPS Payments].[CONTACT ID NUMBER], [DPS Payments].[Payment Data Entered By], [DPS Payments].DatePaymentEntered, [DPS Payments].PaymentAmount, [DPS Payments].[FORM OF DONATION], [DPS Payments].CheckNumber, [DPS Payments].DateDonorWroteCheck, [DPS Payments].[AIRCRAFT TAIL NUMBER], [DPS Payments].DirectFundsTo, [DPS Payments].[SPECIFIC PROJECT DESCRIPTION], [DPS Payments].HonoreeSalutation, [DPS Payments].HonoreeFirstName, [DPS Payments].HonoreeLastName, [DPS Payments].HonoreeState, [DPS Payments].HonoreeCity, [DPS Payments].HonoreeStreet, [DPS Payments].HonoreeZip, [DPS Payments].Sal1, [DPS Payments].FirstName1st, [DPS Payments].LastName1st, [DPS Payments].Street1St, [DPS Payments].City1St, [DPS Payments].State1St, [DPS Payments].Zip1St, [DPS Payments].Sal2, [DPS Payments].FirstName2nd, [DPS Payments].LastName2nd, [DPS Payments].City2nd, [DPS Payments].Street2nd, [DPS Payments].State2nd, [DPS Payments].Zip2nd, [DPS Payments].Sal3, [DPS Payments].FirstName3nd, [DPS Payments].LastName3rd, [DPS Payments].Street3nd, [DPS Payments].City3nd, [DPS Payments].State3rd, [DPS Payments].Zip3nd, [DPS Payments].Sal4, [DPS Payments].FirstName4th, [DPS Payments].LastName4th, [DPS Payments].Street4th, [DPS Payments].City4th, [DPS Payments].State4th, [DPS Payments].Zip4th, [DPS Payments].CODE, [DPS Payments].COMMENTS, [DPS Payments].CreditCardType, [DPS Payments].[EBAY Donation], [DPS Payments].[EXP DATE], [DPS Payments].[Item Description], [DPS Payments].TotalOfPledge, [DPS Payments].[SPECIAL EVENT BOX], [DPS Payments].[PAYMENT ID], [DPS Payments].[SPECIFIC PROJECT BOX], [DPS Payments].[STOCKS-BONDS], [DPS Payments].CashFormOfDonation, [DPS Payments].Memorial, [DPS Payments].DesignationOfDonationTo, [DPS Payments].LetterSent, [DPS Payments].LetterSentBy, [DPS Payments].DateLetterSent, [DPS Payments].AuditBy, [DPS Payments].AuditDate, [DPS Payments].PaymentDate, [DPS Payments].[Membership Donation], [DPS Payments].[DONOR DONATION], [DPS Payments].[Memorial Donation], [DPS Payments].Honorarium, [DPS Payments].[TYPE OF DONATION], [DPS Payments].[Matching Gift Company Name], [DPS Payments].[Name of Event], [DPS Payments].[Date OF Event], [DPS Payments].EID, [DPS Payments].[BoD Invitee]
FROM [DPS Payments];

Answer
OK, so you are doing your data entry to a temporary table, then moving the approved transactions to the master table? That makes sense.

So what I would do, is create a form with two text boxes: txtStart and txtEnd. Then add a button to run the query. Add the following criteria to your query. In the PID column:

BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd

Use the actual formname.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.