Help - Select Query & Finding Record Question

Feb 22, 2005

Hello all, I have a couple questions. First I am trying to create a select query that will show the results that I have two fields , one is "Date" & the other is "Current" For the date I am using the a between statement "Between [start] and [end]. Under the "Current" I have "Applied" & below that I have "Overage" The problem is when I enter my date range, it shows all records with the Overage or Applied, but if I go & remove either "Overage" or Applied, then it works fine.

Is there somthing that I am missing


Second question is when using a find command box, is there a way to have it search multiple tables, if so how would I set the find box to search both tables, if the table names were called Let say Table 1 & table 2

Thanks!!!

View Replies


ADVERTISEMENT

Queries :: Finding Max Record Based On Primary Key In Query

Jul 25, 2015

How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.

View 2 Replies View Related

Select Record From Query

Jun 10, 2005

Hi All,
I hope I can explain what I am trying to achieve..
I have a "Top 1" query (Qry_Avail_StockItem), which selects the next available record in a table.
I have a main form (Frm_MasterStock) with a command button "Add Record".
If the user clicks the "Add Record" button, I need the form to go to the record that the query has selected.

Thanks in Advance..

View 11 Replies View Related

Can't Update Record From Select Query

Jul 30, 2006

Hi,

I have Access 2002 on Windows XP.

The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.

When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.

However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.

How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.

Thanks.

View 3 Replies View Related

Select Open A Record From A Query

Nov 15, 2007

Let's say I have a query on a form that returns results. Is it possible to double-click a record in the recordset and open a form.

I am currently doing this just fine from a listbox, but am wondering if it is possible from a query.

View 1 Replies View Related

Select Form Record Set By More Than One Query

Mar 15, 2006

Hi,

I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.

The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.

If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.

Regards,

Keith.

View 12 Replies View Related

Update Query Doesn't Select 1 Record

Nov 22, 2006

Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.

The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.

This is my code in the "on close" event:

DoCmd.OpenQuery "qryUpdateEmailMinimal_True

and here's the SQL:

UPDATE tblInventory SET tblInventory.emailSentMinimal = True;


I assume what is missing is a reference to an inventoryID number. How do I do that?

View 3 Replies View Related

Select Record In Split Form Query Result

Mar 25, 2008

I have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!

I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?

I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.

I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.

Thanks in advance!

View 4 Replies View Related

Running A Query Based On A Select Record In The Form

Jun 13, 2006

Hi,

I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.

Got any examples of code I could have a play with

I am struggling here so need any help I can get!!

Cheers
Paul

View 1 Replies View Related

Forms :: Listbox Bound To Query - Select Only One Record

May 12, 2014

I have a list box bound to a query. If the list box retrieves two records, I am not able to select only one of them. If I click, it gets both records selected ( highlighted ). Is there any way I can select only one record?

View 8 Replies View Related

Queries :: Select Active Record In A Table Using A Query

Nov 30, 2013

Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).

row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013

I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.

View 1 Replies View Related

SELECT Query To Not Include Record If Already Exists In Another Table

Aug 25, 2015

I have 2 tables:

Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145

[code]...

So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.

The desired out put should be:

Unit
R_Date
Sold_Rate

X
25-AUG-15
200

View 3 Replies View Related

Forms :: Select Record On Subform To Select Default Value In Combo Box?

May 6, 2014

I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.

Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.

I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.

What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?

View 1 Replies View Related

Finding A New Record

Feb 24, 2005

Hi

I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!

Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?

Any help would be hugely appreciated :)

View 2 Replies View Related

Finding A Record

Feb 5, 2007

