Field Value Via List
Oct 14, 2006
Okay, I'm not sure if this is possible, i'm just spitballin' here, so bear with me.
I have a table that has the names of all the months (January - December) and it is linked to a second table that has three fields, 2 of which are populated by field names of a third table.
Table1:
MonthID
MonthName
Table2:
Month
MonthCount (Text, populated by FieldList Table3)
MonthCost (Text, populated by FieldList Table3)
Table3:
Jan_Count
Jan_Cost
Feb_Count
Feb_Cost
etc...
Now I would like to do a query that does some aggregate functions on the specific fields in Table3 based upon the values in Table2. Something Like This:
Query:
Source: Table3, Table2, Table1
Table2.Month, Sum(Table3.[Table2.MonthCount]), Sum(Table3.[Table2.MonthCost])
So I would get a list:
Januaray 200 $1000
February 320 $2300
Etc.
Can I do that?
Thanks
Jaeden "Sifo Dyas" al'raec Ruiner
View Replies
ADVERTISEMENT
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
Oct 14, 2006
Okay,
The subject is probably confusing but I'll try to explain.
Setup:
Table1
FieldID = Number
FieldName = Text
Table2
FieldID = Number
FieldName = Text
T1_ID = Number
Relationship
Table1.FieldID 1-> * Table2.T1_ID (one to many)
Now, I'm trying to create a 3rd table that has field populated by Table1 and a field populated by Table2 but I want to limit the field populated by table2 by what is selected in the field populated by table1.
Table1:
1st
2nd
3rd
4th
Table2:
1st,January
1st,February
1st,March
2nd,April
2nd,May
2nd,June
3rd,July
3rd,August
3rd,September
4th,October
4th,November
4th,December
Table3:
Table1Field = Number, ComboBox-Source: Table1, BoundColumn=1
Table2Field = Number, ComboBox-Source: Table2, BoundColumn=1
If Table1Field = 1st then
Table2Field Will only allow January, February, or March as selections, since ONLY they are a member of the "1sr" group via the table1 list.
Is this possible, or do i have to do some run-time checking or something?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
View 1 Replies
View Related
Apr 24, 2013
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
View 1 Replies
View Related
Oct 27, 2006
Hi
I am using Access 2002 SP1 and have created a pivot table based on a query which works fine. However if I change the structure of the underlying query by adding another field, the field is not availabe in the Field List in the pivot table. I have used the refresh option but I understand it only applies to the data.
Any ideas?
View 1 Replies
View Related
Nov 11, 2014
I had two fields in my table which I previously used in the form. I deleted the fields from the form itself but the fields are still listed under Field List and not even under any table...
Delete buttons doesn't work on it, and neither does backspace?
How do I remove it? Right click only gives me two options:
"Add Field to View" or "Edit Record Source" which returns an error saying this command cannot be used.
View 2 Replies
View Related
Sep 24, 2006
I have a list box which contains a Field list of all the fields in a query in my database. Is there any way in which I can exclude a column from this list? Each record has an AutoID which I don't want the user to see, but this appears as a field.
Thanks in advance,
Gary
View 1 Replies
View Related
Dec 5, 2004
I created a report based off of several tables and queries. Now I need to go in and add another field from different table. The item is not on the field list and trying to edit the expression doesn't seem to be working. Any suggestions?
View 1 Replies
View Related
Apr 27, 2005
I do not have the option to refresh my field list in Access 2003. I am tring to add a new field in my table and then update it in my form. I do not see the toolbar on the top of the field list window, like help said. Please help this has been bugging me all day. This is what help shows me.
Refresh the field list of a data access page
If you're working in a multiuser environment, or if you modified an underlying table or query after opening the page, you might want to refresh the field list of the page.
If the field list is not open, click Field List on the toolbar.
Click Refresh on the field list toolbar.
Thanks
View 5 Replies
View Related
Sep 15, 2004
Hi
on my form i have unbound list box with 10 items.
Below are several fields bound to a query.
I would like to be able to double click an item from the list box and sent it direct to a specific field.
I don't want that specific field to have a combo box of the listed items above, is this possible and how do i implement it.
thks.
View 5 Replies
View Related
Oct 19, 2005
Does anyone know why I cannot select the "Field List" to display the fields from a Table on a Form with the Tab Control Added.
I can select the "Field List" from a simple form with nothing added to it. But as soon as I add the Tab Control I cannot use the "Field List"
Thanks......
View 5 Replies
View Related
Mar 1, 2005
How do I add fields from a different table when constructing the form?
Thanks in advance.
John
View 1 Replies
View Related
Jun 29, 2005
In my list box I have two coloums, Surname and Christian Name, now can I get both names to go into a text field. I can get one of the names i.e surname or christian name by changing the bound coloum from 1 to 2. But I need both names to go across?
View 2 Replies
View Related
Jan 7, 2006
All,
I wish to create a custom form to link into my tblData table, however when I open a form in design view, the field list is not displayed. I have tried to select it from the toolbar but the button is not available ('greyed out'). I know I have done this before but can't remeber how.
Any help please would be great?
JB
View 5 Replies
View Related
Jan 19, 2006
When you use a Yes/No field in your table and then want to have another field that will allow the user to list if yes...
_______________
Example that will be used in my form:
Are there any dependencies? Yes/No (they will choose from a drop down, yes or no)
If yes, please list dependencies: ??
_______________
How do you set up the if yes field in the table? I want it to be able to have it show as a list, not as one long text field with everything seperated by commas or semi-colons.
Is this possible?
View 1 Replies
View Related
Apr 5, 2006
Hello,
I have a form that I need to modify. Currently, the field list shows Query 1 as the 'control source' for the text boxes on the form and I need to change to use Query 2 but I am unsure of how to do this. Do I have to delete the test boxes and start fresh or can I change the field list and use Query 2 as the source?
Thanks for any help.
JTP
View 2 Replies
View Related
Jun 16, 2006
I would like to have a listbox that contains all of the fields names from a table. Since the user will be selecting items from this list box, the field names should be referred to by their captions as defined in the table.
The listbox values will change depending on the value selected from another listbox.
Question: using code, how can I fill a list box with field names and display the field caption?
Thanks!
Lisa
View 1 Replies
View Related
Aug 8, 2013
When I "Add Existing Fields" I'd like the list to be sorted alphabetically...how to do this?
View 4 Replies
View Related
Apr 25, 2012
I'm trying to limit the funders under previous applications to ones with the same name so then when you click on one it loads that other funding application details...
Code:
SELECT [Sheet1].[ID], [Sheet1].[Funder] FROM Sheet1 WHERE [Sheet1.Funder] Like [Sheet1].[Funder] ORDER BY funder;
Code:
[Sheet1.Funder] Like '" & [Sheet1].[Funder] & "*' ORDER BY funder;
[code]...
View 14 Replies
View Related
Dec 3, 2007
I want to edit a report i Access. How do I activate the Field list so I can choose fields to insert into my report? It is gray.
View 3 Replies
View Related
Mar 15, 2008
I'm trying to set up a database of books in a small library - each book has a prefix (Maths, English, Art etc) which I want users to be able to pick from a drop down list - I can do this, but the next field is a sub-category - is there a way of populating the dropdown list for the sub-category field depending on the chosen entry in the prefix field. eg if maths was chosen I would like the user to have the choice of number area, volume etc. but a different choice if the prefix was English.
any help very gratfully received - it seems to be a simple problem but it has me stumped.
View 14 Replies
View Related
Jun 21, 2005
:confused: I've tried everything I can possibly think of to get this to work. I've also looked at other threads and I still couldn't get this to work. The code for the list box in my form is Forms!Display_Engineering_Jobs_Report_Inputs!Repor t_Column1. I am trying to set the value in this list box as the input for the field on my query. This value also corresponds to a heading within a table I have. The information under the heading in this table is what I am trying to show in the query. Please help! Also, I'm pretty new to Access, so explain it as if you were writing a page in "Access for Dummies." Thanks!
View 9 Replies
View Related
Apr 28, 2008
Hi all,
I have a data set such as:
CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832
4003Severn Trent Water LimitedFRANKLEY3~00074831
I would like to only have 1 record of the Company name/ID with the RUIDs listed almost in one record something like...
CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832, ~00074831
Can we do this in SQL? (Specifically and Append query)
Thanks for any suggestions.
View 3 Replies
View Related
Aug 31, 2004
Can you set up a list box so when the form opens the list box is on a blank value at the start of the list. At the moment it opens with the first option selected. I have tried playing with the default value but this doesn't work.
View 4 Replies
View Related
Jun 29, 2007
To anyone who can help,
I have created a function that loops through an amount of records and concatenates them into a single comma-separated string. What I would like to do is concatente the values into a list. Instead of:
value1, value2, value3
I would like to see
value1
value2
value3
This needs to be in a single record only. The code I am using is:
----------------------------
while not rec.eof
string = string & ", "
rec.movenext
wend
string=left(string,len(string)-2)
--------------------------------
Thanks for all your help. I have tried to insert char(20), but this only seems to work in Excel. Access gives me invalid char symbol.
View 1 Replies
View Related
Sep 20, 2013
i have created a form and its working correctly. however i have just been informed some of the fields needed deleting and different ones put in their place. so im just wondering if there is anyway to updated the field list so i can just add the new fields without having to recreate the whole form.
View 1 Replies
View Related
