I am having a bit of a problem with one of my tables. I am using VBA code to add data to one of my tables, my problem is that even when I manually sort the table, it still gives me problem.
For example, I have a table that stores mobile numbers by year and month. I manually sort it by year- Ascending, but it still gives me this jumbled up table. I even tried writing a query to pull out the data and set the Sort by ascending and yet still it gives me this. What am I doing wrong?
I have query that uses the following forumla "Team HCP2: ([Member Frt HCP]+[Member Back HCP]+[Guest Frt HCP]+[Guest Back HCP])/2" to calculate team handicaps. The formula performs as I need it to but when I try to check the sort box to sort "Ascending" it asks for a parameter.
All I want to do is run the query and have the results of the calculation field sort the column of results in a ascending order. All non calculated fields in the query are sortable by using the sort by ascending feature in the design mode.
I have a database of about 2300 entries and new ones are continually being added. However they are not always in numberical sequence, (eg. 604, 605, 606 etc.) sometimes they will be new revisions on an old number, (eg. 345R3, 604R1 etc)
I have noticed that in Access 2000, when you attempt to sort the numbers in to a numerical sequence, they dont always do it, they will go by the first number (eg. 1, 10, 100, 101 etc) How can i get it to go into a correct sequence (eg. 1, 1R1, 2, 3, 4 etc)??????
I'm trying to sort and filter a continuous form. Sort ascending and filters works perfectly fine. But my descending button doesn't work. I basically have a combo box which contains a field list. Then two buttons (asc. and desc.), then a text box for filter, a button to filter, and another button to reset filter. Here's my code:
Code: Private Sub cmdAscending_Click() If IsNull(Me.cboField) Then MsgBox "Please choose a field.", vbOKOnly, "No field to sort." Else Me.OrderBy = Me.cboField Me.OrderByOn = True
My question is simple: I've got a database of my book collection. If i view it in a style sheet, it is something like
Author Title etc.
A 0 C 1 B 3 F 2 D 5 D 4 A 8 F 9
I would like to order the Author and Title columns (permanently). Particularly, I would like to order in alphabetical order all the authors and, if I've got the same author, order his/her book alphabetically, something like:
I got a weird problem. trying to bring back data from my database, i have a query that has worked for the last year perfectly. today, its not. Im asking it to sort via several fields, first is the state, second is the suburb. so what i should (and have) ended up with is a list of states and suburbs in alphabetical order, grouped by state. all of a sudden, W comes before G which comes before A. wtf?
the SQL is quite big as it joins several tables, but the order clause is this: "ORDER BY States.Description, public_publications.mLocation, public_publications.title, PublishingDays.OrderLevel, [ABC-Circulation].iYear DESC , [ABC-Circulation].AuditPeriod DESC;"
States.Description = Name of state public_publications.mLocation = suburb public_publications.title = title of publication PublishingDays.OrderLevel = special order level (applys only when one publication has more than one entry) year and auditperiod are the year and the period of the year that the audit takes.
even if i take out all the sorting and leave in "public_publications.mLocation" it still thinks that G comes first, then W, then B, then F etc etc. ARGGGHHGH
is there anything else that effects sorting? this is driving me CRAZY!!!!!!!!
i have compacted and repaired. I have copied the SQL, re-wrote it from scratch, removed all ordering, nothing works.
again i will say that this worked 100% right 3 months ago (last time i used the query) - and i have not changed the query (at least i dont remember doing it, and i have not had a reason to change it). hence why im going insane.
:mad:
SELECT PublicationTypes.tPublicationType, [ABC-PublicationSubTypes].tDescription, public_publications.title, IIf([Exclusions],'*' & [Issues],[Issues]) AS NumOfIssues, IIf([ABC-Circulation].[DayPublished]=20,[SpecialPublishingDay],[PublishingDays].[DayPublished]) AS Days_Published, [ABC-Circulation].CoverPrice, IIf(IsNull([ABC-Circulation].[CoverPriceDay]),[PublishingDays].[DayPublished],IIf([ABC-Circulation].[CoverPriceDay]=20,[SpecialPublishingDay],PublishingDays_1.DayPublished)) AS CoverPriceDay, [ABC-Circulation].CoverPrice2, IIf([ABC-Circulation].[CoverPrice2Day]=20,[SpecialPublishingDay],PublishingDays_2.DayPublished) AS CoverPrice2Day, [ABC-Circulation].CoverPrice3, IIf([ABC-Circulation].[CoverPrice3Day]=20,[SpecialPublishingDay],PublishingDays_3.DayPublished) AS CoverPrice3Day, AuditTypesMaster.AuditType, Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear] AS Period, IIf([InExcess],[Australia] & "+",[Australia]) AS ANPS, [ABC-Circulation].inclAccomAirlineSales, [ABC-Circulation].inclEducationalSales, [ABC-Circulation].inclEventSales, [ABC-Circulation].inclMultiplePublicationSales, IIf([NZInExcess],[NewZealand] & "+",[NewZealand]) AS NewZealandFigures, IIf([OCInExcess],[OtherCountries] & "+",[OtherCountries]) AS OCFigures, IIf([exclInExcess],[exclAustraliaOther] & "+",[exclAustraliaOther]) AS exclAUSFigures, IIf([Exclusions],[ExcludedDates],Null) AS Remarks1, [ABC-Circulation].BumperIssues, [ABC-Circulation].BumperIssueDates, PublishingDays.OrderLevel, public_publications.mLocation, public_publications.CompanyID, States.Description, PublishingDays.OrderLevel, [ABC-Circulation].iYear, [ABC-Circulation].AuditPeriod FROM ((((AuditTypesMaster INNER JOIN ((((([ABC-Circulation] INNER JOIN public_publications ON [ABC-Circulation].PubID = public_publications.Pubid) LEFT JOIN PublishingDays ON [ABC-Circulation].DayPublished = PublishingDays.lDayIndex) LEFT JOIN PublishingDays AS PublishingDays_1 ON [ABC-Circulation].CoverPriceDay = PublishingDays_1.lDayIndex) LEFT JOIN PublishingDays AS PublishingDays_2 ON [ABC-Circulation].CoverPrice2Day = PublishingDays_2.lDayIndex) LEFT JOIN PublishingDays AS PublishingDays_3 ON [ABC-Circulation].CoverPrice3Day = PublishingDays_3.lDayIndex) ON AuditTypesMaster.iIndex = [ABC-Circulation].lAuditType) LEFT JOIN PublicationTypes ON public_publications.lPublicationType = PublicationTypes.lIndex) LEFT JOIN [ABC-PublicationSubTypes] ON public_publications.lPublicationSubType = [ABC-PublicationSubTypes].lSubTypeIndex) LEFT JOIN [ABC-AuditPeriods] ON [ABC-Circulation].AuditPeriod = [ABC-AuditPeriods].lAuditPeriodID) INNER JOIN States ON public_publications.state = States.StateID WHERE (((Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jan to Jun 2007" Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jul to Dec 2006" Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Apr to Jun 2007" Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jan to Mar 2007" Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Oct to Dec 2006") AND ((public_publications.lPublicationType)=5)) ORDER BY public_publications.lPublicationType, States.Description, public_publications.mLocation, public_publications.title, public_publications.CompanyID, PublishingDays.OrderLevel, [ABC-Circulation].iYear DESC , [ABC-Circulation].AuditPeriod DESC;
I have a simple invoice database with a primary key in the main table (with autonumber as invoice no.) and a subform with the item details which is without a primary key with the data type as numbers. In addition, I had the tables linked as one to many relationship. What happen is that when I am entering the data, the number jumps . For eg: It was suppose to be 7123 but the next entry was 7150. Pls kindly advice.
I have a table in which the data input comes from a dropdown list selected in a form combo box as:
contract signed PIN issued PDD issued validation finished Host country approval EB approved CER's issued.
I am designing a report in which I want to display the above list in Ascending or Descending order but as shown rather than alphabetically. What I mean is no matter how the information is on the table the report showd display it in Ascending as contract signed, PIN issued, etc and in Descending as CER's issued, EB approved, Host country approval , etc.
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so abc1.1 abc1.10 abc1.11 abc1.2 abc1.3 ect
How can I stop this and sort it: abc1.1 abc1.2 abc1.3 ....... abc1.10 abc1.11
I have this form for data entry. I want to sort a field in a certain way which is neither ascending nor descending. The items in this field should be ordered as say, C, A, D, B. What I want is a custom order. How can we do it?
The field I am referring to is not on this form, but in the underlying table.
I am thinking of a VB code to achieve this purpose, where I can define the order. But not sure how to do it.
I would like to do sorting by using one Button like (Toggle Button).In the first click the below code is work good for Descending but in the second click the code doesn't work for Ascending!!
Code:
Private Sub TabItemName_Label_Click() If [TabItemName_Label] = -1 Then Me.OrderBy = TabItemName & " DESC" Me.OrderByOn = True
Hello friends, Please can anyone tell me how i can make the selections in my combo box (combo is bound to column1 of a table) appear in ascending order, by name (column2). I.e. It's bound by column 1 (with is the Id#), but i want it to appear in ascending by column 2 (Usernames) to make selections easier. Please help :)
The file was converted from excel. It is in Datasheet view. I select the first column and clip on the Ascending choice under the Home Tab. It works but leaves a large gab of blank rows. I go to the Database Tools tab and check Compact and Repair Database. The file returns to the original unorganized list.
I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.
This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.
I can't set it to show Descending permanently as some employees can't cope with change. so...
I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.
I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want... when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL: SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes ORDER BY 1 DESC;
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
