Record Count After Filtering Is Incorrect?

May 29, 2012

The filter output from a recordset in the following code is 1. But this is incorrect: How can this be corrected?

Private Sub Command43_Click()
Dim curDatabase As Database
Set curDatabase = CurrentDb
Dim rs3 As Recordset
Dim t As Recordset
Set rs3 = curDatabase.OpenRecordset("Select * from [Courses under Programs]")
rs3.Filter = "ProgramCode = 'ANS.CT'"
Set t = rs3.OpenRecordset
t.MoveLast
t.MoveFirst
MsgBox t.OpenRecordset.RecordCount
End Sub

View Replies


ADVERTISEMENT

Incorrect Record In Table

Nov 22, 2006

Can somebody explain to me, how can happen mistake inside of table like a picture?
1. I don't understand, how can be change automatic number - see the picture
2. Why is there some japanese charakter.

If somebody knows, please tell me.
hurka.deltec@wo.cz

View 1 Replies View Related

Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count

Aug 16, 2015

I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.

BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797

The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.

Right now this is what i get

BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014

This is the output i would like

BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014

Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?

current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));

View 9 Replies View Related

Filtering For Current Record

Dec 9, 2005

Hi,

I have two combo boxes where the first lists values in one table. On selecting one I want a second combo populated with records in another table where foreign key = primary key.

Obviously the query is

select location from client,location
where client.client = location.client

However I can not get to the current record.

Alternatively, I could populate a list programmatically on the afterupdate event but I am not sure how to do this or if this is the best way. Any advice would be most appreciated.

Thanks John.

View 1 Replies View Related

Modules & VBA :: Goto Record Without Filtering Results

Dec 5, 2013

Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).

But NOT filter the results so that user can still navigate as usual after the search has been carried out.

View 1 Replies View Related

Reports :: Filtering Report To Specific Record

Jun 9, 2014

I work in psychological testing and I have created a database to store some data for our patients' test scores. I have a main form with demographics and then 5 buttons on the main form that open into 5 other forms (one form for each test). I also have created a button on the main form to run a Report; however, I would like to filter the report to only show the current record (e.g., Patient ID #1 only).

(Can this be done?) How should I go about filtering the report to show only the open record? Expression is preferred - I'm not very good with code.

I'm also assuming that since each form is linked by the primary key of Patient ID, I should be able to see all data from each form (main + 5 others) in the report for that specified Patient ID... (?)

View 4 Replies View Related

Forms :: Filtering Listbox - Cannot Go To Specific Record

Apr 28, 2014

I am using the Filtering a listbox method from this post [URL] .....

It works great apart from when i type too many characters and no search results can be found i get a run time error '2105' you cant go to the specific record.

I think it may be because my form has a row source. When the example uses an unbound from?

View 1 Replies View Related

Modules & VBA :: Data Filtering By Form / Subform Using Same Record Set

Feb 26, 2015

I have a form and subform where i have table data in the subform and 03 Combo box in the main form header. I need to do some filtering using combox box1 and out of that filtered records i need to do one more filtering using combobox2. then again another filtering by Combobox3. ( same way we are doing in Excel)

Subform is running on a query where i have following fields;

Vessel
Voyage,
POL
POD
MLO

by Combo box 1 i need to filter Voyage
by Combo box 2 i need to filter POL ( out of the data filtered by above )
by combo box 3 i need to filter POD ( out of the data filtered by both above )

View 3 Replies View Related

Forms :: List Filtering Based On Current Record

Dec 2, 2014

I currently have a form that only shows data based on a specific record id. I have placed a list into that form though that has multiple record ID's listed ( It's a log). How do I get a List to Filter to just the Record ID that corresponds to the current form Record.

What I have are as follows:

A form that has client information : Address, phone numbers, Etc.

On that form I have Pages. one of the pages contains a List.

That List however contains information from multiple clients based on Point of Contact ( Call Log)

What I want is a way to narrow the List to only show those that corresponds to the Current Client Record.

Is this possible? If my approach is wrong, which is a better way?

View 1 Replies View Related

Filtering The Last Record Of Many From Many People Using Totals In Query Design View

Jul 1, 2005

I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.

I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like

CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject

I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross
:confused: :confused: :confused:

View 6 Replies View Related

Forms :: Record Count Last Visible Record Only?

Dec 16, 2014

I can use the following to return the last record count of the subform records, but I want the last record count of the VISIBLE records only.

