I am trying to remove a "sort and grouping" field from my report. I delete the field from the menu by using the "backspace" key on my key board. I am getting any error, invalid sort field. When I put something into the field, the error goes away. How do I delete a sorting field I do not want??
I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.
I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46
I would like it to show...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
and a different query like ( the purchase date doesn't matter here)...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.
I'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year 1,2,3,4,£20,4,2014 1,3,4,5,£10,4,2014 1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
I have this code that works fine exept for one thing. If I have two names that are the same it deletes them both. currentdb.execute "insert into charmer91 (last,first) values ('"me.list0 & "','" & me.list0.column(1) & "')" currentdb.execute "delete table1 (last, first) from table1 where last ='"&list0 &"'" How can I change the delete statement so it only deletes the selected name I have in list0, ie.. the highlighted one? Basically Im moving data from one table to another via listboxes.
i how i can get some help with this one, i have imported a txt file, once input into a table it has the fieldname inserted along with the data so for example record_id is the name of the field and in each column i have "record_id=###" the # respresent unique numbers. but what i am looking at doing is creating a "create table query and using an expressions like... "left([record_id],"=") but this won't work.
All the data in the columns has the same thing all have the field name with a = then the data i really need.
I have a report (Access 2007) with multiple totals and subtotals. However, one field, whenever I click "show Grand Total," always shows up all akimbo (out of line with the other grand totals), unbound and without the nifty little blue "grand total bar" above it.
I created a table by importing data from an Excel spreadsheet. Now I have discovered that I am unable to filter the data, i.e. do an ascending and.or descending sort, in the first field of the table, which is a long text field. It is the only field that has text, all others are yes/no fields. Is there any way to fix this?
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.
Is there a work-around for this within the query?
The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
I have a form that has a bunch of project information and scrolling buttons at the bottom to browse by next/last.
Right now the form is sorted by the ID associated with the project, which kinda sucks because they were and are not put in alphabetically.
I discovered if you right click on a field and click "Sort Ascending" or the opposite it works fine but when I open the database it is right back to normal ID sort
I have checked the Data and Other tabs but can't find anything with regards to this as well as I have tried sorting the actual table by name and that changed nothing.
I have a query that lists items in locations in our warehouse. Each location is broken down by Aisle-Bay-Level-Position, for example 50-101-01B; or 51-106-02 with all the even Bays on one side and Odd bays on the other side of the aisle. I have the query set up so it separates odd and even so I can go all the way down one side of the aisle then I have to walk back to the beginning of the aisle to check the other side.
What I am trying to do is change the sort based on another field that I have called SORT with a value of AZ or ZA depending on if I need that section to be sorted ASC or DESC. So for example all even bays in aisle 51 need to be sorted DESC while all ODD bays would be ASC. The way our warehouse is laid out it is not always the odd or even side that needs to be sorted DESC, that's why I made the SORT field. The formula I used for the SORT field is as follows:
SORT: IIf(([Aisle]='50' And [OE]='ODD') Or ([Aisle]='51' And [OE]='ODD') Or ([Aisle]='53' And [OE]='ODD') Or ([Aisle]='52' And [OE]='EVEN'),'AZ','ZA')
Is there a way to make it sort the BAY in ASC or DESC based on the value in SORT?
I have a form where data can be added and it has different tabs. The 3rd tab has idVersion field and Comment field where users can add the version and comment, if needed. When they need to add another version, the blank fields will be added as next row, so on and so forth. The problem is users are adding a lot of versions but not sorted. So for example:
Row 1 of the form: idVersion field is "1" and comment has "Test 1". Row 2 of the form: idVersion field is "8" and comment has "Test 8".
Row 3 of the form: idVersion field is "2" and comment has "Test 2". Row 2 of the form: idVersion field is "5" and comment has "Test 5".
So how can I fix it where after they saved the version and comment, when they close the database and re-open it again, the data will show up as:
Row 1 of the form: idVersion field is "1" and comment has "Test 1". Row 2 of the form: idVersion field is "2" and comment has "Test 2". Row 3 of the form: idVersion field is "5" and comment has "Test 5". Row 2 of the form: idVersion field is "8" and comment has "Test 8".
So even if they add a new version and comment (example version "3"), it will be automatically sorted when the database is reopen.
Below is the sql code I have for a CrossTab Query. Total as GrandTotal shows GrandTotal of all weekly columns. Is there anyway I can get the Query Display to sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ); TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1 SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal FROM [Trends-1-3TON-WEEK] GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory ORDER BY Sum([Trends-1-3TON-WEEK].Totals) PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
hi i found this code here and it works IF the number comes first and is preceded by a letter 123AA
but it does not work if the letters come first AA123
here's the code
Public Function GetString(WholeString As String) As String Dim i As Integer Dim Temp As String Temp = CStr(WholeString) For i = 1 To Len(WholeString) If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then GetString = Mid(Temp, i) Exit Function End If Next i GetString = Temp End Function
Public Function GetNumber(WholeString As String) As Double Dim Temp As String Dim i As Integer Temp = CStr(WholeString) For i = 1 To Len(Temp) If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then GetNumber = Mid(Temp, 1, i - 1) Exit Function End If Next i GetNumber = Temp End Function
the probelm is with this line but i'm not sure what it is
GetNumber = Mid(Temp, 1, i - 1)
(i also get runtime error 13) but my data is in the same format as the example i downloaded.
any ideas anyone?
thanks in advance and thanks to the person who created the code
My table has 3 fields, Employee Number (text), Job Description (text) and Current (yes/no). The table keeps track of the Job Descriptions that an Employee has had along with his current Job Description (actually Title is more appropriate a word but client requested Description) So the table has records such as:
0001 - Floor Sweeping - no 0001 - Ceiling Cleaning - yes 0001 - Dumpster Turning - no
The yes signifies that that is the current Job Description for employee #0001 and the others with no are previous Job Descriptions that the employee #0001 has held.
Now - I created a form to add these records to the table and on that form I want a list box to show the records in the table for this employee but I want to sort it such that the current Job Description is listed first and the rest show up in alphabetical order following. I have the query to list all of the Description, but how can I get the current one to always appear on the top of the list?
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.