Hi guys, need a bit of help with my Database. I have a Table with has ever player to play for my football club (1700 records in total), now i have another table called games (which has a record for every season played for each player, and is linked to the first table using the relationships and a field called ID. Make sense so far?

Well i then use a query to combine and calculate totals of certain fields and then i can use the query to show every single statistic possible on ASP pages. But when viewing my query in Access only 1699 records show up. So my thinkin is that either two records are identical (which is impossible because every p;layer has an ID and they cant be duplicates) OR a player has ZERO games in all years (in other words the player has no records related to it in the second table called games).

My question is, instead of searching 1700 records manually to find which one is there any way i can find which player would have NO records from the related table joint to it?

View 2 Replies View Related

Finding The Most Recent Record

Jul 16, 2005

I have a database with many tables, but only two are relevant to my question.

tblPersonnel holds (amongst other fields) UniqueStaffNo (primary key), Name, Dept, FirstAider (Yes/No).

tblCourses holds details of all courses attended by staff and is linked to tblPersonnel by the UniqueStaffNo. Fields in this table include the CourseTitle and CourseDate (short date format).

I can easily identify who is a First Aider by selecting on tblPersonnel.FirstAider and I can identify which of the thousands of course records are First Aid related by looking at tbl.CourseTitle.

My question is - how can I produce a list of all current first aiders showing ONLY their most recent first aid course (latest CourseDate) so that I can calculate when they need to renew their certificate?

Any help more than gratefully received. Thanks.

View 1 Replies View Related

Finding A Record To Add Data To A Field

Nov 27, 2006

Hi ,

I have developed a very basic contact DB to record enquiries coming into my business. The last field on the DB is for comments, so that anyone entering the DB can see the latest conversation we have had with a particular customer. What I want to do is create a means whereby I can find a customer within the DB and then be able to add the contents of our last conversation into the Comments field. Would there be any way of doing this via a switchboard???
I am a very inexperienced Access user so the simpler the answer the better. Many thanks in anticipation
Keith:(

View 2 Replies View Related

Finding Newest Record After Requery

Aug 17, 2007

Hello,

I have looked for ages for a solution to this and have not yet found one to suit me so here I am.

I am creating an Access 97 db in which I have a form (section1) which shows its records in datasheet format(results from a query). On this form is a button which opens another form (fileViewNewSec1) where the user can input the data for a new record. In the closeEvent() part of this form I have put:

=[Forms]![section1].[Requery]

This successfully refreshes the form and the new record can be found. However, there are many records in this database and it would be nice if once the form (section1) has been refreshed that it would focus on the newly created record. I am not sure if anyone can help me with this.

The records are sorted by "fileRef" and therefore the newly created record is not placed at the bottom of the records. They do however have an auto number primary key, I am not sure this will be useful but they you go.

Cheers

MArk

View 3 Replies View Related

Finding A Record Where 4 Criteria Must Match

Aug 13, 2005

How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name, description, type and date.

Thanks
J

View 6 Replies View Related

Still Need Help Finding A Record Where 4 Criteria Must Match

Aug 19, 2005

--------------------------------------------------------------------------------

How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name (text), description (text), type (text) and date (date). I would always search on all four fields.

Thanks
J

View 2 Replies View Related

Access Database - Finding A Record

Jul 18, 2006

Hi All - I'm developing a database that requires records to be entered and updated. When updating records, the user must select an item from switchboard (Update a Record) which takes them to a screen/form where the user can enter the record number. When the record number is entered and the user clicks on the "OK Button" I need one of two actions to occur ... (1) the user is taken to the record for updating or (2) the application returns a message "Record Not Found".

Can anyone tell me how I can code this?

At this time I get a message that my form name is not recognized within the expression I've "built" in the macro.

If anyone can help me it would be greatly appreciated before I go bald from pulling my hair out!!

Thanks in advance ... Spicoli

View 1 Replies View Related

Finding Last Record In A Table / Form

Jul 18, 2012

Product Name
Start Weight
End Weight

Product 1
379000
389000

Product 2
288435
226710

Product 3
699000
318882

Product 4
36800
50000

Product 5
89180
117280

Product 6
132000
132000

Product 7
18
18

Product 1

Product 2

Product 3

Product 4

Product 5

Product 6

Product 7

View 3 Replies View Related

Finding Specific Fields In A Record (sorry, Newbie!)

Sep 5, 2005

Hi everybody,
My question may sound you stupid but i am a newbie and i think you can help me...
My problem is,
I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record...
Any help will be greatly appreciated...
Thanks...

View 1 Replies View Related

Finding Record To Update In An Updating Form

Jan 4, 2006

I have a form in which old customers can be edited and new customers can be added.
Currently, to edit a customer, the user clicks in the "Last Name" field and then selects EDIT-FIND and types in the last name. The user will then cycle through all customers with that name to select the correct one.
How do I allow a user to begin typing in the last name field and have the name appear? Is there an easier way to do this?

Many thanks.

View 2 Replies View Related

Finding Record On Main Form From Subform

Sep 12, 2006

Hello,

I have tried everywhere for a solution to this:mad: Any help is much appreciated.

I have a main form with a subform. The subform shows related records using a match criteria. Each record that shows in the subform also has a unique ID. This unique ID is also shown on the main form record (although not used to link the main and sub forms).

I would like to be able to click on a command button on each of the records in the subform and goto the Main form record. I can't find any threads that deal with this though - sorry!

Hope it makes sense

DJ

View 1 Replies View Related

Finding And Opening A Specific Record From A Switchboard

Oct 5, 2004

I have created a database with a switchboard and a form ("RMA"). Currently, on my "RMA" form I have a command button that allows the user to find a specific record by entering an RMA number into an input box. My code is as follows:

Private Sub cmdFindRMA_Click()
Dim myFilter as String
If vFindRMA = "" Then 'vFindRMA is a global variable, string.
vFindRMA = InputBox ("Enter the RMA Number to find:", "Find RMA", "", 5000, 3000)
myFilter = "[RMA_Number]='" & vFindRMA & "'"
Me.Filter = myFilter
If Me.Filter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End If
vFindRMA = ""
End Sub

I want to be able to remove this command button from my RMA form and add it to my Switchboard. My problem is, I don't know what code I need to use in order for it to work from the switchboard. Can anyone help?

S.Pommier

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved