Running Access Query Returns Incorrect Number of Records

I am running some code which loops through and runs the queries in an
Access Database. The count of the records is then stored in a

For most of the queries, the figure stored in the Worksheet matches
the number of records if you run the query in Access. But for certain
queries, the count of the records does not match the number of records
when the query is run in Access.

If I change the query to a make table query and then base another
query on that table, the results are correct.

Can anybody suggest running the query from Excel would results in a
different number of records being returned than running the query in
Access ?

The code is as follows:

Function CountTheRecords(ByVal strQueryName As String, OpenConnection
As ADODB.Connection) As String

Dim OConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

On Error GoTo Error_Handler

strSQL = "SELECT Count(" & strQueryName & ".[Patient NHS No]) AS
[CountOfPatient NHS No] " & _
                "FROM " & strQueryName & ";"
Set rs = New ADODB.Recordset

With rs
    .Open strSQL, OpenConnection, adOpenStatic
    CountTheRecords = rs![CountOfPatient NHS No]

End With

Set rs = Nothing

Exit Function
End Function
tim.stone (2)
1/6/2004 11:10:48 AM
excel 39879 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 58


Similar Artilces:

report based on a crosstab query
I've created a crosstab query in which row headings are a project number [ProjNo], column headings are dates [Date] and each of the data fields has a number of hours. I built a report based on the query which worked perfectly until I wanted to change dates. The date field in the report by default uses the actual date value as a data source rather than the field [Date], so if the date is different, the report doesn't recognize the new dates. How can I work around this? Is there a way to 'reset' the dates in the report? Ivor ...

Highlight keywords in result of perameter query
I have a query perameter "*" & [Keyword Search] & "*" the returns the records based on the keyword. Works great. Is there a way the keyword to be highlighted or change color in the results? Not that I know of. Especially in a query. -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Ryan Tisserand" <> wrote in message >I have a query perameter &qu...

Access 2003 HELP!!!!!!
I have an import form that access a linked spreadsheet that is dumped into a tbl_JobTrackingForm, unfortunately when I go to do the import it freezes, gets hung up nothing happens, I end up closing it out. I've updated thet spreadsheet link on the front end (tools,databes utilities etc....) and on the back end by stepping into the VB code and updating. I've cheked the dependencies and corrected some of the macros as well as the queries, any help would be greatly appreciated. Thanks ...

IE Error using New Record Button to create Account or Contact
currently, the only place I can successfully use New Record button to create a new Account or Record is when I'm in Activities. If any Accounts or Contacts are in active view, and I click on New Record, Account, IE gives out a general error and shuts down. This is happening in multiple computers in my company. Is this a design bug? ------=_NextPart_0001_2CA37D80 Content-Type: text/plain Content-Transfer-Encoding: 7bit <> wrote: > currently, the only place I can successfully use New Record button to create > a new Account or Record is ...

Average a returned value in a report
I have a report that returns a value named Efficiency. It is based on a query that takes the total parts produced divided by the hours charged and returns the parts per hour. Then it divides it by the number of parts per hour estimated and returns an efficiency value in %. This has criteria that limit the dates in the report. The report returns the count of items and the efficiency of the different jobs. I want to total the efficiencies in the report and divide it by the count to get an average efficiency. How do I do this? I am using Access 2007. Thanks for any help. ...

Export to specific cells in Excel template from ACCESS
I am planning to export my access table to an excel template R19 to R50 and C1: C8. Is there a way to do this using VBA? Thanks -- Message posted via I am no expert but dealing with current problem very similar and this link to article written by Doug Steele may be just the answer you are looking for. July 2005: Excelling automatically. This will download zip file explain sample db that shows code for multiple ways to intera...

sql request to return port number
Hello, I am looking a for request (probably on sys views or tables) that would return the port number on which sql server is listening to (default is 1433). Thanks for your help Hi You can find this info in ERROR.LOG file "Wilfrid" <> wrote in message news:4b03f777$0$19300$ > Hello, > > I am looking a for request (probably on sys views or tables) that would > return the port number on which sql server is listening to (default is > 1433). > > Thanks for your help > Hi, ...

