Append Query Problem - Posting record only once

Hello,

I have a query that calculates a bank balance.  I want this query to
append the current days date and the bank balance to a table.  I have
a macro setup to autorun the query when my database opens, but can't
get it to work correctly.  Details:


Table where I want the records added:  BankingBalanceRecord
Query that will run:  BankingRecordBalance
                                   -This query is setup to pull
today's date and the balance.  It is funtioning properly.


The main problem I am having is that if the primary record (Today's
date) already exists, it gives me an error message.  As I open the
database multiple times a day, i only want it to record the balance
the first time.


Thanks for the help.

0
wesley
11/6/2007 1:57:07 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
738 Views

Similar Articles

[PageSpeed] 15

Wes,

Before you run the append query, you need to test to determine whether a 
record for that date already exists.  Something like:

If DCOUNT("SomeField", "BankingBalanceRecord", "DateField = #" & date() & 
"#") = 0 then
    currentdb.execute qry_BankingRecordBalance
Endif

HTH
Dale

-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"wesley.allen@gmail.com" wrote:

> Hello,
> 
> I have a query that calculates a bank balance.  I want this query to
> append the current days date and the bank balance to a table.  I have
> a macro setup to autorun the query when my database opens, but can't
> get it to work correctly.  Details:
> 
> 
> Table where I want the records added:  BankingBalanceRecord
> Query that will run:  BankingRecordBalance
>                                    -This query is setup to pull
> today's date and the balance.  It is funtioning properly.
> 
> 
> The main problem I am having is that if the primary record (Today's
> date) already exists, it gives me an error message.  As I open the
> database multiple times a day, i only want it to record the balance
> the first time.
> 
> 
> Thanks for the help.
> 
> 
0
Utf
11/6/2007 4:03:01 PM
I tried your suggestion.  I attempted to add it into the query, in a
private sub function, and a module.  Nothing seems to work.  When I
enter the text, it turns red and says there is a compile error.  I am
not very experienced with SQL, so I may be missing some code or not
entering it in the correct place.

Thanks

0
wesley
11/7/2007 2:01:07 PM
Did you enter the following as ONE LINE.  The newsgroup reader may have 
wrapped it to TWO Lines.

If DCOUNT("SomeField", "BankingBalanceRecord", "DateField = #" & date() &
"#") = 0 then

    currentdb.execute qry_BankingRecordBalance

Also there was a small error in the code, EndIf should be End If

Or try it this way with a line continuation so wrapping should not be a 
problem

If DCOUNT("SomeField", "BankingBalanceRecord",  _
   "DateField = #" & date() & "#") = 0 then

    Currentdb.Execute qry_BankingRecordBalance
End If

That does not go in the query, but should be in a sub or function.  Probably 
the sub for the Open Event of a form.

Another method might be to modify your query, but to suggest an alternative 
for that would require that you post the SQL of the query.
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<wesley.allen@gmail.com> wrote in message 
news:1194444067.491047.203900@o3g2000hsb.googlegroups.com...
>I tried your suggestion.  I attempted to add it into the query, in a
> private sub function, and a module.  Nothing seems to work.  When I
> enter the text, it turns red and says there is a compile error.  I am
> not very experienced with SQL, so I may be missing some code or not
> entering it in the correct place.
>
> Thanks
> 


0
John
11/7/2007 2:56:45 PM
INSERT INTO BankingBalanceRecord ( Balance, [Date] )
SELECT SumBalance.[Working Balance], Date() AS [Date]
FROM SumBalance;

This is a copy of the code.  I am still having trouble.  Sorry.

0
wesley
11/7/2007 8:03:35 PM
You might try the following UNTESTED SQL

INSERT INTO BankingBalanceRecord (Balance, [Date])
 SELECT SumBalance.[Working Balance], Date() AS [Date]
 FROM SumBalance
WHERE Not Exists
(SELECT *
FROM BankingBalanceRecord
WHERE BankingBalanceRecord.Date = Date())

The SQL should run but should not add a record if one already exists for the 
date
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<wesley.allen@gmail.com> wrote in message 
news:1194465815.300884.186290@z9g2000hsf.googlegroups.com...
> INSERT INTO BankingBalanceRecord ( Balance, [Date] )
> SELECT SumBalance.[Working Balance], Date() AS [Date]
> FROM SumBalance;
>
> This is a copy of the code.  I am still having trouble.  Sorry.
> 


0
John
11/8/2007 12:34:39 PM
Like a charm.  Thank you so much.

0
wesley
11/8/2007 2:56:01 PM
Reply:

Similar Artilces:

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

Upgrading database from 2.00 to 2.01 problem SOLVED
Recently, when trying to upgrade the RMS database from 2.00 to 2.01, I ran into a problem that I was surprised I found no posts or mention about anywhere I searched. Because of that, I wanted to post to the newsgroup in case anyone else ever has this problem: Despite Z-ing out and backing up the database, I was unable to upgrade the database from 2.00 to 2.01 in Administrator. I do not use integrated card processing (EDC), but use a standalone terminal for card transactions. Regardless, somehow, when first using RMS in 2005, presumably with the sample database, I managed to r...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

Excel - 2000 or 97 conversion
It did work, but the list that appears when you click, doesnt contai data validated cells -- ajw15 ----------------------------------------------------------------------- ajw150's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=802 View this thread: http://www.excelforum.com/showthread.php?threadid=26202 Are you writing about the button or the combobox from the control toolbox toolbar? If it's the button, I don't understand. If it's the combobox, go into design mode (an icon on that control toolbox toolbar). Right click on the combobox, select pro...

instr for select Query
Hi. I've rersearched this forum extensivley and get the idea on how to do it but don't have enough experience to make it all the way. In a MS Access select query, I have a field that returns names in the following format: ;#Lastname1, Firstname1;#Lastname2, Firstname2;# I can't seem to sting the instr, len etc, functions together properly to clean up the string to: Lastname1, Firstname1; Lastname2, Firstname2 I've seen the Microsoft examples as well, I think I have to parse through it to break it up then concatenate back together the cleaned names, but the closest I can g...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

Scrolling Problem in Excel
I've just started having a problem in Excel with the latest XP and/or Office (2003) update. When I use the scroll wheel on my mouse, the sheet now scrolls horizontally instead of vertically. Very annoying. Anybody know what to do? hi, not sure about xp. but you might find some info here www.lebans.com he's the only one i know that has done anything with mouse wheels. >-----Original Message----- >I've just started having a problem in Excel with the >latest XP and/or Office (2003) update. When I use the >scroll wheel on my mouse, the sheet now scrolls >h...

Append and Append To priviliges
Anybody know what these two priviliges do for a record?? Jay Append allows a user to be able to add items to an object for example you can append an address to an account. Append to allows a user to append this object to something else. You would need append to priv on the address object. They work together. "Jay Mehta" <jay.mehta@conexio.com> wrote in message news:b6caea5f.0310311043.682d756c@posting.google.com... > Anybody know what these two priviliges do for a record?? > > Jay So, in the example you gave of appending an Address object to an Account object,...

call function in query
dear friends i want an query function to have serial numbers between two pre said numbers like between 100 and 105 = 101,102,103,104,105.thus i created an function in standard access module . public function _ foils(firstfoil as int ,lastfoil as int) as integer for foils = firstfoil TO lastfoil step 1 next foils end function IN query window it appeared as foils( <firstfoil>,<lastfoil>) while accessing and running on QBE window and while runnig query error accoring as "undefined function "foils" in qbe how to build query f...

Querying
I have been playing with MS Query in Excel2000, and have noticed that when I type data that contains both numbers and letters, query doesn't recognise it and does not display it. I have tried to format the cell so that it is recognised as text but still it does not recognise it. It is also interesting to note that MS Query puts a decimal point and a 0 behind the data that are numbers. Please tell me what I have to do to make MSQuery recognise data that contains both numbers and letters ...

