Sorting Subforms (yes I Searched)
Dec 15, 2006
I did search the forums on this subject and can not figure out how to do what I want to do.
I have a form and subform setup. Basically by main form has contacts and my subform has log notes. It logs the date for every new record. It sorted fine up until yesterday when I ran an update query to change all the userIDs to UPPERCASE. Now it seems to be throwing the subforms in a random order.
I need the subform to order by Order date. Simple as that. I tried setting the property "Orderby" to Calls.Orderdate but it didn't work.
Thanks!
View Replies
ADVERTISEMENT
Oct 23, 2006
Hello!
I'm quite new to access, but i've encountered a bit of a problem
for myself here. I'm following a book here, and trying to do some of
the advances tasks, where the author doesn't describe how you solve them.
One task, is to make a subform out of a query which contains 3 tables. This
is my query so far;
SELECT Konto.Nr, Bevegelse.BevegelseID, Bevegelse.Beløp, Bevegelse.Dato, Bevegelse.Sted, Konto.Kundenr, Kunde.Fornavn, Kunde.Etternavn, Kunde.Adresse, Kunde.Postnr, Kunde.Fødselsdato, Kunde.Telefon, Konto.Kontonr, Konto.Kode, Konto.Saldo
FROM Kunde INNER JOIN (Konto INNER JOIN Bevegelse ON Konto.Nr = Bevegelse.Nr) ON Kunde.Kundenr = Konto.Kundenr
ORDER BY Bevegelse.Dato DESC;
This makes a perfect and working form + subform, however - in the subform it sorts the data after the BevegelsesID. I want to find a way to sort it desc from the "Dato" (Date in Norwegian) instead.
Any ideas?
View 3 Replies
View Related
Aug 8, 2007
In one record (or row?) there I have 10 fields (columns), how can i find the standard deviation of those 10 numbers. Seems to only want to be able to do standard deviation if all data is in one field or column. I tried doing a query but same thing, if all the fields are across, it doesatn find the standard deviation of them. Please help this worthless access user. Thank you, I look foward to all the help I can get. As you can see, I still am struggling with even terminology. Excel is easy to me, access is a challenge to say the least.
View 3 Replies
View Related
Sep 27, 2005
I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):
Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With
oApp.Workbooks.Close
oApp.Quit
Set oApp = Nothing
End Sub
View 5 Replies
View Related
Dec 9, 2011
Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?
View 4 Replies
View Related
Jul 25, 2014
I'm trying to catalog these shelves for work. So someone can then search for the item and it will pull up the shelf its on. The shelf has alot on it so I want to set the focus on the item on the shelf. But I'm not too sure how to do that with my search button.
This is the code for my search button:
Private Sub cmdsearch_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
[Code] .....
View 14 Replies
View Related
Oct 20, 2005
Hi,
I've been searching the forums, and I find a bunch of posts about the subject, but I just can't seem to really find what i need.
What I'm looking for is how to construct a query, that will exclude any entries in my table that might fall on a Saturday or Sunday.
What is the easiest way to do this?
I'm going to be using the results of this query in a Report and DAP.
Thanks
Chris.
View 3 Replies
View Related
Jun 20, 2015
I am trying to make an amend record form which gets values from a subform. Most of the values that I need to amend on the form do amend. My problem is that I need to amend a value that is also used to find the record.
The value in the Table(BookInTable) that I am trying to amend is Barcode which is a text value. I have been trying to pass the initial value into a string(Bar) and have been using an SQL Update string.
Code:
Private Sub Command23_Click()
Dim Bar As String
Bar = Forms!FrmAmendOrder.AmendOrderSubform!BarCode
If IsNull(Me![POTxt]) Or (Me![POTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![POTxt].SetFocus
[Code] .....
BarTxt and POTxt are textboxes that show the initial values and BarTxt is editable.
By adding watches I can see that when I click the button Command23 Bar = the initial value and BarTxt.value = the edited value. But when I look at the table nothing has changed.
View 3 Replies
View Related
Jan 12, 2007
Firstly hello, I'm new! :)
Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.
From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.
However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.
I have a survey, 20ish questions expecting 200-250 responses.
The questions consist of a mixture of:
Yes/No answers
Select all that apply answers
Select one from list
Select one from list or select Other and enter it yourself
Text/Comments
Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.
I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.
So in essence a flat file response table with each field being a different question, each record being a separate survey response.
For those questions with multiple answers a separate table, related to the main response response table.
Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.
Reporting will be faily widespread with a lot of cross referencing.
I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.
Any views or help would be much appreciated :)
View 1 Replies
View Related
Mar 10, 2006
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
View 5 Replies
View Related
Feb 4, 2006
I forum could someone tell me:
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
regards in advance
Peter:eek:
View 1 Replies
View Related
Jun 14, 2007
Hi Everyone,
Could anyone tell me how I sort a field so it sorts by the surname, not the first name? It's one field with 2 names seperated by a space, i.e. George Michael.
Many thanks for any help.
Essex
View 5 Replies
View Related
Jan 1, 2008
I am doing a database containing names, dates of birth, dates of death etc of my ancestors. Does anyone know the best way of displaying it so I can sort them out by age of death? For example, I want to see someone for example who died aged 70 years and 6 months to be a place above someone who died aged 70 years and 4 months.
View 1 Replies
View Related
Jun 26, 2007
In Quebec there are a lot of place names beginning with "Saint-" or "Sainte-".
When I do an ascending sort on the table itself, these type of names don't seem to follow the expected pattern.
For the names Sainte-Marthe, Sainte-Thérèse, Saint-Ephrem, Saint-Eulalie I should see this order:
Saint-Ephrem, Saint-Eulalie, Sainte-Marthe, Sainte-Thérèse.
Instead, I get Sainte-Marthe, Saint-Ephrem, Sainte-Thérèse, Saint-Eulalie.
The names are spelled exactly as I have them in the table with hyphens and diacritic marks.
Any ideas why there seems to be a problem sorting "Saint-" from "Sainte-?
View 8 Replies
View Related
Jan 10, 2007
Yet another question! When I run the report, it gives me the results in alphabetical order (i.e. April August, etc) instead of monthly order... Is there a way to fix this?
Thanks!
Michele
View 5 Replies
View Related
Jul 26, 2007
I have a field with addresses (numbers and letters) Whenever I try to sort it sorts by the number. How do I get it to only sort by the letters but still include the numbers in the result?
View 1 Replies
View Related
Oct 4, 2005
I have been trying to solve this Access problem for a couple of weeks. I have 2 Access 2002 files where I import info from a Paradox DB. I have been doing this for 12 years. I just bought a new laptop and transferred the programs over (I didn't reinstall because I don't have a floppy drive). Paradox files have a default of an International sort order.
One of my 2 Access files is now requiring an Ascii sort order. The Paradox forum says that something in the Access file is asking for the Ascii sort sequence. I have to keep bouncing back and forth in the Regedit changing the sort order from International to Ascii and back again to use the 2 files.
Do you know how I can make the 1 Access file ask for the International sort order?
View 4 Replies
View Related
Jan 11, 2006
I have a table with an alphanumeric field I want to sort on. If I sort the usual ascending way, data that is numeric is sorted before data that is alpha.
Is there a way to have the alpha data sorted ascending come out ahead of the numeric data srted ascending?
Ex:
Currently,
1
2
3
A
B
c
Desired:
A
B
C
1
2
3
Thanks in advance to all who reply!!!
View 3 Replies
View Related
Jan 25, 2006
i have a simple datsbase with 2 fields - drawing number and description.
the drawing numbers are in the formatt STD123 e.t.c.
When I try to sort numerically the order ends up as
STD1
STD10
STD11
STD100
STD101..........
How do I get it to list in true numeric order in report and table view i.e.
STD1
STD2
STD3.......... Please bear in mind that my knowledge of Access is VERY limited. I've tried a search and don't understand about queries and such like. Even so I'd have thought that even the most basic database programmes would be able to handle something as simple as an alpha/numeric sort list....seems not!!!!!
????????????????
View 8 Replies
View Related
Mar 30, 2006
I have a table with two columns
skill and category
i need to write some type of query or report that will group the skills into the numerical categories 1-9 and then in each of those groups listthe skills autamaticall and then have a way to print it out either in word or excel looking some thing like this
category 1
askill
bskill
cskill
category 2
askill
bskill
cskill
.....
any ideas?
View 1 Replies
View Related
Feb 28, 2007
How can I sort string addresses so as to get the correct number order? I know that strings sort on each character, which causes the problem. I have tried some things (with VAL and Len), but was not completely successful. I do not want to enter an address number as 0630, when I want 630.
Why does this not work:IIf(Len(Str(Val([address]))=3),"0" & [address],[address])? Address has 3 and 4 numbers only.
View 2 Replies
View Related
Aug 9, 2007
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.
View 4 Replies
View Related
Jan 23, 2006
Hey everyone,
Is there any way that you can do a secondary sort on a field?
Any help would be of great!
Thanks - edawson
View 2 Replies
View Related
Sep 1, 2006
Hi The beginner here again.....
How do I set a table to sort by specific fields???
ie i have an ID field which is an autonumber, then 6 fields
I want the table to autosort first by a field called [rank], and then by a field called [name]
Thanks in advance
Egg
View 8 Replies
View Related
Jan 27, 2007
I have a table of plant names such as
×Mahoberberis
+Crataegomespilus
Iris
Scilla
Hebe
Geranium
Acorus
Note that the multiplication and plus signs are part of the names.
If I sort the table (A-Z) I get
+Crataegomespilus
×Mahoberberis
Acorus
Geranium
Hebe
Iris
Scilla
What I would like to see is
Acorus
+Crataegomespilus
Geranium
Hebe
Iris
×Mahoberberis
Scilla
I am not a programmer so need to be led by the hand, but can anyone see a way of achieving this which I could copy? My simple mind tells me that I have somehow to suppress the × and + during the sort. But how?
View 3 Replies
View Related
Mar 2, 2007
I had been using previous versions of Access, and just recently "upgraded" to 2007. I used the search function, and didn't see exactly what I was looking for, so hopefully I don't get flamed too bad if I missed an obvious thread, but here's my situation:
In the previous versions of Access that I used I would get a sort order like this:
R-256
R-276
R-500
R2325
R2598
now I get the following
R2325
R-256
R2598
R-276
R-500
This is a primary key in my table, so stripping the field it apart, sorting, and reassembling is not really an option. I jumped from Office97 to 2007 with this particular database, so I am not sure when the non-alphanumerics were ignored when it came to sorting, but is there a way to get the hypens to be recognized when it comes to sorting?
Thank you.
View 5 Replies
View Related
