DEVICE| DISPLAY1 | DISPLAY2
_______________________________
A | A1 | A2
B | B1 | B2
Table 2 has 2 fields, but the second is a multi-value field
DEVICE | MDISPLAY
_____________________________
A | **MUTI-VALUE** based off the table 1
In the row source of the Multi-value Field MDISPLAY, I have
Select DISPLAY1, DISPLAY2 from TABLE1
The problem. I can get the MDISPLAY field to display the items from Table 1, but it grabs ALL of them.I need it to display ONLY the DISPLAY1 and 2 field associated with the value of the DEVICE for the current row in table 2, which is 'A'
SO if I look at the row of the table 2 that has the device 'A', the MDISPLAY field for that row should have.Just A1 and A2, NOT A1,A2,B1,B2(all the rows). how to access / syntax of the current value of the DEVICE field in my row source.Select DISPLAY1, DISPLAY2 from TABLE1 where table1.Device = Table2.device /or Device ... etc.. 'doesn't work
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
I have a main form with multiple sub-forms (each a separate tab). Having imported the fields from the equivalent tables, none of the multi-value combo box fields on the sub-forms work as they do not show anything. I tried implementing one on the main form (to test) and it worked fine. What am I doing wrong with the sub-forms fields?
i have table on sharepoint which i can not link to my access database so i have to import it. table on sharepoint is same as in access and i need table from sharepoint updated with my access. unfortunately this table has multi valued fields. when i import table from sharepoint it, the table gets renamed so if the original table is tablea the imported table becomes tablea1. im trying to update my table in access but then i get error "An INSERT INTO query cannot contain multi-valued field." if i remove multi valued fields from query, the query is working fine. how i can get the values into my table in access?
How to add a multi valued fields in a form for example a student may have multiple hobbies, access can do this using using lookup option, but access stores multiple values in one field which is difficult to analyze, looking for efficient way of adding multiple option.
The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click() 'On erorr GoTo errr Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter Me.tblFLM_subform1.Requery Exit Sub errr: MsgBox Err.Description End Sub
I have two fields in a table that have multiple values. Example:
Field A:
CT, CA, PA
Field B:
CT, CA
I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.
I have a field in a table that I want it to be multivalued. The values are stored in a different table. I tried to do that and it worked fine. But when I wanted to display a pie chart in a report based on a query, the values didn't show up.
Example:
EmployeeName: X TermenationReasons: 1,2,3 (multivalued field - values stored in a different table)
I though of just changing the field type to a text, and create a combo box in the form that allows me to add multiple values, and appear on the table and the query, and the pie chart too.how to make a multi-valued combo box in a form.
I have a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.
What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.
I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.
However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of
([Lookup_Tags].[TagName]="whatevertagtheypick")
This syntax seems not to be supported when I put it into the SQL statement.
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS: ID_PRODUCT (primary key, autonumber long integer) ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS: ID_ORDER (primary key, autonumber long integer) FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.) FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen) CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS] from PRODUCTS where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK WHERE qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri Masonry NSW');'
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1 FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate() 'Dim LocQryStr As String 'Dim r As Integer 'Dim ctl9 As Control 'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I need to have a field that is labeled zones. This field can have anywhere from 1 to 7 or so values. The Values are numbered 1-15. If I have a person that operates in zones 2,3, and 4 I need to be able to have the annotated in the record so that when I want to see all the people operating in zone 2 it will recognize that Joe Smith works in zone 2, even though he also operates in zones 3 and 4. I need to do this in Access 2003 since that is what they have at work. I know that 2007 has multivalued fields but I don't have that version at work. So if I understand this right I create a separate table with headings Zone and Autonumber as the PK. Then I link that to the main table in a one to many relationship between the zone table(many) and the main table(one)? Then when I create a query that searches for all records that have a 2 in the zone it will find Joe Smith's record? Is this right? Thanks for your help. If there is an easier way please explain it to me. Thanks again.
i'm trying to connect to a table valued function on sql (2005) but keep getting the following error: argument are of the wrong type, are out of acceptable range, or in conflict with one another.here's the code
Code: Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = New ADODB.Connection cnn.ConnectionString = "DSN=mydsn;UID=myuser;PWD=mypw;" cnn.Open
Hi, I've got a problem with the syntax of a calculated field. This is what I have so far: TransportA: IIf([weightunitised]+[weightloose]<500,25,([weightunitised]+[weightloose])*0.05)
I want to add the following:
If [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])<500 minimum charge is 50 if [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])>500 then ([weightunitised] + [weightloose])*0.07.
I am using a function to calculate the number of days in a quarter. I have the Control Source for field "A" set to =CalcQtr1Days([AdminDate]). I am calling CalcQtr1Days and passing the AdminDate. It calculates the number of days I'm looking for and returns that number in my field. Then, I want to use that number, along with DaysAbsent, to calculate DaysPresent. So, in field "B" Control Source, I have =CalcQtr1Days - DaysAbsent. This gives me #Name? in field "B". I've also tried =DaysPresent = CalcQtr1Days - DaysAbsent but doesn't work either. What is the syntax for using the value calculated in field "A" in my calculation for field "B"?
I am trying to set a multiple field index on a table. I would like to use 3 fields. One is a text field, the other two are date fields. The text field and one date field will always have a value. The other date field can either have a value or no value. The index will work if I only use the text field and date field that always have values. If I try to add the third field, it will not find a duplicate record. Can I not include a field that may have a null value?