Problem with signature formatting
Hello, I'm running Outlook 2003 on my machine and am having an extremely irritating problem with the signature formatting. I compose my messages in text and my signature is text - no html. In the signature setup and in the compose window of my messages my signature looks fine: My Name Position, Unit Region Company Phone: 333.333.3333 Fax: 333.333.3333 Web: http://web.address.com However, when I print or print preview, it looks like this: My Name Position, Unit Region Company Phone: 333.333.3333 Fax: 333.333.3333 Web: http://web.address.com So the second, third, and fourth lines get ...

append query with dtae and username
I am using an append query to add a new line to my table - one of the field in this table is "Update_Date" and one is "Updated_By". How via this query I can populated those two fields with the current date&Time and current username of the user . Many thanks, Dan Please ignore- wrong Group, sorry "Dan" wrote: > I am using an append query to add a new line to my table - one of the field > in this table is "Update_Date" and one is "Updated_By". > How via this query I can populated those two fields with the current ...

Status of Opportunity record wont change
Hi, I am trying to update the Status of an opportunity record using sdk 4.0.The application doesnt throw any exceptions even if "statecode" or "statecodename" is used as attributename.But the Status state wont change.It remains "Open". Please provide information on how to change the Status state through sdk 4.0. Regards, Pradyumna.N.V. Check out the following link for setting the Opportunity StateCode using crmService: http://msdn.microsoft.com/en-us/library/bb959545.aspx You may also want to look at the SetStateOpportunityClose if you need to create an Opp...

Unlock new records?
Hi. I have a form that I am trying to protect from accidental changes. I used the code from Allen Browne's tip sheet "Locking bound controls" at http://allenbrowne.com/ser-56.html. It works perfectly. However, I would prefer each individual record to always stay locked unless a user clicks on the cmdLock button. I would also like for the record to lock back automatically when a user exits the record. I accomplished this by putting the following line in the OnCurrent property instead of the OnLoad property: =LockBoundControls ([Form],True) This works great, too. Now to the prob...

Problem preserving Excel Correcitons
A Heritage Society secretary gave me her mailing list for the recent newsletter. It is an Excel document, and it was a disaster. A couple dozen listings didn't print. I found about a half dozen that had serious mistakes. She later identified them as entries that she had corrected some time ago. Does Windows and/or Excel have an automatic setback feature that would explain this? Or what else would explain it? Thanks -GECKO Not Excel or Windows, but who knows what she actually put in the entries in the Workbook, or even what she used to edit it with. You will likely need to examine ea...