So if records 1-15 are visible then it returns 15

If records 34-49 are visible then it returns 49

Code:
RecordsetClone.recordcount

View 14 Replies View Related

Record Count?

Nov 1, 2005

Hey all, I was wondering if anyone might know how to obtain a reocrd count, I need to create a PERMIT number based on the number of records in my table. So I need the number of records in my table plus 1, which will then give me half of my permit number, I will then add the 2 last digits of the current year and add a "-" I think I have the date done, but I dont know how to obtain the number of records in my table? Can anyone help? Thanks

View 14 Replies View Related

Incorrect Information

Sep 9, 2005

I have set up 2 queries which are working correctly. The problem is when I try to combine them it brings back incorrect information. The 2 queries that work correctly are set up like this

Query1:
SELECT Projects.[Work Stream], Count(Poles.[New Pole No]) AS [CountOfNew Pole No], Sum(Projects.[Line Length]) AS [SumOfLine Length], Projects.Team
FROM Projects INNER JOIN Poles ON Projects.[Scheme No] = Poles.[Scheme No]
GROUP BY Projects.[Work Stream], Projects.Team
HAVING (((Projects.Team)=[EnterTeam]));

Query2:
SELECT Projects.[Work Stream], Sum([Material Cost]+[Labour Cost]) AS [Total Cost]
FROM Rates INNER JOIN (Projects INNER JOIN [Pole Work Instructions] ON Projects.[Scheme No] = [Pole Work Instructions].[Scheme No]) ON Rates.[Rate No] = [Pole Work Instructions].[Rate No]
GROUP BY Projects.[Work Stream];

Do you have any idea how I can combine these to get accurate results?

View 3 Replies View Related

Why Is This Query Incorrect?

Nov 1, 2005

SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format (([SAP Co],"0000") AS NewField), (([cst ctr], "0000000000") as costcenterappended) INTO [Interim Table]
FROM Asset_Map;

View 1 Replies View Related

Incorrect Syntax

Jun 30, 2005

I am getting the following error:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/province.asp, line 24

And this is my code please help:

<%
province=Request.Querystring("province")

Dim intRecordsPerPage
Dim intRecordLoopCounter

SET Conn = SERVER.CREATEOBJECT("ADODB.Connection")
Conn.OPEN "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/db/dealers.mdb")
SET RS = SERVER.CREATEOBJECT("ADODB.Recordset")

strSQL = "SELECT * FROM tblDealers WHERE "
strSQL = strSQL & "ProvState = '" & province & "'"
Response.Write strSQL
strSQL = strSQL
RS.OPEN strSQL, Conn, 3, 3

RS.PageSize = intRecordsPerPage

If RS.EOF Then
Response.Write "<CENTER>There are no dealers in this state"
Response.Write "<br>Please check back later<P></CENTER>"
Response.End


Else
Response.WRITE "<CENTER><B>List of Dealers</B><BR>"
Response.WRITE "<BR><HR><BR>"
Response.WRITE "<table valign=top border=1 cellspacing=0>"
Response.WRITE "<TR>"
Response.WRITE "<center><TABLE valign=top border=1 cellspacing=0 BGCOLOR=#DFDCEE>"
Response.WRITE "<TR>"
Response.WRITE "<TH>Company Name</TH><TH>Street Address</TH>"
Response.WRITE "<TH>City</TH><TH>Province</TH><TH>Postal Code</TH>"
Response.WRITE "<TH>Phone Number</TH>"
Response.WRITE "</TR>"
For intRecordLoopCounter = 1 to intRecordsPerPage

If RS.EOF Then Exit For
Response.WRITE "<TR>"
Response.WRITE "<TD WIDTH=200><font size=2>"
Response.WRITE RS("CompanyName")
Response.WRITE "</FONT></TD><TD WIDTH=200><font size=2>"
Response.WRITE RS("StreetAddress")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("CityName")
Response.WRITE "</FONT></TD>"
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("ProvState")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("PostalCode")
Response.WRITE "</FONT></TD><TD WIDTH=150><font size=2>"
Response.WRITE RS("PhoneNumber")
Response.WRITE "</FONT></TD></TR>"
RS.MOVENEXT
Next
End If
Response.WRITE "</TABLE></center>"


SET RS = NOTHING
Conn.CLOSE
SET Conn = NOTHING
%>

View 1 Replies View Related

