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 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.
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?
View 1 Replies View RelatedI have 2 tables with text fields
Table 1 has 3 fields and 2 rows
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 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 am trying to make a database containing the technicians of our companies.
I found a online template i am using, and this has a quick search field.
I have created a multivalued field that contains the technicians skills. (pulldown with checkboxes)
When i try to use the quick search field access returns a error message saying something like:
"The multivalued field '[Category]' can not be used in a WHERE- or HAVING-string."
The field i use for skills is an old category field.... Just renamed and changed a bit..
have a look on the attached sample database.
How can I get the query to work in order to count specific values per day.
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?
View 4 Replies View RelatedHow 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.
View 8 Replies View RelatedThe 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
[code]....
I am using MS Access 2007.
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 am using MS Access 2007.
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 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.
View 14 Replies View Relatedi'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
[code]...
Hello,
I am a Beginner in MS Access and SQL and I need a query to compare two fields.
I am having two tables; T1 and T2, and I want to compare their
fields TF1 and TF2, and display the non matching fields in new
field.
Example:
Table T1
TF1
Location Louisville
Number 555345
Name Smith
Table T2
TF1
Location Louisville
Number 553450
Name Smythe
Thanks in Advance.:)
I have a database with a pre-surgery field and a post-surgery field. The use must enter the current procedure in the pre-field and then the actual surgery in the post field. I need to check to see if the fields match verbatim. The field is a memo field.
The users know how to copy and paste from the pre to the post fields. 90% of the time it is the same but not always.
Can anyone provide some help?
Thanks,
Deborah
I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?
Any help would be greatly appreciated.
Hello,
I have 2 fields that are supposed to have the same information for each record. I got the information from different sources, which is the reason for having 2 fields. I wanted to make sure that the information was accurate. Turns out, that some of it isn't accurate--all the fields don't match.
Here's a little example of what I have:
|___TITLE___|___ISSN 1___|___ISSN 2___|
|__TITLE 1__|__12345678__|__12345678__|
|__Title 2__|__22224444__|__23224322__|
|__Title 3__|__98765432__|__98765432__|
As you can see, the ISSN numbers for Title 1 and 3 match. There's no problem there. I want a query that would list all those like Title 2, where the ISSN numbers don't match. Then I'd have a list of all of the problem Titles, and I could look up the real ISSN numbers.
Thanks
-Siena
The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.
Table_1 with below column
Mode
6
6
6
6
6
4
4
Primary KEY
0001
0002
0003
[code]....
Initially the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,
However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.
Once I have sorted this out, I would like to achieve this as the calculated field
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
how I can do this?
It has been a while since I last used Access, recently I've used mySQL and PHP.
First a little description of what I'm trying to accomplish:
I have three tables...
tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025
Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.
tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025.
Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.
tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025.
Note: the fields a00# have the datatype set to "yes/no"
What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .
Here is some dirty pseudocode:
if i001 = 001 then
INSERT 1 INTO tblResult
else
INSERT 0 INTO tblResult
The above example gets a little repetitive since I would have to do that for each question.
Is it possible to put the answer fields of each table into a recordset then compare them?
(I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)
Alternativly, can I accomplish this using only SQL statements?
What would be the best way to attack this?
Any suggestions (or alternate suggestions) would be greatly appreciated.
Thanks,
salmonman
Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks!
P.S. Sorry for my poor english. It's not my native language.
Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?
SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");
query is refering to 2 tables
Table_1 with below column
Mode
6
6
6
6
6
4
4
Table_2 with below column
Type
06
GL
PL
04
16
Both tables are joined, and the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
I have tried below, but doesn't work.
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.
Ideally would like this
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
or
if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
I have form where some or more field exist. I want to compare two field as :
1. cboBatchID As Combo box
2. txtBillNum As Text Box
Private Sub cboBatchID_AfterUpdate()
If Me.cboBatchID.Column(4) <= 0 Then
Me.txtBillNum = 1
Else
Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
End If
End Sub
I mean, if cboBatchID.Column(4) <=0 then txtBillNum start from 1 automatically or cboBatchID.Column(4) >=0 then txtBillNum = cboBatchID.Column(4)+1
I'm already trying with the code above. But does not work. Generate run time error.
I have a form control with the input mask on it for a phone number. I am using an append query to append the information entered in the form to a table. I need to pull two fields from a linked table from another database. What I need to do is be able to compare the entered phone number in the form with the phone number from the table. The problem I am running into is that it won't match the phone numbers. The same mask is used in the table I am pulling from. Why can I not just put =[Table].[Phone_No] in the Criteria of the field where the number from the form is placed so that I can get the right record from the linked table?
View 4 Replies View RelatedIn my table I have the following 4 fields with the associated date field:
Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015
I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?
=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))