Return Info Based On Other Fields
May 3, 2006
Hi All
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")
Any suggestions would be greatly appreciated
cheers
sallee
View Replies
ADVERTISEMENT
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Nov 7, 2006
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
Thank you.
View 2 Replies
View Related
Nov 21, 2014
I have a table (tblInstallations) which has records of date installed (for machines).
It is calculating that "next due maintenance" is one year after installation.
Sometimes we need to visit the site to maintain or service the machine before/after the specified date in "next due maintenance" and we need this to be calculated to reflect a year after date of visit.
I have a table called "tblMaintenance" which takes information about the machine an engineer is visiting from where originally the information are kept and that is in "tblInstallations".
How can I link the new date of the visit to update the record for "next due maintenance"...
View 4 Replies
View Related
Feb 7, 2014
I'm trying to obtain all of the information in a text field for a query.
The user entered information and used a return (enter key) to enter information on a new line within the same field. The information after the return is not captured in the field in the query. Is there anyway I can capture all of this info?
View 3 Replies
View Related
Feb 2, 2005
I have a Form1 that is based on a select query with a criteria [Enter PIN number]. When we enter the PIN it returns the correct result. I want to have a button that opens a second form based on the same PIN number entered to open the first (in fact there will be numerous forms I want opened based on this PIN).
Example:
Sidewalk - button
Curb X button
Tree - button
Hydrant X button
In the example above, only CURB and HYDRANT returned an entry. Therefore when I click on the button next to CURB I'd like the frmCurb to open showing the data based on the PIN numbered entered to open the From1, and when I click on the button next to HYDRANT it would open frmHydrant showing the data for HYDRANT based on the same PIN number entered to open the Form1.
Thanks,
SKK
View 12 Replies
View Related
Oct 6, 2005
Wow, seems like this is my favorite place in the world now.
My problem is that I need to append data from one table to another, on the condition that any of the fields in the record have changed since the last time the query ran.
For example,
tblEmployee has the fields 'EmployeeID', 'Name', 'Position', and 'Office'. We are appending to tblEmployeeHistory with the same fields in addition to a field for a date. When executed, the query should append the records where something has changed and attach today's date to them. This enables us to later use another query to call up old information based on the date.
Once again, this is a hypothetical situation and not my actual database. Any solution or comments are welcome.
View 2 Replies
View Related
Nov 20, 2014
I want to filter a database based on the info typed into the text box, after this, clicking on the button search and filter the database..I have an issue in my code, because I know just how to filter it based on a field but not for all..The fields I have are: Batchnumber, ModelnumberID, OperadorID, Datet, TestResults, Units and Status
My code is:
Me.Requery
Me.Filter = ""
Me.FilterOn = False
[code]....
View 4 Replies
View Related
May 21, 2012
More Info" button based on Combo Box selection. I need to create a command button that can be used to see all of the data in the table for whatever name is selected in a combo box. For example, if John Smith is selected from the combo box, the user could click a "More Info" button, which will open a separate form showing all the data in the table for John Smith.
View 1 Replies
View Related
Jun 15, 2006
I need to make a query work so that when someone enters info into a master form, it will pull the data for that job onto other forms when the job # is entered. Thanks for any help.
View 1 Replies
View Related
Mar 15, 2005
Someone once posted an database on how to do this but I was not able to see how they were able to automatically input the data into a text field.
I know how to do a combo box to query and input those results into a combo box but I am confused how they got the information from the combo box selection to automatically put the other variables into the testbox. They did this without using the dlookup command. In fact it was done without any code.
They showed how to do this automatically without code and with two other methods (one being dlookup). Can someone please help me if it is clearcut as I don't have too much time to search the forums for empty answers (believe me I have already wasted too much time) Thank You
View 4 Replies
View Related
Feb 25, 2005
I have a booking form comprising of a main form which holds the person details, and a sub form which holds the details
of each course they book. I want to be able to produce a report/ letter from the current form (ie once a person has made a booking)
confirming that the booking has been made and giving a print of the details.
How I can produce a report based on just the current booking information just entered in the form?
Can anyone please give me some advice on this one, I know how to produce general reports but cannot see how to link one to a current form's data?
Regards
Peter
View 3 Replies
View Related
Nov 10, 2004
Dear All:
Thank you for all you help with the above title. Your suggestions were all implemented.
Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
Please help,
Dion
View 5 Replies
View Related
Nov 18, 2014
I have a form where I can add a new resident. It runs off a query that shows ID as null and has a button to add the data and refresh the form and query. I used to drop in OLE files but have since changed it to a linked image file. What I want to do is pull the info from the data that I input as a new resident.
Basically it means that any staff member can take a photo of the resident and put it in a folder and the database will point to that file.
The location is..."C:UsersRP"&[First Name]&" "&[Last Name]&".jpeg" First Name and Last Name are in the table already. I have tried using an append and an update query to no avail.
View 2 Replies
View Related
Jan 3, 2006
I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
01/01/04
01/01/05
01/01/06
02/01/04
02/01/05
02/01/06
Does anyone know a solution - I have been searching all afternoon!!??
View 1 Replies
View Related
Nov 9, 2004
Dear Access Wizards:
I have created a form based on a query with students' gpa in the form. The gpa is in a specific field.
There is another field for honors with is for summa cum laude, magna cum laude, cum laude.
Here is the issue: If the students' gpa is 3.8, return "summa cum laude"
If the students' gpa is 3.5, return "magna cum laude"
if the students' gpa is 3.2, return "cum laude"
This applies to undergraduate students only, which there is a field for.
Any ideas on how to resolve this issue?
Many thanks,
Dion
View 3 Replies
View Related
Mar 14, 2015
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
View 1 Replies
View Related
Jul 9, 2014
I have a database (small one which i designed)
I have created all the tables.
I then have a "form" called July-14 (i need to figure out how i can automatically replicate this form for new months)
Anyway, one of the cells pulls out a product name (using the lookup wizard), i then have a cost cell which i would like to populate automatically based on the product name.
The table July-14 links to a product table which has ONLY those 2 attributes in there, the name and cost.
How do i get it to pull the cost automatically.
View 5 Replies
View Related
Aug 8, 2012
I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
View 1 Replies
View Related
Dec 1, 2012
We have Cardiac Rehab patients that come 3 times a week (M-W-F). Once they are on program they stay there for 3 months. There are classes given hourly from 8am thru 6pm. There are 12 available slots per class. Right now this is being handled in Excel, but I thought I might improve things by putting it in Access.
The Excel "form" I built has fields for PatientName, MedicalNotes, DoctorName, and Notes. Because the names rarely move (a patient gets a slot and stays there) the user opens Excel, reviews the form to make sure no one cancelled-is out sick-etc, then prints the schedule. When the day is complete I have a macro that takes this information and saves it in a tablelike manner on another sheet. A dynamic pivot table references this info to give the user quick answers as to daily volume, etc.
The thing I can't get my head around is how to make this as easy in Access. My thought was I could have a PatientT, listing all of the fields mentioned above. I could have DailyF open, bound to PatientT, with additional unbound textboxes to add "today's date", cancellations or no shows, and then Save all of this in another table and print out the daily schedule.
View 7 Replies
View Related
Aug 27, 2006
I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.
I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).
I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?
Any help would be appreciated.
View 2 Replies
View Related
Apr 23, 2007
I have the following tables: Rates and SelectedDates, which are structured as follows.
Rates: RateID, EffectiveDate, Rate
SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate.
I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table)
IF there is only one Rates.EffectiveDate for the SelectedDates.Date
return the associated Rates.Rate
ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1
return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date
ELSE
return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date.
I hope this is clear, as it is late and I am tired...:confused:
Thanks in advance,
Rod
View 1 Replies
View Related
Apr 26, 2005
Dear All:
I have a form with three items:a checkbox called "Check231", a textbox called "text921" and another textbox called "text762".
What I wish to do is: Enter text in textbox921, which stays the same as I scroll through each record. Then If checkbox check231 is checked, it displays text from textbox921 to textbox762. Textbox762 is bounded to the form.
Any ideas on how to get started?
Many thanks,
Dion
View 1 Replies
View Related
Oct 30, 2013
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code:
Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))
View 3 Replies
View Related
Aug 9, 2013
I inherited a spreadsheet that uses an Index (Match formulate to return a value. I want to use Access to manage the data going forward. I am just starting to build my tables in Access so I have some flexibility and want to do it right. Here is a sample of the data:
KeyEnroll JanFebMarAprMay
abc1/2013103521
def3/201300789
ghi5/201300008
So if Enroll date starts in Jan I want the 10 amt returned from the Jan column. If the Enroll date starts in March, for that client I want the March amy returned, etc.
results: for client "DEF" the amt returned should be "7" because their enrollment date is 3/2013.
View 4 Replies
View Related
Aug 27, 2015
Event#
EID
Arrived Time
Code
Cancelled Time
[code]...
I need to find all EIDs where the Code is 611 where an Event# starting with F was Cancelled along with the time of the cancellation. For these results I also need the Arrived Time for the Event# starting with E for that EID.For instance, a result I'm looking for would be:
EID
Event# (F)
Cancelled Time
Event# (E)
Arrived Time
[code]...
View 1 Replies
View Related
