Modules & VBA :: Report In Wrong Order
Feb 26, 2015
I have a cool little form for the use of the organization's treasurer wherein she enters checks and deposits. I have a query that generates a running sum, so each line in the accounting report based on the table has the account total as of that date. The query behind the report is exactly what I want and is in the correct order. For example, the four transactions for 9/15/2014 are in the order entered, and the line total is correct. The report insists on listing the 4 in the example in some other order with or without OrderBy specified. With code from someone, perhaps on this forum, I added the following to the OnPage event:
Code:
Private Sub Report_Page()
Const TWIPSPERINCH = 1440
' Offset from right edge of Control where our
' Vertical Line will start - adjust as you desire.
' Expressed in Twips
Dim intLineMargin As Integer
[code]....
The result is a beautiful report (albeit in the wrong order) with vertical lines all the way to the bottom of the page.If I remove the OnPage code, the report is still in the wrong order, so obviously that wasn't the problem. OK, I think it is corrupted. I open a new db, import all into it. Still in wrong order. So next, I begin to recreate the report from scratch. Hooray! Right order! But I still want the vertical lines between the columns. So now, I copy the OnPage code and paste it into the new report's OnPage code. I get error message: "Compile error: User-defined type not defined" with the line "Dim MIPSstr As udtPrtMips_str" highlighted. Ok, I delete all of it and reenter it line for line manually, leaving out the comments. Get the same error message. After trying to research it, the only suggestions were to add Activex reference which can't be the problem, because the other version of the report doesn't throw an error and both are in the same db.
I have attached a clip of the report and a clip of the underlying query. As you can see in the first capture.png, the check numbers are in the wrong order and the amounts in the Total line don't sync with the total above adjusted by the amount on that line.
View Replies
ADVERTISEMENT
Jan 11, 2008
Hi everyone!
I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:
Week: Format([Date];"yyyy ww")
The problem is that when I go to datasheet view, it is sorted wrong.
It looks like this:
2007 1
2007 10
2007 11
2007 12
.
.
.
2007 19
2007 2
2007 21
But I want it like this;
2007 1
2007 2
2007 3
2007 4
.
.
.
2007 9
2007 10
2007 11
and so on...
The other column has the rihght values for each week, but I need it in the right order...
What am I doing wrong? Or how can I fix it?
Thanks in advance
Jake
View 8 Replies
View Related
Aug 19, 2005
Hi,
i was working with a database and accidently deleted the first row, i couldnt get the automatic number to number another row 1, so i took out this table and created another one. however even though my new record is in order, when i pull this through to the website it's coming in the order 1,3,4,5,6,2... and the coding for the page worked fine with the db beforehand!!! so now i'm a bit lost
if anyone has any ideas, they'd be appreciated!!!
Thanks in advance!!!
Megan
View 14 Replies
View Related
Sep 13, 2006
Hi there.
I have a report which has multiple pages with multiple running totals. It is an invoice style report producing an invoice run for all customers at once. The invoice detail lines are a subreport for each customer. Every time a customer changes a new invoice page is produced (grouped by customer). If there are no invoice detail lines for any one customer a total is still displayed using the IIF command to avoid #Error totals. A running GRAND total of ALL the invoice totals is displayed as a report footer. The problem I have is when I generate the preview report and flick to the last page to see the grand total it is there no problems. However, when I print it I get a different amount!!! If I then start leafing back through the invoices on-screen - say 20 - 30 customers - then go to the end again the grand total changes again! I know the grand total shown initially, when going straight to the end is correct as I have checked it with queries. The report is 630 pages long. Any ideas as this is driving me nuts! (I assume it has something to do with using the IIF statement or perhaps the subreports?)
View 1 Replies
View Related
Aug 3, 2015
Query 1: has all the data (description, account, amount).
Query 2: sums Amount by Account No.
Query 3: combines Query 1 and 2.
Query 3 also has a join by the Account No. There is a report based on query 3.
Issue: I can run query 3 to equal 0 and generate the report correctly but when I generate the query to not equal 0 the report brings in all the data.
View 4 Replies
View Related
Sep 30, 2013
I have a query[view] where I have six employee numbers, I left join these to an Employee table to retreive the names.
I've created a report that uses this query/view as its source. For some reason my report keeps making all six name fields refer to the same control source Employee.Name and not Employee_1.Name, Employee_2.Name, Employee_3.Name,.... respectively.
In design view everything looks fine but when I switch to report view the switch happens, it displays the same name in all fields. When I switch back to design view all of the fields have changed to the same control source.
why this is happening? My tables are in a SQL server.
View 10 Replies
View Related
Oct 24, 2014
I have 2 controls on a form cmbFed and cmbState both are formatted as fixed with decimal places as 2. They display the correct values. cmbFed = .18 cmbState = .06 When I add a new control to add the 2. =[cmbFed]+[cmbState] result is .18.06 instead of .24 It looks like it is treating them as text. I even tried to do it in code Me.text639 = Me.cmbFed + Me.cmbState with the same results. It must be something simple I am missing a format for text639?
View 5 Replies
View Related
Jul 21, 2014
i am using the following function in a query:
Code:
Public Function Progress(ByVal varCompany As Variant _
, ByVal varPercent As Variant) As Variant
Static varCompanyOld As Variant
Static varValueOld As Variant
Dim Value As Variant
If varCompany & vbNullChar = varCompanyOld Then
Value = (1 + varPercent) * varValueOld
varValueOld = Value
[code]....
This function is working if I use it with two or more companies.If I have only one company, it works at the first (query) run, too.But with the same company, a second (query) run calculates different results.
I think it is because of this condition:
Code:
If varCompany & vbNullChar = varCompanyOld Then ...
If the company name is e.g. Microsoft
At the first run of the query:
Microsoft & vbNullChar = "" -> wrong
The Else calculation will be carried out.
-> That is right for the first datapoint of a company
At a second run of the query:
varCompanyOld is saved as Microsoft & vbNullChar, so:
Microsoft & vbNullChar = Microsoft & vbNullChar -> True
The main calculation will be carried out.
-> That is wrong for the first datapoint of a company
View 14 Replies
View Related
Jul 1, 2013
Access 2010
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo
Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents
Field: RankOrder
Here is what I have:
Code:
Private Sub RankOrder_BeforeUpdate(Cancel As Integer)
Dim lngRankDup As Long
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0))
If lngRankDup <> 0 Then
MsgBox TestEventID & " already exists in the database"
End If
End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I have tried:
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Me.RankOrder, 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & [RankOrder], 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!sfTestInfo!Form!RankOrder, 0))
But nothing seems to work.
View 3 Replies
View Related
Apr 30, 2015
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;
View 5 Replies
View Related
Feb 23, 2015
I have a field that is pre filled in with a value. The name is "Business".Then there is a combo box with various values. If the user would select a value that does not match with values that can be selected if in "business" a value is selected, it should show a message that the selection is wrong and user needs to select the correct one.
Example:If in the field "Business" the value "Food" is selected, then the possible values to be selected in the combobox would be: Fruit or Meet or Fast Food. If the value in "Business" would be "Wood" only selections could be made: Talble or Chair or Cabinet.So, if a user select "Food' in "Business" and in the combo box "Chair", it should show a message that the wrong selection was made and don't let them use the wrong one.
View 11 Replies
View Related
Nov 5, 2013
I my form i have a textbox into which the user can enter a value that will serve as criteria in a query.
In the beforeupdate of this textbox there is a check about the validity of the input. If this input is wrong, a message tells the user what's wrong .
In such a case i like to cancel the event but in the same time clearing the textbox.
Canceling isn't a problem, but automatically clearing the wrong input seems not to be so evident.
How can i do that ?
View 5 Replies
View Related
Oct 31, 2004
How do I let users choose the way they want to sort their reports through a form dynamically?
View 1 Replies
View Related
Feb 9, 2005
I have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
View 2 Replies
View Related
Nov 7, 2013
I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.
Code:
Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)
[code]....
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.
Code:
Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String
[code]....
View 7 Replies
View Related
Nov 16, 2005
Need help pls
I'm designing a report that should be sorted by field A, B, C, D, or a combination of BD or CD.
Problem is (it seems) the report ignores the sort order and lists the results in ascending order of the autonumber field - 1,2,3,4,5 etc.
The reports run off queries and the queries work fine - they sort the fields in the proper order.
Is the autonumbering somehow interfering with the sort order in the report?
HELP!!!
View 1 Replies
View Related
May 17, 2013
I have a report that generates the position of certain items. When I produce this report it doesn't put the data in order. All associated data with that position is correct, it is just not in numerical order. The attached screenshot shows the issue I am having. In the position column it should read 1,2,3,4 but in some cases the positions are not in order.
View 2 Replies
View Related
Sep 5, 2014
I have report which record source is a Union Query, The query contain Order Details and a single row for "Freight Charges", I just want to set Freight Charges show in Report at last row.
View 3 Replies
View Related
Nov 7, 2012
I have a Report that uses a query as it's record source. I have the query ordered by a field, which works as expected in the table view of the query. In the report, however, the ORDER BY clause does not seem to carry through. The field is not sorted Ascending. What's going on?
View 5 Replies
View Related
Sep 30, 2004
I have a problem getting pages of an Access 2002 report to print in the correct order.
I have a report which prints one of four pages per record. The data source for the report was initially sql which retrieved records from 2 tables. The sql was working fine. The order of the records was set by the ORDER BY clause. ORDER BY ON is set to Yes. ORDER BY is set to a text field called SortKey. Because the report was not printing the pages in the correct order, someone suggested that if I create a table (I called it SortReports), make sure that the table is sorted by SortKey, and make the table the data source, it should work. But it does not.
The pages in the report still do not follow this sort order. I have also made SortKey the primary key field. No matter what I do, the report continues to sort by another field called SystemKey, as well as something else. I have been looking at Microsoft and other websites for a similar problem and solution, but no luck yet. Any ideas?
I would greatly appreciate any help, or a push in the right direction.
Thanks.
View 1 Replies
View Related
Aug 22, 2013
I have a main report / subreport relation, just want to have a fixed length of each printout. e.g the total number of lines of the subreport should be 8 lines. However, lots of subreports contain records less than 8. I would like to know how to insert of blank line in the subreport depending on the records with content at each print, in order to make the total number of lines in each subreport is 8 each print!!
View 1 Replies
View Related
Feb 26, 2015
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
View 14 Replies
View Related
Jun 25, 2015
I need to be able to assign a title to a set of dependents that is not what they are normally called. As in, every dependent is either called "Dependent" or "Step Child", but I need to call them "Child1" "Child2" "Child3" etc, for however many children an employee has.
I have a function that does this, however, I need the oldest child to be Child1 and so on, but every time I add an ORDER BY clause to my sql, it duplicates the records. I confirmed this by adding the child's name to the end, and instead of this:
Child1 Martha (eldest)
Child2 Edward
I got:
Child1 Martha
Child2 Martha
Child3 Edward
Child4 Edward
[Code].....
View 1 Replies
View Related
Jul 12, 2014
How do I get VBA to say: Go to the first control in the tab index after the current active control (that is visible atm),
except such and such controls (because they will lose visibility in a moment).
View 14 Replies
View Related
Aug 30, 2013
I am a VBA beginner and am using Allen Browne's method for filtering a field in a subform: URL....It works well except that I cannot seem to figure out how to order the results when the subform field is filtered. I want to order the results by CompanyName.Here is the code I am using for changing the record source from the main form to the subform:
Code:
Dim sSQL As String
If Me.cboFilterFavorites = Null Or Me.cboFilterFavorites = "In Business" Or Me.cboFilterFavorites = "Out of Business" Or Me.cboFilterFavorites = "Unsure" Then
If Me.RecordSource <> "qryCompanies" Then
Me.RecordSource = "qryCompanies"
End If
[code]...
I feel as if I have tried everything. I even changed the subform record source from a table to a query (qryCompanyTypes) so that I could add the CompanyName field and sort but to no avail. I have tried adding the CompanyName field to the SQL string but keep getting errors. I have tried simply using Order By in the SQL and still get errors.
View 2 Replies
View Related
Jan 12, 2014
How to define Order that starts with a dependency on the existing value.
Basically, a function that chooses what Status Validation to run then knows when to stop that displays some very simple predictive Inference.
Each Record has a Status. The Status will generally be correct 85% of the time. Once in a while, various things in the database are updated that triggers the business need to change the status.
-A collection of Rule based validation functions matches each Status.
-The objective is not to run Rule 1 through 40 sequentially for each record.
-Each Rule Validation function is lengthy and can take 0.1 seconds per rule (per record).
-If the current record's rule is still valid 85% of the time, Then run that Status Rule Validation function first.
-If the Validation returns True, there is no need to run the rest of the rules. Move to the next record.
The challenge: Statistically, if the Current Status failes its Rule Validation, there is a high probability that one of the Status Validation Function near to the current Status will validate to true.
Like the board game Clue, once a Validation Rule comes out False, there is no need to check it for that single record again.
In an extremely simplistic world, think about Status of : Order in Progress, Order Completed, Completed Order Paid, Shipping in Progress, Item Shipped Ready, Shipped, In Transit, Delivered, Rejected, Payment Recended, ...
So, if the current status was In Transit, there is a higher odd that Delivered would be the next. There would be no need to start from the beginning again. The wonderful world of Metadata.
View 2 Replies
View Related