Hi Everyone
I have been working on an access 2003 database for four weeks now and have been asked to create a field in a table that allows a user to select mutliple entries from a list.
This information in this field of a table needs to be able to be queried and reported on in written and graphical reports.
I have tried and hit a brick wall any help with setting this up would be most appreciated.
Thanks
Mish
Hi Everyone
In Access 2007, we are able to use a multivalue field, however I don't see the functionality for it anywhere, anyone else notice this??
Hi Everybody,
I've a question that is teasing me. How would I create a multivalue/multiple answer field in access 2002. P'se forgive if this question has been asked before. Thanks in advance,
Samia
I have two tables "Tab_Issue_1" and "Tab_Issue_2". Tab_Issue_1 has two fields "Issue" and "AssignedTo". Tab_Issue_2 has the same fields. However, the "AssignedTo" is a multi-value field in both tables. I want to append data from Tab_Issue_1 into Tab_Issue_2. I use the following SQL but it pops up this message "An INSERT INTO query can not contain a multi-valued field".
[SQL]
INSERT INTO Tab_Issues_1 ( Title, AssignedTo )
SELECT Tab_Issues_2.Title, Tab_Issues_2.AssignedTo
FROM Tab_Issues_2;
[SQL]
I've been doing some reading on multivalue fields and it seems to be exactly what I'm looking for. My question: Is there a way to use the multivalue field without making it a lookup? I need the user to be able to put multiple values in a field but can't limit them to a list. The field that needs multiple values happens to be for SKU numbers from a large department store franchise.
One work around I've come up with is to have a pop up that asks for the separate sku numbers so they can be used for the value list each time, but then the user also would have to select those from the value list in order to enter it.... too many end user steps.
I want to avoid making 10-15 different fields each for SKU number, Item, and cost... I also need to avoid having to populate my db with the department store's full list of products...
I have a multivalue field containing values of A,B,C and D with primary IDs of 1,2,3 and 4 in that order.
If I was to run:
Code:
UPDATE Table
Set Table.Multivalue.Value = 1
WHERE Field 1 = "True" AND Field 2 = "True";
Then run
Code:
UPDATE Table
Set Table.Multivalue.Value = 2
WHERE Field 1 = "True" AND Field 2 = "True";
Will I have a Multivalue field as A,B or just B? I would like it to be A,B but I have my doubts as to whether this would work.
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule
FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value
WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
Hi
I'm adding to a database that someone else set up. I went to the Design View page and clicked on one of the buttons at the bottom to add a text box etc etc. It all looks fine, but the field doesn't seem to be active - if I add some text into it for one page of the databse, the same text appears on every page of the databse. I notice that my "new field" is not listed in the fields list. How do I add it? I keep looking through "The Missing Manual" but without spending 3 days reading the whole thing (which I don't have time for) and learning much much more than I need to know just to get to the part that I need to know, it isn't helping me much.
Please can anyone enlighten me? My email is stu_paranormal@yahoo.co.uk
Thanks!
Stu
A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values
View 3 Replies View RelatedI'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.
So I have Table A:MemberID (autonumber, Primary Key)
NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level
I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:
TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8
So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.
Table B: I want a list of "friendly name" for the different training levels.
TLID (Number, No duplicates)
TLFriendlyText (Text)
So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.
Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].
How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.
Hi there anyone and everyone.
My problem concerns importing a date from a txt file. I've been doing this for years with '97 and 2003 with no problem, but with 2007 I am now getting import errors which I can't resolve. The files, either fixed width or delimited, contain date information in the format ddmmyyyy (without any date separators). After importing I receive an import error message, and the relevant field in the table is blank.
In Access '97 or 2003 I can import date information directly into an Access date/time field by using an import specification where I have set the date delimiter to blank (by default this is set to "/" , so I simply delete this to leave the box empty). Using this method a file containing "25122007" (without the inverted commas) will be imported into Access as a date, and can be subsequently manipulated as date without having to resort to any fancy reformatting tricks. Obviously the import will not be sucessful if the date delimiter in the import spec is left as "/", or set to anything else, eg "." or ":".
But now that I've updated to Access 2007 this is just not possible no matter what I do or try. I can only import a date if the txt data has a separator, and that the same separator is set in the import specification.
In some new databases I have done a couple of workarounds, importing it as text into a text field, then using a CDate(Format(CLng([date]),"00/00/0000")) type expression in a subsequent query, but this is a pain having to do it, a really backward step.
But the real pain is older databases ('97 or 2003) which have been converted up to '07; they no longer work, and there is a lot of redesign to be done unless somone can tell me what I'm doing wrong.
Unfortunately I am not able to change the format of the dates in the data I receive, so the solution has to come from my end (ie Access). Surely Access 2007 should be able to recognise 25122007 as a date? All ideas gratefully received.
I've been happily using various versions of Access since 1994 and I've learnt lots along the way, and have always previously been able to sort out problems that I've come across without resorting to a forum, but I'm now really puzzled by a problem that I have come across in Access 2007. So although this is my first post I certainly wouldn't call myself a newbie. Hopefully I have described the problem accurately (it is realy simple in fact), but very happy to provide more information if required.
Many thanks (in anticipation)
Benjy
I'm using Access 2003.
if I create an Import Specification, how do I add an extra field in the middle of the field list? All I can see to do is re-enter all fields from the new field downward.
When I export a report to Excel, a memmo field is truncated to 256 characters. If I export the query behind the report, the memmo field is exported correctly. Is there a way to export an Access 2003 report to Excel and maintain all of the data and report formating in memmo fields?
View 1 Replies View RelatedHello,
I have installed Microsoft Access 2003 and Microsoft Access 2003 Developer Extensions. I can create Access application by using the Package Wizard.
But after I upgrade from Microsoft Access 2003 to Microsoft Access 2007.
I can't use the Package Wizard.
How can I do to solve this?
Thanks.
I have two database applications and they are:
- the (A) application is for administration use.
- the (B) application is for normal users use.
the idea is that: I made the (A) application for administrators who have full control over the database objects (tables, forms, queries, and so on ...).
the (B) application I have created for normal users who will have only to use forms to insert some data and display data only.
but the two applications has a respective table called "vacation request" table. where I linked them, so the both administrators and users can share the data.
The real question is that: How can I prevent the users from seeing the database objects in their application. I used the database options which have helped me in hidding the database objectives when the users open the application, but unfortunately they managed to access to the database objects by pressing the special keys.
I would like to have an access to the (B) application when I want to make some modifications to the forms and then lock it from users where they only have to use the forms for requesting vacations and view the vacations.
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');'
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
I am working on a massave aplication that has been running in Access 2000, but recently several of the file sharing users have installed Access 2003 because of the limited availability of Access 2000. All the users are using the same file off the server.
The problem we are having is that when we reference a subform in the "[Forms]![FormName]![SubformName]![FeildName]" Access 2003 does not recognize it and returns an error. I have found that if I will modify it to "[Forms]![FormName]![SubformName].[Form]![FeildName]" it is recognized in both 2000 and 2003.
To try and change every instance of a subform reference will take forever and I am garuteed to overlook something. We reference subforms all over our program, missing any one of them would be a disaster. Before I went to the tedious task of looking through everything I just wanted to throw the situation out there and see if any of you had any great ideas on how to get it fixed efficiently. I would apreciate any ideas.
In my table, I setup a multivalue field. In the screen, I can check off more than one selection. It stores the selections in the table as value1, value2, value3, etc...which is fine. However, when I go back to review the entry in a report or a screen, it displays it exactly the same as it is stored in the table - value1, value2, value3, etc...Because of size limiations, the field is only so wide, thus it only shows value1, va...and then the rest cut off.
Regardless of how wide I make the field, all of the values will not be displayed, plus, if there is only one value, it will be a waste of space. What is the best way to display one (if there is only one selected) or all of the values selected ? Is there a way to display then vertically, like value1, (next line) value2, (next line) value3 ?
I upgraded a 2000 db to 2003 recently. When I ran the function below it gave me an error on the line in green. Any thoughts? Thanks.
Const FIRSTROW = 13
Dim wsp As Workspace, dbv As DAO.Database, tblResolve As DAO.Recordset
Dim tblVchs As DAO.Recordset, tblImpTmp As DAO.Recordset
Dim ObjXLApp As New Excel.Application
Dim FileToOpen As String, WhereCriteria As String, ImportTemp As String, tmpVch As String
Dim TotalDupes As Integer, TotalGood As Integer, TotalRejects As Integer
Dim TotalBlank As Integer, R As Integer, C As Integer
Dim BadSheet As Boolean, ImpFail As Boolean
Dim tmp, tmpType, tmpCtr, tmpRsn
If Forms![Import Block Vouchers]![Import Program] = "0" Then
MsgBox ("You must select a Program to load.")
ObjXLApp.Quit 'Exit from MS Excel
Exit Function
End If
I have an Access DB that I created in Access XP. It works perfectly when used in Access XP. When used in an Access 2003 environment some of the functions don't work anymore. I have users who use Office XP and users who use Office 2003. Does anybody know how to make this DB work in Access 2003 without having to do wholesale code changes?
I have already changed the security to "Low" to bypass the security feature built in in Jet 4.0 but it didn't help. I might have to uninstall Windows XP SP2 to make it work because I know that the application works in an Office 2003 environment with Windows XP SP1 only. But I'm trying to avoid having to uninstall XP SP2 because it's a pain. Any ideas would be greatly appreciated.
Thank you.
My workstation environment is as follows:
OS: Windows XP SP2
Office: Office 2003 SP1
Jet: 4.0
Raneil
Hi,
I've created a database in access 2003 with the default file format being Access 2000. Everything works fine on my computer and many others, all of whom are running different versions of access (2000, 2002 & 2003).
I have not had any problems with this database until recently and in the last few weeks we have had 2 users, both of them being Access 2000 users who have had the same problem.
The problem they are having is that when they click on any of the switchboard items, the buttons depress an then come back up again when the cursor is moved away, but nothing else happens - no action, no error message - nothing!!! Its the same for all of the buttons on the switchboard.
Does anyone know what is causing this? I have another computer with Access 2000 on it and this works fine, as it does on several other computers runiing Access 2000, I just cannot understand why it is not working on these 2 particular machine.......
Many thanks in anticpation.
Steve
I have recently installed access 2003 w/access 97 installed both are on my hard drive. When I create a new db in access 2003 it saves it in an access 2000 format. Does any one know why it does this?
Thanks--Appreciate any help
i am trying to use access 2000 database onto to access 2003. When i try to click design mode to change something it gives me this message:
You dont have permissions to modify.
If i run it on access 2000 it runs fine.
Someone please help.
Hello,
I have two questions related to deplying an Access project I have been working on.
1. I started developing the project in Access 2000, but midway upgraded to Office 2003 and so the remainder of the project has been developed in Access 2003. Things seem to be fine, except that in the top most title bar, after the project name, there appears the following text:
xxxxx: Database (Access 2000 file format)
where xxxx is the name of the .mdb file.
Why is this appearing and should I have done something or do something to make it a Access 2003 format?
2. I am at the point of deplying the db to a small group of users (about 5). Some of them may have Access 2000 or Access 2002. Should I be doing something in particular to make sure things work in their machines.
Any pointers to what may be problems or what I should do will be much appreciated.
Thank you.