Group by Range in Crosstab Query
I have created a crosstab query which displays the number of days from referral to consult. I want to groups the days into ranges (0-14 days); (15-21 days);(>21 days), etc. and show total unit numbers based on that range. The sql statement currently reads as follows: TRANSFORM Count(qryWaitTimesMOReferralToConsult.Unit) AS CountOfUnit SELECT qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)], Count(qryWaitTimesMOReferralToConsult.Unit) AS [Total Of Unit] FROM qryWaitTimesMOReferralToConsult GROUP BY qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)] PIVOT Format([Re...

Backing up with Exmerge Problem
I need to be able to backup Exchange mailboxes using Exmerge. When I run the Exmerge program I get the message. The application has failed to start because EXCHMEM.dll was not found. Re-installing the application may fix this problem. I was trying to run the Exmerge from the Windows 2003 Server that the Exchange 2003 server is running on. Can anyone tell me where I'm going wrong? Many thanks in advance. What is wrong with using NTBACKUP for backup purposes? Barry Johns wrote: > I need to be able to backup Exchange mailboxes using Exmerge. When I run the > Exmerge program I get...

problems printing on offset press with MP
I have Microsoft Publisher 2002. I used it to prepare a 16 page newsletter. The newsletter has both text and pictures (originally from a digital camera). I printed one copy using a laser color printer and text and pictures looked very good. Then I sent the newsletter out for printing 500 copies. The printer used an offset press. The pictures in the newsletter printed using the offset press did not look good. The printer said there were three reasons that the pictures did not look good: (1) Microsoft Publisher should not be used for publications printed on an offset press. Pagemaker wou...

dynamic query?
I have database in which 1 field called sector has multiple values (e.g. A, B, C, D) and a field call industry which is a subfield to sector. So sector A can have mulitiple industries as can sector B and sector C etc. I have a table defined where the data looks like: Sector Industry A industry_1 A industry_2 A industry_3 B industry_1 B industry_2 B industry_3 C industry_1 C industry_2 C industry_3 On a data entry form, when sector A is chosen in th...

adding a toolbar to a specific query
I am wondering if there is a way to add a customized toolbar to a specific query. I have a query, accessed through a switchboard, that I want users to be able to export to excel (I created a macro). When I try to do it, it makes the toolbar available all the time, not just for this one query. Am I trying to do the impossible? Linda Linda wrote: > I am wondering if there is a way to add a customized toolbar to a > specific query. I have a query, accessed through a switchboard, that > I want users to be able to export to excel (I created a macro). When > I try to do it, it ...

problem with AfxMessageBox in a thread in a dll
Hi, I have a problem with AfxMessageBox, I have a dll and in the dll I have a thread. when I give a message using AfxMessageBox in that thread, there is an "Debug assertion failed" , any idea about it ? thanks, Behzad >I have a problem with AfxMessageBox, >I have a dll and in the dll I have a thread. when I give a message using >AfxMessageBox in that thread, there is an "Debug assertion failed" , >any idea about it ? And if you debug it in the debugger, what does the line of source code that's throwing the assertion tell you? You really should av...

Open all Excel spreadsheets/fles before running queries
I have a number of spreadsheets which use Ms Query to retrieve data from an Oracle database. I have set it up so that queries run automatically when I open a spreadsheet. In Excel 2003, if I open several such spreadsheets at the same time, Excel will open all spreadsheets first and then run the queries. This means that I can open all the spreadsheets I want to update, get immediate notification if any one of them is in use by someone else, open as read-only if so, and then go off and do something else while all queries update (15-20 minutes in some cases). In Excel 2007 wh...

OE6 Problems
A friend is having problems with OE6, he started having "Popup","Spyware" and "Virus" problems. I loaded Spyblaster, Adaware & 12Ghost, that fixed that problem, but he said he can't get his e-mail now. I know several people running the same configuration without any problems. I have disabled everything I loaded and even disabled Mcafee Security and still no luck. He gets a "Connection to the server has failed" error and it's very very quick. I have never seen that error that quick. He has talked with the cable company and they said ...

Query in date range
Hello. Thanks for helping. I would like to create a query that will sum up expenses for various categories within a date range. I have created the query that sums the amounts, but I want it to use a high end of "Date()" or "Today()" and a low end date range that is the first day of the current month. Any idea if this can be done? Thanks, On Oct 31, 6:05 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Use This -- > Between Date()-Day(Date())+1 AND Date() > > Remember that Date() is as of midnight and will not return a record as...

Spot Color, Word Art, Autoshapes
I designed a project using PUBLISHER 2002. It came out great. However, when I save for pre-press SPOT COLOR (PANTONE), all the WORD ART graphics, and AUTOSHAPES turn GREY. The graphics that turn grey were all FILLED with a cloud graphic that was included with the program. I recolored the graphic using the FORMAT PICTURE>RECOLOR option to the PANTONE as I was advised by my printer. I also selected COLOR PRINTING>SPOT COLOR prior to saving. I also tried it by setting COLOR PRINTING>SPOT COLOR and selecting the PANTONE prior to designing the document. I am about to go to ...