How Do I Specify Retrieval Order Of Records
Apr 19, 2005
I'm very confused. I have two tables in my db that should be the same except for columns(I copied one table from the other). In each I have a "default" entry as the first record in the table just to prevent my select queries from erroring out if they don't find a match for the criteria. When I use the following statement,
Code:SQL="SELECT ID, Name, Picture FROM background WHERE Name ='" & Fname & "' OR Name='Default'"
it returns the default value as the record, not the match for "Name", even though there is one(when I remove the OR Name='Default' it pulls the record).
When I use this query for the other table from the same .asp page,
Code:SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'"
it returns the record where "Technician" matches even though it is below "default" in the table.
I want the query to return the "default" record only if there is NO match for the first criteria, which it does in the second case, but not the first. Is there some way to force this? Thanks in advance for any assistance provided! -Chris Gordon
View Replies
ADVERTISEMENT
Nov 6, 2006
I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName])
objWord.Application.Activate
SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
WordApp.Application.Activate
MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc"
SendKeys MessageE
Set WordApp = Nothing
GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
View 2 Replies
View Related
Feb 5, 2008
I am trying to build a database for my digital photo collection.
(having pretty much gven up on the packages I see in the market)
Since most of the images reside on CD's and DVD's, I want to create
and store thumbnails and pointers (ie. location fully qualified name),
rather than a copy of the full image.
1. How do I define the fields in the table for the thumbnail, and the pointer?
2. How can I use the pointer value to retrieve the full image?
Any ideas and tips on where to start?
Thanks,
Roy
View 2 Replies
View Related
Jan 30, 2006
Hi,
Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.
Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.
:confused: .
View 1 Replies
View Related
Jan 11, 2008
Hi,
I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?
Thanks
View 1 Replies
View Related
Apr 17, 2008
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
View 4 Replies
View Related
Feb 24, 2014
Why I getting an error when trying to run the below code ? If I take out
Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .
add "And" to
If Me.ClientNameList = rs.Fields("[Client Name]") . . .
I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.
The code follows:
Private Sub ClientNameList_Click()
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Progress Tracking")
[Code] .....
View 14 Replies
View Related
Jan 19, 2006
Hi,
I am a bit stuck now and I want to get some ideas from you.
The context first : I am writing a timesheet application and I've got a main form + a sub-form.
In the mainform, I select the engineer, the contract and the week nbr.
My mainform calculates the first and the last day of the selected week and modifies the recordsource property of my sub-form to select the existing records with the right engineer + contract nbr + range of first and last of week. This means that I can only have maximum 7 records for the seven days of the week because the application records only a global time per engineer/contract/per day.
Now I want to know how can I add new records in the subform for the missing days and how to put them in the right order ?
Example: I retrieve from my select in the subform, the times from day 2 and day 4 but I need to generate the missing days 1, 3, 5,6 & 7.
How can i display them in the right order: day 1 (auto), day 2 (table), day 3 (auto), day 4 (table), and so on...
In which event do I have to put code and what kind of code ??
Please help !
View 2 Replies
View Related
Mar 1, 2006
Our organization (a University) has been engaged in becoming "paperless."
As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.
The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.
At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).
My question is this: has anyone in this forum ever tried to implement such a solution with Access?
Any advice or insight would be very much appreciated.
Thanks in advance.
View 1 Replies
View Related
Mar 11, 2005
Hi,
I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:
Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True
The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.
I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.
Any suggestions would be much appreciated.
Thanks,
Keith.
View 2 Replies
View Related
Aug 31, 2014
I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.
On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.
However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.
In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...
View 5 Replies
View Related
Feb 10, 2006
I am making a database that requires a list of all the crew people in our company. This list is not sorted by alphabetical order but by order of significance (or frequency of) crew person. This list is rather lengthy and if I add a crew person it can only be added to the bottom of the list. The only way I have been able to do this is by manually resorting and retyping the list. Is there any way to add a row in the middle of a table (like I can in Excel)??
Thanks, Joe
View 7 Replies
View Related
Sep 2, 2006
I have a form with a subform. I want the records on the subform to be in alphabetical order of first name. At the moment, they appear in the order the records were entered.
I have opened up the subform itself in design view and specified the record order. If I then switch to form view for this, the records correctly display in alphabetical order.
However, when this form is displayed as the subform, the ordering reverts to the original order of record entry.
How can I change this?
Thanks,
Gary
View 1 Replies
View Related
Nov 10, 2006
Hi, how do I retain the order of my records in a table in terms of when added ie
1st record id = 3456
2nd record id = 1234
3 rd record id = 2456
but when i go into the tabel it is now in order of 1234, 2456, 3456...i dont want numerical ordering!!
Cheers
Tania
View 1 Replies
View Related
Mar 20, 2005
I have records which represent tasks and the tasks need to be scheduled. They can be ordered to some degree by sorting the table on specific fields and then by sorting parts of the table by specific fields, however, the final ordering needs to be done manually as it cannot be done by a field sort. So, how can you move records around manually? It would be like a CUT and PASTE INSERT. Thanks.... Lester
View 3 Replies
View Related
Mar 22, 2007
Hi,
I apologise if this has been posted in the wrong place.
I am currently making changes to a VB front end for a database of jobs in our company. we have approx 2000 job records in the job table. The program uses a recordset to move sequentially up or down the table, record by record as it searches.
The code is doing its job fine, but for some reason or another, the records in the database are not in order. They loosely follow the order you would expect from 1 to 2000, but every now and then deviates. Obviously, when pulling data out one record at a time, this gives the data in the wrong order.
I did not design the database. There was orignally no primary key or indexing. The new job number (an obvious choice for a primary key) is generated by the exectuable program.
I have spent hours trying to apply sorts in different ways, and reading about how records are stored, and my limited understanding has led me to believe that merely sorting records in access is not enough to change the order that they are physically stored in.
My coding abillity is not good enough code round this problem, and the ideal fix would be to find out some way of forcing an ordering from now on (ie would making job number and indexed primary key now prevent this happening to future records) and to find some way of re ordering the previous records (not just sorting them to be viewed in order).
Am I being really silly and missing something or is this a problem people have had before?
Thanks in advance,
Jamie
View 2 Replies
View Related
Apr 21, 2005
I have and order entry/lookup form with a subform to view the line items on the order.
Once an order has been saved and status is ‘placed’ I want the records for that order to be locked.
I would also like to display a message box with a yes/no answer to confirm that they want to issue the order and if they confirm then lock the record otherwise return them to the open order.
I can't find out how to do this, but I'm sure it can be done.
Any help will be gratefully accepted.
View 3 Replies
View Related
Oct 20, 2004
Hi,
I just made a form for order entry which has a subform with order details where each line is a product number with quantity, extended price, etc...
The problem is that when I add a product in the subform, I add it in the new record which is at the bottom of the form. I can add many product like that it works no problem , just adding them to the bottom of the list.
But, when I close the main form and reopen it, the records are now liste is the opposite direction, meaning that the last product i added is now the first row of the subform...So the subform is now populated backwards with the latest record being the first one...
What can I do for the subform to always list the products in the same order I originally input them?....
Thanks,
Hugues
View 2 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
May 8, 2005
I have a subform embedded in a mainform which contains loan payment details over a number of months. The record starts with the payment period number ie 1,2,3 etc. The problem is that every now and again the subform changes the display order of the records so that I get 4,5,6,1,2,3.
When I look at the underlying table the order is OK as is the non embedded sub form. I cannot see any reason for the change and it only happens randomly (apparently).
The numbers are not autonumbering but manually input.
Hope someone can help please.
View 1 Replies
View Related
Feb 27, 2006
Hi everyone. I need help on this one
I have a form with a subform. The subform is basically the sale history of the item on the main form (The main form shows a record and then the subform reports its history). This all works fantastic, except that on the subform, every time a new record is entered, it goes to the bottom of the previous one, so after a while, if I want to enter more history into the subform, I have to scroll down through all the older ones to get to the more recent ones.
How can I do it so that in the subform the most recent entry is always at the top and each time a new record is entered, the older ones move down one. This way the most recent records are always on display and older ones move down as they become less important. Likewise, the empty record used to enter data is also at the top!
Thanks!!
View 14 Replies
View Related
Nov 29, 2004
Access 97 does not display records in their original order of entry in a report or its query whether they are indexed or not. Results
are always in ascending or descending order even when sorting and groupings are not set. Any suggestions?
View 2 Replies
View Related
Nov 10, 2014
I have a table with entries describing a sequence of Tasks (tblTask)
tskID TaskDescription
10 Clean Room
12 Wash Dishes
etc
I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder]
So if we look at washing dishes as a task then the steps are as follows:
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink , 1, 12.1
512 Turn on tap, 2, 12.2
205 Put in detergent, 3, 12.3
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5
etc
If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5
Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 3, 12.3
435 Turn off tap, 4, 12.4
View 6 Replies
View Related
Jun 25, 2013
I am on code want to display records on the form by order_number, but i am unable to do it.
Private Sub Combo63_AfterUpdate()
Dim rs As DAO.Recordset
Dim intcount As Integer
On Error GoTo ErrorHandler
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Master_Log WHERE Order_number = " & Combo63.Value & "", dbOpenSnapshot)
[Code] ....
View 6 Replies
View Related
Jul 1, 2014
I have a difficult problem (because of my own capabilities). I have a table (tblFoods) which includes a number of food with their analyzes. I have a second table (tblExclusiveFoods) which is connected with a third table (tblCustomers) with one-to-many relation. Namely, o tblCustomers is the "One" and tblExclusiveFoods is the "Many".
Into table tblExclusiveFoods there is a field that acts like a ComboBox and gets data from the tblFoods such as drop-down-menu. So, using the combo box i can select the foodID from tblFoods and then showing into exFood field which is a part of the tblExclusiveFoods fields. When i collect the foodID's from the tblFoods, i would like to to build a query which will exclude these foods from the tblFoods.
View 10 Replies
View Related
Aug 9, 2013
I have a form with a dataset based on a table. (there are a number of buttons on the form with code so it's important I don't delete the form and start again with a new one)
The data displays correctly but the order is wrong. It displays the last record added as the first record when viewed in form view.
I want to reverse this order, how do i go about it? It seems very simple but I've yet to find a solution.
I also don't want to base it on a query because of the code mentioned earlier.
View 4 Replies
View Related
