	AllExperts > Experts
	Search

About Scottgem
(Top Expert on this page)

Expertise
I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience
I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

You are here: Experts > Computing/Technology > Business Software > Using MS Access

Questions Answered By Expert Scottgem
In Category Using MS Access

Subject	Date Asked

joint queries	11/17/2008
Q: I would like to ask the question about jointing queries for instant: if i have 2 queries like this ... A: This would be easier in a report. But if you want to do it in a query: SELECT UCase([Ingredient]), ...
Creating append query to remove duplicate records	11/17/2008
Q: I believe its simple but I am missing a logic somewhere. I have an Access database which I use to ... A: Like I said, all you need to is set the Order_Number field to No Duplicates. When you try to run an ...
joint queries	11/17/2008
Q: I would like to ask the question about jointing queries for instant: if i have 2 queries like this ... A: Are these tables or queries? If they are tables this is not good design. You shouldn't have separate ...
Creating append query to remove duplicate records	11/17/2008
Q: I believe its simple but I am missing a logic somewhere. I have an Access database which I use to ... A: That doesn't quite answer my question. However, if you are appending records to a table and you ...
future value function in access	11/17/2008
Q: How do you do the future value function that you normally use in Excel but in Access 2007? A: The following is from Access VB help: FV Function Returns a Double specifying the future value ...
Ms-Access Database connectivity	11/17/2008
Q: I am using Ms-access 2000.I created a table in Access.I have to access table fields in ms-access ... A: Why do you need to use ADO? I'm not clear what you are trying to do. Do you just want to create a ...
If statements	11/17/2008
Q: I am trying to group a bunch of names and call it Other holds her is my if statementHolds: ... A: Try IIf([HOLD_CODE] = ("PH") Or [HOLD_CODE] = "BMU",'OtherHolds',[HOLD_CODE]) Since you are ...
project	11/15/2008
Q: I am making a questionare about student's school problems,in which questions about different type of ... A: First you create a query that sums the answers by problem type. Next you join that query with the ...
Referencing Query Data	11/14/2008
Q: I have a table of an equipment inventory. I want to generate a report from a form I;ve also ... A: You don't generate a report from a form. You can use a form to enter criteria for a query that will ...
query	11/14/2008
Q: There are two tables and a form, when the user types in the number into the text box and choose the ... A: At some point, maybe when you call the form or do the search, you need to test (using a Dcount) ...
no record returned	11/14/2008
Q: i need to find out how to show a msg box if a record is not returned from a paramater query. all is ... A: I almost never use paramter prompt queries because they are so limiting. This is one example. What ...
How adding (personal) photos to Ms Access	11/13/2008
Q: I make a table which is having, ID Num, Name, Date of Birth and ect... in this table i want to add a ... A: The best way is to add a text field that contains the path to the image file. You then use an ...
counting number of c's I's and n/a's	11/13/2008
Q: I found a access survey database that Im using called at your survey. I remember you said to change ... A: Ok, the key here is tblReponses. When a user fills in a survey. the first thing they do is create a ...
Entity relationship design in Ms Access	11/13/2008
Q: sorry for my English, I am not a native English speaker.I am trying to simulate citizens of a ... A: That's fine. Its a one to many because one country can have many citizens. Referentail Integrity ...
Unique sequential numbering in Access 2007	11/13/2008
Q: I am a access newbie (1 month) and have created a database that tracts our staff members that apply ... A: Either you are misunderstanding what you have read or you need to use different sites. Every Access ...
Password protecting an access button	11/13/2008
Q: I need a way to simply password protect a button on the form I use as my front page via input box ... A: In the On Click event of the button use code like: IF InputBox("Enter Password") = "password" Then ...
Unique sequential numbering in Access 2007	11/12/2008
Q: I am a access newbie (1 month) and have created a database that tracts our staff members that apply ... A: Why do you need a sequential number? what you should be doing is have an autonumber field as your ...
Search Function for a subform	11/12/2008
Q: What is the best way to search for data in a subform? I have a simple database that logs calls and ... A: Add an unbound textbox to your MAIN form. Make the subform bound to a query. In the query set the ...
format two tables in same report differently	11/12/2008
Q: I am a novice in Access and was hoping you could help. I have a Union query that returns calculated ... A: Access uses a "banded" report writer. Each section in Report design is considered a band. By default ...
format two tables in same report differently	11/11/2008
Q: I am a novice in Access and was hoping you could help. I have a Union query that returns calculated ... A: First what you describe in Design view is SOP. There is only one set of controls to represent the ...
File Browse Feature	11/11/2008
Q: I noticed that this question has been asked before and you directed the person to the API website ... A: First, the code doesn't go behind a button, it should all go into a global module. The only thing ...
Access 2007 - Linking data	11/10/2008
Q: Having an issue with trying to link data and then closing the original form. I have the following: ... A: So you are creating a new record in FRM_SALES? If so, you can pass the ECID in the OpenArgs argument ...
Access 2007 - Linking data	11/10/2008
Q: Having an issue with trying to link data and then closing the original form. I have the following: ... A: Yes it does make sense. First, you do not have fields on a form. Fields are pieces of data in a ...
setting a permanent color for a datapoint in ms access bar graphs	11/10/2008
Q: i have a bar graph on a report. i need to know how to set the color for the different data points ... A: I wonder if you can use conditional formating here. In the settings where you select the color for ...
Cross-reference lookup	11/7/2008
Q: Scott, I am working on a proposal for changing our office from Excel spreadsheets to Access for a ... A: First create a query using the Query Wizard joining the two tables on Account and Line. Test that ...
Cross-reference lookup	11/7/2008
Q: Scott, I am working on a proposal for changing our office from Excel spreadsheets to Access for a ... A: I'm not quite picturing this. Are you saying that the BudgetID field is the primary key of the ...
MS Access 2007	11/7/2008
Q: Is it possible to align the items in the group header with the items in the detail section on a ... A: Yes it is possible. I don't have Office 2007 here, But in previous versions, there is a Block style ...
Using API code for retrieving user names	11/7/2008
Q: I took the code from that MVP access site to capture a users network ID but I am not sure exactly ... A: Functions are used in Access the same way whether they are built in or custom. Since a function ...
How to calculate negative value in MS Access	11/7/2008
Q: I'm trying to build an inventory database, which calculates Stock In and Stock Out to display ... A: I wouldn't use any formatting. Your query should be used as the source of the current stock, using ...
Updating a field sequentially not using Autonumber	11/6/2008
Q: I have a database with 121,888 records each with a unique ID number (that is not a primary key). I ... A: You add a new column with that expression, then set it to Append to the ID column of the main table. ...
Updating a field sequentially not using Autonumber	11/6/2008
Q: I have a database with 121,888 records each with a unique ID number (that is not a primary key). I ... A: This would have been easier before you appended the data. I would do it this way: Run a Make Table ...
Password Protecting buttons in Access 2007	11/6/2008
Q: I have some sensitive data on forms inside my database and I want to protect the buttons on my ... A: You create a table like so tblUsers LogonID (PK) AccessLevel You assign an access level to each ...
Password Protecting buttons in Access 2007	11/6/2008
Q: I have some sensitive data on forms inside my database and I want to protect the buttons on my ... A: First, let me state that Access is not a very secure platform. A reasonably knowledgeable Access ...
MS Access 2003 Query	11/6/2008
Q: Scottgem, I was hoping you could give me some insight on a query I need to make in Microsoft Access ... A: Why do they have text datatypes? That's a dumb way to set things up. That means you can never sort ...
MS Access 2003 Query	11/6/2008
Q: Scottgem, I was hoping you could give me some insight on a query I need to make in Microsoft Access ... A: Firdt you need to understand how Access stores dates. Access stores date/time values as a double ...
How to calculate negative value in MS Access	11/6/2008
Q: I'm trying to build an inventory database, which calculates Stock In and Stock Out to display ... A: Hmm and you are saying that Sum(Nz([ActualStockIn]-[ActualStockOut])) AS CurrentStock returns a 0 ...
Password Protecting buttons in Access 2007	11/6/2008
Q: I have some sensitive data on forms inside my database and I want to protect the buttons on my ... A: Are you using Switchboard Manager? If so, it gets more complex. To do this with static buttons is ...
MS Access calculation	11/5/2008
Q: I would like to convert units of the ingredients to calculate the food cost. I still stuck on the ... A: You can join the conversion table on the unit so only the matching record gets converted. Or, if ...
MS Access calculation	11/5/2008
Q: I would like to convert units of the ingredients to calculate the food cost. I still stuck on the ... A: Ok, What you need is a conversion table. For example: from KG to G would be 1000. You then divide ...
Finding total amount of C I and N/A in form	11/5/2008
Q: I have a simple access table that has 68 questions that are to be answered using the drop down box. ... A: The problem here is in your design. Your database is not normalized at all. Specifically, you are ...
Access form design limitations	11/5/2008
Q: I am new to building databases and would like to know how to expand the length of a form in Access. ... A: The problem here is not Access's limitations but your design. You should NOT be trying to duplicate ...
RE : Sales form that records trans and decrements inv	11/5/2008
Q: I have a simple database that has the following setup tbl_inventory ID PK auto UPC Qty ... A: First, you should NOT be storing quantity on hand. As a general rule we do not store calculated ...
Email macro	11/4/2008
Q: I have a form which has the email report button and it works fine. The report is based on a query ... A: You can reference the value of a control on a currently open form using the syntax: ...
min function across columns	11/4/2008
Q: I am using access to create transfers from a warehouse to a seven stores. I have a query with an ... A: That's not the correct function. I'm not clear what you want. Do you want to assign what store1 has ...
Email macro	11/4/2008
Q: I have a form which has the email report button and it works fine. The report is based on a query ... A: Generally I don't use macros, they are very limiting. But I believe you can do this. In the macro, ...
How to get a list of saved queries in a form	11/4/2008
Q: I have made a form to appear on startup rather than the database window, as it makes it easier for ... A: 1) You missed the part about using the After Update event. Yes, just selecting the value does ...
syntax error (missing operator) in query	11/4/2008
Q: I have included the code below... the message box where the error popped up pointed at the WHERE ... A: That's good Type Mismatches are easy to deal with. Text strings have to be surrounded by single ...
Using a pop up box to alert the user	11/4/2008
Q: I have a database that includes quite a lot of dates. I would like to create a pop up box that ... A: Need more details of the workflow here. One example would be to have a query that is set to filter ...
How to get a list of saved queries in a form	11/4/2008
Q: I have made a form to appear on startup rather than the database window, as it makes it easier for ... A: First, I would not allow users direct access to tables. That is a BIG nono. Access to data should ...
Split DB has slow response	11/4/2008
Q: I have split my DB into FrontEnd on two user PC's linked to tables in a Backend on a local server. ... A: I think the problem is more likely in your network. I've had many Access DBs with greater loads ...
Reporting Multiple Values from one Table	11/4/2008
Q: Greetings, I have a large table imported from excel. I imported it so I could sort it without doing ... A: Yes, what you are looking for is a Group By query. In Query Design mode, press the Sigma icon on the ...
syntax error (missing operator) in query	11/4/2008
Q: I have included the code below... the message box where the error popped up pointed at the WHERE ... A: At least part of the problem is you need to end your statement with a ;. You also need to look at ...
validation	11/3/2008
Q: I have two tables like the following: table1 id variable 1 speed 2 distance 3 acceleration table2 ... A: If there was only 1 allowed value per ID this could be done in a query easily. But since there are ...
Pasting Excel columns into existing Access database	11/3/2008
Q: I've got an Access database that has numerous columns and approximately 14,000 records. I've taken a ... A: First, you link the spreadsheet table to your Access Database. From the Tables tab of the database ...
Making the date criteria for a query optional	11/3/2008
Q: I am trying to put together a search form in Access 2003. I've got a form and a subform linked to a ... A: I disagree. You don't need a subform for this. I just tested this to make sure. I created a ...
Making the date criteria for a query optional	11/3/2008
Q: I am trying to put together a search form in Access 2003. I've got a form and a subform linked to a ... A: Did you try looking up Filter by Form in Access Help? It should explain how to use it. If you are ...
Determining Overtime hours on multiple shifts	11/3/2008
Q: I'm having trouble getting Access to figure out when overtime occurs for a bunch of S.W.A.T. team ... A: Part of your problem is that I suspect you are storing only the time in your field. You should be ...
MRP in an ACCESS database	11/3/2008
Q: I'm helping a student in the Operations Mgmt Class I teach to create an MRP scheduling database ... A: What you are asking for is a VERY complex set of relations. I would have to spend some significant ...
Error on Form when using the sub form	11/3/2008
Q: I have a form with transactions then I made a sub form that is related to the main form with the ... A: Can you send a stripped copy of your db to dafiles@optonline.net. I'll try to take a look at it. ...
MRP in an ACCESS database	11/2/2008
Q: I'm helping a student in the Operations Mgmt Class I teach to create an MRP scheduling database ... A: Actually this would be better using a combination of MS Project and Access. The scheduling is a ...
MRP in an ACCESS database	11/2/2008
Q: I'm helping a student in the Operations Mgmt Class I teach to create an MRP scheduling database ... A: No, each workcenter shouldn't be its own table if the information being stored is the same. One ...
access	11/2/2008
Q: I am using insert into...values. The table has 5 fields, studentNo, X_Module, AssessmentType, ... A: First, I do NOT recommend using composite primary keys. They just cause more headaches then they are ...
how to copy records ...	11/2/2008
Q: I have several .mdb databases containing the same kind of data. There is an .mdb for every country. ... A: So create a new master with a field to indicate the country. Then link to each country's table ...
docmd.transferdatabase followup	11/2/2008
Q: Here is the question we were working on: Subject docmd.transferdatabase QuestionI am using ... A: First, You might be less confused if you made sure you understand how the various commands work. No ...
docmd.transferdatabase	11/2/2008
Q: I am using DoCmd.transferdatabase to replace some of the tables in Database2 with the tables that ... A: You can use transferdatabase to create a link to a table in an external Access file. You can name ...
Error on Form when using the sub form	11/2/2008
Q: I have a form with transactions then I made a sub form that is related to the main form with the ... A: Double check that there is no generated code in either the main form or the subform. Make sure the ...
Grouping in Access Reports	11/2/2008
Q: I am creating an access report that will group dates based on academic year (7/1/08-6/30/09). I ... A: What I think you need is a function that will return the academic year. I would use something like ...
editing listbox	11/1/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: There is no coding that you would need to do with my suggestion. The listbox wizard would ...
MS Access 2003	11/1/2008
Q: How can I force an idle user out of a multi-user application? I know how to do so with code running ... A: I think the first function in this sample app is what you need: ...
Treeview to open forms	10/31/2008
Q: Sorry Scott.I am resubmitting my Post hoping that you can help me. This is the web site from which I ... A: I think the issue is in this line: Call OpenmySSA21 This is calling a module named OpenmySSA21. ...
filter data	10/31/2008
Q: I have a table of data that has dollars and account numbers. I want to sum up the # of dollars for ... A: Use a Group By query. In Query Design mode, you can press the Sigma icon on the tool bar. This adds ...
Making the date criteria for a query optional	10/31/2008
Q: I am trying to put together a search form in Access 2003. I've got a form and a subform linked to a ... A: I would recommend using the Filter By Form feature instead of creating your own search form. Filter ...
Fill Current Record with data from previous record using VBA code	10/31/2008
Q: Sir, I am Dhandapani working in Central Silk Board, Bangalore. The ms access form contains one to ... A: Can you give me the structure of the table bound to the subform? Without that, I can only be ...
editing listbox	10/31/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: If I understand you, each of the three listboxes is a filtered view of the same table, depending on ...
MS-ACCESS merging	10/31/2008
Q: , yesterday i had asked u about the merging; actually my question is we make different forms in ... A: An Access file is a container for several different types of objects; tables, forms, reports, ...
Printer Properies	10/30/2008
Q: I have a print button on a form that uses the default printer. How can i make that button use a ... A: Though this article refers to Access 2003 I think it will work for 2003 as well. Give it a try: ...
Disable the mouse wheel	10/30/2008
Q: I have a form that has 3 subforms imbedded. When a user uses the mouse wheel to scroll to the bottom ... A: Scrolling thru the records of the subform shouldn't create a new record on the main form. But you ...
Calculating Stock Levels	10/30/2008
Q: I am trying to calclate stock on Hand by deducting Sales from Purchases. I have a transactions table ... A: Dont use DSums first of all. What you should be doing is have a Quantity on Hand query. In your ...
replica design master changes	10/29/2008
Q: but some thing i would like to know : 1- when i add the new table and link it to the master design : ... A: I've never actually used replication. So I'm not the best person to ask on how it works. I suggest ...
duplicate body controls into tabbed controls	10/29/2008
Q: I have been working on some controls in the body of the form and added some codes to the fields. ... A: Moving onto a tab can sometimes be tricky. I would Cut and Paste, making sure you are in the Tab ...
Siingle Records	10/29/2008
Q: New Access User here - so I apologize for any absurd questions in advance. I've heard this is a ... A: Yes you can do what you want. In fact the Command Button wizard has a choice that create a button to ...
Access Help Please	10/29/2008
Q: Basically I am trying to design a database that when data is submitted from a form which asks ... A: Sorry, but your design is not the best approach. One of the problems is the use of Option1, Option ...
DSUM	10/29/2008
Q: When using DSUM to calculate the cumulative %yield when flowid is equal, the function is giving an ... A: The DSUM adds the value of a field in all records that meet the criteria. If your criteria is the ...
How to make runtime.	10/29/2008
Q: There is a way to make a .EXE file but it is rather advanced. I did it back in MS Access 2000 with ... A: It sounds like you are quoting what someone else said. If that's the case, that someone was wrong. ...
Storing calculated dates	10/28/2008
Q: Scott: I am working on upgrading an employee audit DB (Access 2000) for my office. It uses ... A: You are correct storing the NEXT eval date is bad design. However, having a history record of evals ...
DSUM	10/28/2008
Q: When using DSUM to calculate the cumulative %yield when flowid is equal, the function is giving an ... A: That would make sense. Look at it this way. Lets say you have 5 different FlowIDs. Within each ...
transferdata/import data	10/28/2008
Q: I have two questions it would be greate if you could answer both. Question1 Is ther another way to ... A: So you are using a temporary local table for performance gains. What I suggest is to have an ...
transferdata/import data	10/28/2008
Q: I have two questions it would be greate if you could answer both. Question1 Is ther another way to ... A: Again, I need the answers to those questions first. Why do you have two tables? How do you ...
Module not found in MS Access	10/28/2008
Q: How does one "clear the break" as you referred to in previous module question reply. I keep trying ... A: When in the VBE there is a stop button (icon of a square) that stops the currently executing code. ...
transferdata/import data	10/28/2008
Q: I have two questions it would be greate if you could answer both. Question1 Is ther another way to ... A: 1) The question isn't clear. Once a data file is linked within Access, then the table can be used ...
Access 2007 - Click button to show/hide footer	10/27/2008
Q: I have a form that has a subform on the footer and I want users to be able to show/hide the footer ... A: This is a code snippet I would use in such an instance: If Me.cmdSubform.Caption = "Show" then ...
Headers in Reports	10/27/2008
Q: I am running a report that organizes customers by weeks over. Meaning how many customers are over 3 ... A: You need a column in the Recordsource of report that indicated the number of weeks over for that ...
Expression results into table	10/27/2008
Q: I am aware that it is almost always incorrect to store expression results in a table. However, I ... A: I'm not following you. Forget forms for a moment, you are really dealing with tables. Sounds like ...
Table/Form Linking	10/27/2008
Q: I am concerned with a relationship between two tables in my database. Currently I have the tables, ... A: The process assumes you are going to open the second form with the current record shown on the first ...
no records generated with criteria	10/26/2008
Q: I have imported comma delimited data into a table from the real estate assesors office. I have all ... A: Try using a criteria of LIKE "1105". If that works, then there may be extraneous spaces stored in ...
Using Dmax in query	10/26/2008
Q: I m trying to use Dmax function in a query, when the criteria part is referring a field from the ... A: Sure it can if you do it right. You need to concatenate the field name to use its value. ...
Get a yearly total to reflect in a report.	10/24/2008
Q: I have a simple database I created in Access 2000 for generating expense reports. Every month my ... A: Ok, then you will need to add a conrol on the form to enter the total budget. You will also need to ...
Querry Reporting operations	10/24/2008
Q: I am wondering if i can do some arithmetics/logic operations on a report generated via a querry. In ... A: I'm sorry, but I'm not quite understanding what you are asking. You can use a query as the source ...
Get a yearly total to reflect in a report.	10/24/2008
Q: I have a simple database I created in Access 2000 for generating expense reports. Every month my ... A: The question here is where you get the total budget amount from. If you can explain that I can tell ...
Multi Location Inventory	10/24/2008
Q: I need to develop a Multi location inventory using MS Access. We are a retail company with 15+ ... A: Adding the ordering process is a separate piece. Microsoft has an order entry template on their WEB ...
Update query	10/24/2008
Q: I am absolutely brain-dead on how to write this query..can you help? Using the example below, I ... A: Ok, first, I question the design of your database if its as shown. You should not be using text ...
Multi Location Inventory	10/23/2008
Q: I need to develop a Multi location inventory using MS Access. We are a retail company with 15+ ... A: Ok, First the database is fairly simple. Basically three tables: tblProducts ProductID (Primary Key ...
Multi Location Inventory	10/23/2008
Q: I need to develop a Multi location inventory using MS Access. We are a retail company with 15+ ... A: This depends on how you will be tracking inventory. Are you going to do a periodic physical ...
STILL ATTEMPTING TO MAXIMIZE FORM TO FULL SCREEN	10/23/2008
Q: I followed advice on this site: On the form properties->Event tab click on the 'On Activate' ... A: If you remove the tabbed interface then the Min?Max buttons will not appear in the Title bar. They ...
query	10/23/2008
Q: tring to learn ms-access.I need to know how to claculate age. table name:agetable birthdate ... A: Umm No. As a general rule we do NOT store calculated values. So you should NOT even have an age ...
exporting form data to a table	10/23/2008
Q: I have a custom form created with multiple listboxes and checkboxes. I want to place a button that ... A: If the Multi-Select Property is set to NO, then you don't need that code. I'm not sure I understand ...
MS access database for dogshelter	10/23/2008
Q: I volunteer for a dogshelter in the Netherlands/Belgium. We need to create a database and have ... A: This site has free data models http://www.databaseanswers.org/data_models/index.htm You might ...
Table/Form Linking	10/23/2008
Q: I am concerned with a relationship between two tables in my database. Currently I have the tables, ... A: I do use using the OpenArgs argument of the OpenForm method. I pass the keyfield value using ...
MS Access	10/23/2008
Q: I am trying to make a "program" using ms access. I think that it can be done but I don't know. I ... A: Well here's the problem, what you are asking is for someone to give you specific, step by step ...
Calculations in Access	10/22/2008
Q: I am trying to design a restaurant stock database. I currently have a table and form for quantities ... A: You probably need to do this in steps. Generally I use a single transactions table that records all ...
Access General Setup	10/22/2008
Q: Can I use access to manage expenses? I want to create a monthly report showing expenses for the ... A: You can use Access for just about anything. Whether its the best tool, depends on on how you want to ...
Table/Form Linking	10/22/2008
Q: I am concerned with a relationship between two tables in my database. Currently I have the tables, ... A: Ok, First, you cannot link two tables that are in a one to one relationship using autonumber PKs. A ...
Using DB Properties values in a Report	10/21/2008
Q: I have figured out how using the DAO dataset to enter and retrieve (dbs.Containers) values in the ... A: This is the function I use to return Custom DB properties: Public Function CustomDBProp(PropName As ...
Mass Email Shot VBA	10/21/2008
Q: I adapted the code as per your recommendation and got an error: Compile Error - Wend without While ... A: I only see one While and there is a Wend, so it may be something inside the loop is causing the loop ...
Mass Email VBA	10/21/2008
Q: You helped me with some issues I was having with the VBA that sends an email to many people. I have ... A: Your best bet here is to remove the BCC portion and just assign the current e-mail address from the ...
Running Macro in Multi-User Database	10/21/2008
Q: I have a multi-user Access 2000 database that is used for data entry. There is field on the data ... A: The database should be split into front and back ends with the backend having all the tables and the ...
how to copy records ...	10/21/2008
Q: I have several .mdb databases containing the same kind of data. There is an .mdb for every country. ... A: First, I question the need for separate MDBs. This is generally not good planning. All you need is a ...
access queries	10/20/2008
Q: I have a database which I am importing to excel for calculation purposes. Now I have a few ... A: I can't imagine needing so many criteria. I suspect your database may not be normalized properly. I ...
How to retrieve the information with from two different tables?	10/20/2008
Q: I wanted follwoing information to be stored in the database. Vessel No, setup Name, ... A: Sorry, I hadn't gotten the other images. I've looked at them and they really don't change anything. ...
How to retrieve the information with from two different tables?	10/20/2008
Q: I wanted follwoing information to be stored in the database. Vessel No, setup Name, ... A: Well this depends on your table structure. You should have two tables: tblVessels VesselNo (PK) ...
access queries	10/20/2008
Q: I have a database which I am importing to excel for calculation purposes. Now I have a few ... A: You can link to an Access table from Excel. I'm not sure how the conditions are setup so I can't ...
Calculation	10/18/2008
Q: I'm designing a db of school information. I need the child's grade information. I chose to use the ... A: Sure and it should be fairly easy, but your examples confuse me. Lets say that the school goes from ...
WhosOn() - UserRec Definition	10/17/2008
Q: I like your function, but I'm getting and undefined error on UserRec. You don't show what it is. ... A: Ok, you should have included that link in the first place. Actually that is code I got from someone ...
Conditional query/process.	10/17/2008
Q: Bob, I am an MS Access beginner/novice and an intermediate+ user in Filemaker Pro. I am working ... A: FK=Foreign key No, A FK is usually the PK value of the corresponding record. So you would want to ...
update query	10/17/2008
Q: I am used to microsoft SQL server and am currently trying to get to grips with using access SQL. I ... A: I'm not sure I follow the purpose here. It looks to me like you are updating a record in the payment ...
Combining 2 files into one	10/16/2008
Q: I have two access files that list the Miles driven by state and by quarter. The problem is that one ... A: First, do you mean tables rather than files? Second, why would you have separate tables? You should ...
Conditional query/process.	10/16/2008
Q: Bob, I am an MS Access beginner/novice and an intermediate+ user in Filemaker Pro. I am working ... A: I'd need to know a little bit more about your data structure to advise specifically. The strucutre ...
Sending Data from Access to Word	10/16/2008
Q: I have some code which sends data from my database to a word documents. It uses the bookmark ... A: You can do this more easily by using this line instead of what you have: ...
Sending Data from Access to Word	10/16/2008
Q: I have some code which sends data from my database to a word documents. It uses the bookmark ... A: Sure, just duplicate what you are doing for Addr2. I'm assuming here that you copied this code from ...
Search Form in Access	10/16/2008
Q: I am new at using Access and although i've had success in setting up most of my database and forms, ... A: I suggest you use the Filter by Form feature. This feature turns any form into a query form. The ...
Auto fill	10/15/2008
Q: I have a database with several tables linked to a form with the purpose of entering only valid data. ... A: No, First, users should NEVER have direct access to tables. All interaction with tables should be ...
Protecting the table aftercreate a workgroup.	10/15/2008
Q: I have created a workgroup, I protected the forms,queries, and report, but I am unable to protect ... A: What problems? You generally have to make sure there are no syntax errors in your code before an ...
Protecting the table aftercreate a workgroup.	10/15/2008
Q: I have created a workgroup, I protected the forms,queries, and report, but I am unable to protect ... A: The way I would handle this is to distribute the app as an MDE and to set your forms so ...
What's Wrong With This Program!	10/14/2008
Q: Trying to get the count for consecutive results from Q0 to Q7 that are greater than GWPC And SWPC. ... A: Have you stepped through the code line by line to check what values are being assigned along the ...
editing records	10/14/2008
Q: Scott, thanks for taking my question. I have a table and I need the ability to look up a record and ... A: Design a form bound to the table (use the Form Wizard). When you have the layout of the form to your ...
difference between .mdb and .adp	10/14/2008
Q: What is the difference between .mdb file and .adp file ? Can multiple users use a same access file ... A: The difference is that an ADP is designed to connect to a SQL Server backend. It makes it easier to ...
MS Access: Referencing Combo Box list Index	10/14/2008
Q: I am Using MS Acess 2003 wherein I am developing a small .mdb application having single table and a ... A: First, you pass the value of the combo to the OpenArgs argument. You then test to see which value ...
Problems Splitting A Database	10/13/2008
Q: My name is Kenneth. I work for a small company and we have a Microsoft Access database. I believe it ... A: You don't want to IMPORT the table you want to LINK to it. With Access 2007, the best way is to use ...
Problems Splitting A Database	10/13/2008
Q: My name is Kenneth. I work for a small company and we have a Microsoft Access database. I believe it ... A: Before you can split a database, you need to make sure its code is clean. Press Ctrl+G to enter the ...
SetValue macro command	10/13/2008
Q: I am using Ms Access 2007. I can't find the SetValue macro command though the help file mentions ... A: Ok, you have two options. One way is to pass the value to the new form as part of the OpenArgs ...
MS Access Intersection Tables	10/13/2008
Q: I have a many-to-many relationship between two entities and hence in Access it means I have an ... A: Generally, the way I do this is with a mainform/subform setup. I will have a main form bound to one ...
Query Criteria	10/13/2008
Q: I have been looking this up for hours and haven't been able to find anything that matches my ... A: Try this: SELECT * FROM table WHERE RD_Contact = [Enter Contact] AND (MFG_Proc = 2 OR MFG_Form = 2 ...
Integrating a licensing code	10/12/2008
Q: Scott, thank you for any my previous two questions. You were a big help. My final question is in ... A: The way I do this is using an algorhythm that generates a license key based on the company name. ...
Prevent a record from being captured when insufficient stock available	10/12/2008
Q: I have designed a stock control system using action queries and macros (I have VERY limited VB ... A: Well, first, I'm sorry to say that your application is not properly designed. You should NOT have a ...
Cells	10/11/2008
Q: I know how to add another cell above or below by going to the tables menu. I'm not sure how to ... A: Access doesn't have cells. It has records and fields within a record. You can delete a record while ...
Mass Email Shot & Creation of an individual log for each email sent.	10/11/2008
Q: With reference tohttp://www.allexperts.com/user.cgi?m=6&catID=1440&qID=4699225 I used the exact Sql ... A: sSQL = "INSERT INTO ContactTracking (Applicant_Ref, Contact_name, Contact_date, Contact_type, Notes, ...
MS Access: Referencing Combo Box list Index	10/11/2008
Q: I am Using MS Acess 2003 wherein I am developing a small .mdb application having single table and a ... A: What you need to do is put an IF around the Next record code in the CMD1 module. If Sequential ...
Mass Email Shot & Creation of an individual log for each email sent.	10/11/2008
Q: I have some code which when run sends an email to all records in the current form that have an email ... A: You need to be carefule with how you concatenate. sSQL = "INSERT INTO ContactTracking ...
Could this be a memory leak?	10/10/2008
Q: I created a sales application in MS Access 2007. The sales order form has the basics in the header ... A: A public variable has global scope. Only private variables are confined to within a form. The only ...
Binding Collection Error	10/10/2008
Q: i encounter a error,when i click the combo, it shows "Field not updatable,Bound Property Name:Text, ... A: First, Name is a reserved word in Access so shouldn't be use for object names. That could be ...
MS Access 2003	10/10/2008
Q: I am buidling a database for a survey that I created for a group. A couple answers have multiple ... A: You need to use a listbox with the Multi-Select field set to Yes. You will then need to have a ...
Could this be a memory leak?	10/9/2008
Q: I created a sales application in MS Access 2007. The sales order form has the basics in the header ... A: The only thing I can think of is that the code that is populating the sales tax is getting lost in ...
Creating a database for registrations	10/9/2008
Q: I am needing to create a database for workshop registrations. The goal is to have students 'sign in' ... A: This should be pretty simple. You have a table of students with their information. Then you have a ...
boms	10/9/2008
Q: I am making a database to track the compliance of our parts. Our parts have BOM's with upper ... A: First I do NOT recommend using compound keys. Especially when they will be used for foreign keys. ...
Access Runtime Synch-ing with Original Database	10/9/2008
Q: I have created an access database that consists of forms and tables which we intend to use as a ... A: Sorry I had a brain fart its UNC coding. The format of which is: ...
Forecast Report	10/9/2008
Q: I have designed a database for forecasting monthly sales volumes. It currently works by adding a ... A: If I'm following this, I think you would be better off using a spreadsheet rather than a database. ...
One-touch replace text	10/9/2008
Q: In my query, I have the same three character string in multiple fields, which is the key to the ... A: Ah, OK you want to enter 112 just once instead of having to replace it 6 times. What you need to do ...
Prompt...	10/9/2008
Q: I am creating a database to hold information for all my employees. There is one field in particular ... A: Can you explain a litle about how probation is ended? Generally, in my experience, probation just ...
Mass Email Shot & Creation of an individual log for each email sent.	10/9/2008
Q: I have some code which when run sends an email to all records in the current form that have an email ... A: You do not need to list any fields not being populated in the fields list. In some cases you ...
Importing Data	10/9/2008
Q: I was wondering if there was a way when importing raw data, break it apart into separate fields when ... A: It depends on what format the text file is in. If there is a pattern to the data, you should be able ...
Updating fields in table from fields in linked table	10/8/2008
Q: Access 2002 question. I have a linked table which auto-updates from the contents of a folder in ... A: Using an array is a possibility but may not be necessary. Before I come up with a solution, my ...
Running Totals	10/8/2008
Q: I've seem to come to a road block and hoped you could help. Every month, I get a file which ... A: I thought it sounded familiar. What I think you need to do is add a Datesent field to you click ...
Mass Email Shot & Creation of an individual log for each email sent.	10/8/2008
Q: I have some code which when run sends an email to all records in the current form that have an email ... A: The code should append a record to tblNotepad with the current value of RecpientID (I assume this ...
Access Runtime Synch-ing with Original Database	10/8/2008
Q: I have created an access database that consists of forms and tables which we intend to use as a ... A: Did you split the database between forn and back ends BEFORE you packaged it? Did you make the ...
Rank Runners in Access 2007	10/8/2008
Q: I've volunteered to create an Access database for a Cross Country meet and I'm new to using Access. ... A: No this SHOULD be done in a query or report. You would create a query that sorts the records in the ...
access table comparision	10/7/2008
Q: I am using access i have two databases. The first database conatains values which are permitted for ... A: The unmatched wizard creates a query with an outer join. It then filters the query so it only shows ...
MS Access and VBA DB	10/6/2008
Q: I don't know whether I must use Regular expressions in MS Access/VBA to do the following: Input a ... A: Its hard to answer that without knowing the exact needs. Generally, if you just want to enter some ...
access table comparision	10/6/2008
Q: I am using access i have two databases. The first database conatains values which are permitted for ... A: You can use the Query Wizard to create an unmatched query (after linking them) This will show you ...
Access 2003	10/6/2008
Q: I am new to developing a database myself and I am trying to build a database based on a form. The ... A: First, having a lot of check boxes is generally a sign of a denormalized database. Very often this ...
Executing functions from within Access	10/6/2008
Q: I am familiar with VBE from Excel, but new to Access2007. I have converted a macro and it has ... A: You are mixing up functions and procedures. Both process data. The difference is that a function ...
Report field- separate lines	10/3/2008
Q: I have a question in regards to MS Access 2003. I am designing a report and in one of the fields, I ... A: You are going to need to parse out each name and concatenate in a vbCrLf. To do this you would ...
Refreshing subforms within subforms	10/3/2008
Q: I would be forever grateful if somecould tell me how to refresh a subform within a subform from ... A: This article should help you: http://www.mvps.org/access/forms/frm0031.htm As to when to use the ...
docmd.transferdatabase	10/2/2008
Q: I am using DoCmd.transferdatabase to replace some of the tables in Database2 with the tables that ... A: You have two choices. You can delete the relations in the relations window. the only reasons you ...
QBE using dates	10/2/2008
Q: I am new to Access and I see plenty of dates that are specific, but I want to create a QBE that ... A: DateAdd("ww",-1,Date()) Entering that as criteria in the date column will return any date matching ...
Search SQL statements	10/2/2008
Q: You helped me with some SQL VB code few weeks ago, I want to build Like into it. I have tried ... A: The WHERE clause of an OpenForm or OpenReport can use any valid SQL WHERE clause without the WHERE. ...
writing code to force user entry in a field	10/1/2008
Q: I have a button that runs a query that passes info from one table to another. I am not quite sure ... A: Add an If Statement to test the controls Private Sub cmdPasstoJMAS_Click() On Error GoTo ...
Using Dlookup in a query as criteria	10/1/2008
Q: I have the following query I am looking up field in another table to fill in the BETWEEN AND ... A: Try it this way: BETWEEN #" & DLookUp("[MinDt]","tblPHSVPCoDVM1") & "# And #" & DLookUp("[MaxDt]", ...
Using Dlookup in a query as criteria	10/1/2008
Q: I have the following query I am looking up field in another table to fill in the BETWEEN AND ... A: Your DLookups are correct syntax. The correct syntax is: DLookup("[fieldname]","tablename") So ...
Linking pictures to forms - Access 2000	9/30/2008
Q: I have a table with an OLEObject field containing a company logo. I would like that logo to appear ... A: I would recommend using a subform. Create a subform bound to the table with a control to display the ...
data field validation Access/form/subform	9/30/2008
Q: Currently I use Access form/subform to design a database interface. I have a couple of combo box ... A: You need an Else. First I would put this in the After Update, not the Change event. The change event ...
Truncate using update query	9/30/2008
Q: Windows XP;MS Access 2007.Using an update query I want to leave the left 5 digits in a zip code, and ... A: This is usually a Missing reference issue. Go into the VBAEditor (press Ctrl+G) and select ...
Form structure/auto-fill	9/30/2008
Q: I've got a question in regards to auto-filling certain fields in a form based off of the same ... A: I wrote the following article which deal directly with this issue. I'm also bothered by a talbe ...
data field validation Access/form/subform	9/30/2008
Q: Currently I use Access form/subform to design a database interface. I have a couple of combo box ... A: First a combobox is a control not a field. You don't have fields on forms, you have controls that ...
Linked combo box	9/30/2008
Q: I'm creating a from with a date control called VisitAgenda. How can I display a date picker instead ... A: First, I'm glad I was able to help, but, in the future, if you have a new question you should post ...
Ordering query results by date in a field made by the	9/30/2008
Q: I am pretty new to all this but I was wondering if you could help me with my problem on Access 2003. ... A: Actually it made sense the first time and my answer doesn't change. But let me explain in greater ...
Update Query	9/30/2008
Q: This is a SQL View of an update query that i created. Could you please help me convert this query to ... A: What do you think you need to convert? SQL in VB is pretty much the same as SQL in Access. And I'm ...
Two table values in one column	9/29/2008
Q: URGENT! Windows XP Access 2003 I have a query that pulls from four tables: Customer, Job, ... A: Are you saying that Jobs and Sales Orders are similar tables and you need to show the number in the ...
Ordering query results by date in a field made by the "Query Wizard"	9/29/2008
Q: I am pretty new to all this but I was wondering if you could help me with my problem on Access 2003. ... A: Are you actually entering a text string for May 2005? If so, that's why the sorting. I would add two ...
Data corrupting in Query	9/28/2008
Q: I didn't phrase my question properly before. Let's try this again. I have a field called ... A: Ahh, that could be the cause. There is some formatting hidden inthe text that I think is causing ...
random access tables	9/28/2008
Q: I want to randomly select a list of 2,100 names from a list of 8,000 A: Lookup random numnber in Access Help. It whill show you how to generate a random number. You can ...
Date picker	9/28/2008
Q: I'm creating a from with a date control called VisitAgenda. How can I display a date picker instead ... A: Whatever calendar you are using, the selected date is stored with the calendar. You then have to set ...
Access Data Base	9/28/2008
Q: We want many users to our data base but want to restrict information on tables etc to the particular ... A: There are a number of ways to do it, but Access is not the most secure platform. Most ways can be ...
Date picker	9/28/2008
Q: I'm creating a from with a date control called VisitAgenda. How can I display a date picker instead ... A: Access comes with an ActiveX calendar control. But I don't like it. There are several form based ...
Access 2002: Disabling fields after Combobox selection	9/27/2008
Q: I really hope you can help me. I'm under the gun to create a database to track calls to our ... A: A flag is simply a field that identifies a record as a certain type. It sounds like what you need to ...
Access 2002: Disabling fields after Combobox selection	9/27/2008
Q: I really hope you can help me. I'm under the gun to create a database to track calls to our ... A: I'm not sure if your database is designed properly. I'm bothered by the statement; " I also have 3 ...
Updating Multiple Records from a Ranged Table	9/26/2008
Q: Scott, I am having problems making a query to update an existing field within an existing table ... A: You can't do this with an Update query. What you need to do is loop through the records of table 1 ...
form with one-to-one relationship	9/26/2008
Q: I am creating a table that will go over 255 fields, and wanted to know how to break them out into ... A: I've being designing databases for over 20 years. And I've never come across a table that needs so ...
Using DSum function in a Form with Subforms	9/26/2008
Q: I have a Main form where I am calculating Total vacation time allotted (all positive #'s in [Days]) ... A: First, you don't have fields on a form. You have controls that may or may not be bound to a field in ...
linked table manager	9/26/2008
Q: Many thanks for the help in advance. I have mdb frontend, SQL backend, I am trying to update linked ... A: Ahh, I see. I've never tried to deal with a DSN on a shared drive. You might want to try using a ...
Using DSum function in a Form with Subforms	9/26/2008
Q: I have a Main form where I am calculating Total vacation time allotted (all positive #'s in [Days]) ... A: i would do most of these calcs in your queries rather than with domain aggregate functions. For ...
linked table manager	9/26/2008
Q: Many thanks for the help in advance. I have mdb frontend, SQL backend, I am trying to update linked ... A: File DSNs are local to each PC. If you change the name of the DSN, then I wouldn't use Linked Table ...
form with one-to-one relationship	9/26/2008
Q: I am creating a table that will go over 255 fields, and wanted to know how to break them out into ... A: I suggest you relook at your design. Its extremely rare that a table should that large. More likely ...
amazon price adn access	9/26/2008
Q: i have forgotten access altogether it seems. I want to create a database list that would have ... A: I think Amazon utilizes WEB services which would allow you to retrieve data from them through ...
Default date in query in access	9/26/2008
Q: In the query at Access, I type <=[Please enter today date] in the criteria, it can generate today ... A: No, using a parameter prompt is very limiting. What you can do is use a form to enter criteria and ...
Using DSum function in a Form with Subforms	9/26/2008
Q: I have a Main form where I am calculating Total vacation time allotted (all positive #'s in [Days]) ... A: What error did you get? Assuming EmpID is a number datatype this should work: =DSum("[Days]", ...
command button to populate date field	9/25/2008
Q: I am trying to write code for a button that will allow me to populate a second date field using the ... A: First, frmMain_Edit has to be open and, second, there has to be a control on that form named ...
Updating table thru OnOpen event of a Form	9/25/2008
Q: this time, i have 2 different tables: 1. A table "Currency_Master" that has two fields "Currency" ... A: I'm confused by this. If you want the Info_Master to always show the current rate then you shouldn't ...
increment item No.	9/24/2008
Q: I am implementing a quotation system, when I add items in my quote detail table, how can I get a ... A: Do you really need a sequential number? If not, just use an Autonumber field. If you musgt have a ...
Searching 6 fields all optional	9/24/2008
Q: I have had to open a new message as it disallowed me to carry on with the previous. I tried to ... A: First, Telephone numbers should be a text datatype. You should use a number datatype only for fields ...
Check box	9/24/2008
Q: I have an Access database that uses the code shown below. When a date is entered and either the 3 ... A: Well its obvious. No where in your code do you set the 12 month box to Null. But I really would not ...
Searching 6 fields all optional	9/24/2008
Q: Sorry, forgot to mention in my last message, I have a second problem which I would love to get some ... A: Lookup Filter by Form in Access Help. This is a VERY powerful feature that allows user a great deal ...
ms access audit trail	9/24/2008
Q: hey scott its me again ive used many examples for audit trails and cant seen to get any to work. i ... A: They aren't really hidden, you just have to dig them out. I'm not specifially familiar with ...
import/replace BE tables in code	9/24/2008
Q: I'm working with a split database. I'm trying to create a function that will run every time I close ... A: Nope, TransferDatabase will do both, it can import into the current or export into another file. ...
Number of characters in a Text Box	9/24/2008
Q: i want to set the max character limit to 500 for a text box. how do i do that? A: The max # of characters for a text field is 255. If you use a memo datatype the limit is 64K. You ...
Query parameters	9/24/2008
Q: I need to make a query where the parameter is choose from a combo box. My problem is in case that ... A: Ok, try using a Union query for the RowSource something like this: SELECT "*" AS fieldname FROM ...
Query parameters	9/23/2008
Q: I need to make a query where the parameter is choose from a combo box. My problem is in case that ... A: First you need to create an unbound form with the combo. Next you need to make the default value of ...
Runtime Error # 3070	9/23/2008
Q: i have the following code in place: Private Sub Destination_AfterUpdate() Dim str1 As String Dim ... A: Try changing it to this: .FindFirst "Route = '" & str1 & "'" 'this is the line where i get the ...
Searching 6 fields all optional	9/23/2008
Q: Sorry, forgot to mention in my last message, I have a second problem which I would love to get some ... A: Frankly, I would train the users to use the Filter by Form feature. That's what I do. With this ...
Multiple Selection Combo - need to select all.	9/23/2008
Q: I have a multiple selection combo on my form and I need a macro or VB code which when a cmd button ... A: This code snippet may be what you want: Me.ListBox1.RowSource = "whatever the Row Source is goes ...
Grandchild Forms	9/12/2008
Q: This is my situation. I am in the process of updating a tracking system for letters for multiple ... A: It doesn't matter, So if there are no docs, the listbox will be blank. So you have a button that ...
Grandchild Forms	9/12/2008
Q: This is my situation. I am in the process of updating a tracking system for letters for multiple ... A: You seem to need nested forms. And you can't nest a continuos form in a continuous form. What I ...
Switchboard Help	9/12/2008
Q: I already have a pre-designed switchboard with 5 options, I wish to add some more buttons to it for ... A: Sounds like the switchboard was created using Switchboard Manager. So go to Tools>Database Options ...
Adding Multiple Queries (Or a Query with multiple Tables)	9/12/2008
Q: My question is a little convoluted, at least I think so anyway. I was having an issue with a query ... A: I do NOT recommend using a multi table query as the Recordsource of a form. As you found out, too ...
Using the "on close" form property to update fields?	9/12/2008
Q: Is it possible to update a field with calculated values that are completed in a form, when after ... A: This is why you DON'T store calculated values. There is no need to do so. You can use the same ...
Tabbed Forms	9/11/2008
Q: I have a tabbed form with three tabs in it. When the form is opened it defaults such that the tabs ... A: Set the Tab order so either the tab control or a control above it at the head of the list. Or use ...
Including working days only in a query	9/11/2008
Q: I'm using an Access database to check the turnaround time between a record being completed (by the ... A: This article: http://www.mvps.org/access/datetime/date0006.htm has functions that can calulate the ...
changing records	9/11/2008
Q: I have tables Components, SalesPeople, etc. where each component/salesperson has a PK. Then I have a ... A: Simple, you never delete component records. If you establish referential integrity Access won't let ...
help	9/11/2008
Q: sir, I have created database in msaccess2000 for journals. I have created (a)Receiving date field ... A: As a general rule we do NOT store calculated values. It sounds like your database is working as it ...
Access 2007 design - Windows XP Pro	9/11/2008
Q: I have been attempting to take an Outlook 2007 contact list, maintain it as a linked table in Access ... A: First, you shouldn't be storing the pix in the Access database. So what you need is some way link ...
Group Properties	9/10/2008
Q: I have a report sorted by last and first name with a total of hours volunteered for each person. ... A: Hmmm, that could be a problem. Try changing the sort order in the sort and grouping dialog. If that ...
participant-family-attendance database	9/10/2008
Q: I need to set up a db that tracks participant attendance (day + hours)in courses. Also need to ... A: Your Attendance table appears to be your junction table for the many to many relationship. That ...
Add new field	9/10/2008
Q: I'm trying to add a new field to a form. The Datasheet view does not have "add new field. When I ... A: You have to add the field to the Recordsource first. If the Recordsource is a table, you have to ad ...
Avoiding Overlaping Dates	9/10/2008
Q: I would like to do the following, but not sure how to. Any assistance you can provide will be most ... A: Lookup Recordsets In Access Help or Looping. That should show you how to set them up. Within the ...
Avoiding Overlaping Dates	9/10/2008
Q: I would like to do the following, but not sure how to. Any assistance you can provide will be most ... A: This would be difficult. You have the right methods for 1 and 2, but 3 requires that you loop ...
Linking Tables in a query	9/10/2008
Q: I have a problem with my database and am not sure if it can be fixed. I have 3 tables (details, ... A: No, you won't be. You should NOT have a form bound to a multi-table query that will be used for ...
Linking Tables in a query	9/10/2008
Q: I have a problem with my database and am not sure if it can be fixed. I have 3 tables (details, ... A: First, you should NOT have a table that duplicates this info. You can combine the three tables into ...
participant-family-attendance database	9/10/2008
Q: I need to set up a db that tracks participant attendance (day + hours)in courses. Also need to ... A: First, I would look to see if you actually need to enter daily logs. Generally, when I do an ...
adding objects to a form	9/10/2008
Q: i would like to know how to add objects to a form on open based on parameters receive by another ... A: The Add Method differs depending on what it pertains to. Basically, it adds something to a ...
Project Budget	9/10/2008
Q: I am trying to build a tracking database for a site improvement project. Basically I need to add an ... A: As a general rule we don't store calculated values. Since balance is a calculation, there shouldn't ...
runtime and split database	9/9/2008
Q: I am creating a database in Access 2007 for a company that does not have Access 2007 on their ... A: Not only CAN you split the database, you need to. EVERY app that is multi-user or supported by ...
adding objects to a form	9/9/2008
Q: i would like to know how to add objects to a form on open based on parameters receive by another ... A: You can't. What you CAN do is create a form with a quantity of controls on them, then loop through ...
How do you run a query from a command button?	9/9/2008
Q: I've inherited an ordering system (based on the Northwind free dbase I think) which I'm trying to ... A: You can't. But there is a better way. In the code behind your button use: Me.txtInvoiceNum = ...
Table not updating from a form	9/9/2008
Q: I am having a problem getting data from a form into a table. There are basically three tables ... A: Yes, you need to change your approach here, you have things backwards. I do not recommend using a ...
Creating Query to display one column	9/9/2008
Q: In a query I have two columns, A and B. I want Access to look if A has a value, to display it. If ... A: If you ONLY want the text in front of A use A-B: IIF(IsNull([A]),[B],"Final Deadline Date" & [A]) ...
report footer	9/9/2008
Q: I have a report. In the page header I have column titles that I want to appear on all pages. In the ... A: No, what I meant is its difficult to determine if any part of the Detail band is displayed on the ...
Record update date	9/9/2008
Q: Access 2003. Having updated the data about a person I would like to click on something in order to ... A: I do this automatically. My data tables generally have 4 fields: CreatedBy CreatedWhen UpdatedBy ...
IIF statement question	9/9/2008
Q: it was a great help, I now have hit another stumbling block. I have 2 fields in my table, a: Issued ... A: I'm surprised it didn't come up with an error, since an IIF requires THREE arguments; comparison, ...
Access 2007 - 2 N00b Questions	9/8/2008
Q: I have 2 questions regarding a conference database I am working on in which we would simply like to ... A: You need at least three tables here: tblAttendees: stores identifying info about the attendees ...
report footer	9/8/2008
Q: I have a report. In the page header I have column titles that I want to appear on all pages. In the ... A: This is a hard one, since its difficult to determine if there are any records on a page or not. The ...
Group Properties	9/8/2008
Q: I have a report sorted by last and first name with a total of hours volunteered for each person. ... A: Your problem is in your structure. You should have 2 tables here. One to identify the people and one ...
Linked table limiting amount of records.	9/8/2008
Q: I have a linked table from Excel into Access. The table will not allow more then 555 records into ... A: Check to see how the table is linked. Its possible it was linked to a named range and that you ...
Caps Lock	9/8/2008
Q: I want to make Caps Lock of Keyboard "on" as I click on a button. What should be the coading. ... A: I'm not sure you can do that. If you can it would mean using an API. But if you want an entry to be ...
Access Invoice	9/7/2008
Q: I've set up an access database to deal with invoices. It simply has a form for entering data which ... A: Take a look at the Northwinds sample database that comes with Access. Or one of the order entry ...
Concatenating and Stock ID's.	9/7/2008
Q: I've been trying to create a Stock system for a small family business. I want a Stock ID field, ... A: Your friend was correct. What you are doing is creating a visible identifier from multiple values in ...
Combine Access 500 files to 1 file	9/7/2008
Q: Please help me to solve this problem. I have 500 access files which have each only 1 table and same ... A: You need to create a table of all the filenames in the destination file. Then loop through that ...
Access search	9/7/2008
Q: I want to create a search form that contain multiple textboxes and display the result of the search ... A: I would strongly suggest you use the Filter by Form feature. This feature turns any form into a ...
Format last report page differently	9/7/2008
Q: "I have an Access report of 10-20 pages. I want to suppress or hide a few text boxes in the page ... A: When you use the Report wizard it automatically inserts a page of pages label in the page footer. ...
Build a financial Databace	9/6/2008
Q: I have made 3 tables in access: members: IDmember (PK) Fname Lname Transactions: IDmember ... A: Are you asking to contract for my services in designing this database? If so, I would be happy to ...
Records in form	9/5/2008
Q: I have a table called TableConference and in form, I have created a form. In the form, the fields ... A: It sounds like you are editing an existing record instead of creating a new one. I generally use ...
adding to fields in a query	9/5/2008
Q: I would like to add two fields in a query and I want to place a / in between them. But if the first ... A: You can use an IIF to add the /. For example: field1 & IIF(IsNull(field1) And IsNull(field2),"", ...
MS Access - Formula text boxes storing results in table	9/5/2008
Q: I am designing a database for a small company that requires to store and print invoices. I have ... A: You don't. As a general rule we do NOT store calculated values. You can use