Record Count (simple)

Apr 21, 2006

:mad: I have created a database to hold information on non conformances we have with our suppliers, I am really stuck, what I am looking for is to put a field into a table which counts issues with the different suppliers, if anyone can help it would be much appreciated! Thanks

View 1 Replies View Related

Count Fields Within A Record

Jun 5, 2006

I'm building a health data system that stores data from health tests as follows:
[test01], [test02], [test03],.........[test25], etc etc.
Each of these tests comprised of a "Pass"/"Fail" value.

Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].

So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?

ie count([test01] [test02] [test03] [etc] [etc], ="Pass")

As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.

Many thanks in advance for any tips.

View 3 Replies View Related

Empty Record Count

Jun 10, 2005

I have a query that updates 1 field in tableA which is the sum of values from 3 other related tables. However, when 1 of the 3 tables has a record count of 0 then the query does not work??? How can I update tableA excluding tables with record count <1 ?

Thanks.

View 1 Replies View Related

Count Occurances Of A Record?

Jun 14, 2007

I am working through a lot of data and this is basically what I'm trying to do.

Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."

How would I do this?

View 2 Replies View Related

Updated Record Count

May 17, 2006

Cant find what I am looking for on the forum so if anyone can help me or tell me what to search for that would be great...

I need one of my forms to display a running total of updated records, but only those records that the user clicks a command button on. i.e if i have a recordset of n records each time I move to the next one (by clicking a command button) I want the control on the form to display the count of those records that have been updated only.

I am assuming I would need to add some code to the after update event of the form or on click event of the command button but cant really figure out what code to write.

Any pointers?

View 3 Replies View Related

Record Count On Group

Oct 5, 2004

In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.

eg if the main table had 5 customers that lived in london
and 6 in Maidstone then

London 5
Maidstone 6

etc

View 1 Replies View Related

Reports :: One To Many - Count Last Record Only

Aug 15, 2013

I have two tables - one contains customer names, the other customer appointments. So one customer - many appointments.

Each appointment is booked at a set interval (every 3 weeks, 4 weeks, 5 weeks...) which can vary from one appointment to another.

I want to do a count, in a query, to show in a report.

I need to count:

Total Cus_ID by interval - so how many customers are booked every 2 weeks, every 3 weeks, etc.

I need the count to be based on the customer's LAST appointment only.

I have tried, select query (group), crosstab (!)... querying a query... Total line using Max... then Last...

Nothing I tried works. The sum of appointments by interval should equal the total number of clients in the database... It gives me 4 times that... so it is counting every appointment, not just the last appointment entered.

I also will be including two other fields: activecustomer = yes and source = Eve - need to know criteria to set.

View 14 Replies View Related

Incorrect Collating Sequence

Oct 27, 2005

I tried this question a few weeks ago, but I'm trying again.

I have 2 Access 2002 files. One has a sort order of ascii and one has a sort order of international. I need them to both have the same sort order. It doesn't matter which one. Right now I get the above error message on one of them.

How can I change the sort order so that I can import Paradox files like I used to?
:eek:

View 11 Replies View Related

Incorrect Result For An Average

Sep 21, 2006

Have searched but could not find my solution. I have a bowling league database and I am doing averages based on games bowled. On certain averages the results are incorrect. Such as

Tot pins = 1169 divided by
tot games = 6

the result should be 194.83

but the result in my query is 196

have tried the Round function, Abs function and cLng function to no avail.

Thank you

View 10 Replies View Related

Query Calculation Incorrect

Nov 23, 2007

Hi all

I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592)
Access calculates this to: 17,0144948838454
Excel as well as my own calculator gets: 17,0144948787062
All table fields is defined as double.

If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.

View 4 Replies View Related

Incorrect Results From Query

Feb 25, 2008

Thought this thing was working great.Seems this is happening:If the sum of credits exceed the sum of charges, the query doubles the sum of charges. The query-SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, nz([SumOfCharges],0)-nz([SumofCredits],0) AS RunBalance, TblCustInfo.HerbieFROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID=TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID=TblPayments.CIDGROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel, TblCustInfo.HerbieHAVING (((TblCustInfo.Cancel)="n"));When I run the query, the SumofCharges calc is the culprit since it shows in that column. I can't dup it in the SumOfCredits, but I would suspect it will also do it somewhere down the line since they are virtually the same.HELP !!!!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved