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.
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).
Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.
How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?
I'm new to this forum and I'm sorry if this is a repeat question...I looked in previous threads, but could not find my particular questions.
Here's the situation: I enter a bird name in my form (I've set up a combo box of 200 choices). Each bird species has an max and min possible wing measurement. So when I enter a newly captured bird in my form, the wing measurement must fall between the minimum and maximum for that particular species. I've done similar things with cascading combo boxes, but can't figure out how to the do a between-type statement in this situation.
Now my question, im trying to hide the rows of those who age is older than 26. If thats not possible maybe highlight the entire rows of those under 26, i used conditional formatting to highlight the age, but not the entire row.
I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form) i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new ext record
I have written some pseudo-code to show what I am trying to accomplish:
if keydown = down-arrow and current cell contents isnumeric then Cval = current cell contents if current record = last record then create new record move down 1 record set cell value of new record to cval+1 else move down 1 record if cell value = null then set cell value of record to cval+1 end if end if
Table 1: Account Number Start Date End Date Cost data** Budget data**
Table 2 (Imported excel file with cost/budget data): Account Number Cost data** Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
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.
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:
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")
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.
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?
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?
I'm developing a complex form that will pull records/fields from a number of tables. The form is designed in a tabular form - with a form showing a number of "logical" records - each of the records consisting of fields from a number of tables.
The first column of the records is genetated using a query that results in a unique set of values. When originally displayed the second (and the other columns) will have null values. What I want to happen is that when the user goes to the relevant second columm in a row he is displayed with a drop down list of possible values depending on the value in the first column/row. The possible values being determined by the a query on another table given the first column/row.
as a concrete example a form will display the following 3 rows, each with 3 fields. The first field having a country displayed:
When the user selects row_1 in the second column then a combo/list box should display the hotels appropriate to the USA - and the hotels being found by a query on another table based on the "USA" value in the first field in the row. Likewise if the user selects row_2 and the second field the hotels in the UK will be listed in a drop down box.
I've tried parameter driven queries and that does not seem to allow specific column/row values to be fed into thea query - neither does event notifications seem to be able to have a function parameter of the colum/row value so a VBA query could be issed
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?
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:
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.
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.
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:
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.
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
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
(Access 2013). I deleted an old text box cell on a form and then I created a Combo Box from the design controls and it worked, but underneath it, the old label of the text box is still there, except now it's just described in the properties as an 'empty cell'. It still has the old text description showing on the form, but there is no caption field in the F4 properties.
I've tried everything I can think of to get rid of it, including closing the database and running a repair, but nothing works. I can select it, but I can't delete it or move it and I can't click into it to create a new label, hovering over it just produces the 'selected' cross hairs.
I have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID WHERE (tblTransactions.TransDate=tblBalances.BalDate) GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID WHERE (tblTransactions.TransDate=tblBalances.BalDate) GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID WHERE (tblTransactions.TransDate=tblBalances.BalDate) GROUP BY tblBalances.BalDate, tblBalances.AccountID;
I have a complex IF Statement within VBA. When I step thru the code the if statement variables should return true but instead treats it as False. See '>>>> this happens on the 2nd Pass of a Do Loop Statement, the First Loop the if Statement Returns True
Variable Values:
Record 1 contractNo: 00001634 nfld: 33.40% nTier6: 30.00%
Record 2 contractNo: 00001634 nfld: 137.52% nTier6: 28.50%
so the 2nd if should be true but it acts as false an moves to next If statement.
Code:
If nfld = Format(0, "Percent") Then nOvrAmt = 0 BkOvrCalc = nOvrAmt GoTo cont: '>>> ElseIf nfld > nTier6 Then nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