How does RMS record Deposits forfeited by customer?
When you cancel a layaway, and keep part of the deposit, how does RMS record the deposit amount kept for accounting purposes? It doesn't appear to show up on the Z report, and it doesn't seem to transfer to the accounting system when you post closed batches. However, the cash drawer balances. From an accounting perspective, you only have 1/2 of the transaction accounted for. Good question, I've been struggling with this too. I'd also like to be able to set the default vale of "Deposit to Keep" to $0, rather than the full deposit amount as it seems to be set. ...

Protocol: SMTP, Server Response: '250 HELP', Port: 25, Secure(SSL): Yes, Server Error: 250, Error Number: 0x800CCC7D
I cant receive or send emails in windows mail. What do I have todo? Please copy and paste the error message into the body of your post . . . not the "Subject" line - it got truncated. "valeria migelson" <> wrote in message news:eW$sk6#kKHA.4772@TK2MSFTNGP04.phx.gbl... > I cant receive or send emails in windows mail. What do I have todo? Judging by the fragment of the error message you have in the subject = line, it looks like you have the wrong settings for Gmail. Use these:

Save a date as a variable and run/convert formula against the vari
Good Day, What I'm needing to do is retrieve a date field from a closed workbook, save it as a variable and then calculate a week number based on it. which I'll use for another function. I've attached two types of code I'm struggling with in VB and hope someone can help... The first part retrieves a date from a close workbook and I need to change "MsgBox" to a varible to save the date retrieved. Sub GetVal() MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ", "K6") End Sub Function...

Merry Christmas of the microsoft.public.access team
-- Best Regards! If you have any doubt,please don't hesitate contact me! Peiyong 2017 ʥ���ڿ쵽�ˡ�������һ����ף�ɡ�����СҮ�ͻ��ڣ����������ڡ�����ĩ�� ֻ����Եġ��úõĹ�ÿһ�졣СҮ�ͻ�������ͬ�� ���� "pqin" <> д����Ϣ news:ewlT45TgKHA.5380@TK2MSFTNGP06.phx.gbl... > > > -- > > Best Regards! > If you have any doubt,please don't hesitate contact me! > Peiyong 2017 > > СҮ�ͻ�ͬ���Ĵ����ʴ������ð�ʾ���ڴ���Ҫ��ʾ�ˡ��ҵĺ����ǡ��˹��ͨ�����硣СҮ�ͻ� ���� "pqin" <> д����Ϣ ...

Numbering Lines and Paragraphs
I'm using MS 2007. I'd like to number the lines of a doc, but restart the numbering with each new paragraph. The only options I see are: Continuous Restart Each Page Restart Each Section Suppress for Current Paragraph Is there any way I can change the settings, short of making every paragraph it's own section? (I'm doing some editing for a project and we use shorthand to reference where to make changes, such as: P10 L15 [paragraph 10 line 15]) Thanks! To clarify... I also need the line numbering to start new at each paragraph so in the example (...

Can't record folders to disc
I was trying to copy folders to disc using Sonic Record Now. I tried different discs , but each time I clicked the burn button I got the message "Please insert an appendable data disc and try again" I have made copies before, but for some reason my machine (HP Pavilion f1703 with OS XP Home Edition) is not letting me copy some picture folders to disc. I am using brand new dvd discs, and I know there is enough room to make the copies. A solution to this problem would be greatly appreciated. On 1/4/2010 3:05 PM, Jonjo wrote: > I was trying to copy folders to disc ...

Multiple records in table to display single records
My table has 3 columns, A is account numbers, B is first name, C is last name. If an account has 2 owners, the account number is listed twice in C1, like this: A B C 123 Hank Jones 123 Ann Jones 231 Mike Smith 321 Jim John I want to query this table to display the names side by side if there is more than one owner, so that my data appears like this: A B C D E 123 Hal Jones Ann Jones 231 Tim Smith 321 Jim John Help please! Sorry, got the names mixed up! "Hurrikane4" wrote: > My table has 3 columns, A is account numb...

Form goes blank when no records 12-15-09
Messaggio multiparte in formato MIME. ------=_NextPart_000_009F_01CA7D8E.88CB66E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a form that goes blank when a user deletes all records. Is there a way to have vba to add a record when the form is open. This=20 would keep the form from going blank. ------=_NextPart_000_009F_01CA7D8E.88CB66E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">...

Outlook *.pst import to Outlook Web Access???
I am wondering if there is a way to import my Personal Folder.pst into Outlook Web Access application? JimC No, that's not possible, because OWA is a browser application. It knows = nothing about files on your hard drive.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers =20 "JimC2007" <> wrote in message...

Cannot open .mdb file in Access 2000 running Windows 2000 Professional
Good Afternoon All- I have a secured database that I am attempting to open with Access 2000 and I am getting a runtime error. There are three old machines in my office which run Windows 2000 Professional. I was able to get it to work on one machine by installing the updated MDAC file from a download from Microsoft. The other two machines generate the same error in that there is a runtime error with the switchboard. The MDAC file cannot be updated in Windows 2000 as it is in Windows XP. The i386 file does not contain the MDAC file and you can't right click on the file and then click i...

Select query problems Access 2003
Good morning everybody I'm using Access2003 on Windows XP SR2 I want to run a select query using parameters to query a table of placenames and details. All pretty straightforward. When I try a click event using the selection of a combo or listbox to set the criteria in the query { Forms![frmSearch]![cboName] } the query gives a nil return nothing happens....the form opens but doesn't display any results. But when is use a txtBox and manually enter known data and run a command button everything works as it should This is the code for the cmd button. Private Sub cmdS...

access to reporting services
Hi, Please can someone advise how i can give a normal domain user access to reporting services (sql 2005), he needs to be able to have full access to view, test and change report settings, he is not a domain admin or anything. He needs to access via sql management tools. Have tried various things but cant figure it out. Thanks See under security tab roles can be assign to the login Browser May view folders, reports and subscribe to reports. Content Manager May manage content in the Report Server. This includes folders, reports and resources. My Reports May...

upgrade casues "Access Denied"
After upgrading from Exchange 2000 to 2003, I notice that from the server that I cannot access the server via IE, I get a access denied. Admin has all rights, so what gives? ...

Incorrect date in line chart in Excel.
I'm having difficulty with a line chart. I've got two ranges of numbers of two types of calls (from 1972 through 2004) that goes up from 50 to 7700. My chart's Y axis has automatically decided to show from 0 to 16,000. On the chart itself, my data placement starts at about 6,000 (for both 24 “F” calls and 649 “M” calls in the year 1972) and goes up to a point above 14,000 (for a figure that should only be just above 7000). I've tried to change the scale of my Y axis, but when I am able to so (2 out of 5 times) my data then goes off the chart. So I've looked at m...

possible to check a cell for both even number and positive number.....
I have found a workable formula to check for even numbers but I am not being sucessful in having it also check for positive numbers..... I am using the following data verification statment to check for even numbers and it works fine..... =MOD(G17,2)=0 problem is that the number they are supposed to enter has to be a positive number because negative numbers are invalid data in this application. Any help would be appreciated. Hi Try this one. =IF(AND(MOD(G17,2)=0,G17>0),"True","") HTH John "M G Henry" <> wrote in message new...

Auto Numbering 09-13-05
I would like to create an auto number field to be used as a customer number. I would have thought this would be a common requirement. Is this possible. thanks in advance Todd this is a common issue and the way to achieve it is to use post callout code which finds the last number used and then calls back into the crm application layer to update the record. here is some example code for crm 1.2 "Tango" <> wrote in message news:89FA...

Maintain the same number in a spreadsheet
I use a spreadsheet to calculate my contract price for a bid. I would like to maintain the same price amount with a formula that would automatically adjust the percentage of profit up or down when a direct cost in the spreadsheet amount is changed. For example; if I have a bid price of $1000 with a 10% profit ($100)and the price of an item that is part of the direct cost goes from $50 to $60 the percentage woud autamatically adjust my profit from 10% to 9%, decreasing my profit to $90, accounting for the additional $10 of direct cost, but the bid price would remain at $1000. On Mon, 29 Mar 201...

problem with append query
Hello experts, I have a form witha subform, I need to append the records from the subform into a table. I set it up and it seems to be fine but there is a problem. Only one record from the subform is appended into the table and it is always the record that is selected. is there any way to send all the records that are showing in the subform window to the table? Thanks again in advance Isn't this the same question I just replied to in the Reports NG? Please ask your question in a single NG. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UC...