Using MS Access - Business Software - Computing/Technology

	AllExperts > Using MS Access
	Search

About Richard Rost
(Top Expert on this page)

Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site.

Experience
I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

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

Questions Answered By Expert Richard Rost
In Category Using MS Access

Subject	Date Asked

lookup equation for foreign exchange DB	11/17/2009
Q: Vlookup the currency table (name: CCY) for the text, e.g. AUD, or USD. Then return the value of the ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
database reports	11/17/2009
Q: I am having trobule linking information that is added in a form to show up in the report. Any ... A: Forms don't hold data. TABLES hold data. If your form was designed properly, it saves its data in a ...
Query Combo all values	11/12/2009
Q: I’m using a combo box for the criteria for a saved query. Is there any way to show all the records ... A: Sure, just add an IIF function to your query: ...
summing data in a ranged query or report	11/10/2009
Q: My level of expertise is intermediate and I have no programming skills to speak of. I created this ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
Converting a multiple-value field into several different fields	11/9/2009
Q: My experience level is beginner. I am using a commercial DB program that uses ACCESS as the engine. ... A: I'm not sure I understand your question correctly. If you could show me a sample of data and what ...
query to trim decimal places	11/6/2009
Q: i have a data source, EXCEL that is created with data that has literal decimal places, sometimes out ... A: You'll need to use the ROUND function to round off the number to however many digits you want, and ...
Adding to an existing database	11/6/2009
Q: Good morning Richard. I have a database that is complete in Access 2003. My boss wants to add a new ... A: Christina, I don't understand the context of your question. Without seeing your existing database, I ...
Combining multiple tables	11/5/2009
Q: I have 14 tables, one of which has 1033 records. All of the other 13 tables have: a) fewer ... A: Add the new fields that you want into the Master table, and then use an UPDATE QUERY to pull in the ...
Formula Issue	11/5/2009
Q: I have Price A from Table 1 and Price B from Table 2. I have a formula subtracting price B from ... A: Does this problem still exist with larger numbers? Like 30 - 10? Do you get a 20? Let me know if ...
A Search Function in Access 2003	11/4/2009
Q: I have a table that contains a list of Vendor Names, Product Description, and a website link. On my ... A: There is a command button wizard for a simple search. If you want more customizability, you can use ...
Unmatched records query problem	11/4/2009
Q: I have two data tables in an Access 2007 database. They are identical size in terms of number of ... A: A Find Unmatched query will find records in Table1 that don't have a matching record in Table2, like ...
Gleeming data from a Word Doc.	11/4/2009
Q: I am an intermediate user. I am trying to import data from a Word doc. The Word Doc is password ... A: Without seeing the document, it would be hard to tell you exactly how to go about it, but I would ...
embedding and making PDF files searchable	11/2/2009
Q: Beginner level I have too many magazines with only a few pages of projects of interest. I intend to ... A: My recommendation for any database like this is always to save the scanned images as FILES in a ...
automated email report sending via a button	11/1/2009
Q: I am constructing a database for my friend and have managed to create a button that picks up info ... A: Yes, it's possible. You can use the DoCmd.SendObject command. I cover this in detail in my Access ...
Drop Down List/Look Up Table Question	10/30/2009
Q: I was wondering of you could help me.I have had some experience with access and would say that I am ... A: If I understand you correctly, you want to select a value in one combo box and have the values in ...
embedding and making PDF files searchable	10/29/2009
Q: Beginner level I have too many magazines with only a few pages of projects of interest. I intend to ... A: If you're going to take the time to scan these in, does your scanning software have an OCR feature? ...
Save value unbound textbox	10/28/2009
Q: I have a question concerning an unbound textbox. The controlsource of the textbox is the following: ... A: Is the combobox bound? That should be what you use to save the value in the table. Otherwise, ...
Database opening query	10/27/2009
Q: When I open this database that I have been asked to correlate, I cannot see the window correctly and ... A: Scroll up (using that scroll bar all the way to the right). Let me know if you have any other ...
Multi level users	10/26/2009
Q: I was wondering is there a way If i can create a database for multiple users and then restrict ... A: Microsoft Access is a great database program. I love it, and I've used it since the first version. ...
Queries- Access 2003	10/26/2009
Q: I'm using five controls (state, city, room type, floor, room type) on a form to run a search query. ... A: Creating a multi-field search form is not easy. One field is relatively easy. You can just set the ...
Adding fields to table / form after initial form design.	10/25/2009
Q: I have a table which I have designed a form to capture / edit the data. I now need to add an ... A: You have to make sure that the checkbox is BOUND to the field in the form. If you just add a ...
Forms using multiple tables	10/23/2009
Q: I classify myself as a beginner although I have built 2 small databases. Both use a single huge ... A: Brian, the first thing you should do is put all of your data in ONE table. If you need to store data ...
Order Form	10/23/2009
Q: I want to create a form for Candy Sales. Basically a form that will allow me to insert NEW orders. I ... A: George, this isn't a quick or easy answer. In fact, I spend 2 or 3 classes in my Access tutorial ...
Access Grouping	10/23/2009
Q: How do I get Access to display the results of a grouping across the report instead of down it? For ... A: If you use grouping levels in a report, I'm afraid you're stuck going DOWN. If you use an aggregate ...
Event Procedures	10/23/2009
Q: I want to use an event procedure in the On Change property of a data input text box on a form to ... A: The more important question is WHY do you want to press the Tab button? Are you trying to actually ...
SQL Parameters	10/22/2009
Q: I am trying to create a SQL query with parameters in Access 2007. Below is the statement I am ... A: Sure, you could just build this as a string in VBA and then use it to feed a form or report: ...
duplicates and VBA	10/22/2009
Q: I have a query of books. It has five rows of "Book Id", "Import Date", "ISBN", "Cover Color", ... A: Use a FIND DUPLICATES query. There is a wizard for that. Let me know if you have any other ...
Filter to find Blanks	10/21/2009
Q: I have a form where I filter the records depending on who tested the product. I have some that are ... A: You're missing the most important one: Is Null or if in VBA code: IsNull(FieldName) Let me know ...
combo box	10/21/2009
Q: Richard: I created a form using wizard. I also have a query setup to concatenate employee first and ... A: Well, I would need to see your form to tell you for sure, but it sounds like something's not set up ...
Export Row Source and Field Name to Excel	10/20/2009
Q: My coworker created 20 forms, with at least 5 fields each, in MS Access 2007. I am interested in ... A: Yes, it's possible, but it's going to require some serious VBA programming. You'll essentially have ...
Days Elapsed between Dates	10/20/2009
Q: I’m a beginner with Access. Basically I need to add a control in my form which will show the days ... A: It's pretty much the same thing. You don't really even need the "Date Today" field. You can do it by ...
Inventory	10/20/2009
Q: I have to questions, First is I am creating an inventory list I have amount list and a price list. ... A: You could just use a second table for this. Have one table that lists YOUR products with product ...
Filter by Check Box (Cont.)	10/19/2009
Q: Ok, I now understand that stName can't be in the quotes. I am still getting asked for stName tho. ... A: Use parentheses. WHERE Status='Open' AND LeadTime='Late' AND (Tester='Dan' OR Tester='Kelly') Let ...
Inventory	10/19/2009
Q: I have to questions, First is I am creating an inventory list I have amount list and a price list. ... A: Rich, your first question can be answered by creating a CALCULATED QUERY FIELD to multiply two other ...
Sum Fields	10/19/2009
Q: which are basically score cards for quality control. I would like a sum field, to tally up the ... A: To answer the first part of your question, as long as those forms are OPEN you can refer to their ...
Delete table content	10/19/2009
Q: I am using external data to feed my access tables. When I want to update these tabel I am using ... A: I would keep the delete queries and just call them in VBA code: Docmd.OpenQuery "QueryName" You ...
sql query	10/19/2009
Q: I have a table named "tblantennadatanodups" that has fields "tagid", "date"," time" and "number of ... A: Just use the query technique that I outline in the video link I sent you. Refer to the listbox in ...
Autojoin is not working	10/18/2009
Q: I enabled AutoJoin in Tools>Options>Tables/Queries. However, when I add two tables to a query, ... A: Hmmm… well, I'm not sure then. I'd have to see your database to be able to tell you what the problem ...
sql query	10/18/2009
Q: I have a table named "tblantennadatanodups" that has fields "tagid", "date"," time" and "number of ... A: Just add a WHERE clause to your SQL and reference the listbox. S = "SELECT * FROM TableName WHERE ...
Autojoin is not working	10/16/2009
Q: I enabled AutoJoin in Tools>Options>Tables/Queries. However, when I add two tables to a query, ... A: The field names have to be EXACTLY the same for Access to join them automatically. Let me know if ...
Days Elapsed between Dates	10/16/2009
Q: I’m a beginner with Access. Basically I need to add a control in my form which will show the days ... A: That's a simple calculated field. If you have D1 and D2 and want the difference between them, make ...
Inventory Control	10/16/2009
Q: I am a beginner to Access. I want to create a simple inventory control database using Access 2007 ... A: Yes, it will absolutely require some code, and if you're just a beginner, you might want to get a ...
Filter by Check Box (Cont.)	10/15/2009
Q: Ok, I now understand that stName can't be in the quotes. I am still getting asked for stName tho. ... A: Why are you using both a where string and a filter? You don't need to use both. You just need to ...
Filter by Check box	10/15/2009
Q: I need to be able to check any combination of the check boxes and have that information returned on ... A: You can't use stName INSIDE your quotes if it's a variable defined in your code. You'll have to send ...
Filter by Check box	10/15/2009
Q: I need to be able to check any combination of the check boxes and have that information returned on ... A: Well, what is stName? I don't see it defined here in your code. Let me know if you have any other ...
average of averages	10/15/2009
Q: "Good morning, I have a MS Access report that, for the sake of arguement, has 8 lines of data with ... A: You should be able to just take the average of the other average fields in your footer. Just make ...
Filter by Check box	10/15/2009
Q: I need to be able to check any combination of the check boxes and have that information returned on ... A: That's why this method is perfect because using simple text strings you can build as complex of a ...
Grouping fields in report	10/15/2009
Q: Is it possible to group the results of three separate fields in a report and then sort them from ... A: Certainly, just set up a SORTING AND GROUPING level in your report, and the GROUP BY that field. ...
Data entry look up values	10/14/2009
Q: Im a beginner with a little background understanding of relational database theory. I am trying to ... A: Yes, this makes perfect sense. The example that you used (groceries on an order) is the EXACT same ...
History Function	10/14/2009
Q: I am new to MC Access and Visual Basic and this has roughly given me some trouble. I have created a ... A: I would need to see your database to tell you why some fields worked and others didn't. This should ...
Filter by Check box	10/14/2009
Q: I need to be able to check any combination of the check boxes and have that information returned on ... A: I hate using the filter property. Its unreliable. I much prefer the WhereCondition. In your case, I ...
email field	10/13/2009
Q: I have an address book table with email field set as hyperlink. On my form when the email address ... A: You didn't indicate which VERSION of Access you're using. Access 2007 doesn't have this problem. If ...
Will only Print the 1st Record	10/13/2009
Q: Howdy Richard! I created a corrective action database using the Access 2007 Issues Template. I ... A: I would need to know more about your database and how its set up in order to give you definitive ...
Data entry look up values	10/13/2009
Q: Im a beginner with a little background understanding of relational database theory. I am trying to ... A: Wow, this is a strange request, but I like it. :) So let me get this straight… each Sample is made ...
crosstab percentage calculation	10/13/2009
Q: i am using access 2003 and i am intermediate with no programming. I would like to add a column that ... A: Make a query with RateOfReturn as a calculated field. Then use this for your crosstab. Here's a ...
Access 2007	10/13/2009
Q: well i am going to try to explain you my problem I have a DBF5 file in other disk(k:for ... A: I'm not exactly sure what the problem is with your code. I'd have to see it working on your system ...
History Function	10/12/2009
Q: I am new to MC Access and Visual Basic and this has roughly given me some trouble. I have created a ... A: OK, so if I understand you correctly, you want to fill in some values in the main form and then ...
Copying information to a new field	10/12/2009
Q: I have a database with 1000 entries, with the following fields - CatNo, Name1, Name2, Sex1, Sex2, ... A: I would run a APPEND query to move all of the person 2 data into the person 1 fields, then just ...
How to draw vertical lines?	10/12/2009
Q: I'm trying to draw vertical lines between columns in the detail section of report. And also how to ... A: You have to just draw little vertical lines with the line tool that are the height of the whole ...
filtering records using multi combo boxes	10/9/2009
Q: I'm fairly new at access and using 2007. Now my problem is I've got a form with 3 combo boxes of ... A: Make your subform get it's data from a QUERY. The query will use your combo boxes to get its ...
Search form	10/8/2009
Q: i want to create a search form at which user can enter more than one search critira. for example ... A: This is easier to do with a tiny bit of programming, but you could make a form with NO data in it ...
Display previous 2 months data within a form	10/7/2009
Q: I have a text box bound to a table. What I would like is for the user to enter a figure eg 100 in ... A: You can use a DLOOKUP or DSUM function to look up this data, create a query field for it, and ...
query and sorting	10/7/2009
Q: I have the following problem. I have a table with the collected offerings of suppliers. For the ... A: If you put MIN in the supplier field, you're going to get the LOWEST supplier NAME (or ID, whichever ...
Access query to combine multiple rows into one	10/7/2009
Q: I have an MS Access 2000 database recording funding applications made by researchers. It has an ... A: You might be able to do it with a crosstab query… yeah, I'm sure it IS possible somehow without ...
MS Access auto popup message	10/6/2009
Q: I need to make an automatic popup message on a form, It needs to compare one date with another date, ... A: Do you want a LIST of customers to contact, or do you just want a popup message if you open that ...
Multiple values paased by parameter	10/6/2009
Q: How do I pass multiple values to a query from a form. I need to pass a selection of a single zip ... A: I would make multiple ZIP code text boxes on your form and have their defaults be "*". Make as many ...
Access field Cangrow Height	10/5/2009
Q: I'm using Access 2008. I have a report with detail fields that have a border. All of them are set ... A: Ungroup the fields and that might fix your gap problem. Access 2007 tries to group all of the ...
calculation using dates	10/5/2009
Q: Using access 2003, can you tell me how to calculate using dates for instance i want to get the ... A: There are many different techniques in Excel you can use to break apart dates or times into their ...
How to add a sum in a bound text box	10/5/2009
Q: I have a text box bound to a table. What I would like is for the user to enter a figure eg 100 in ... A: You would need to use an AfterUpdate event to save the value in the other field. See this tutorial: ...
Access query to combine multiple rows into one	10/3/2009
Q: I have an MS Access 2000 database recording funding applications made by researchers. It has an ... A: I cannot think of any way to do this WITHOUT using some programming or SQL. You'd have to run a ...
65000 Row Limit	10/2/2009
Q: I'm using Access 2007 and Excel 2007. I use an Access front end to a SQL database. I want to export ... A: WHY do you want to do this? Excel is for working with SMALL groups of data - a few thousand lines ...
auto date / time question	10/1/2009
Q: Version: 2002 Level of expertise: Have created multiple complex databases over the years, but can ... A: You need to use the CODE BUILDER, not the macro builder. MyFieldName is not what you actually put ...
Field results of a formula	10/1/2009
Q: I have a simple percentage field in MS Access showing ###### as a result of subtracting one ... A: That's very strange. I've never experienced that before. Could it be a rounding problem? Perhaps ...
MS Access - multiple criteria to form field, how to design the query	10/1/2009
Q: I have an access 2000 application, I am advanced user and I am looking for a solution how to ... A: I would create a listbox to display your records (instead of a query) and then just dynamically set ...
Running Sum in Query for Count Field	9/30/2009
Q: Access 2003: I would like to create a running sum in a column of my query. The query is currently ... A: This tutorial does something similar: http://www.599cd.com/tips/access/dcount/?key=AllExperts I ...
MS Access 2003 Form Input to filter Report	9/30/2009
Q: Why isn't my DoCmd OpenReport... not filtering for name selected in the CBO on a form? Private Sub ... A: Don't use the Filter parameter. Use the Where condition (next one). It's usually better. Next, the ...
Search in Access	9/29/2009
Q: On one of the tabs there are 2 subforms side-by-side. The left side lists products from the Products ... A: You could create a parameter field for your listbox. Make a text box called MyParameter and put a ...
Access field Cangrow Height	9/29/2009
Q: I'm using Access 2008. I have a report with detail fields that have a border. All of them are set ... A: Since there is no Access 2008, I'll assume you mean 2007. The only way to do this would be to use ...
Access	9/27/2009
Q: The main product list (table ) has all the products and codes (primary key)+ there is a range of ... A: So you have pricing data in two tables and you want the SPECIALS pricing to override the normal ...
Dcount	9/26/2009
Q: I am following your example of a student attendance database on accesslearningzone.com I am ... A: Tom, I think your problem is just that FALSE is a value, not a text string. Try: ...
auto date / time question	9/23/2009
Q: Version: 2002 Level of expertise: Have created multiple complex databases over the years, but can ... A: You can EASILY do it with one line of code. In the OnGotFocus event, just say: MyFieldName = Now() ...
Record Saving	9/23/2009
Q: When I add a record it is suppose to save automatically, which it normally does. Recently, it is ... A: Sounds like a software problem. Access is supposed to save the data as soon as you leave the record. ...
IIf statements in Access Reports	9/23/2009
Q: I have a query that is arranged by activity date, it is checking for errors on benefit records. One ... A: Make a separate query and exclude NULL results for that query. Now use this new query for the ...
Storing an array	9/23/2009
Q: I want to store an array of integers in an Access database as an array. Is it possible to do this? ... A: Yes, it's possible. You can DIM an array in VBA just like normal Visual Basic. You'll have to use VB ...
joining records	9/23/2009
Q: I have created a database for our annual school fundraiser. The sponsors contribute money on behalf ... A: First of all, I would create a separate FAMILY table and have MEMBERS of that family as subrecords. ...
Reformating fields to zip codes and carrying over data	9/23/2009
Q: I have a ms access dbase that already has 10,000+ records in it. Each is identified by a plot_ID ... A: Wow, that's a lot. Let's break this down into several steps. In fairness to everyone else who is ...
Security without using User Level Security	9/22/2009
Q: I have a database with front and back ends where I have built a user login for accessing the ... A: You can't really protect back-end tables in Microsoft Access. Microsoft wants you to use SQL Server ...
Dlookup in Access 2007	9/22/2009
Q: I'm almost a beginer at Access and I've created the following expression to lookup a depot name from ... A: Try this: =DLookUp("[Depot_Name]","Centre_List","[Depot_Number]=" & [Depot_Number]) Assuming ...
Field name and sorting	9/21/2009
Q: I think I have a fairly simple question, but I can't seem to figure out the answer. We changed the ... A: I would create a calculated query field that doesn't have the input mask and then use that for ...
Checkbox if then statement	9/21/2009
Q: Sir, I would like to add a check box to my form. Once added, I want to go into the "Afterupdate" ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Rename or copy file	9/21/2009
Q: Environment: MS Access 2003; Operating System: XP Situation: I'm creating an Access subsystem for ... A: This really isn't an Access question. You're manipulating text files in Visual Basic. You can do it, ...
Multiple Combo Boxes	9/21/2009
Q: I have a form with two sub forms embedded. I would like to add a combo box to each sub form. The ... A: If I understand your question correctly, you want to select a customer in a combo box and have their ...
Greater than operator	9/21/2009
Q: I need a query that will show me were we paid more than we should have. I have a field for paid and ... A: In the criteria for PAID just put: >Allowed Now when you run the query, you'll just see the ...
Access	9/21/2009
Q: The main product list (table ) has all the products and codes (primary key)+ there is a range of ... A: I would need to know more about how your tables are set up. How are your specials tracked? Is it a ...
Report field with pull down limit info	9/20/2009
Q: I am volunteering for non-profit creating resource list. Creator of list has much personal info ... A: Add a new field to your table called PERSONAL (y/n value). Then make a query with a criteria only ...
Adding totals for each record	9/18/2009
Q: Have an Access 2003 database that tracks campaign donations first/last names, up to five date fields ... A: Ken, first I'll say that you SHOULD be using a second table to track the transactions. Now you're ...
Access field Name Prompt	9/18/2009
Q: Richard, Is there a way to make a query prompt me for a field name? I am not trying to prompt when ... A: You want to dynamically change the name of the field in a query, so that when the query runs you can ...
Cross Country Racing Database	9/18/2009
Q: I am trying to develop a DB to track my daughter's Cross Country team info. Table organization is ... A: I would create a table for your race data. In that table, you would store an AthleteID so you know ...
Report using parameters	9/18/2009
Q: Good morning, I have a report, using a query as the data source. There is a parameter ... A: Sure. This is going to, of course, require some VBA programming. Here's a tutorial on Recordsets to ...
Getting information from other table is database	9/18/2009
Q: I have a 2 tables and 1 form which are the following: - suppliers and customers table - transactions ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
DMax Recycle Numbers	9/18/2009
Q: I'm totally new to Access. My experience is in MySQL/Oracle mainly. I was curious about the DMax ... A: You cannot reuse Autonumbers in Access. You can, however, create your own number fields and set them ...
If/Else Statement	9/17/2009
Q: I have a contact table with a field set to "Yes/No" for membership. I want to be able to print a ... A: Instead of using an IIF function, just use a little bit of VBA code in the button that prints the ...
Change Query Criteria	9/17/2009
Q: I am new to access and currently working on a database that will store names and numbers. I built a ... A: This is a little tricky, and if you're new to Access is might be a bit over your head. Here's what I ...
Access to Word Merge	9/17/2009
Q: I'm trying to create a mail merge in MS Word using a table in Access 2003. The table contains a ... A: Create a query in Access. Link the two tables together based on that ID. Bring in the related field ...
Expression for Promotion	9/17/2009
Q: I need to create an expression for promotions within my company. While I have built a few small ... A: Since your requirements vary, I would create a custom function for this. It's not that hard, but it ...
creating a Church attendance database	9/16/2009
Q: I just took the Video Professor "Learn Access" course and learned a little, mainly how to set up the ... A: That's going to be a little more advanced than just a basic "Access Beginner" class. You're going to ...
Drop Down Look Ups	9/16/2009
Q: I'd like to create a special look up form. So far- I have a combo box that is linked to the field in ... A: In your button, just say: Docmd.Openform "FormName",,,"MemberID=" & ListBoxName (From memory, I'm ...
sorting multiple fields in a listbox	9/16/2009
Q: good afternoon. I have a form with a list box and I would like to know how I can sort a couple of ... A: There are two ways you can do this. Both will involve some programming. The BEST way to do it is to ...
Acess DB	9/16/2009
Q: We are using a seperate s/w to process information and store it in access db in local machine. Its ... A: Without looking at your system, I can't tell you for sure. If it's not in your Recycle Bin, then ...
Printing Multiple Reports with dates	9/15/2009
Q: Wonderful, you have answered the first part of my question in another post, but I need to print 8 ... A: Sure. Instead of asking for the date in a query parameter (which I'm assuming is what you're doing) ...
multiple users running mdb on a network	9/15/2009
Q: In Access 2003 how can you set it up to allow multiple users to access a .mdb from a shared server? ... A: Just copy the MDB file to a shared network folder. If both users open up that same MDB file, Access ...
Queestion on setting function between 2 field	9/15/2009
Q: I am creating a database and I have to replace the information in field one with a value in field ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
MS Access 2003 resizing a form	9/14/2009
Q: I am a beginner user in MS Access 2003. I have a page that has several buttons that when you press ... A: Without seeing your database, it's going to be hard for me to direct you, but the "popup" window ...
Junction tables	9/14/2009
Q: I'm trying to setup a Junction table in Access 2007, but when I try to open it or run a query ... A: You don't need a junction table for this. You need to create a CARTESIAN PRODUCT. Just put both ...
Sync Combos on a Subform	9/14/2009
Q: I am trying to sync 2 combo boxes on a subform. It is working when I am using it as a normal form, ... A: I'm not sure what you mean by "sync 2 combo boxes." Do you mean you're trying to use one to filter / ...
passing value of filter to a query	9/11/2009
Q: I have several years of experience with Access, including VBA, and I am currently using Access 2000. ... A: Looks good. I'm glad you got it to work. Thank you for posting this for the benefit of others. Let ...
adding new record to a query problem	9/11/2009
Q: I'm currently working with a database which I'm trying to add a new record to a query on a form. I ... A: Try this tutorial and see if it helps with your problem: ...
Report parameters from a form	9/11/2009
Q: I am a beginner/intermediate user with Access 2007, I am trying to set report parameters by pulling ... A: How is your combo box set up? What are the columns? You would need to make sure the first column of ...
Forms	9/10/2009
Q: I have a sheet that is identified by a control number. The sheet has 3 columns (Item number, ... A: Are you working with two tables here? If so, set up a form with a subform, and you can use the ...
Sequential Numbers - update after deletion of record	9/10/2009
Q: I'm hoping you can help me with an access query. I have a table (tblappt) of people who have ... A: This sounds like it's certainly possible, but without knowing more about how your database is ...
Creating a query for Multiple Select Statements	9/10/2009
Q: I am a beginner using Access 97 but I need to extract data from a common columns that are in ... A: You could use a UNION query, or if this is just a one-time thing, try APPEND queries. You can use ...
Multiple tables in one Report	9/9/2009
Q: I have multiple tables, 12 to be exact, that I need to combine into one report. I tried using all ... A: Try a UNION QUERY. SELECT * From Table1 UNION SELECT * From Table2 UNION etc. Let me know if you ...
Show $0.00 in blank currency fields	9/9/2009
Q: I need to show $0.00 when I have a blank in my currency columns when I run my query in Access 2007. ... A: Just set the DEFAULT VALUE for your field (in the table or form) to 0. Let me know if you have any ...
ms access 2003 creating a form where a date range is entered	9/9/2009
Q: I need to create a form that I can reference in a query where the form pops up when running the ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Compare partial text in two tables	9/8/2009
Q: I want to create a query to compares the text in two tables so I can match similar text. i.e. Table ... A: How about a FIND DUPLICATES query? Let me know if you have any other questions or comments. Also, ...
Access Reports	9/8/2009
Q: I've built a report, but one of the fields is gathering information from a look-up field from ... A: Sounds like the field is a LIST BOX instead of just a TEXT BOX. Change it and that should fix the ...
Image processing in Access	9/8/2009
Q: I am currently running Access 2003, and would like to know how to allow users to store images and ... A: You can store an image in a OLE Object field in your table. When you drag that field from the field ...
Multi Select Loop Function	9/8/2009
Q: I am a university student, on work experience creating a database for a company. The purpose of the ... A: The best way to handle a multi-select list box is with a RECORDSET. Recordsets are basically ...
Help formatting time	9/8/2009
Q: I am trying to make a simple database example to show empoyee time tracking . I have ... A: For help working with calculating the difference between two times in Access, please see this ...
Creating a Process that Auto moves reports	9/7/2009
Q: I am a beginner/intermediate user and use Access 2007. I'm creating a database that is important to ... A: You can do it with a MACRO or with some VBA code. I would peronally use VBA. Where exactly depends ...
pls, looking for solution	9/5/2009
Q: I have fields student name, module, class mark, exam mark and total mark and want to add(+)the ... A: It sounds to me like you just need to learn how to properly create CALCULATED QUERY FIELDS. You can ...
about report ??	9/5/2009
Q: I want to ask about the report in access 2007. I have two tables in two forms. one for the student ... A: Just join them together in a query, and use that query to create a report, grouped by adviser. Let ...
About record serial number??	9/5/2009
Q: I want to ask you about the record serial number in the bottom of the from that give each record his ... A: I don't know what you mean by a "record serial number." Do you mean the Autonumber? If so, it ...
Creating a Process that Auto moves reports	9/5/2009
Q: I am a beginner/intermediate user and use Access 2007. I'm creating a database that is important to ... A: The easiest solution might be to just run an APPEND query followed by a DELETE query as soon as the ...
Question about the autonumber??	9/4/2009
Q: I want to ask you about one of the data type in access 2007 (the auto number). I have three columns ... A: You can't reuse Autonumbers. Once you delete numbers in the middle they're gone. YOU shouldn't ...
Populate value in subform from main form	9/4/2009
Q: I have created a main form with a sub form. The parent-child relationship is a field called ... A: Try this tutorial: http://599cd.com/tips/access/student-attendance-append-query/?key=AllExperts ...
Update records in MS Access	9/4/2009
Q: How can I add random seconds to a datefield in MS Access. Each row needs to have different seconds. A: Use an update query. To add one second you would add: 1/24/60/60 So then just multiply that number ...
Report Question	9/4/2009
Q: I have a qry which pulls records from a table for a specific date (time period). I need to create ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
passing value of filter to a query	9/3/2009
Q: I have several years of experience with Access, including VBA, and I am currently using Access 2000. ... A: Instead of filtering the results, just have the user click on the record you want to filter, then ...
Variable not defined in access	9/3/2009
Q: why would I get the message "variable not defined" when I use the following code. Option Compare ... A: You haven't defined strMessage anywhere. Let me know if you have any other questions or comments. ...
Access2003 check boxes and queries	9/3/2009
Q: Is it possible to change the "criteria" field of a query based on a checkbox on a form? For example, ... A: You can do it with a complicated IIF function. Something like: =IIF(Chk1,"Like X","Like *") This ...
Value increment onclick	9/3/2009
Q: Access 2007: Is it possible to have a value increase by 1 in a table each time a certain button is ... A: I would need to know more about how your database is set up, but if you want to click a button on a ...
What is wrong with this expression?	9/2/2009
Q: Level: Beginner Version: 2002 I have just started writing expressions. I am trying to write an ... A: That looks like it should work just fine. Are you having a problem with it or getting an error? If ...
Populate value in subform from main form	9/2/2009
Q: I have created a main form with a sub form. The parent-child relationship is a field called ... A: If the values are just default values, then you can access them using: =Forms!FormName!FieldName ...
Displaying currency values	9/2/2009
Q: My database stores currency values in whole numbers but I would like to summarize these values in ... A: Sure, just create a calculated query field that divides the number by, say, 1000 and converts it to ...
access 2007 image on form	9/1/2009
Q: On my form I want to display an image with each record. First I set the field 'Picture', as OLE and ... A: To store images in the table, you should use a Bound Object and an OLE data type. Usually, images ...
all form data will move the the data sheet	9/1/2009
Q: I am using Access 2007 with Windows Vista. I have created a data entry form to my small biz ... A: This is not a bug. This is how calculated fields are designed to perform. The calculation will only ...
Table Issues	9/1/2009
Q: I have a training database in MSAccess 2003. I keep the employee training history records in ... A: Well, I would suggest trying to figure out a one-table solution with a field to indicate ...
Global Change for a date in Epxression Builder	9/1/2009
Q: I have a query with an Expression Builder that has dates embedded in the IIF statements and code. ... A: Don’t hard-code the dates. Perhaps use a form to enter them. You can then change the dates however ...
databases	8/31/2009
Q: first, thank you for your time. second, i'm quite the novice, despite taking computers 101 in ... A: Microsoft Access would be perfect for this. You can watch my free Access 101 tutorial (see below) ...
Filtering old data in Access dropdown	8/31/2009
Q: I have a database that we use for quoting products that works on MS Access 2003. Basically, when we ... A: You're on the right track. Add a "Discontinued" field to your table. Now, make a QUERY and set a ...
Sequential Numbering	8/30/2009
Q: I have a database that tracks work orders. The user enters the work order request in frm_WorkOrder. ... A: See this tutorial: http://599cd.com/tips/access/incrementing-your-own-counter?key=AllExperts Let ...
CONVERT NUMBER TO TEXT VALUE	8/29/2009
Q: Is there a way to convert a number value to a text value? I am using MS access to write a check. In ... A: Yes, it is possible. It requires custom VBA code however, as it's not a feature built into Access. ...
DLookup Access 2003	8/28/2009
Q: I hope this is fairly straightforward - A table named 'ASSIST' contains the following fields, ... A: What you're doing is perfectly fine. See this tutorial for more help on DLOOKUP: You can use the ...
Create a table from an existing form in Access 2003	8/27/2009
Q: I recently created a form in MS Access. Due to the complexity of the form, I created the form first, ... A: Yes, create a table with similar fields. Now comes the hard part. First, you have to open up the ...
Select Last 6 Records by Team Name	8/26/2009
Q: I have created a database which details all football results from all English leagues since the ... A: I can't think of a NON-programming way to do this. You could create a recordset loop to generate the ...
If then or Elseif Statements	8/25/2009
Q: I have a database query set up to open with vb with user selected criteria. My issue, I have ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
How to select most recent dates for records	8/25/2009
Q: I am trying to retrieve records with the most recent changed time stamp. I have tried using the top ... A: Try using the MAX function in the query designer. Group by whatever field you're trying to find the ...
Updating Table from Linked Table	8/25/2009
Q: I have a database set up for tracking employees required training certificates. In this database, ... A: Ah, you have one of those "this table is sacred, don't touch it" situations. I understand. You ...
Transferring data from one form to another	8/25/2009
Q: I am a beginner on Access and I am creating a database for a car repair and spraying business. One ... A: I don't understand your question. Can you please clarify? Also, why are you using two tables for ...
Query	8/22/2009
Q: I have an access 2007 database with my telephone field currently having 7-digits, I want to modify ... A: This is just a simple UPDATE query. Something like... Phone - Update To: "888" & [Phone] See ...
Select Last 6 Records by Team Name	8/21/2009
Q: I have created a database which details all football results from all English leagues since the ... A: Do you need to generate this for ALL of the teams or just a specific team? If it's just one team, ...
Access 2003 - Shared Databases and "Could not lock table"	8/21/2009
Q: Richard, I have set up a shared database on a server, but keep receiving "Could not lock table" ... A: First, make sure everyone's out of it, and then compact and repair your database. Next, make sure ...
picture slideshow	8/21/2009
Q: I was wondering if it would be possible to have photos that change with time on the mainpage of a ... A: After your X=X+1 command you should say: IF X=11 then X=1 Sounds like you need a beginner's course ...
BuildFilter Function with textbox and combobox including date	8/21/2009
Q: I have a search form with two textboxes to filter a set of records but I need to change it to 1 ... A: I would continue on with the same style you're using… just add on to the WHERE clause: If ...
Updating Table from Linked Table	8/21/2009
Q: I have a database set up for tracking employees required training certificates. In this database, ... A: Well, my first question is WHY do you have two separate tables to store the same data? An employee ...
visible property question	8/21/2009
Q: My problem is that I'm loading a form which has a header and detail. In the detail (approx 300 ... A: I understand what you're trying to do, but if the button is in the detail section of a continuous ...
Access 2007 Query	8/20/2009
Q: I have one table that has sales data for 1/1/09 through 7/31/09 that has Sold to and Order #. I ... A: I would recommend putting the data together in one table and adding a field to track your locations. ...
Need some help with Access	8/20/2009
Q: My level of expertise is intermediate and I've the following issue I'm trying to resolve to help a ... A: I have done programming between Access and Outlook before using this method, but I don't remember ...
How to run modules?	8/20/2009
Q: I have 5 modules and they all generate random numbers for different categories. I am trying to run ... A: Well, a SUB won't return anything. You might be able to display something with a MsgBox command, but ...
Updating field using DlookUp of previous field	8/19/2009
Q: IIf(Nz(DLookUp("[Component]","zPegInfoOS","[ID]=" & ... A: Do you have an ID 1? If so, you might be getting an error when it tries to find ID 0 (which doesn't ...
Loop to select text files from a folder	8/19/2009
Q: There is a folder that I need to use the text files names from. I would like to create a "for ... A: Use the DIR() command. It will allow you to loop through all of the files in a specific folder. I'm ...
MS Access Form	8/18/2009
Q: I have a table that stores inventory amounts on different days. The database is set up to have one ... A: So you want to automatically populate the products in a subform? You will need some programming to ...
After Update	8/18/2009
Q: I'm trying to a simple AfterUpdate event on a combo box called cmbManuf on a form called frmMS1CPU ... A: I'm not sure. I'd have to see your database… but if you just open up the property for cmbManuf and ...
referencing a query in a form	8/17/2009
Q: I have to track annual performance reviews for my company. So I have set up a table with the unique ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
report only contains rows with blank fileds	8/15/2009
Q: We need to check that all fields within our database are populated. We run a report with all ... A: Use the IS NULL criteria in a query. I show how to do this in my Access 102 beginner class: ...
picture slideshow	8/14/2009
Q: I was wondering if it would be possible to have photos that change with time on the mainpage of a ... A: You need to increment your counter each time the code runs. X = X + 1 I figured this was assumed. ...
Selecting multiple dates in a form	8/14/2009
Q: I'm an intermediate user and have set up a db for sports ground management. I'm trying to include ... A: You could use a multi-select listbox with dates in it, but that would involve some programming. I ...
requery of a field	8/13/2009
Q: Rost. I'm building a database for a music collection. This is my problem: On "not in list" for, say, ... A: How about a GotoControl command to move to a different field, and then another GotoControl to move ...
Clear combo box after requery	8/13/2009
Q: I have filtered my Manufacturer and Model combo boxes. The tutorial you posted on filtering combo ... A: Filtering more than two boxes just uses the same technique between boxes 2 and 3 that you'd use ...
Future service dates forecasting using last service date plus frequency.	8/12/2009
Q: Future service dates forecasting using last service date plus frequency. I would like to write an ... A: If you're always going to need four and only four service dates (never more) then just make extra ...
access	8/12/2009
Q: What purpose does the form detail section have? A: That's where all of your actual data goes. The header and footer are used for titles, sum ...
IIF statemet in a query	8/12/2009
Q: I have a form that I am using to "query" a table based on a selection of text boxes on that form. ... A: You could try: IIF(IsNull(Forms!FormName!FieldName),"Like '*'",Forms!FormName!FieldName) The "Like ...
Using the IIF Function	8/12/2009
Q: I am creating a database to track commission sales. I have used your "IIF Function" example to come ... A: I would add the commission rate as a field in your employee table. Then, you could use DLOOKUP to ...
Import Text using Open Dialog Box	8/11/2009
Q: I have a macro that imports a text file. I would like the user to have the option to use the open ... A: You are correct. It's going to require some VBA. You can use the Microsoft Common Dialog Control to ...
Report broken into multiple pdfs	8/11/2009
Q: Mr. Rost, I have an access report filled with error logs grouped by the last name of the system ... A: Yes, it is possible, but it will probably involve some programming. You would need some way to loop ...
Serial number	8/11/2009
Q: I have started a University project and I have to develop a stock control database. My question is ... A: Well, you would need VB code to obtain that HDD serial number. I don't have any handy. Perhaps a ...
Change color in ONE field...	8/10/2009
Q: I'm a beginner and this is probably a really simple one but i cannot get it to work... I have been ... A: 1. The easiest way to accomplish this would be with CONDITIONAL FORMATTING. I cover this in my ...
picture slideshow	8/10/2009
Q: I was wondering if it would be possible to have photos that change with time on the mainpage of a ... A: Sure. The EASIEST solution would be to add all 5 pictures as images, and then have a TIMER event ...
Masking Data Already Present	8/10/2009
Q: I have a table that includes social security numbers. I know how to set the input mask so I'm only ... A: You want to OBLITERATE the data in the database and replace 111-22-3333 with XXX-XX-3333 in all ...
Joining Queries	8/10/2009
Q: I have 2 queries. My 1st Query is a Rejections query with patient name, account, total, etc.. about ... A: You will need a common field between both tables - such as PatientID. It sounds to me like you need ...
addition in a database	8/10/2009
Q: I have a database that I made for keeping track of expenses for a property i manage. I want to add a ... A: I wouldn't add this as a FIELD to your table. Instead, you can calculate the value in the FOOTER of ...
Suppress Multiple User Pop-up Window	8/9/2009
Q: I have set my database in the shared mode, and I am posting it on a shared drive for multiple users ... A: They shouldn't get that error message unless ONE of your users (or you) have the database open for ...
Continuous form	8/7/2009
Q: I´ve probably have a really easy question, but I can´t figure it out: I´m making a database for a ... A: Use a couple of subforms? It's kind of hard to tell you what to do without much information. Let ...
Sorting and Grouping Reports	8/6/2009
Q: Need your help still kind of new to access. In my database I am trying to create a report that ... A: Don't put the Patient Name in the DETAIL section. Set up a GROUP for Patient. Put the patient name ...
Convert Text to Date in Access	8/6/2009
Q: I have an access database in which I import an excel table. The table contains dates in a text ... A: It seems like you just need to cut off the time, right? Yes, DateSerial would work, or... Pull this ...
Search Form	8/6/2009
Q: I want to create a search form for a department where it will have two textboxes, one for name and ... A: I would make a search form with a text box for your search criteria. Then when you click on the ...
Query doesnt allow adding new records	8/6/2009
Q: This time I am going to sound more stupid I suppose. I have a patients database with 16 tables. ... A: Sometimes it's possible to make queries that are just too complex to add or delete records from. ...
Search form will not see blanks.	8/6/2009
Q: I have a search form that will not see blank fields, I am querying multiple text boxes to narrow a ... A: If you copied and pasted that statement, you've got an error. It should be: Forms!Search!Textbox1 ...
Calculate freight	8/5/2009
Q: I have access 2003 and i would like to calculate a freight charge based on the order subtotal the ... A: Well, start by telling me how you would LIKE to calculate your freight charge. Do you want it based ...
Time Difference in access	8/4/2009
Q: I have 2 fields, begintime and endtime set as date/time datatype.I want to calculate the time ... A: There are many different things you can do with dates and times in Access. You can use the DateAdd ...
Passing values	8/4/2009
Q: I'm trying to pass values from one form to another form but when I click on the enter button on the ... A: Oh, I see what you're trying to do. You're going to need to manually set the values in a button or ...
Query Date not showing some specific dates in table Part 2	8/4/2009
Q: ..in my criteria in my query it show the syntax like this ... A: You would put it in your WHERE clause, but you're going to need to convert the TEXT BOX values to ...
Passing values	8/3/2009
Q: I'm trying to pass values from one form to another form but when I click on the enter button on the ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Radio Buttons	8/3/2009
Q: Richard, I've 'inherited' an access database and forms that holds training critique assessments ... A: You could do this a couple of ways. You could use a Recordset: ...
Multiple users	8/3/2009
Q: I am an intermediate user of MS access. I have created a database for numerous clients each with ... A: You could do this with a password in the database, but it's NOT going to keep out someone who knows ...
Query Date not showing some specific dates in table	8/2/2009
Q: I am an intermediate self learner(with a very little in programing). I have a table that have a ... A: Without seeing what your data looks like, I really can't help you. It sounds like you might be ...
Form refreshes multiple time	8/1/2009
Q: I have a continuous form with 38 fields, the query has a number of fields that calculate data and ... A: Sorry, but this is something I really can't answer without playing with your database. The only ...
Making a copy paste button	7/31/2009
Q: here i go... i have this database used to make quote for clients. In one of my form, i can choose in ... A: The best way to do it would be with a RECORDSET. You could also try an APPEND QUERY. You could ...
Find and replace from another table	7/31/2009
Q: I have made a mistake. I did an update query by mistake, and wiped out a column of 416 ... A: This is a completely different question, not a followup. Please re-submit it as a new question. ...
Is my database normalized/set up correctly?	7/31/2009
Q: I am wondering if you would take a look at my database. I attached a picture of the relationships. ... A: It sounds to me like you have a pretty good handle on what you're doing. I must apologize that I ...
Reminder feature?	7/30/2009
Q: Is there a way to set a reminder in access so that if you enter a date and then the database does ... A: Yes, it's certainly possible. It would involve some programming, however. Have you done any VBA ...
Assigning passwords to the Switchboard	7/30/2009
Q: I have a main switchboard with three buttons on the screen: One for a Management switchboard, one ... A: Well, I don't know what to tell you. I don't have the time to explain Scott's code line-by-line. You ...
Is my database normalized/set up correctly?	7/30/2009
Q: I am wondering if you would take a look at my database. I attached a picture of the relationships. ... A: Sure, it looks fine to me… but without a better understanding of what all of your tables and fields ...
filter operations	7/30/2009
Q: . am designing a database in access to monitor training for a company.. everything works well. ... A: See this tutorial: If you want to refer to a value on a Form, you can use the notation: ...
Find and replace from another table	7/29/2009
Q: I have made a mistake. I did an update query by mistake, and wiped out a column of 416 ... A: You forgot Rick's First Rule of Action Queries… ALWAYS make a backup of your table FIRST. Hopefully ...
VBA Coding Technique Question	7/29/2009
Q: I am working with an Access 2000 database written by someone else. Although I've done alot of VBA ... A: I'm not sure why he's doing it this way. I don't see the benefit from the small sample of code ...
How to run macro without logging into database	7/29/2009
Q: I tried leaving the ms access open once i logged in and then ran the windows scheduler but still ... A: This requires some knowledge of VBA programming. There is an event called OnTimer. You can have ...
How to run macro without logging into database	7/29/2009
Q: I tried leaving the ms access open once i logged in and then ran the windows scheduler but still ... A: Don't use the Windows scheduler for this. I would create a form IN your Access database with a TIMER ...
Use MultiSelect to filter a form on a field in a subform	7/29/2009
Q: Richard, Thank you for taking my questions. I am using Access 2003 on Windows XP and still a VBA ... A: What you're doing is pretty complex. I don't have the time to tear it all apart here (I can't spend ...
syntax error in query	7/29/2009
Q: I am an intermediate / advanced Access user (used to be a lot more advanced, but I've not done ... A: Alright, instead of specifying it as a where condition to the report, why don't you try making a ...
User Level Security Permissions	7/29/2009
Q: So I have my database secured using the User-Level Security and have gone through all the ... A: If you want to prevent someone from DELETING A RECORD, then you use the AllowDeletions property. If ...
password protecting a report in a database	7/29/2009
Q: Trying to password protect only one report in a database. Thanks for any help ANSWER: Don't give ... A: The easy answer is DON’T let them bypass the startup menu. Create an MDE (or ACCDE) and only give ...
Microsoft Access Pivot Chart	7/29/2009
Q: How can I format my values in a pivot chart. I have formatted in design view but the values show up ... A: Click on the axis labels. Click on the PROPERTY SHEET button on the ribbon. Make sure "Value Axis 1" ...
password protecting a report in a database	7/28/2009
Q: Trying to password protect only one report in a database. Thanks for any help A: Don't give your users access to the database window. Make a button that asks for a password using an ...
User Level Security Permissions	7/28/2009
Q: So I have my database secured using the User-Level Security and have gone through all the ... A: User-level security in Access is buggy at best. Here's what I have to say about it: ...
word and access 2007 ??	7/28/2009
Q: I want to ask you if I have a student name record in access 2007. and I have the certificate form ... A: You can either use a query criteria in your Access database, or use a filter in Word. Let me know ...
syntax error in query	7/28/2009
Q: I am an intermediate / advanced Access user (used to be a lot more advanced, but I've not done ... A: Is the combo box you're getting the value from bound to an ID (int or autonumber) or text field? If ...
word and access 2007 ??	7/28/2009
Q: I want to ask you if I have a student name record in access 2007. and I have the certificate form ... A: The EASIEST way to do this would be with a little mail merge. Use the certificate as your form ...
query for updates	7/27/2009
Q: In Access 2007, how can you query for data modified since a certain date? I'd like to see only data ... A: You would need to store that ModifiedDate field in your table. Access doesn't keep track of when a ...
validation rules	7/27/2009
Q: I am trying to make a validation rule so that all entry into the field is in ProperCase. I've used ... A: You're probably not going to be able to do that as a validation rule. Instead, make a form and use ...
ms access 2007	7/27/2009
Q: I created a database with 3 tables all kinked by the same primary key. When I open forms only the ... A: Without knowing more about the structure of your tables and their relationships, I can't help you. ...
MS Access 97 - Text box backcolour	7/24/2009
Q: I am using MS Access 97 to create a report. Basically it takes from a table, each record has a Yes, ... A: Mel, the good news is that what you've done looks perfectly correct. The bad news is that I don't ...
CAlculations	7/24/2009
Q: i ahve created a program and now had to go back and add a table to track usage of medical supplies. ... A: Sure, make one query that limits your data between two dates using a parameter: Between [Start ...
Hyperlinks between Excel and Access	7/24/2009
Q: First of all, thanks for taking the time to read my question. I have between beginner and ... A: Well, if you're going to keep doing it this way, you could use a calculated field in an Access query ...
Opening a Form by selecting a certain item	7/23/2009
Q: I have a table with a text box that is set to Lookup. I have manually typed in @ 15 items to lookup. ... A: Make a combo box. I'll call it MyCombo. Then in the AfterUpdate event just say: If MyCombo="Jones" ...
User defined queries	7/23/2009
Q: I have worked on Oracle database with PHP, but now I am developing a DB for Access. I have been able ... A: You can specify criteria for running your queries, such as limiting between two dates, checking for ...
Date Formula	7/23/2009
Q: How do i convert this date formula into this? from 1090621 into 6/21/2009 A: You'll need to learn the string manipulation functions: left, right, mid, etc. plus the IIF function ...
MS Access 2003 Adding a date box to an existing report	7/22/2009
Q: I'm presently using MS Access 2003. I have an existing report that I want to add a box for dates. ... A: I'm assuming the date is a field in your underlying table? If so, just open up report design and ...
monthly expiry report	7/22/2009
Q: I am currently creating a training database for my company. they would like a report creating where ... A: Make a calculated query field showing the current month: M: Month(SomeDate) Now set a parameter ...
Parameter prompt	7/22/2009
Q: I have created the query below to run a select query. Why does it prompt the user to input the ... A: I don't know. I'd have to see this in action to tell you for sure. I only see one parameter in here. ...
User names and Passwords	7/22/2009
Q: I have created a contracts database to manage our agreements. I would like to know if it is ... A: What version of Access are you using? If 2003 or earlier, you can set up USER LEVEL security with ...
How to store user selected value from a combo box to my db?	7/22/2009
Q: I am using access 2007. I have two combo boxes. One is depending on the other. If user selects Area ... A: Do you have the combo box bound to a field in the underlying record of that form? Try doing this my ...
How to Compare a Table to Itself	7/22/2009
Q: I am using an Access database. Within a single table, I am trying to "group" or analyze each row ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
Access Locking	7/22/2009
Q: I would like to be able to have my Access database opened by other users, but not have it lock-up, ... A: Split the database. Put the backend (tables) where everyone can get at it. Make TWO copies of the ...
127 character limit in Memo field	7/21/2009
Q: I am working in A2000. I am between intermediate and advanced level of experience. I have a Memo ... A: I have never encountered this problem. To the best of my knowledge there is no practical limit on ...
Opening access database from within access	7/20/2009
Q: I have two reports that run from two different Access databases. Instead of having two icons on the ... A: If the reports are in two different databases, then you'll have to open each database to run the ...
help question	7/20/2009
Q: In a column field, I often have identical short data to plug into the field up to 100 times. I ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
IIF Statement	7/20/2009
Q: How can I populate a column based on three criteria. I want to show text when a value is less than ... A: It's called a CALCULATED FIELD in Access, and you can create one in a QUERY. In your case, I would ...
MS Access get currency from web	7/20/2009
Q: I am pretty new at MS Access, I have background in MS Excel programing but limited Access ... A: Well, I can tell you that this is certainly possible. In fact, I just developed a series of video ...
Maximum Number of Users	7/20/2009
Q: I have a shared Access application where all users should log in & then they have to use the same ... A: There is no set maximum number of users. It all depends on network speed, the amount of data ...
COMMAND CONTROL BUTTONS ON FORMS	7/19/2009
Q: I WANT TO PUT A BUTTON ON MY FORM TO OPEN ANOTHER FORM BASED ON A SEPARATE TABLE. I HAVE TRIED THE ... A: Well, you have to make sure you have a related field in both tables. If not, Access doesn't know ...
Suppressing the Printing Alert box	7/17/2009
Q: We are using the DoCmd.OutputTo method to generate many .rtf files. When each file is generating ... A: To the best of my ability, there is no way to surpress this dialog box. You could TRY: DoCmd.Echo ...
acesss autofill	7/17/2009
Q: i ahve created a program and now had to go back and add a table to track usage of medical supplies. ... A: Sure, use the DLOOKUP function to find the value: You can use the DLOOKUP function to look up a ...
Access Text to Columns using charaters as splits	7/17/2009
Q: I'm wondering if access allows one to split columns usinf specific cahracters (such as /'s, -'s, or ... A: I don't understand what you're trying to do - or why you'd want to split columns. In Access, each ...
Access	7/17/2009
Q: g code below the detail bar it shifts the detail bar down and the code keeps above the detail bar. ... A: Try opening up the detail section first so you have a nice tall white band of space there between ...
Autofill	7/17/2009
Q: I have tables in my database and have had to create a third to run reports. If I have a stock number ... A: Sure, use DLOOKUP. See this tutorial: <a ...
Hyperlinks between Excel and Access	7/17/2009
Q: First of all, thanks for taking the time to read my question. I have between beginner and ... A: Well, of course, I'm going to suggest you IMPORT the data into Access and just use Access. Why ...
Using the same expression	7/16/2009
Q: I want to use the same expression or formula when a new report is generate based off of the same ... A: I don't understand your question. Are you saying you want the user to be able to enter in criteria ...
Query for Inventory Database	7/16/2009
Q: I have set up an inventory database which contains item descriptions and item sizes (Height, Depth ... A: Sounds like you'd just need to make a query to link the two together by a related field, and then ...
Lock or disable one record of subform	7/16/2009
Q: I need some help to lock or disable some records of my subform depending on the data of one field. ... A: Try putting the code in the ONCURRENT event. That event runs when you move from one record to ...
Access 2007 and budgeting	7/16/2009
Q: I have heard that for budgeting and other financial reports, Excel is much better that Access since ... A: It is true that Excel has more built-in financial functions, however, most of the important ones are ...
access list and combo boxes	7/15/2009
Q: I am designing a data entry form for grocery items- broken into Dept., then further to Category, ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
setting report text box lengths based on a value	7/15/2009
Q: I'm somewhere between an intermediate access person with only a little bit of programing ... A: You can do this, but you'll need to put code in the BUILD EVENT for the section (right-click on the ...
Conditional Expressions	7/15/2009
Q: I am creating a form that has a "Location" field and an "Address" field. I need to write ... A: I would create a TABLE with the data in it, including your Location keyword and then the address you ...
auto-security	7/15/2009
Q: I have created my database and it is functional and everything with my own ULS. My question is ... A: See: http://office.microsoft.com/en-us/access/HA011071331033.aspx Let me know if you have any ...
Viewing Fields on form	7/15/2009
Q: I wanted to pull a field into a form. It did not exist in the table so I added the field. I can ... A: Open the form in design mode. Open the FIELD LIST box and see if it's in there. If not, perhaps the ...
Query in to a Selector Type Box	7/15/2009
Q: I have made a Query consisting of 320 customers with products that have been pacifically selected ... A: Make an unbound form with a combo box containing your list of customers. Now, make a button that ...
Displaying two columns in one drop down menu	7/15/2009
Q: I am the super beginner. I figured out how to combine two fields into one drop down (last name and ... A: You want to fill other info like their name, address, phone, etc. into other text boxes? That's ...
Access Form Design with Fields from 1 table and 2 Queries	7/14/2009
Q: I am using Access 2003. I created a form using design view where I specified that it use fields from ... A: In that case, you need to use an AFTERUPDATE event in a form to have Access perform calculations for ...
headings in correct order on report	7/13/2009
Q: I am intermediate. Fundraising database. I have query assigning donor level according to amount ... A: I would make a TABLE that basically has the following data in it: ID, Name 1, Platinum 2, Gold 3, ...
creating bounded textbox	7/13/2009
Q: In access how to create a text box in a way that when i enter the data in it in the form the data ... A: Create your table. Now create a form BASED ON that table. Now open up the FIELD LIST and drag a ...
ACESS FIELD QUESTION	7/13/2009
Q: Within my database I have created a field for the number of months of experience someone whas within ... A: The BEST way to do this would be to actually store the DATE that the person started gaining ...
Dropdown	7/13/2009
Q: I have very minimal Access training, but my employer wants me to work on Database for work. We are ... A: First, please make sure you watch my FREE beginner Access 101 tutorial (see below). 1. Yes, you can ...
Date/Time import error	7/13/2009
Q: I'm am trying to import a text file with data from a SQL database. The format on one of the ... A: That is not a date/time format that Access will recognize without some manipulation. Try to get it ...
Report that totals quantites and avr price	7/13/2009
Q: I am trying to run a report that will show me the total quantity of each product sold as well as the ... A: Oh, well if you already have the data in a query already, just run the report wizard and select that ...
Display query's top 10 values in access report	7/13/2009
Q: I want to display (in a report) certain fields from 10 records in a query. I want to select the 10 ... A: In a query, open up the properties for the field you want the top 10 of, and set the TOP VALUES ...
MS Access	7/12/2009
Q: MS Access 2003, MS Access intermediate level(low level programming) Hi Richard, In my MASTER form ... A: You can do this in a couple of ways. Both require some programming. The first would be a loop in a ...
Date Function	7/11/2009
Q: How can I use a date function so it returns a value of past 14 days. I need to generate a report ... A: Try: =Date()-14 or =Now()-14 Let me know if you have any other questions or comments. Also, be ...
Command Button in Access 2007	7/11/2009
Q: Please I needs help on Access.I have a Form with this field DATE,QUANTITY,BOOKS,ISBN & AVAILABLE ... A: See this tutorial for getting criteria into your queries: You can specify criteria for running your ...
Filtering subreport to a record that is using the same table as the parent report	7/10/2009
Q: I have a pretty complex problem and I hope it possible to even achieve. Basically I have a form set ... A: If you base a report off of a query (in other words, the query data FILLS the report with data) then ...
bit of VBA	7/10/2009
Q: So I have created my custom ULS system and on one of the buttons on the menu bar i have coded a ... A: I would recommend making a TABLE that has the list of all of the forms you want the user to be able ...
Passing Data from form to Append Query issues	7/10/2009
Q: I am an intermediate Access person. I saw your post about Using MS Access Append Queries and I got ... A: You probably just need to add quotes around your WHERE condition. When you have a criteria that's a ...
Link a checkbox with a text box depending on value?	7/10/2009
Q: I have a database that I have imported data into from an excel spreadhseet. Part of the spread sheet ... A: You could do this all with an IIF function in an append query. Just use IIF to determine which ...
Updateing table field when a macro is run	7/10/2009
Q: I am new to Access (but ok at vba in excel) and have being trying to find the answer to what must be ... A: You're very welcome. Let me know if you have any other questions or comments. Also, be sure to ...
Query	7/9/2009
Q: I need a Parameter Query to accept more than one entry so i can have it return the resuls for ... A: I would do this as a form. Make a form with the most number of text boxes that you think you would ...
Filtering subreport to a record that is using the same table as the parent report	7/9/2009
Q: I have a pretty complex problem and I hope it possible to even achieve. Basically I have a form set ... A: I wouldn't use a filter. Just set up two queries - one based on each of those combo boxes - then use ...
Calculating Age in Access 2007	7/9/2009
Q: I am trying to calculate age in Access 2007. I need the exact age, not just the datediff age. I have ... A: How exact? To the day? DateDiff is pretty exacting, but you could also just use regular old date ...
Updateing table field when a macro is run	7/9/2009
Q: I am new to Access (but ok at vba in excel) and have being trying to find the answer to what must be ... A: If all you care about is the LAST date that a macro was run (and you don't need to store a complete ...
Ms Access	7/8/2009
Q: I have 5 forms in Ms Access 2003 and want to embed them into one main form and use back and fort ... A: Yeah, you'd probably need a little VBA for this. I would suggest making 5 subforms and then make a ...
Event on form closure?	7/8/2009
Q: First and foremost I want to thank you for all of your help with this database I must say this would ... A: You can prevent the form from closing by going into the form properties and turning off the CLOSE ...
Repeating details	7/8/2009
Q: I am creating a report based on 1 table (RTL). The fields being used from the table in the report ... A: What does your TABLE DATA look like (show me a few records)? What do you want the REPORT to look ...
List Box altering equations	7/8/2009
Q: I am attempting to create a formula that will alter itself according to a list box the users choose ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
Setfocus	7/8/2009
Q: I have a subform with the emplid as the first feild. when they tab it does the Dlookup. If it can't ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Combo Box	7/8/2009
Q: I currently trying to make two combo boxes synchonize where one updates based on the selection from ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
Filters	7/8/2009
Q: Earlier you were asked this: Question Hi, Creating the report of the access from which contain ... A: Sure, you can either use a parameter query to ask for the user ID: ...
forgot to attach the image..	7/8/2009
Q: i send you a question earlier today, but i forgot to attach the image. Here it is. A: I see your relationships now. I think you might be confusing Access with too many related fields. ...
information is not copied into next tabel	7/8/2009
Q: First of all, I am a complete newbie with using Access, and i do not know anything about ... A: Funny, I didn't know Europe was a country. :) It sounds to me like you know what you're doing. If ...
Programming in Access Module	7/8/2009
Q: I am an advance user of Access 2003. I can not get my form command button to execute the program in ... A: Look at the name of your sub: Calculate_Acct Click() I just noticed - it's the same error you had ...
Expression in Access	7/7/2009
Q: I am creating a query in Access. Within, the query, I am creating an expression. However, an error ... A: I'm not sure what you're problem is, but I just tried the same thing with a field called MyTime and ...
MS Access 2007 - DoCmd.OpenReport	7/7/2009
Q: When switching from Access 2003 to Access 2007 (not converting) the code "DoCmd.OpenReport "MotoCros ... A: I can't think of anything else. In fact, I just tested it on my PC and it runs perfectly fine. It ...
Help with sequential numbering	7/7/2009
Q: I have a two tables called “tblProject” and “tblSample” related by “Project_No” field (one to many). ... A: So you basically need to insert new automatically incrementing records in a subform… and you have ...
Auto Increment String Field on Form	7/7/2009
Q: I have a database that was built to house information about our company's standard operating ... A: Well, here is a tutorial I recorded a while back on DMAX and incrementing your own counter: ...
Combobox selector	7/7/2009
Q: I was curious if it was possible to get a combo box to select a form from the drop down list and ... A: Ryan, sure… anything's possible in Access. The EASIEST solution would be to just make a combo box ...
Access	7/7/2009
Q: I am taking a intro to computer class this summer. Currently I am stuck on an Access tutorial ... A: Sorry, I have no idea. It sounds like a problem with your keyboard, but if it's working with other ...
MS Access 2007 - DoCmd.OpenReport	7/7/2009
Q: When switching from Access 2003 to Access 2007 (not converting) the code "DoCmd.OpenReport "MotoCros ... A: The only thing I can think of is that your DEFAULT PRINTER is some kind of PDF file creation utility ...
Securing the Database and Granting Permissions	7/7/2009
Q: I am working with MS Access 2003 & have the Inside Out Book. I am trying to secure the data base and ... A: First of all, here are my thoughts on the Access 2003 User Level Security Wizard: ...
Automatically updating fields within a table	7/7/2009
Q: Let’s say I have a database with two columns. Is there a way that if I enter a value in the first ... A: You have a database with two FIELDS - columns are for spreadsheets. :) Yes, you can do this with a ...
Setfocus	7/6/2009
Q: I have a subform with the emplid as the first feild. when they tab it does the Dlookup. If it can't ... A: First, don't set your field to spaces. That's just bad practice. Set it to EMPTY: "" Of course, ...
filter operations	7/6/2009
Q: . am designing a database in access to monitor training for a company.. everything works well. ... A: See this tutorial I recently recorded on students and courses. It's the same concept and should get ...
Add fields to subform & report	7/6/2009
Q: Please how do i do this? Thank you Peter A: Peter, I don't understand your question. If you want to add fields to a form or report, just open ...
Access filter report	7/2/2009
Q: Creating the report of the access from which contain the checkbox entry . want to make report which ... A: You would need to have a BUILD EVENT in the DETAIL SECTION for your report that basically says: IF ...
Multiple option group	7/1/2009
Q: I am trying to set up a form that is liked to a table for a research project. To keep things concise ... A: See this tutorial on how to set up a Form with a SubForm to show related records: ...
Design Question	7/1/2009
Q: Ok so here is my dilemma: I have a database that requires one input per day for 7 fields. The ... A: Don't make either field a primary key (or indexed / no duplicates). Instead, you could perform a ...
Updating records in a table	7/1/2009
Q: The table that has Product Desription(15,000 records) + Code numbers that are not duplicate. There ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Updating values within a form	6/30/2009
Q: I'm fairly new to access, currently, I'm building a fee schedule for a company i'm interning at. My ... A: Load both values into a COMBO BOX and then use the.column property to figure out the BPS. For ...
Compare dates in VBA - advanced (some VBA	6/30/2009
Q: my question is regarding comparing dates in VBA. I need to calculate eligibility_date based on a ... A: There are many different things you can do with dates and times in Access. You can use the DateAdd ...
Database Structure	6/29/2009
Q: I have worked on normalizing my information but i can't seem to get the information related together ... A: Brandon, your tables look OK, but you might want to consider a separate record for each person that ...
Access Security	6/26/2009
Q: I need to add data level security to my database. I would like to restrict a user from being able ... A: Please see this post in my Blog about Access and security: ...
Setting up combo boxes	6/26/2009
Q: Ok so i got a database for presenting information for different creeks. Right now I got a combo box ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
Sending groupwise email from an access application	6/26/2009
Q: I can send emails with multiple recipients and attachments (really cool - thanks!) but am now ... A: Sorry, I've never used Groupwise, so I couldn't tell you about that application. However, if I were ...
Database Calculation	6/25/2009
Q: My question is in regards to a query or report in Access 2000. I have a database of field elements, ... A: This can be done, but it might be a little complex. If all of the call boxes are sequentially ...
Multiple selection dependent unbound form	6/25/2009
Q: You had a really nice answer to a previous question on this topic ... A: Yes, of course. Anything is possible in Access. You'd need to set up a MULTI-SELECT LISTBOX. Here's ...
VBA Programming	6/25/2009
Q: I'm using 3rd party software to send text messages (works correctly). A feature of the program sends ... A: I don't see the problem. Have you tested your loop with just a MSGBOX to see if the loop is working? ...
Multiple Users	6/25/2009
Q: I have a table that contains information that is unique to the user, but also to the day of the ... A: You could use a DLOOKUP statement to check the combination of user and date: Result = ...
Clearing a form without wiping data	6/25/2009
Q: I am currently using MS access 2007 and I have a form that has 6 fields that the user enters data ... A: Just move to a new record. That will give the user the illusion that their data was saved, and all ...
Where to put Age Calculations	6/25/2009
Q: A month ago I taught myself to use Acceess 2007 to keep track of all my soldiers. I have a DOB ... A: You wouldn't want to calculate age and STORE it in your table because that value is going to ...
User Search Form	6/24/2009
Q: I'm new to access and cannot seem to find a solution through Access Help. I have a Name and ... A: You can make a form with drop-down boxes for your states and categories, then use the following ...
Word/Access Automation	6/24/2009
Q: I'm sorry if this is not the right place to post but I really just need to pick someones brain about ... A: Do you just need the PRINTED forms to look like the forms you are used to, or does it have to appear ...
Update Table	6/24/2009
Q: The file is 15000 lines (product list in excel)and gets updated with new additions and deletions ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
VBA language usage	6/23/2009
Q: I need some help with the VBA language. I created a form that will allow the user to perform budget ... A: I'm not sure. Your code looks fine to me. If tblBdgtLine is your only table in that SQL statement, ...
Access recurring event	6/23/2009
Q: I want to create a recurring event in a database I've created in Access 2007. We certify equipment ... A: WHERE do you want to put this? If you just need to calculate this date in a query, that's easy. Just ...
Attendance Database	6/23/2009
Q: I want to make such a database, wherein, I can make attendance of my students everyday, I do not ... A: See this tutorial: http://599cd.com/tips/access/student-attendance-append-query/?key=AllExperts ...
Using a combo box value to update field in table	6/22/2009
Q: The above is probably very simple but due to my inexperience, I am at a loss! I have three tables ... A: It sounds like whatever code or relationship you have set up there is only supposed to hold the ...
updating fields in access	6/22/2009
Q: first of all i forget how to use access but im good in SQL, i got two tables checkIn table and ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Auto-populate end of month	6/22/2009
Q: Richard, When a user enters a form, I want the field [Date] to auto populate with the last day of ... A: Adam, see this tutorial on my web site: ...
using a calculated field to change a table's field	6/22/2009
Q: I am trying to store the value from a calculated field in a table. I know this is not recommended ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
Access Database calculation	6/22/2009
Q: I have a database which is used to record defects on windows, by size, type and location. I want to ... A: You can use a SORTING & GROUPING LEVEL in a Microsoft Access report to group various like ...
Duplicate Data in Report	6/19/2009
Q: Here is my dilemma: I want to see product in (PO), and product out (SO), on my report. So I list all ... A: I’m assuming your PO and SO are in different TABLES if this is happening to you. Yes? They should ...
Access Help	6/19/2009
Q: in my Access report, I am trying to find a way to keep track of dates after certain time intervals. ... A: Make a query with a CALCULATED FIELD that has your +24 days date in it. Let's call it CalibrateDate: ...
MATCHED query	6/18/2009
Q: I need to do the OPPOSITE of the Unmatched Query. I need to find all the records in a field in ... A: A simple INNER JOIN query (normal select query) should show you just the records that are the same ...
textbox query	6/18/2009
Q: i saw an access sample on the net where the form returns a particular query(e.g. employees in a ... A: There are a couple of ways you can do that. You can use filters (which I don't recommend) or you can ...
Programming in Access Module	6/17/2009
Q: I am an advance user of Access 2003. I can not get my form command button to execute the program in ... A: Your command button's CAPTION has nothing to do with the code it runs. You need to right-click on ...
data entry in a pop-up form	6/17/2009
Q: I have a pop-up form which right now successfully pulls up records that are related by an ID number ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
gap between detail and page footer	6/17/2009
Q: I am a form that has columns separated by vertical lines. I have a gap in the vertical lines in ... A: Make sure there is NO space between the bottom of the detail section and the page footer. The lines ...
Formula for Inventory	6/17/2009
Q: I am working on access 2007 and i need to design report that will show my ending inventory = to my ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Pivot table or crosstabular query?	6/17/2009
Q: I want to display (for comparison purposes) the # of students registered, the average grade and the ... A: I would use a crosstab query, and then use that to feed a report - you can hide any fields you don’t ...
Recordset Loop	6/17/2009
Q: Sorry to disturb you richard by I figured out how to do it. I changed my code into this Private Sub ... A: If this works, then GREAT. If it's something simple like just changing a value, you could also ...
Recordset Loop	6/17/2009
Q: I am currently looping through my records in a subfrom and trying to test for a record value and i ... A: As a general rule, I usually don't analyze code for people - but why don't you start by telling me ...
Enter Parameter Value and (~sq_c OR 2501)	6/16/2009
Q: Richard, I'm using Access 2003. I have some knowledge of VBA but am a beginner. This information ... A: I'm sorry, but your question would involve me actually re-creating this example and trying it - ...
Access Subforms	6/16/2009
Q: I have a database that keeps track of all the companies we interact with. I have a table the lists ... A: You need to set up a MANY-TO-MANY relationship with THREE tables: your companies, your list of ...
update database for new year	6/16/2009
Q: Good Afternoon, I work in Access 2003. I need to update everything in it to use for a new year. ... A: It would take me too long to explain the whole procedure here. I do cover it in my Access courses: ...
Set variable	6/15/2009
Q: I'm trying to develop a macro which will allow me to save an access table to .xls But I'm having ... A: I don't think you can do this with a macro. You'll have to use the VBA TransferSpreadsheet command ...
Problem in creating password	6/15/2009
Q: Im having problem in creating a login password in MS Access 2007..im only the beginner of Access ... A: They removed user-level security (usernames and passwords) from Access 2007. If you're used to 2003, ...
Access	6/14/2009
Q: Peter A: You're better off IMPORTING the data into Excel. File > Get External Data > Import Then pick your ...
Business Hours/Public Holidays	6/13/2009
Q: I've been trying to track down a MS Access Module that will calculate the number of minutes between ... A: I don't have this exact function set up, but you can use DateDiff to calculate the difference ...
Updating an Access Database	6/12/2009
Q: I am creating a table in Access of customer ID numbers and information. However, my company inputs ... A: Is there any way for you to attach your Access database directly to the tables in your management ...
Remove print set-up warning about margins	6/12/2009
Q: I have a button that opens a report. My report is set up with very small margins, but that's how I ... A: Hmmm…. You could try using the command: Docmd.SetWarnings FALSE before the actual print command ...
IIF Statement - Access	6/12/2009
Q: Good Morning, I am in HR and looking to build a report that generates anniversary reminders and ... A: You wouldn't need to use the IIF statement for this… all you need are a couple of query parameters ...
displaying multiple images on a form	6/11/2009
Q: I need to be able to display a variable number of jpeg's on a form. I have built a form which will ... A: How about putting the images in a subform that you can scroll through? I would need to know more ...
Microsoft Access	6/11/2009
Q: I have a problem in Ms Access, I created a calculated control in access, Now I want the calculated ... A: Use an AfterUpdate event in one or more of the fields that make up this calculation to change the ...
Printing multiple reports	6/10/2009
Q: I'm trying to make a button in the switchboard that, when pressed, will print multiple reports. ... A: You can do this with a little bit of VBA or a macro. I would prefer the VBA (I'm not a big fan of ...
listbox for access	6/10/2009
Q: i want to display some text like 'mango','banana','guava' etc in a listbox and selected item to ... A: For just one item, that's easy. Just make your table with your list of items and have that feed your ...
Building a medication management database	6/10/2009
Q: I learned how to use Access about 5 years ago in using MS Office 2003. I haven't used it much so I ... A: Honestly, 2007 is not that much different from 2003. I was able to pick it up in about an hour. ...
Filter on list box with Value List	6/9/2009
Q: Richard, Thank you for taking my question. I'm using Access 2003. I have some knowledge of VBA but ... A: Why don't you try accessing your multi-select listbox using a Recordset instead. Here's a tutorial: ...
Create a Link from Access to Specific Documents in Word, Excel, etc.	6/9/2009
Q: Level of Experience: Slightly Advanced - little VBA I have created a database in Access with ... A: If you look at how the hyperlink is actually stored in the table, it looks like this: ...
Deleting records	6/9/2009
Q: I have 2 tables, a tbl_Product and tbl_Product_Current. In tbl_Product are all my products, month ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Programming in Access Module	6/9/2009
Q: I am an advance user of Access 2003. I can not get my form command button to execute the program in ... A: Well, what is the error you're getting (the EXACT error, please)? You're problem is probably that ...
Parameters and Queries	6/8/2009
Q: I have created an Access Database for multi-lined time cards. I then created a query that allows me ... A: You can post the SQL from your query here if you'd like. But before you do, please watch this ...
Multiple Record Entry	6/8/2009
Q: I have two tables Table1(TripEventTypes) TripEventyTypeID EventTypeID ... A: I don't understand what you're trying to do. If you just want to manually input this information, ...
Undo?	6/8/2009
Q: I am certain the answer to this is no but wonder if you can advise if there is any kind of "undo" ... A: Nope. Once you run an Update Query (or any kind of action query) the results are permanent. There's ...
textbox query	6/8/2009
Q: i saw an access sample on the net where the form returns a particular query(e.g. employees in a ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Labels	6/7/2009
Q: I have created an order entry form that will print out an invoice for each order and a label for ... A: If you need multiple items, you should have that data stored in another RECORD. Don't use multiple ...
Constraint Error	6/6/2009
Q: I am creating an Access database from excel. In the code I get the Database and Tables name from ... A: Why don't you just LINK to the Excel spreadsheet as an attached table (File > Get External Data)? ...
Access Form Design with Fields from 1 table and 2 Queries	6/6/2009
Q: I am using Access 2003. I created a form using design view where I specified that it use fields from ... A: Make the query with your calculated fields. Edit the properties of the form and change the ...
Access	6/5/2009
Q: "please,i am a class teacher, i designed a report data for my students, but i can't place them on ... A: See this tutorial on creating a Running Count: ...
Click Button to Open "Subform" Based on 2 sets of Criteria	6/5/2009
Q: I have a form which I created a simple Macro to open a form to display additional information ... A: Just use an IF statement: IF Field1="X" and Field2="Y" then docmd.openform "FormName" end if ...
Access 2003	6/5/2009
Q: I am new to access and need help. Example Table1 has categories: Id no, score, gender. Table2 has ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Add barcode capability to MS Access	6/4/2009
Q: I have just finished my first access asset tracking program. I would like to automate it with a ... A: See this blog post about Access and barcodes: http://www.599cd.com/blog/display-article.asp?ID=260 ...
beginner level_MS Access 2003 embedded images in a form	6/4/2009
Q: I'm working with MS Access 2003. I have a form that has several embedded images. The images use a ... A: You could try a simple screen capture (press PRNT-SCRN on your keyboard) and then drop the images ...
Access 2007 Autofill fields and auto update fields with new info	6/4/2009
Q: When I make a new record I need to be able to type in the sponsor ID number and have all the ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
dependent combo boxes	6/4/2009
Q: I've read through a number of answers by you and others, but I still can't get my dependent combo ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
Query - Parameter Value	6/4/2009
Q: I have a query and have setup a Parameter Query which at run time asks for "Enter PIN:", is there a ... A: You can't easily do this with just a parameter query. You would need to make a form with multiple ...
Birthday Query	6/4/2009
Q: I have a table with a Date of Birth field (mm/dd/yyyy), I want to create a query that will return ... A: Just make a parameter query and enter in the dates as a criteria: Between [Start Date] and [End ...
update database for new year	6/3/2009
Q: Good Afternoon, I work in Access 2003. I need to update everything in it to use for a new year. ... A: Sure, open up each table and DELETE the records. You could use a DELETE QUERY if you want to ...
Microsoft Access visual basic tabular forum letters for aplphabete	6/3/2009
Q: I have 2 list boxes on a form, one for author name, the other list box for bookname. I want a ... A: This tutorial might help you. It uses filtered COMBO boxes, but the concept applies to LIST boxes as ...
Open Excel File	6/3/2009
Q: Can you tell explain how to open the excel program using an access switchboard? To say I'm a ... A: I don't really use the switchboard much. I would make a button with some VBA code. Using the SHELL ...
Event Procedure	6/1/2009
Q: Travel Itineray Database Design I am trying to input a button on a main form so that when it is ... A: I think your problem might be with your form notation. If you want to refer to a value on a Form, ...
Duplicates in Access	6/1/2009
Q: Hey, I am trying to write a code in VBA Programming. I have a table in which one column has various ... A: The only way you could do this would be with a recordset. Recordsets are basically objects you can ...
Access	6/1/2009
Q: how can I round up a number, for example: I want to round 2.1 to 3. I want to round up to the next ... A: See this tutorial: http://599cd.com/tips/access/round-int-fix/?key=AllExperts Let me know if you ...
How to search names in a form ?	6/1/2009
Q: I've created a student database in Access 2003. I want to create a textbox and command button also ... A: Access provides you with a search button on the toolbar. You can build your own with VBA, but that's ...
Reset Checkboxs	5/31/2009
Q: Richard: I have a subform which has 8 records. One field in each record is a checkbox. When I ... A: Just use an update query instead. You can use an Update Query to change records in a table. See ...
Creating an update query that will update the value of a field if it is Null from the value above	5/28/2009
Q: Like many other posts start….. I am not an expert when it comes to MS Access queries, but I am ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
expressions	5/28/2009
Q: I am trying to make an expression that will add a certain amount of months on specific dates. For ... A: There are many different things you can do with dates and times in Access. You can use the DateAdd ...
Cannot join linked DBF tables	5/28/2009
Q: 1. In Access 2007 I linked DBF tables through External Data-> More-> dBase Files-> Linked Data ... A: Do you have key fields that unify these tables together? Try pulling them into your query one at a ...
Access	5/27/2009
Q: please,i am a class teacher, i designed a report data for my students, but i can't place them on ... A: Use a RUNNING TOTAL in a REPORT. Let me know if you have any other questions or comments. Also, ...
Autoexec macro	5/27/2009
Q: I have a database that I need to update overnight when no one is in it and have used the autoexec ... A: I would develop a second database that only you run. Have a TIMER event that runs your macro when ...
Access Contacts Database > Outlook	5/27/2009
Q: Is it possible to create an Access 07 database containing email addresses in a similar fashion to ... A: Yes, it's possible, but because of the security, you can only send your emails ONE at a time. For ...
Random Test Generation	5/26/2009
Q: I'm creating a access 2003 DB for my company to generate random tests from a pool of questions. I'm ... A: Steven, this isn't something I can answer quickly. I do, however, have an entire hour-long tutorial ...
Calculation Question	5/26/2009
Q: Here's a fun one. I have attached an image to help you better understand my question. No ... A: I'm assuming you reset their checks to zero when they get a pin? How about just checking to see if ...
Order - Pull products avail only to a specific cust	5/26/2009
Q: Intermediate user - I have an order form using order query with a sub form with order detail - the ... A: Include your subcode field on your order form somewhere (get it when you select your customer) and ...
Access To SQL Server - Security	5/24/2009
Q: If an MS Access project (adp) form is connected to SQL Express over the internet that is located on ... A: You cannot assume that ANY of your data is secure if it is going over the Internet and is NOT on a ...
Sort an Array	5/21/2009
Q: I am using MS Access 2007, and I was wondering if there is a simple way to sort items in an array. ... A: There is no simple, built-in function that I'm aware of. You'll need to write your own array sort ...
zip codes	5/21/2009
Q: Is it possible to set up your form so that when you enter a city name it will automatically enter ... A: Sure… you can use an AfterUpdate event and DLOOKUP the info from a table - of course you'll have to ...
Moving a Value from/to fields in Access	5/20/2009
Q: I am new to Access, but I am very familiar with Excel. I am trying to create a macro or VBA or what ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
How to get Access table into text file	5/20/2009
Q: I want to export some data from my access table into text file and I want at the same time to manage ... A: Go to File > Export and there is a wizard that will walk you through it. Let me know if you have ...
Access 2007 returns Row ID instead of text in report	5/19/2009
Q: I'm running a report on an Access 2007 db that I just built. Some of the fields in the table ... A: Make a query. Bring together all of the tables that contain the values you need. Use that query for ...
Before/AfterUpdate or On Click	5/19/2009
Q: I've read through almost all of the Before/After macro writeups and I just can seem to make this ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
input forms in relational database	5/19/2009
Q: I'm creating a database for an organization with a lot of people holding different positions at the ... A: What you're talking about is a many-to-many relationship. You're pretty much doing it right. You ...
Dynamically updating a form	5/19/2009
Q: Should be very simple to do, but I can not get it working for some reason. I have a DB of ... A: Set up the subform so that the linked parent and child fields are whatever field your combo box is ...
Access Form Filter	5/18/2009
Q: I am trying to filter items in a form based on the value the user selects in the combo box. I tried ... A: Just use that query you made as the control source for the form and forget about filtering. In the ...
Count	5/15/2009
Q: I have a report that is run from a query with date parameters in the query, so that when the query ... A: Make a calculated field in a query that gives you a 1 for an "x" and a 0 if not: ...
Data Entry – Hyperlink Fields	5/14/2009
Q: Hallo Richard, I am working on a relatively simple contacts database. I would place my Access ... A: 1. You would have to build your own code to test and make sure that a correct/valid email address or ...
Data Type in design field	5/14/2009
Q: Is it possible to have a checkbox and a date field in one box? I would like to be able to click yes ... A: You could have the checkbox NEXT to it, or even make it invisible once it's checked, but you can't ...
How to add numbers in text field comma seperated?	5/14/2009
Q: I have a text field that contains one or more numbers separated by commas. How can I run a query ... A: You're going to have to use some VBA to split that text field apart. You'll need to know string ...
MS Access	5/13/2009
Q: How do I use an expression in main form to lookup in one table that consists of "Name" and checkbox ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Conditional	5/13/2009
Q: I'm creating a database to track donations. I have a table created with each individual donation ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
last queestion - i found strage solution	5/13/2009
Q: The access table contains all the following fields + autonumber fiele PropId. When I run the ... A: Glad you got it working. Let me know if you have any other questions or comments. Also, be sure ...
ACCESS startup	5/12/2009
Q: I have inherited several ACCESS 97 databases that start into a window with no drop-down menus or a ... A: Hold down the SHIFT key when starting the database. That will bypass the startup routines and ...
Getting the sum from a filtered subform	5/12/2009
Q: I have a form with a combobox and a subform. The combobox will filter the data to display by the ... A: You can place an unbound text box in the FORM FOOTER of your SUBFORM and use the =SUM(Fieldname) ...
Scroll Bar Issues in a Form	5/12/2009
Q: I have a basic form that is larger than the display and it requires scroll bars. I have set the ... A: In Access 2007 you should automatically see scrollbars if the form is too big for the window - ...
retrieving email address from table	5/12/2009
Q: I want to retrieve an email address from a table and use in the "TO" of email to send a report. I ... A: You could use DLOOKUP in some VBA code to look up the email address you want to send, and then place ...
Access 2007 dynamic query using expression builder	5/12/2009
Q: I want a dynamic query that uses expression builder to take a date range off a form ( my reason for ... A: Your syntax is correct. I almost never use the Expression Builder, and I haven't played with it at ...
MS Access 2003 Error	5/12/2009
Q: I was working in a database I built for work last week. I've been using it for a few years. But ... A: Sounds like your database got corrupted when your system froze. Try to COMPACT AND REPAIR it (under ...
auto replace (convert) partial field	5/12/2009
Q: Hope you can help me with SQL as I have been doing this with manually and it was very time ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Set default hyperlink to email in Access 2000	5/11/2009
Q: I have email fields within my tables/forms, and want to use the hyperlink setting so they will open ... A: Use a Hyperlink FIELD type in your table and when you click on it, your email program should open. ...
Import Excel into Access	5/11/2009
Q: I am looking for a solution to import an Excel file into Access table which can be run ... A: Create a DATA IMPORT SPECIFICATION and then use a MACRO with the IMPORT SPREADSHEET command. I ...
History of events	5/9/2009
Q: I have a database using Access 2003 with a table with the following fields: MemberID - Autonumber ... A: You'll have to just use two tables (one for members, one for positions) and then make a one-to-many ...
Tabbing between fields	5/8/2009
Q: Richard, I have only entry-level experience with Access. In my job, I am working with a database ... A: Go to Form Design Mode and click on Edit (or View depending on the version) then TAB ORDER. That's ...
Pre-filling a table	5/7/2009
Q: I have created an "Employee" table that has an ID (Primary Key), Employee Name, Business Unit, ... A: See this tutorial on tracking student attendence: ...
dlookup	5/6/2009
Q: I am trying to do a dlookup and get an error. Here is what i have entered in the control source for ... A: Your field names are nonsense. If you want to refer to a value on a Form, you can use the notation: ...
table sql lookup	5/6/2009
Q: Okay, I have a table called Box_IPs that looks like this: HostName IP Address host1 ... A: Setup a query where you join together the IP addresses, then use that query for the recordsource of ...
Calculating Dates	5/5/2009
Q: I am an intermediate user with no programming background and I am trying to create a recurring ... A: Yes, but you would need to do it with programming code, such as with a recordset. I can't think of a ...
Subform and forms	5/5/2009
Q: Im in need of some urgent help for work. I have a database with a main form and 4 subforms. I have ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Need help with an Equation setup	5/4/2009
Q: I'm working with access 2007, I'd like to include into my report an equation that gives the % of two ... A: I would need to know how tables 1 and 2 are linked in order to answer this. You have to have some ...
Access	5/1/2009
Q: Can I create a box in a report of access, that only will appear when there is data in it? Because I ... A: Sure, you can control whether or not the box appears by changing its VISIBLE property in the Build ...
Condensing Code	5/1/2009
Q: This code is from a cmdbutton on one of my unbound forms. It works great...I am just wondering if ... A: Sure, make a different sub that actually does the SQL part, and send to it your textsubX value. Let ...
Subform question	5/1/2009
Q: I have one table into which I want to enter purchase data. Parents can purchase a book for the ... A: You should have TWO separate tables - one for parents, one for books. They would be linked by the ...
After Applying Filter to Form, Cannot See Records	4/30/2009
Q: I have a database for which I created a data entry form and I wrote VBA code for the Form's ... A: Try a different approach. In the BeforeInsert event, perform a DLOOKUP to see if that record exists ...
Ms Access Linking data	4/30/2009
Q: i am tryin to make a sales database for my friends restauraunt. The basic structure i have in mind ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
access 2007	4/30/2009
Q: i want to link two fields f different table. say suppliers table and contractors table. in suppliers ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
Connect to an ODBC as read only	4/29/2009
Q: I have an ODBC connection to a VFP database and can link to the tables in the database in access ... A: I would control this at the FORM level by turning off AllowEdits, AllowAdditions, AllowDeletions. ...
Query on a Firebird Database	4/29/2009
Q: I'm working on a Time and Attendance app for my Company. I was fortunate to be able to tap in to ... A: You can use DMAX and DMIN for this. They're very similar to DLOOKUP. Here's a tutorial… You can use ...
active forms	4/29/2009
Q: i have different forms opened by different users based on their login information. Their login info ... A: A simple If/Then should do this: If CurrentUser() = "Joe" then docmd.Openform "A" elseif ...
refreshing forms and hiding labels	4/28/2009
Q: Please let me know how I can refresh a form with VBA (if possible) and how I can hide a label if the ... A: Refreshing a form is just issing a Me.Refresh command. You can hide the label in the AfterUpdate ...
Average Function for MS Access	4/27/2009
Q: I have a table with many fields (almost 200). Suppose 4 of the fields are numeric and named A, B, C, ... A: Jim, I've got a VERY long queue of questions to get to, so I'll answer the first part of your ...
openReport error 3464	4/27/2009
Q: I'm getting a Run Time Error 3464 - Data type mismatch in criteria expression I have a DB with ... A: You can't say: OpenDate = Between X and X Try getting rid of the EQUAL sign and see if it works. ...
How do I display room occupants in a lookup list?	4/26/2009
Q: First, I have very limited experience with Access 07 (beginner). I can do the basics (tables, ... A: The EASIEST solution would be to have a CurrentOccupant field in your Room table and then just fill ...
Stripping Out Duplicate Numbers	4/24/2009
Q: I have been asked to create a query that pulls some demographic information from a database. One of ... A: Yes, you can have multiple records with the same SSN. Access doesn't care - as long as you don't ...
removing spaces from the middle of a text value	4/24/2009
Q: Would you please let me know how i can remove a space from a concatenated text field. the fields ... A: Use the IIF function: X: First & " " & IIF(IsNull(MI),MI & " ","") & Last This will give you MI+" ...
Access Import from Excel - Memo Field	4/23/2009
Q: When importing Excel 2007 spreadsheet into Access, the memo fields are not picking up the entire ... A: I would have to see your data to tell you for sure, but I don't know of any specific problems along ...
corrupt access database	4/23/2009
Q: how are you. hope all is well. my database got corrupt somehow. when i attempt to launch the ... A: Yes, open Access and go to Tools > (Database Utilities) > Compact & Repair. Let me know if you have ...
Report in access	4/23/2009
Q: I have a field in a query that uses the following input: eg.. Feb-Jan09 I would like to know, how do ... A: You're going to have to convert that field into something Access can understand - preferably a ...
MS Access on Shared network Drive	4/22/2009
Q: I have an access db on a shared network drive where approx 3-4 people will add records at any given ... A: That's very odd. If you're using a Virtual Private Network, they should be able to work with the ...
export data from access to excel	4/22/2009
Q: I ran query in access 2003 and would like to export the output to excel. the problem is the output ... A: I believe Excel 2003 can only handle 65K rows. You'll need to either keep the data in Access, or ...
VBA code	4/22/2009
Q: I'm trying to update a txt box to zero if the user forgets to enter a value in the field. I thought ... A: How about: If IsNull(Layer1AnnualPremium) then Layer1AnnualPremium = 0 End If Let me know if you ...
access 2007...college student stumped	4/21/2009
Q: i am trying to make a list box, which would need to pull inventory items from multiple tables, to ... A: Brandon, it's certainly possible, but I couldn't explain how to do all of that here. It is ...
Some Ideas Comparing Records Project	4/21/2009
Q: I'm Planning to change a manual process I do at work from Excel to Access to see if I can Automate ... A: Pedro, this is all certainly possible, but it's not easy to do. This is exactly like running ...
Calculating current date and past date	4/21/2009
Q: I have to produce a report for any employee that has called out 7 or more days in a row based on the ... A: By 7 days in a row do you mean 7 business days, or 7 calendar days? The latter would be much easier ...
Lock a record	4/21/2009
Q: i have created a payroll using access 2007. is there an option to lock only one specific record ... A: You could lock the record using some VBA code in a form's OnCurrent event. I'd make a field in the ...
Access	4/20/2009
Q: I imported two tables from Excel to Access and each table has the same column heading/fields. I ... A: If it's just a one-time deal, you can copy and paste all of the records. If you need to do it more ...
Automatically entering information	4/20/2009
Q: "I need information automatically entered in another field after entering a date in a field. ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
MS Access - Building related lists	4/20/2009
Q: I have built a MS database, and need to build a list with a set of criteria and a sub list related ... A: Sounds like this is what you need: I get asked this question all the time. You've got two or more ...
Automatic filling of the Form Fields	4/19/2009
Q: I have created a database with two tables and two forms. 1st table (tblsitedetails) i used to enter ... A: They're not just going to automatically populate. If you want to copy the data from one table to ...
Sending emails with access	4/18/2009
Q: Greetings, I have a database where i need to send notifications via Email when an equipment is due ... A: Absolutely. I would just make a ListBox on a form with a button. The button could run your macro, ...
VBA date function	4/18/2009
Q: I'm new to Access and I have a form called Memo. In this memo I've a date field. I want that form to ... A: Where is this date in the memo form STORED? Is it stored in a table or is it the default value of an ...
Navigating away from incomplete records	4/18/2009
Q: here's the issue I am filling out the text boxes to create a new record, but then change my mind ... A: Well, Access doesn't NORMALLY do what you describe. It sounds like whoever built your database put a ...
VB problem	4/17/2009
Q: Could p'se help me...what's wrong with my code? I would like to search a table after update, find ... A: You need to use DMAX to find the last record entered. It'll be the record with the highest ...
PivotTable Help	4/17/2009
Q: I'm currently stuck trying to make a PivotTable open in form view using MS Access. I was wondering ... A: Just construct the form and then: DoCmd.OpenForm "MyFormName" Let me know if you have any other ...
Access - VBA	4/17/2009
Q: Richard, I have a simple form with a button and a combo box. I want the button to open a report ... A: The best thing to do is to base the report on a QUERY that uses the form field as a PARAMETER. See ...
Input Mask	4/17/2009
Q: I am fairly new to MS Access. I am using Access 2007. I am setting up a database to keep record of ... A: VERY good question. You have two options. You can either expand your input mask with a few ### ...
Access2003 Office 2007 compatibility	4/16/2009
Q: I have an Access 2003 application using an MS SQL Server 2000 database. It has been running fine ... A: They should work fine together 99% of the time, however there are some minor differences. It sounds ...
access to excel - limit transactions	4/16/2009
Q: I have a series of records to export to excel. Multiple records are entered and exported. Say I ... A: First I would ask WHY you're exporting this information to Excel when Access can handle everything ...
Saving	4/16/2009
Q: On a database i recently designed for the entering of security conditions around an area hospital I ... A: Access saves a record when you close a form or move to another record. I would suggest just put a ...
Switch from Access 2003 to 2007	4/15/2009
Q: have a data base that was created in Access 2003 which showed all reports created in left view, now ... A: There is nothing I can do without a copy of your database. Maybe the reports didn't convert properly ...
MS ACCESS	4/15/2009
Q: I created a database and I hid all my toolbars (for design, etc.) How can I get them all back? I ... A: View > Toolbars ??? Or did you wipe out the menubar too? Let me know if you have any other ...
Populating Listbox with VBA - Problem with Commas	4/15/2009
Q: I have a problem i am trying to add a currency value (with commas) to a listbox. The problem i have ... A: I haven't tested this, but try enclosing it in an extra set of quotes. ...EmpName & ";" & "'" & ...
combining entry field to make a combined field	4/15/2009
Q: In my project I have a "project name" that is basically 4 individual identifiers combined into one ... A: See this tutorial: You can use an AfterUpdate or BeforeUpdate event to set values, change ...
form/subform question	4/15/2009
Q: Hi I am an intermediate user but a little rusty and I just need to know where do I go to manually ... A: Just open the properties for the subform and set the LINK child and parent (master) fields. Then ...
Access 2007 Lookup Columns	4/14/2009
Q: Level: User/business analyst experience, beginner programming In Access 2007, I have a lookup ... A: Use a COMBO BOX on a form that is based on a query with a criteria to remove those records from your ...
Access 2003	4/14/2009
Q: I need to create a method that generates a unique ID based on the first three letters of an entered ... A: See this tutorial: http://www.599cd.com/tips/access/incrementing-your-own-counter?key=AllExperts ...
Access & Sharepoint	4/13/2009
Q: I am trying to decide would it be more user friendly to create a data access page and link it to ... A: Personally, I hate Data Access Pages. If all of your users are in the same office on the same ...
Access 2007 Rank query results/rank ID	4/13/2009
Q: Perhaps I should start with a little explanation of my stiuation. I have (successfully) run a ... A: You would create a sorted report with a running total to show the rank. See: ...
MS Access 2003	4/13/2009
Q: I have a report in the Access Database. I want to set up conditional formatting where it changes the ... A: Create another field (perhaps a calculated query field) that determines if the assignment is ...
Filtered form list	4/13/2009
Q: i have a contacts table and a contacts_list form. i want to filter the contacts_list form and click ... A: I would use an append query with a form field parameter to fill your merge table with the data you ...
Users add photograph to curent record	4/13/2009
Q: This is probably an old question, however I would like to be able to allow a user to add either a ... A: Kevin, you can either EMBED a photo in a form / table or you can LINK to it using an OLE OBJECT ...
Reports to print or not sub totals	4/12/2009
Q: I am attaching a image. I do not wish to print a line over a sub total where there is a null value ... A: Name your line object MyLine or something similar. In the Build Event for that section, just say: ...
looping through form elements	4/11/2009
Q: "Hi Richard, I'm trying to write some VB to loop through the form elements in MS Access and retrieve ... A: What about: MyText = Me.Controls.Item(i).Value Eh? Let me know if you have any other questions or ...
After Update question	4/9/2009
Q: I would like to enter a date in one field, and after entering that date, I need another field, which ... A: Well, you will need to use an AfterUpdate event for your first date to set the value. To get the ...
using autonumber as foreign key in a table	4/9/2009
Q: Richard, Here's my problem, I have 5 tables in DB. One of them is Guest info table which uses a ... A: The autonumber just doesn't magically appear there. You have to set it, or create something like a ...
Accessing form in MS Access	4/9/2009
Q: i have a table for uploading trial balance into database. can you please suggest a code which works ... A: Make a button. In the button's event use an Inputbox function to ask for a password. If the password ...
autocad drawing in report as OLE object	4/9/2009
Q: In my form the image of the acad drawing is perfect , when i print a report with the same image (or ... A: Try saving it in a different file format. JPG, GIF, etc. Let me know if you have any other ...
MS access pivot table query	4/8/2009
Q: after creating a pivot query from an MS Access table - Is it possible to save it as a MS Access ... A: I've never tried. Normally, you can't SAVE queries as tables. You can export the data using a ...
Reconciling 2 Tables	4/8/2009
Q: I have two tables that I have been trying to reconciling. One table is data from our software system ... A: OK, join the tables together using an OUTER join. Bring in the IDs from both tables. The ones that ...
Report Formula	4/8/2009
Q: first of all! I have struggled with this for a few weeks. I am not a beginner, but not into VBA ... A: Roger, I have NO idea what you're trying to accomplish based on the information you gave me. Why ...
Basic Information	4/8/2009
Q: I am working with Access for the first time and I don't really understand how it works. I was ... A: Watch my free Access 101 tutorial, then ask me your questions. Let me know if you have any other ...
Auto Create Records	4/8/2009
Q: I have a table for sales contracts (contract table) of a product that is sold in gallons. These ... A: You can use an Append Query to add records from one table onto another. See this FREE tutorial on my ...
Final	4/7/2009
Q: My boss has me working on a projest to computerize a detailed list of security events around our ... A: You can do this by setting up security using the User-Level Security Wizard (Access 2003 and ...
Access Query Expression	4/7/2009
Q: Want to build a query that yields all active members during June 2008. Have enroll date field, ... A: I would need to see your specific database and data in order to tell you exactly what you're doing ...
Combo Box doesnt have all values	4/7/2009
Q: I have a form with a combo box, if the value required isnt there a create a new item so that it can ... A: This isn't easy to do. In fact, I spend a good deal of time in my Access 324 tutorial going over ...
Security functions about MS Access 2003	4/7/2009
Q: I am working on a MS Access 2003 db. This is a very simple one which has only one table. I was ... A: Look into the USER LEVEL SECURITY WIZARD. I'm sure a Google search will turn up all the info you ...
Combo boxes and Text boxes	4/7/2009
Q: I have two combo boxes. The second combo box is populated based on the value selected in the first ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
multiple entries to single forms	4/7/2009
Q: I'm a beginner access user, no programming experience, am only starting to learn to build db and ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
Autofill text	4/7/2009
Q: I am new using 2007 access data base. I keep member information for a nonprofit organization I ... A: You would need to use a Combo Box for this and pre-fill all of the options. Access doesn't have that ...
Linking two combo boxes	4/7/2009
Q: am preparing a form, which contains something like Total Experience = Combo box1 (years) combo box2 ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
VBA - Locking certain columns in a query	4/7/2009
Q: is it possible to lock certain columns in a query with help of VBA? (this query is inserted as ... A: Access doesn't have "columns," it has "fields." You can lock the fields by using a FORM (even in ...
Access 1997	4/7/2009
Q: Good day. I have a business that currently is set up on Access 1997. We have an option that allows ... A: Personally, I would recommend keeping it in HTML. Unless you need graphics, there isn't anything you ...
INSERTING RECORDS	4/7/2009
Q: HOW CAN I INSERT SERCHED RECORD OF A TABLE USING FORM TO ANOTHER TABLE? EG. ADDING DETAILS INTO CART A: You can use an APPEND query or a RecordSet. You can use an Append Query to add records from one ...
Duplicate Entries in Table	4/6/2009
Q: I have a table that contains customer information. The customer information is used to populate a ... A: Awesome. Glad it worked. Let me know if you have any other questions or comments. Also, be sure ...
per user multiple logins	4/6/2009
Q: We are sorry, but Bob Heifler wasn't able to answer yourHi We have an access 2000 databases, and ... A: You would have to code this yourself, possibly in the OnOpen event of a Main Menu form. ...
Syncronizing a subform combo with main form combo	4/6/2009
Q: I want to synchronize a combo box in subform say ComboBox B from Combo box say ComboBox A in main ... A: Here's a similar answer: I get asked this question all the time. You've got two or more combo boxes ...
calculation problem	4/5/2009
Q: Richard, I am having a problem with a calculation in my query. I am trying to add together two ... A: I assume you realize you're missing a square bracket. That's probably just a typo. What are your ...
query	4/5/2009
Q: how do i write a query that wil take a number for ex: $1200.00 and offer a 15% discount and come up ... A: It sounds to me like you just need to learn how to properly create CALCULATED QUERY FIELDS. You can ...
Questions - Time capture, record update and display	4/3/2009
Q: I have a few questions for you. 1. How do i capture the time when a button is hit. 2. how do i get ... A: You get one answer per question. You can "capture the time" using the =NOW() function. If you want ...
Relationships in MS Access	4/3/2009
Q: I hav 2 tables one Stud_contact_details(containing personal details) n another class(academic ... A: The point of having relational tables is so that you DON’T have duplicated data (like the student's ...
Access query	4/2/2009
Q: I have a query that has time values. When I run the query I only want it to show all records in the ... A: The correct criteria would be: =Now()-(1/24) Remember, in Access, time is measured in DAYS, so one ...
MS Access - passwords	4/2/2009
Q: OK, i have 1 form - which is like main menu and from it you can access 2 different forms let's call ... A: If you're using Access 2003 or earlier, you could run the User-Level Security Wizard to setup ...
field default help	4/1/2009
Q: In advance, please excuse the inaccurate terms. I'm new at all this. My objective is to design ... A: I strongly recommend against using subsheets in tables. It's much better to use a form/subform ...
find/replace help	4/1/2009
Q: I run a query, and want to simultaneously change the records of one YES/NO field. They are all NO, ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Returning multiple columns from a lookup	4/1/2009
Q: I've created a db to keep track of radio production work. I have a table (music_inventory) that ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Access fields	4/1/2009
Q: I am working on a database in Access 2003. The form has two fields that are the exact same dates ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
Microsoft Access queries	4/1/2009
Q: I have a database of different clinics run over the course of the year. I have three different ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
MS Access 2007	4/1/2009
Q: I hope that you can solvge my problem. Well in Access when using queries i would like to know how to ... A: There are many different things you can do with dates and times in Access. You can use the DateAdd ...
Modifying the Access 2007 Contacts template	4/1/2009
Q: I'm a beginner to intermediate level Access user, pretty intuative for the most part. I'm modifying ... A: Here's a tutorial on IIF: http://599cd.com/tips/access/iif-function?key=AllExperts All you really ...
Command Button Hyperlink to Excel sheet	4/1/2009
Q: I have been trying to create a command button with a hyperlink to a specific sheet and cell in an ... A: Try enclosing your sheet name in square brackets. Let me know if you have any other questions or ...
If statements??	3/31/2009
Q: hope you can help me, I am probably beginner/intermediate level. I am trying to build a query where ... A: You would just need a basic query with a criteria to limit the records to 5000-5999, and then use ...
Web Page Fields Space Differently on Different Computers	3/31/2009
Q: Hey, I have a database with web pages to share data with my colleagues. My problem is that the ... A: I would need to see this to tell you for sure, but make sure you have your web pages set up with the ...
MS Access Queries	3/30/2009
Q: Q: I have a column in my database which is "Categories", which consists of various categories such ... A: To search for ALL items, use: LIKE * Let me know if you have any other questions or comments. ...
Microsoft Access, Retrieving	3/29/2009
Q: Sir Richard Rost My question is how to retrieve forms in access.....i have seen one of this ... A: There is a wizard to create a search form. I recommend you search for a tutorial in how to use it. ...
Combo box to text box	3/28/2009
Q: (Beginner.) Hi, I have a Car table and a CarMaintenance table. I have a AddCarMaintenance form with ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
Automatically populate information in a form	3/27/2009
Q: I'm in need of a livesaver right now. I am in the midst of launching my own company on a shoestring ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Count text fields per record	3/27/2009
Q: I have a database that is used to track personnel assignments. Each day a supervisor enters which ... A: Use an aggregate query to limit your list of employees to 1 occurrence only (group by). To do this, ...
delete and add autonumber field to table	3/27/2009
Q: An answer to yourI have table1 that has 2 fields, field1 is an autonumber and field2 is a text. I ... A: It would be easier to delete the whole table and copy it from a backup table - or even delete all of ...
how to update records using regexp	3/27/2009
Q: I have a table with a single column of names in "last,first" format, however the entries vary in ... A: You can use an Update Query to change records in a table. See this FREE tutorial on my website for ...
Changing a number	3/27/2009
Q: I've already done the code to get my data how I need it. I sort it from ascending and it sorts it ... A: Use a FORMAT or INPUT MASK in your table / form / report. Let me know if you have any other ...
Security	3/27/2009
Q: I've created a DB for my office and put it on a server so that many users can get access to it. I ... A: Create an MDE file for your front-end (or ACCDE file if Access 2007). Let me know if you have any ...
DLookUp function	3/27/2009
Q: I am building a billing database and have three fields - CompanyName (CN), Location(Loc), and ... A: You would put your DLOOKUP statement in the AfterUpdate event for either of your other text boxes, ...
query criteria	3/27/2009
Q: I have a very long table which contains information about faculty members including their rank coded ... A: Use an Aggregate Query with the MAX function to generate a single list of all of the professors and ...
textbox restrictions	3/27/2009
Q: how do you put restrictions on a text box, like you can't enter a number field or a text field on ... A: Look up VALIDATION RULES. Let me know if you have any other questions or comments. Also, be sure ...
Displaying Query/Multiple fields in a single textbox of a Form	3/26/2009
Q: I'm somewhat new to Access (minimal understanding of SQL) and I'm creating a database for in-house ... A: You're fine… built it as a calculated field in a query just as you did. Now, pull in all of the ...
Assigning an order/sequence number in a query	3/26/2009
Q: I hope you can help me please. I have a database with emergency vehicle details. Each row in the ... A: You should have a separate table to track this, perhaps call it VehicleArrival: VehicleID ...
Inventory	3/25/2009
Q: I am trying to figure out a way to do inventory off of an existing database, any help on this would ... A: It is certainly possible to build a database with complete inventory management with Microsoft ...
Duplicate Entries in Table	3/25/2009
Q: I have a table that contains customer information. The customer information is used to populate a ... A: Is that lookup form BOUND to anything? If you're just looking up the information, don't bind it to ...
Access	3/25/2009
Q: am Robert and i own a small business in Ghana.i created a database that contains particulars about ... A: I would transfer everything over into your Access database. Excel is very limited for tracking ...
MS Access If/Then statement	3/25/2009
Q: Is it possible to use COUNT in an If/Then statement, such as (and I realize this syntax isn't ... A: I would set up the count of each of the fields in a query (an aggregate query if you can) and then ...
Protection	3/25/2009
Q: I have created a database for my office and it works just perfect. the only thing that i cannot do ... A: Then you want to make an ACCDE file (same thing). Database Tools > Make ACCDE Let me know if you ...
Reconciling 2 Tables	3/24/2009
Q: I have two tables that I have been trying to reconciling. One table is data from our software system ... A: Have you tried a FIND DUPLICATES query? Let me know if you have any other questions or comments. ...
Open Documents in Access 2007	3/24/2009
Q: I have a form that uses two command buttons. One is used to "Browse" for a file using an Explorer ... A: Tony, look into the SHELL or SHELLEX commands. They're VB commands you can use to launch other ...
Verifiy append querry added info to table	3/24/2009
Q: I need to verify that data was saved to the table from an append Query....it would give a ... A: The only way to verify that the query worked would be to manually check to see if those records are ...
MS Access 2007 - Date problem	3/24/2009
Q: I am trying to convert a date e.g. 04/24/25 or 06/18/27 to a format yyyymmdd. I use ... A: That’s a matter of the REGIONAL SETTINGS in your Windows control panel. Your system is setup to ...
Query With Form To Supply Parameters	3/23/2009
Q: I have built a query and linked it to a form to set the criteria. My problem is that to retain the ... A: The easiest solution is to add an * record - or at least a blank record - to your actual tables, and ...
having a problem with DSum	3/23/2009
Q: I am having trouble writing a formula that will calculate my checks cleared amount from my deposit ... A: Your screen shot is too small to see any details. Why not make a query field for the underlying ...
appointment database	3/23/2009
Q: hey I am designing a database where you can book appointments for a hairdressers but i am lost on ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
parameter query	3/23/2009
Q: I have a form with a subform, and what I want to do is basicly a search on a form, where the user ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Working with total hours	3/21/2009
Q: I'm renting an equipment to a customer by the hour in a local currency. I have a special program ... A: There are many different things you can do with dates and times in Access. You can use the DateAdd ...
inserting photos	3/21/2009
Q: Microsoft AccessI have used it and feel comfortable with the basics ...... I have created a ... A: Paul, I cover adding images to your database in my Access 102 tutorial: ...
How to disable the "security warning" in access 2007 using a macro	3/21/2009
Q: I want to know how to disable the "security warning: the certain content in the database has been ... A: If Access security was easily disabled the way you suggest, it would be worthless. The only thing I ...
access	3/20/2009
Q: I know that you can buy ms access by itself but can it be used without ms office? Pat A: Sure, you don't need the rest of Office (Word, Excel, etc.) to use Access. Let me know if you have ...
Protection	3/20/2009
Q: I have created a database for my office and it works just perfect. the only thing that i cannot do ... A: What version of Access are you using? If you're using Access 2003 or earlier, just create an MDE ...
MS Access If/Then statement	3/19/2009
Q: Is it possible to use COUNT in an If/Then statement, such as (and I realize this syntax isn't ... A: Where do you want to use this, in a query or VBA code? I would generate both counts, and then use ...
Changing text color and background on individual cells	3/19/2009
Q: I am trying to design a database which holds information collected from a wide array of tests about ... A: You're probably going to need to write AfterUpdate events for your fields if there are complex ...
Duplicate Entries in Table	3/19/2009
Q: I have a table that contains customer information. The customer information is used to populate a ... A: It sounds like you didn't set things up right. What do you mean by "when I access a customer using ...
Access email addresses	3/19/2009
Q: I'm having problems copying all the email addresses I have in access to send out in a microsoft ... A: Hyperlinks are actually stored in multiple parts in the table because they support a visible ...
MS Access	3/18/2009
Q: I created a new project in Access which includes 2 tables 2 forms and a query. I try to multiply 2 ... A: You should set up all of your calculations like this in a QUERY. Make sure your DATA TYPES in the ...
Useing a listbox to create records	3/18/2009
Q: I am working on a laboratory dbase for analysis, but think my question wuld be more precise if I ... A: Fred, your question makes perfect sense. I would recommend using a subform with continuous forms, ...
Access query - Getting the latest date from multiple date fields	3/18/2009
Q: I work for a mother company that trades in four different company names. Our db has a single table ... A: You can use either the DMAX function to determine the last call date, or build an Aggregate Query ...
preview report	3/18/2009
Q: why does every time i create a report when i preview it only half of the page is shown? i mean it ... A: Is it possible that your DETAIL SECTION is too tall? Shorten it up and see if it works. Let me know ...
Using Access with a Batch File	3/17/2009
Q: This is the second time I am submitting this question. the first person couldn't help me and I ... A: Why don't you just dynamically create this batch file each time you need it using FILE I/O ? Open ...
Creating an alert in MS Access via report or pop up window	3/17/2009
Q: I am setting up a basic personnel/HR database. I have an area in the database that has a lot of ... A: First, create a query that shows the data you need. I assume you know how to do this. Now, without ...
access -updating a table	3/17/2009
Q: A very good software friend of mine created this database and every so many months I get this ... A: JoAnn, I don't understand your question. Are you saying that there is a password needed to get into ...
Un-Filtering Filtered Combo Boxes (Cascading)	3/16/2009
Q: I am stumped. I have two unbound combo boxes where the value selected in ComboBox_1 filters ... A: Are you using actual Filters? If so, I recommend dumping them. Use the technique I recommend in this ...
validation rules	3/16/2009
Q: writing validation rule for control on Access2007 form. Criteria is (in english): if [rank] is like ... A: This is too complex for a simple validation rule. You'd need to use a BeforeUpdate event and ...
Counting Yes/No values in single record and adjusting denominator in formula based on count	3/16/2009
Q: Richard, Thanks for taking the time to view this question. I have built a form in Access 03 ... A: AC, do you need to do this for each score card as the data is entered, or do you need to do it en ...
project	3/15/2009
Q: When a project is created in MS_ACess how do we create a link from the desktop to my our project ... A: Create a macro and name it AutoExec. Let me know if you have any other questions or comments. ...
insert into	3/13/2009
Q: been searching hrs and hrs not much joy. I have created multiple search boxes for various criteria ... A: Felix, glad I was able to help get you up and running! Enjoy. Let me know if you have any other ...
Changing text color and background on individual cells	3/13/2009
Q: I am trying to design a database which holds information collected from a wide array of tests about ... A: How about Conditional Formatting? Let me know if you have any other questions or comments. Also, ...
increment form line number	3/13/2009
Q: I have the following code I'm trying to get working. I have one unbound textbox that accepts the ... A: This is very similar to a tutorial I prepared some time ago: ...
validation	3/12/2009
Q: Can you answer an access question? I think what I'm looking for is a validation program. I have a ... A: Sure, but that all depends on when and how you want to look this information up. If it's just a ...
Filtering?	3/12/2009
Q: Let me tell you first, that this is not a school project, since my school days have passed quite a ... A: I would use the DMIN function to determine which of the suppliers has the lowest price. DMIN is ...
Separate Lookup Table	3/11/2009
Q: I'm trying to replicate in Access a feature I really like in Microsoft Navision Databases. I would ... A: This is not a "built in" featue that I'm aware of. You would have to code it yourself. I would ...
questionaire database design	3/11/2009
Q: Basically a member fills out a form and there anwsers are recorded. There are 3 forms and the ... A: Where are your answers? Shouldn't you also have an answer table to store the answers they gave? If ...
ACCESS QUERY BY FORM	3/11/2009
Q: I am trying to set a query by form. I have two tables, Company and Contacts. What I need is a form ... A: Creating a subform is not EASY, but its not incredibly hard either. In fact, there is a wizard for ...
MS Access query question	3/11/2009
Q: Hey Richard, I wonder if you could help(MS Access 2003)...each week i receive a cumulative list ... A: How about importing the data into Access and using a Find Unmatched Query, linking both tables by ...
USING THE SWITCH FUNCTION?	3/11/2009
Q: I'M CREATING A DATABASE FOR A CHILDREN'S PROG I'M RUNNING AND WANT TO CREATE A FIELD THAT'S ... A: Personally, I wouldn't use that function. I would make a second table that has just: 1, 1st 2, 2nd ...
Multiple record update form	3/10/2009
Q: I have a simple database with one table. I need a form to update multiple records with the same ... A: If you're using a multi-select listbox, that's a bit more complex than I can go into here. It ...
Combo Box	3/10/2009
Q: Good afternoon Mr. Rost: Please see code below) - When I type in the new data to be added my ... A: I'm not sure. Your code looks fine to me. Without reproducing it, I couldn't tell you for sure. ...
Relation between field names	3/10/2009
Q: I am having problem with relating file names.I have one field name GENDERCODE and another GENDER.If ... A: Why are you using two different fields to store the same information? Just use one. You can always ...
Insert blank spaces to string	3/9/2009
Q: I have a query and a field named registration number which contains 17 characters including numbers ... A: Add spaces to your string first. You could do it in VBA code - you'll probably have to write your ...
MS Access?	3/8/2009
Q: I work at a company that serves adolescents and their families. We have about 50 forms that repeat ... A: You would just need a CHILD INFO table that stored this information, then a separate table for each ...
A query	3/7/2009
Q: I have a data base that lists computer # and shift # and scanned items names and who scanned them ... A: Put all of the data together in a query. Use that query to feed a report. You'll need sorting and ...
de-concatenation	3/7/2009
Q: I need to importing data from another database into mine. However the common key from the other db ... A: I would just pull all of the data into your database as it is, and then create your three new ...
Countif Function in Access	3/6/2009
Q: Okay, so I know this doesn't exist in Access, but is there a way to get a report to count for a ... A: You can use DCOUNT which is very similar to DLOOKUP. ...
Report listbox grouping	3/6/2009
Q: I have a report that shows employee injury data (body part injured & date) presented in List Boxes ... A: I'm sure it's possible. You could add another column to indicate which shoulder it is. Personally, I ...
Printing iin Access	3/6/2009
Q: I am trying to print to one page (legal size) and it prints two pages. How do I get it condensed to ... A: Reduce the size of your report - either you have too much data, too large of a margin, etc. Let me ...
Splitting Database	3/6/2009
Q: I built a database with the intent for multiple users accessing it at the same time. So I decided ... A: You should be able to. Access is, by nature, a multi-user database provided neither of you have ...
IsNull function in MS Access	3/5/2009
Q: I am working on a database system with entry forms and am having an issue with the IsNull function. ... A: Jennifer, I cannot recreate this problem. I created a text box based on a memo field and make a ...
Intercept Delete command via OnDelete event	3/5/2009
Q: I have a subform based on a query. I do not want the user to delete records due to the intricate ... A: Forget the OnDelete event. Just make a button that SAYS "delete" on it that sets your HIDE checkbox. ...
content of a cell in record	3/5/2009
Q: Is there way in VBA do detect what is written for example in fifth record in second field? (That is ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Report/Query Questions	3/5/2009
Q: I have a table that contains one record with a date from, date to and total lodging rates. Ex ... A: You'd need a recordset to loop through the dates manually in VBA code. That's the only way I can ...
Adding up check box totals for query	3/5/2009
Q: I need help in running a query that will only add up the yes/no check boxes from my table. A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
De-Select Default Text in Text Box	3/5/2009
Q: I have a text box on a form that is prefilled with default text. 99% of the time I want to continue ... A: I have no idea. Must be a quirk on your system. I even tested this code before I sent it to you and ...
Running Total	3/4/2009
Q: In my database we have a table that tracks hours put on small engines each day with assigned ... A: I'm sure it's possible. Just use a query criteria to see a specific engine, or use sorting & ...
MS Access Generate Serial Number from Financial year	3/4/2009
Q: I wish to create a serial number from the financial year and an incremental number which starts ... A: The year is easy: =Year(Date) The counter is the hard part. See: ...
Group by Query Ignores Case?	3/3/2009
Q: If a text field in a table contains values that are mixed case, such as I, i, R, r etc. Why would a ... A: This is just how Access performs its sorting. Anything else would involve some complex programming. ...
Extension Change	3/3/2009
Q: Sir, I need to change extension of mdb file at the close of application vb6. A: I don't know WHY you'd need to do this, but just use the NAME command in VB to rename a file. Let ...
Table relationships	3/2/2009
Q: I am building a database which will be used to enter data about in-house publications. I've created ... A: Mark, it sounds to me like you have a good handle on it. You're right to create separate tables for ...
ms access importing field query	3/2/2009
Q: Sir, I have a db table of baseball results, call it table1 - fields are entitled: hometeam, ... A: You really don't HAVE to look up that information and store it in a duplicate table, since you could ...
Scanning barcode into Textbox	3/2/2009
Q: We have barcodes that are scanned into a textbox. Is it possible to scan in a barcode into the ... A: Not really. You see, 99% of all barcode scanners that I have worked with send the scanned data ...
SOLVED: Vba to requery a subform from another form's OnClose event	3/2/2009
Q: Other posts around the Internet (and on here) I've read people are requerying (i.e. live updating) a ... A: Glad you got it working. Let me know if you have any other questions or comments. Also, be sure ...
copy and delete temp table to table	3/2/2009
Q: I converted old church dbase lll input programs to access. After I input the data in and it looks ... A: You can use an Append Query to add records from one table onto another. See this FREE tutorial on my ...
Reports in Access	2/26/2009
Q: I'd like my report to sort by invoice # but then I want it to group any invoices from the same ... A: You can use a SORTING & GROUPING LEVEL in a Microsoft Access report to group various like ...
public functions in VBA - MSAccess	2/26/2009
Q: I have a program that uses many separate forms that do similar things. As such I have user-defined ... A: A public function needs to be declared in a MODULE (outside of all of your forms) to be available to ...
MS Access all read only now	2/26/2009
Q: For the past few days I have been trying to put all my DVD's into a database using the " Lending ... A: Check the READ ONLY attribute of your database file (MDB/ACCDB) and make sure it's not checked on. ...
Setting path.	2/26/2009
Q: I have a command button on a form, that command button popups a System Import Dialog box, I need to ... A: Use the Microsoft Common Dialog Control ActiveX object. I cover it in this class: ...
De-Select Default Text in Text Box	2/25/2009
Q: I have a text box on a form that is prefilled with default text. 99% of the time I want to continue ... A: Make an OnGotFocus event for your textbox that says: MyField.SelStart = len(MyField) This will ...
Less than or equal to	2/24/2009
Q: If I have two fields in a querry that I want to know if one field is less than or equal to the ... A: Use IIF. ThirdField: IIF(Field1<Field2,"Less Than","Greater Than") You can use the IIF function to ...
query value to a form	2/24/2009
Q: I have two tables-- Customers and Account Transactions. I have created a query that provides a total ... A: Use DLOOKUP in an unbound form field OR make a transactions subform with a footer total. See: ...
insert into	2/23/2009
Q: been searching hrs and hrs not much joy. I have created multiple search boxes for various criteria ... A: Felix, I have NO idea what you're trying to do. There are two things I think might help you: either ...
Multiple entries in one column in Access 2007	2/23/2009
Q: I am working on this project. I need to be able to combine multiple fields from a form and store all ... A: This goes against proper relational database design. You should have multiple items stored in a ...
Calculate working days and dont include public holidays	2/23/2009
Q: The below function calculates working days which works just fine. I need to adapt this so that it ... A: After your SELECT CASE statement. If it's a holiday from your table, add or subtract days ...
insert an incremental number in access	2/23/2009
Q: I am setting up an Access database that will automate a ranking system for a table tennis school. ... A: Looks good. Let me know if you have any other questions or comments. Also, be sure to visit my ...
How to change the recordsource of a Pivotchart form from another form	2/23/2009
Q: I have a form "frmPivotChart" which is a pivot chart whose recordsource is "Select * from Table1". ... A: You can't call: Forms!frmPivotChart.caption ??? If you want to refer to a value on a Form, you ...
Access use with calender	2/22/2009
Q: I run a charity project providing transport for elderly using volunteers with their own cars. I am ... A: You might be better off using Outlook if all you're doing is scheduling simple trips for a few ...
how to store calculation information from a form to a table	2/22/2009
Q: In the database that I have, I have a form that, based on the data entered in certain fields, does a ... A: Make a field in your table for the value. Change the ControlSource of the box to that field. Now ...
project	2/22/2009
Q: When a project is created in MS_ACess how do we create a link from the desktop to my our project ... A: Put the MDB or ACCDB file in a shared network folder. Go to their machine. Browse to that folder. ...
Audit DB Access 2003	2/20/2009
Q: I am building a DB using Access 2003 it is basically a checklist for auditing our employees, there ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
invoice problem	2/20/2009
Q: I make an invoice, I chose product name in a combo box when unit rate should come automatically ... A: There is no "automatically" unless you MAKE it happen. You can use the DLOOKUP function to look up ...
Link Email to a Check Box on a form	2/20/2009
Q: I have a list of check boxes with respective collegues. I also have a button labled "send". I ... A: It's certainly possible to do this, but I can't explain it all here - it would take far too long. I ...
Vba to requery a subform from another form's OnClose event	2/20/2009
Q: Other posts around the Internet (and on here) I've read people are requerying (i.e. live updating) a ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
Sync Combo Box with Form	2/19/2009
Q: I have a form/subform. On the main form I have a combo box that is linked to a field that is on the ... A: I can't really give you more specific help because I'm still not 100% sure what you're looking for. ...
3 tables 1 Form	2/19/2009
Q: I have 3 tables contract cta = primary key parent address cta = foreign key child ... A: If you're a complete novice, then I have to warn you that you're not going to be able to do this ...
Query using multiple related tables	2/19/2009
Q: I have designed a database to allow a Company (table) multiple Points of Contact (POC's) (Table), ... A: I would build a CROSSTAB query and then just bring in all of the information that you need as ...
Simple Cancel button	2/18/2009
Q: Richard, I have a background in MSSQL but am new to MSACCESS. I am currently using Access 2003. I ... A: Cancelling the input prompt can still cause Access to prompt you for more input from other ...
Reset Security settings?	2/18/2009
Q: I was playing around with the User-Level Security Wizard and created security settings I do not ... A: This all depends on what version of Access you have (you didn't specify). Easiest solution: just ...
Combo box	2/18/2009
Q: I sent a question earlier but it seems some of it got chopped. I wanted to run a query with [Enter ... A: Make a small form with a combo box on it to get the parameter. Then open the results with a command ...
Moving test query to QDF	2/17/2009
Q: I have assembled a test query and now need to convert it into a QDF using parameters taken from a ... A: Simplify it first by making a query that filters your data, then make your QueryDef based on that ...
Parameter Search...maybe?	2/17/2009
Q: I'm VERY new to creating access databases, and am only using the wizard and design views, I have ... A: You want to open a form and show a specific record. If you're new to Access, this is going to be a ...
Scanning barcode into Textbox	2/17/2009
Q: We have barcodes that are scanned into a textbox. Is it possible to scan in a barcode into the ... A: Scanning a barcode is just like typing to the computer. All you could do is lock the field after so ...
Relationship in MS Access 2K	2/17/2009
Q: I have multiple tables in ONE Access db like Tbl-1, Tbl-2 etc. (fields in a table = Fld). Most of ... A: I have no idea. Without seeing your database, I can't tell what's going on. Why are you looking up ...
Field with incrementing number Access 2007	2/17/2009
Q: I have built an access db to enter requests. I need an automatic numbering for the request No. So ... A: 1. Before Insert is a FORM event. Look at the form-level properties. 2. Yep. Let me know if you ...
VBA in Form	2/17/2009
Q: I have a Form with several controls. For 2 of the controls I want an action to happen on ... A: Unlesss your controls are named exactly the same (Button1, Button1) which is technically impossible, ...
Displaying multiple recordset results in a pop-up form	2/16/2009
Q: I have a MS Access 2003 database with multiple forms and one main table. On the search form the ... A: I don't think you're using the recordset correctly, but without seeing your database and ALL of your ...
Web Form	2/16/2009
Q: Visitors to our website http://www.assistedlivingonline.com can view retirement centers listed by ... A: This is not a Microsoft Access question. This is a web-design question and all depends on what ...
When to allow zero length values or nulls	2/16/2009
Q: I'm having difficulty figuring out when to have a Null or a zero-length value. What is the ... A: Excellent question. See: http://599cd.com/tips/access/null-zls?key=AllExperts Let me know if you ...
Datepart	2/16/2009
Q: I am trying to figure out the Week number that a patient has been in our facility. For example, if ... A: I don't understand how you're calculating the week-part of a result that's a number of days. What ...
Form won't allow fields to be edited	2/16/2009
Q: I've got a change form that uses a query as the record source. I've got all update flags turned on ... A: Check the AllowEdits property of the form. Make sure your underlying query is editable. Sometimes ...
Update Query	2/15/2009
Q: I try to update a field in a table with writing another query to "update to" field.However it only ... A: You can use an AfterUpdate or BeforeUpdate event to set values, change properties, run macros, and ...
Subform Display	2/13/2009
Q: I am having a problem with displaying my subform. Previously, I was not able to see my subform in ... A: It should stick. When you save your form, that setting should be saved along with it. Let me know ...
Linking forms together	2/13/2009
Q: I have built a database of our clients, within that i have divided the information in to four ... A: You need to create a command button from your clients form to open up the billing form (tab) showing ...
merging info from multiple fields to one field	2/13/2009
Q: I need to add a string to both the beginning and the end of a field. I have a database in which a ... A: Leave the data as it is in the table. Just make a calculated query field to assemble it as needed: ...
Parameter and Append Query	2/12/2009
Q: I have created an append query the pulls sales from a very large table. I only want to pull ... A: The EASIEST thing do to would be to add a "DataHasBeenPulled" value (Y/N) in your sales table. After ...
Update a field in a table based on value choosen in combo box	2/12/2009
Q: I have a table called "Extensions_TBL" which has two fields (extension, which is an Integer and ... A: Just use an Update query. The values are already on your form. If you want to refer to a value on a ...
Criteria [Enter Name:]	2/12/2009
Q: When I run this query it does as I was taught by you. I have noticed that it would be better to ... A: You would need to build a small form with just your combo box on it (and perhaps a button to launch ...
counting in Ms access	2/12/2009
Q: I am trying to do some filtering inmy table. I have a table with the following fields : date / ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
insert an incremental number in access	2/12/2009
Q: I am setting up an Access database that will automate a ranking system for a table tennis school. ... A: I can't think of any NON-VBA way to do this with sequential numbers from 1 to X. You would need to ...
Choose Data Source based on Radio Buttons	2/11/2009
Q: I am trying to reduce a growing amount of redundancy in my Access 2003 DB. This is a simplified ... A: With a FORM you could just dynamically change the RecordSource property, even with the form open: ...
Access - User ID	2/11/2009
Q: <<Previously answered question, with un-usable results>> I tried using “=CurrentUser()” in a form in ... A: The CurrentUser() function does NOT return the user logged into Windows. It returns the user that ...
Numeric Field Overflow	2/10/2009
Q: I keep getting an error saying "Numeric Field Overflow" when I run this Make-Table query. If I just ... A: I can't tell just from looking at your SQL. It's a mess. Can you simplify it any by making multiple ...
Choose Query (sort of)	2/10/2009
Q: I've developed a database for an elementary school. It has alot of tables and queries, but right now ... A: Brenda, all you need to do is create a query and RELATE StudentID from both tables together. I'm ...
Access 2007 Calendar Form	2/10/2009
Q: I need to display event information on a form in Access 2007 that looks like a calendar. Can you ... A: Setting up a complete calendar iin an Access report would not be an easy undertaking if you want it ...
Suggested reading list for Database Creation	2/10/2009
Q: What books can you suggest to read to help me with the following database issues: normalization ... A: Of course, I recommend my OWN video tutorials and handbooks which you can find at ...
MS Access - Reporting with multiple tables	2/10/2009
Q: I am a beginner with Access and am in the process of creating a database to help my dad with his ... A: You need to set up a SORTING AND GROUPING level for your report. See the free tutorial below. Also, ...
Query criteria altering command button	2/10/2009
Q: What would be the best way to use a command button to instruct a query to toggle between two ... A: Use an unbound text box on a form to simply hold the value for your criteria. See: If you want to ...
Sync Combo Box with Form	2/9/2009
Q: I have a form/subform. On the main form I have a combo box that is linked to a field that is on the ... A: You want to synch the combo box with the subform? You'd need an AfterUpdate event and then requery ...
3 tables 1 Form	2/9/2009
Q: I have 3 tables contract cta = primary key parent address cta = foreign key child ... A: You're going to need some programming to do this. Do you know any VBA at all? What is your skill ...
insert into	2/9/2009
Q: been searching hrs and hrs not much joy. I have created multiple search boxes for various criteria ... A: The SQL statement you have there is only going to insert one record into a table. In order to insert ...
Query using multiple related tables	2/9/2009
Q: I have designed a database to allow a Company (table) multiple Points of Contact (POC's) (Table), ... A: David, what's your question? If you want to see all of the related items for each of your POCs, then ...
Keep items together in Columns in Access 07	2/9/2009
Q: I have a report with several League and within it several 6-team divisions on 2 columns per page. We ... A: Use a Sorting & Grouping Level with a "Keep Together" setting. You can use a SORTING & GROUPING ...
Creating a query that does calculations between 2 tables	2/9/2009
Q: I am new to Access. I am trying to create a query that will net off usage of inventory against the ... A: If you are new to Access, this is probably beyond what you should try right now before getting some ...
Residual Password	2/9/2009
Q: Some time ago I had a clients database on my laptop to to some VBA development. The original ... A: You need to use the Access Workgroup Administrator to change the password of your admin account. Or ...
Combining .mdb form data	2/9/2009
Q: Under the "Forms" menu, we have a "Contacts Data Entry Form" that contains basic information about ... A: You would need to figure out what your common field(s) are. Ideally, the customers would all have ...
True/False on Record comparisons	2/9/2009
Q: I have a file withe about 9,000 records that I would like to identify duplicates and then do a ... A: How about a Find Duplicates Query? Or you could use an Aggregate Query to get a COUNT of how many ...
Linking PDF's to a table	2/9/2009
Q: I would like to be able to set-up a tab that would relate a specific form's PDF copy. Click on the ... A: Use an OLE object and paste a link to the file in there. Let me know if you have any other ...
Linked mssql database	2/9/2009
Q: We have several mssql databases. We are running an access database to 2 of them. We had a third ... A: You could do it with source code. I know there is ADO or DAO code (recordset stuff) that you could ...
How to make multiuser MS access 2000 form?	2/9/2009
Q: I am using MS ACCESS 2000. I want to know how to convert single user ms access database to ... A: The MDB file is THE Microsoft Access Database File. When you make an Access database, that's the ...
Changing FontBold property on MouseMove	2/9/2009
Q: My question is how do i set the FontBold property of a command btn to True only when the ... A: I gave you a different answer a minute ago, but I just played with it and figured out an easier one: ...
how to store calculation information from a form to a table	2/9/2009
Q: In the database that I have, I have a form that, based on the data entered in certain fields, does a ... A: How are you calculating the value? In the ControlSource of the text box? If so, scrap that and ...
Comparing Table Check Boxes and returning Unmatched Values	2/8/2009
Q: I am creating a database to compare skills required for a job with current skills acquired by an ... A: How are you storing the data? Is it a true Yes/No field, or the word "YES" in a text field. If the ...
records 1 thru 3 on same line of report	2/7/2009
Q: we are a homeschooling family, I have been using Access to create databases to track grades etc.. ... A: The only EASY way to do this would be to use multiple columns in your report. I cover this in my ...
dates in Ms Access	2/7/2009
Q: There is a sub form in which i want to display the current year automatically. I want to know how to ... A: Make the default value for that textbox: =Year(Date()) Let me know if you have any other questions ...
OpenForm to a Specific Record	2/7/2009
Q: I'm a VBA newbe and I'm trying to get a Subform called "CallList Subform" in datasheet view to open ... A: Specify a WHERE condition. DoCmd.OpenForm "FormName",,,"Value=" & FieldName I cover this in my ...
Access Question Involving DISTINCT	2/6/2009
Q: When I use the following Command SELECT DISTINCT [Significant Failures].[O&M], FROM [Significant ... A: If by LINES you mean all of the fields, then just include those fields in your SQL statement. ...
log file	2/6/2009
Q: i am writing a program for a cybercafe on ms access. im facing a hard time trying to create a ... A: Yes, it's possible. In fact, I spent a good portion of one of my tutorials covering this. It does ...
Using a query result to determine the value for a control	2/6/2009
Q: I have a form with two fields - "ItemNo" (number field with Duplicates OK) and "NewItem" (a Yes/No ... A: You'd be better off with a DLOOKUP statement instead of a Find Duplicates query to see if that ...
Transfering a caluclated age to a linked form	2/6/2009
Q: I am not particularly familiar with MS Access, but using sites such as this, have worked out how to ... A: Ssee this tutorial: You can use the DateAdd function to add or subtract date values. Want to add 6 ...
Decimal values in Access	2/6/2009
Q: I have a doubt regarding decimal values in access... how could i round up a decimal entry in ... A: Good question. See this tutorial: http://599cd.com/tips/access/round-int-fix/?key=AllExperts Let ...
insert an incremental number in access	2/5/2009
Q: I am setting up an Access database that will automate a ranking system for a table tennis school. ... A: This would not be easy to do WITHOUT some VBA programming if you need to store these values in the ...
Access Forms VBA Code	2/5/2009
Q: I want to be able to activate a field on a form base on the selection from a dropdown box. Example, ... A: A little AfterUpdate event should do it: IF ComboBoxName = "Value 1" THEN MyField1.visible = ...
Null Value	2/5/2009
Q: I have an ‘if statement’ within my form to clear the data entered according to the combo box ... A: Your IF structure is wrong. IF condition THEN stuff ELSEIF condition THEN stuff ELSEIF ...
Calculate working days and dont include public holidays	2/5/2009
Q: The below function calculates working days which works just fine. I need to adapt this so that it ... A: You'll need to make a table containing the holidays you want, and then DLOOKUP to see if that ...
stopping duplicates from the form using IF	2/5/2009
Q: I have an access database, my PK is already set and I do not want to chaneg it. However, there are ... A: Yes, but where are you initializing PreviousRecordID? If it's a form field, then don't look for it ...
problem with insert into command in vba	2/5/2009
Q: I have a problem with insert into statementin vba. I want to insert the user input details from an ... A: Why don't you attach (link) to the table and then try it with a simple APPEND query? I can see ...
Selecting	2/4/2009
Q: What I am looking for is the next step up from a combobox. I have a form to record issues from ... A: You're right - a combo box can get unruly when you have thousands of records in it. How about a ...
Field Validation	2/4/2009
Q: I have a MS Access Form, and one of the fields is open for a currency entry. However, I want to set ... A: In order to have validation across multiple fields, you'll have to code it yourself in VBA. You can ...
Really Stupid Question....but....	2/3/2009
Q: This will seem like the most stupid question of all time, but I really can't find an answer ... A: Send "focus" to an object like a button. Put a "CLOSE" button or something in the form footer. In ...
Forms??	2/3/2009
Q: I've just started using access recently and very new to referencing/tailoring forms to specific ... A: I don't understand… how do you want to execute this? A drop-down list on a form is probably the ...
limit amount of text	2/3/2009
Q: I'm told that you can not limit the amount of text entered into a memo field (i.e. 2000 characters). ... A: I would use a MEMO field. While it is true that you cannot specify a TABLE-level limit on the amount ...
msaccess prompt table name	2/2/2009
Q: I am a newbe to msaccess and would like to get some help. i would like to know whether it is ... A: It's not possible to do this without at least some programming. If you're a newbie as you say, I ...
Access 2007 option group totals on report	2/2/2009
Q: Another allexpert is asking for $$, so I'll ask my question here if you don't mind. I'm new to ... A: You mean if someone checks A,C, and E you want to just add those items together? Simple… put a ...
Text box displayed from table record	2/1/2009
Q: This question has probably been answered before but I can't seem to find it. I have a database with ... A: My first question would be WHY you have separate forms for each state. But, getting beyond that, I ...
text in a report	1/30/2009
Q: ..i'm trying to make a report that is an agreement that i use for business every day...the only ... A: You could create a table just for this stuff and then include it on your form, and pull it into your ...
Export access table to Excel format	1/30/2009
Q: I am trying to use the following code. Its working fine. But my problem is I want to use the below ... A: You need parentheses around your SELECT statement. Let me know if you have any other questions or ...
scanning bar code information in access	1/29/2009
Q: I want to create a database in access.I also want to scan bar code label information into few ... A: Yes, it is possible. You'll just need to purchase a barcode reader and font if you want to make your ...
Conbo box - Blank Entry	1/29/2009
Q: My question is regarding a combo box which is bound to a table, I would like a blank entry from the ... A: Then you'll have to add a BLANK item to your table data. Let me know if you have any other ...
How to make multiuser MS access 2000 form?	1/29/2009
Q: I am using MS ACCESS 2000. I want to know how to convert single user ms access database to ... A: Just put the database MDB file in a shared network folder and everyone should be able to open it. ...
Comparing Table Check Boxes and returning Unmatched Values	1/29/2009
Q: I am creating a database to compare skills required for a job with current skills acquired by an ... A: Don't hide them. Use a QUERY CRITERIA to simply filter out the NO data before creating your report. ...
Autogenerating a custom primary key	1/28/2009
Q: I am trying to keep track of projects. The current format used to identify requested projects is the ... A: See this tutorial on creating your own counter: ...
Access 2007- Reports	1/28/2009
Q: Richard, this is a follow-up question to one I asked yesterday. I had asked how to create a report ... A: Dawn, honestly I have never experienced this issue. Like I said in my previous message to you, I ...
Communication with Access 2007	1/28/2009
Q: I am currently working on a project in where I am transmitting collected data from labs using XBEE ... A: I can give you the nutshell, but explaining it in detail would take far too long. First, you'll ...
stopping duplicates from the form using IF	1/28/2009
Q: I have an access database, my PK is already set and I do not want to chaneg it. However, there are ... A: You're never setting the value of PreviousRecordID anywhere. Other than that, your code looks fine. ...
Validation Rule	1/28/2009
Q: on a form i placed a date field which validation rule is that the users not allowed to enter more ... A: You're better off using your OWN validation in the form, preferably in a BeforeUpdate event. Don't ...
specific report format	1/27/2009
Q: I am not a programmer. I am a soldier. I was asked by my supervisor to find a way to automate a lot ... A: Yes, you're exactly right. You need to make a crosstab query. You need a table for students, a table ...
Compare records while entering in a form	1/27/2009
Q: I'm using Access to store quality sampling info. There can be several entries regarding one item's ... A: I don't understand your box and cube and colors and all that, but you can use a DLOOKUP in the ...
Access 2007 Macro	1/27/2009
Q: Is the SENDKEYS the right macro action to use for keystroke responses? I'm using macro to create a ... A: You could use SendKeys, but a better way would be to NOT need the Yes/No dialog boxes in the first ...
Query question	1/27/2009
Q: I'm new to databases and I'm using access 97 and need to build a query from a table with the ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
Access 2007- Reports from Forms w/Subforms	1/27/2009
Q: Richard, I am trying to create a report from a form that has a subform. It is an employee database ... A: Bring both of your tables together in a query. Then, in your report use SORTING AND GROUPING levels ...
Database for Survey	1/26/2009
Q: I am building a database to house data collected from a very large survey of about 270 questions. ... A: Your RESPONSE table links the tables together. Response!QuestionID > Questions ...
Grouping a date field by week	1/26/2009
Q: I have worksheets that are entered daily and a wages report from Sage which is imported weekly. I ... A: You can use an Aggregate query with the SUM function to add up all of the dates from a specific ...
if Options	1/26/2009
Q: My question is regarding a table with a field which has two options Completed or Cancelled if ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
MS Access and VB.Net Question	1/24/2009
Q: The project is the reminder for Happy Birthday of the peoples. I have a MS Access database and in ... A: I can't read your code. It's too small. Use the Month() and Day() functions to compare the birthdate ...
Calcuating Avg of a Calculation	1/23/2009
Q: I have a form on which I have users enter a start date and an end date. I then have a field where I ... A: What about running an Aggregate query with the AVERAGE function? You might need two queries… the ...
Iff Statements in Access reports	1/23/2009
Q: Not sure if this is to much code, but thought I would give it a shot. I am trying to do an If then ... A: It's not too much code. It looks like you're trying to compare ALPHAnumeric text values. Is this ...
radio buttons selection	1/23/2009
Q: I am very new in working Microsoft Access 2003. I have created a form . In that form I created two ... A: You would need an AfterUpdate event for your buttons that set the Visible property of your ...
Date Must Fall on Saturday	1/23/2009
Q: Can you provide me with a rule to ensure all dates entered fall on a Saturday. Or maybe a simple ... A: Forget doing this at the table level. Put a BeforeUpdate event in your form field. Something like ...
Unable to populate the list box according to the value of Combo box.	1/23/2009
Q: How are you? I am using ms access 2000. I have created one COMBO BOX & LIST BOX. I want to populate ... A: If you were using Access XP or beyond, you could use the AddItem method of the Listbox, but Access ...
insert statement	1/23/2009
Q: how do you declare and insert statement in VBA? i put an event procedure in my button, a modified ... A: That's an SQL statement, not a VBA statement. You can run SQL with the Docmd.RunSQL command. Google ...
How to get a form control to issue an alert when its value changes	1/22/2009
Q: I have a split DB which is designed to have 1 admin and several users on at the same time. The users ... A: The code you have now wouldn't run on anyone's screen EXCEPT the admin. If someone else runs it, ...
Admin Permissions	1/22/2009
Q: While working with my database security, there was a power issue resulting in all but my Admin user ... A: Yes, restore a backup copy of your MDW workgroup file from before it was corrupted. Let me know if ...
Averaging Formula	1/22/2009
Q: I'm a little confused with how to insert an averaging formula into one of my tables in Microsoft ... A: Yes, you should definitely look into some tutorials. You can't put functions (like an average) in a ...
Access db calculations	1/22/2009
Q: I am building an Access database that tracks daily errors created from Batch Records for a small ... A: You could use an AGGREGATE query with the SUM function, or use DSUM by itself. To do this, use an ...
Import Excel data with varying names	1/22/2009
Q: I have set up a macro to import data from an excel spreadsheet by clicking a button. This appends ... A: You could use the Microsoft Common Dialog Control to select a file. I cover this in my Visual Basic ...
required field	1/21/2009
Q: how do i put an alert or msgbox when the button save is clicked and a required field has no ... A: In the button code, check for your condition and then throw up a MsgBox. If IsNull(MyField) then ...
Access 2007 audit history issue	1/21/2009
Q: I am successfully tracking changes made on my form for specified fields when they are updated ... A: In your button code, just call the AfterUpdate event for each of the fields you are setting. For ...
How can I get a text box to display the arabic version of a name	1/21/2009
Q: I'm building a database in English but it has arabic people. One of the features that I need to ... A: You would need a table that has both the Arabic and English names in them, then use the DLOOKUP ...
Maximising Forms	1/21/2009
Q: I have a form (Menu) with selections to open other forms. The Menu form is set to maximize when ... A: Greg, I have never seen this behavior before, nor can I reproduce it. Do you have any automatic ...
Query returning a zero sum if no records	1/21/2009
Q: I have a query which sums the number of hours an employee is absent based on the date. This then ... A: Use the NZ() function. It will return a ZERO if the result of the value you send to it is null. Let ...
Sorting a a feild in a form	1/20/2009
Q: I am trying to sort a form by the date feild but when I do it does not sort properly. I entered ... A: That's odd. Have you made sure that it's a DATE/TIME field in your table and not a text field? Have ...
Rolling Average in MS Access Query	1/20/2009
Q: Hope you can help me. I'm trying figure out how I might do a rolling average in MS Access query. I ... A: You can't easily do that with just a query. You could do it easily in a REPORT with a running total. ...
Synchronized Combo Boxes on the continuous form	1/20/2009
Q: I have a problem on the Synchronized Combo Boxes on the continuous form the problem is the unit ... A: After reviewing your video, it looks like your UNIT combo box isn't properly bound to whatever your ...
Contact Database	1/20/2009
Q: I have used a combo box to autofill company information in service record form from a location form ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Ms Acess Write Conflict	1/20/2009
Q: I have a simple table -> create a dynaset, --> edit some records ---> close the dynaset (either ... A: I can't write your code for you, but I can forward you to one of my tutorials: Recordsets are ...
Comparing Table Check Boxes and returning Unmatched Values	1/19/2009
Q: I am creating a database to compare skills required for a job with current skills acquired by an ... A: Chris, since you're new to Access I should probably warn you that this isn't going to be easy to do ...
Missing Data in Access	1/19/2009
Q: We have been using an access data base since 2000. From 200 up until Sept 2005 others entered data ... A: Ignore the forms. Look directly in your tables. Is the data there? If not, re-check your backup ...
Using DLookup and Autofill queries	1/15/2009
Q: I've been reading some of your posts regarding using DLookup, and I feel like I'm doing something ... A: You should use DLOOKUP in the VBA of your form, probably in your button code. If you haven't watched ...
Pulling information where one field matches	1/15/2009
Q: I am building a database at work and have created a form. One of the input functions is a sequence ... A: You can put that in an AfterUpdate event… You can use an AfterUpdate event to set values, change ...
Access 2003 Pie Chart	1/14/2009
Q: I am trying to create a pie chart that has percentages on it. I have cretaed a pie chart but there ... A: Absolutely. If you right-click on the pie chart you will see Format… options for just about ...
Access '07 data entry	1/12/2009
Q: How can i enter data into multiple tables using a single form? Am a beginner, so if possible ... A: If you are a beginner, then there is no simple way to enter data into multiple tables using a single ...
SHOWN AN EXISTING COLUMN	1/12/2009
Q: There is an existing column in Access doesn’t want to show when I open each table from Datasheet ... A: Sounds like someone resized it to zero width. The easiest way to fix the problem: select AROUND that ...
Unable to work multiple if then else in AfterUpdate() Event	1/12/2009
Q: Good evening, first of all how is your vacation??? I hope you enjoyed a lot. Richard i have created ... A: It doesn't look like you have your IF/THEN blocks set up correctly. If X=1 Then Something End ...
single record on report	1/12/2009
Q: I have been reading through past questions but I can't see what I am looking for. I need to have, in ... A: If you're COMPLETELY new to Access then you're going to need to learn a few things before you can do ...
MS Access 2003 search form	1/5/2009
Q: I was wondering if you could help me, of which I am hoping you can. I need to create a form is ... A: I could certainly point you in the right direction, but I can't help you build all of this HERE. I ...
How to Set the value of a textbox based on the value of another form textbox	1/5/2009
Q: I am unable to set the text box value based on the value of another form textbox. Please suggest ... A: Try: Forms!Userform1!Username = AgentName That's it. Drop the Me. and the.Text Let me know if you ...
MS Access - Autofill on a form	1/4/2009
Q: I followed your tutorial to filter one combo box to feed another. I'm having problems with it ... A: ENTER PARAMETER VALUE means the query can't find the combo box you're referring to. If that combo ...
Codes required for Backup data	1/3/2009
Q: Sir I need a form where thru a command button i can backup my whole database and previously backup ... A: You could either copy the entire MDB file with a VB FileCopy command, or you could use a series of ...
Dependent combo boxes	1/3/2009
Q: I followed your tutorial on youtube about populating one combo box based on the choice in the first ... A: Tom. The technique I show should work for any number of combo boxes, really. You would just need to ...
Pull unique records	1/2/2009
Q: I am building a training database for my employer and I am having issues filtering some records. ... A: Use the DMAX function. Here's a tutorial: ...
Hide a group footer	1/2/2009
Q: I want to hide a group footer section (that contains a textbox to calculate a subtotal) if the ... A: You're right. You could try using a COUNT function in a textbox and if the number of records is 0, ...
Report on Format Event	1/2/2009
Q: Can you show me how I could modify the code below? I am using this code in the On Format event of ... A: Get rid of the Me! and see if it works. It looks fine to me. Your code should be in the build event ...
Having Trouble Dynamically Populating a Table	1/2/2009
Q: Hey, I am trying to create a table saves the quarterly accounts of participants in a certain program ... A: Again, you would need to use a recordset or action query to dynamically change the values in another ...
Ms Access Invoice	1/2/2009
Q: I made a database where I can input my stockists billing details date wise. My fields are "INVOICE ... A: Sure, you need to set up two related tables to handle this. It sounds to me like you need to learn ...
MS Access	1/2/2009
Q: I have one table in MS Access that has an id (primary key), short date, string, and a yes/no ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
MS Access 2003	1/2/2009
Q: I would like to know how i display the date field which exist in SQL table in text box of a access ... A: Attach to the table (Link to it) then bind the form to that table and add the text box from the Name ...
defining variables	1/2/2009
Q: I need to add an additional combo box to a form, which is easy enough to do; however, it appears the ... A: Well, apparently Access cannot find ComboProductLine. Make sure it's spelled correctly in the NAME ...
retrieving and displaying information	1/1/2009
Q: I have a training database which has basically officers details and training completed. I want to be ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Structure Duplication of Forms, Reports Etc	12/31/2008
Q: I have developed a data base for use in my work. The table was originated for 2008 data. I have made ... A: There isn't any easy way to do this. The BETTER solution is to include a field INSIDE your table ...
Extract one Duplicate from a table	12/31/2008
Q: I have a table with 11,078 reords in it. Now about 2,090 of the records are duplicates. My ... A: Use a FIND DUPLICATES query. There is a wizard for it. It's real easy. I also cover it in one of my ...
How to Set the value of a textbox based on the value of another form textbox	12/31/2008
Q: I am unable to set the text box value based on the value of another form textbox. Please suggest ... A: If you want to refer to a value on a Form, you can use the notation: Forms!FormName!FieldName You ...
combo boxes	12/31/2008
Q: I have a form with 2 unbounded combo boxes . i select the team manager Names on the 1st one and ... A: Sorry… I get so many questions I jumped the gun on answering yours without fully understanding it. ...
defining variables	12/30/2008
Q: I need to add an additional combo box to a form, which is easy enough to do; however, it appears the ... A: You don't need to "define" a combo box. It sounds like you renamed or deleted a VARIABLE that you ...
database	12/30/2008
Q: we have 3 to 4 terminals in our office running access database windows 2003 server all of a sudden ... A: This could be caused by a million things and I'd need a LOT more info from you to help. Are any ...
Duplicate Extract from Table	12/30/2008
Q: I have a large table of data that has duplicates. I would like to pull one of the duplicates out ... A: You would need to mix a Find Duplicates query with a Make Table query… or use a Recordset. ...
combo boxes	12/30/2008
Q: I have a form with 2 unbounded combo boxes . i select the team manager Names on the 1st one and ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
Having Trouble Dynamically Populating a Table	12/30/2008
Q: Hey, I am trying to create a table saves the quarterly accounts of participants in a certain program ... A: I would use a Recordset to populate your table. You can check the dates in your VB code. Recordsets ...
How to Set the value of a textbox based on the value of another form textbox	12/29/2008
Q: I am unable to set the text box value based on the value of another form textbox. Please suggest ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Display username field	12/29/2008
Q: I have designed an access database with multiple users, and I would like to know if there is a way ... A: Are you using Access 2003 or earlier with User Level Security and a workgroup? If so, there is a ...
Access Report Grouping	12/24/2008
Q: I'm working on a project to compute MPG for a fleet of vehicles. I get a download of raw ... A: WITHOUT using VBA? Good luck. You would need to use a VBA function such as DMAX or DMIN to look up ...
how to apply Nested iif function	12/23/2008
Q: Unable to apply nested iif function in the ms access. I have created this formula in ms access ... A: Get rid of the Switch function and the calculated fields, and put all of this in proper VBA code in ...
Creating a dictionary with MS Access	12/23/2008
Q: I am a beginner in MS Access and I have a long bilingual glossary in Excell (1 table with 2 colums). ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Deriving data from Access via Excel	12/22/2008
Q: Here's the scenario: I have created a few userforms in Excel and for an added security measure, the ... A: It sounds like you should just be able to pull in the data from Excel to Access and then use a ...
prompt input for a query	12/22/2008
Q: I am using Access2003, and try to set up a simple query with input prompt for some criteria. under ... A: You need to put something in the brackets, like [Enter First Name] or something. You can specify ...
images displayed from Combo	12/21/2008
Q: I hope you can help. I am limited to my extertise and still learning! I have developed a Sales ... A: You could try using DLOOKUP in an AfterUpdate event to display the picture. I haven't done this ...
Access 2007 + Invoice	12/20/2008
Q: I am starting a courier business. I would like to have a form in which all information can be ... A: You can use several methods to do this, including a recordset or set of action queries. This is ...
Stock control database	12/19/2008
Q: 19/12/2008 Hi Richard Rost I want to know how to control items in stock. I started with three ... A: Does this code work if you substitute an actual VALUE for your Item_Code instead of reading the ...
pharmacy database	12/19/2008
Q: i am developing a database on a pharmacy, i want automatic display of price from the stock's table ... A: You can use Recordsets or Update Queries to adjust your pricing or stock quantity levels. ...
Forms with input hint in Access 2003	12/18/2008
Q: I've got a database that requires a LOT of user input on the form. It would be much more ... A: Sure, just create a label off to the side and call it HintLabel. Now in the GotFocus event for each ...
column summation	12/18/2008
Q: When adding columns together, sometimes a sum will show as a negative number. This will not work for ... A: Use a calculated query field and an IIF function: NewValue: IIF(X<0,0,X) You can use the IIF ...
Validating forms	12/18/2008
Q: I have a form where the user will enter about 30 items of data, however before they press a button ... A: You can set up table-level or form-level validation using a simple VALIDATION RULE which I cover in ...
MS Access Report Configuration	12/17/2008
Q: How do I subtract one time field from another time field, the results captured in a 3rd field ... A: Use DateAdd. It works with Time values too. You can use the DateAdd function to add or subtract ...
Access Formulas	12/17/2008
Q: I need to calculate the Instructors payment. eg. 1 student = $100.00 The instructor will be paid ... A: In your query, make a new calculated field called NumPay and say: NumPay: ...
Formula Field	12/17/2008
Q: How do I do this in Access 2007? A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Select multiple records for printing	12/17/2008
Q: I have read your answer to this subject dated 12/27/2007, but for my case i have a number of ... A: Easiest way to do this would be to add a field to your table called PrintMe or something like that. ...
Access 2003, modify a form	12/17/2008
Q: I have a form but I need to add one extra field on it (lets say Taxes 2008),, this extra field is ... A: Add the field to the underlying table (and possibly query) then it should show up on the form's ...
History Request	12/16/2008
Q: I have been asked to provide date change history by month on the first of each month. Ex ... A: I would make a second related table that stores any historical changes like this… unless you only ...
DCount - MSAccess Report	12/16/2008
Q: I have a report that summarizes the # of riders ("RiderCount")on 12 different shuttle routes ... A: Your DCOUNT function looks fine. You might want to try: DLOOKUP("*","QueryName","RiderCount<3") ...
Calculated fields	12/16/2008
Q: I have created a table with a form to match for entering the information. I created calculated ... A: Calculated fields are UNBOUND by definition. The data isn't stored in the tables. Just recreate the ...
Import Text to Access	12/15/2008
Q: I know it's possible to import text to an Access table. What I need to do is import text to a query. ... A: Queries don't hold data. Tables hold data. Queries only display data in different ways. Let me know ...
DLookup	12/15/2008
Q: I am trying to use the DLookup function. I have a customer database with numeric account numbers ... A: Your code looks perfect to me. You can use the DLOOKUP function to look up a specific value from a ...
Query calculations	12/15/2008
Q: Richard, I have two tables that I need to use data out of to calculate the average time it has ... A: See this tutorial: http://599cd.com/tips/access/difference-in-dates-times?key=AllExperts Let me ...
Access Reports	12/15/2008
Q: We have 12 individual reports that are used to details time reporting for a group of employees. ... A: That is the proper way to build it. You shouldn't need 12 separate reports. Now… what is your ...
problem with searching	12/15/2008
Q: let me explain what i am doing first i have a table and in that table i have alot of fields each ... A: This tutorial should help you… I get asked this question all the time. You've got two or more combo ...
Problem recalculating totals	12/14/2008
Q: I have a subform that includes the purchased items as well as their price. I need to recalculate the ... A: My previous answer included a link to a tutorial explaining how to use the SUM function. If your ...
Carry out a search using keywords	12/12/2008
Q: where as a business we have to quote on baskets of products supplied to us by a customer. The issue ... A: Use an AfterUpdate event with the InStr function to check for your strings. If you need to check for ...
Custom format for calculated control	12/12/2008
Q: I have a calculated control in report footer which is =sum([amount]). I want it to display the ... A: Convert it to a numeric value and use the format 0,000 Let me know if you have any other questions ...
how to apply Nested iif function	12/12/2008
Q: Unable to apply nested iif function in the ms access. I have created this formula in ms access ... A: Your syntax is wrong. It should be: =IIF(X=1,A,IIF(X=2,B,C)) However, you're probably better off ...
Importing from Excel to Access	12/11/2008
Q: I am familiar with excel but pretty green with access, so this question may be a bit rudimentary. I ... A: Importing info from a Excel is relatively easy, and yes you can set up an Import Specification in ...
Wont Qry AutoNumber Access 2000	12/10/2008
Q: Basic Database is being created. Only Primary key (autGageDispositionNumber)is autonumbered, with ... A: If you want the exact number, don't use LIKE. Just say: =[enter value] Let me know if you have any ...
Access Calculations in Reports	12/10/2008
Q: We have used the report wizard function to comprise our information. In the end we need to ... A: Sure. You can put totals in REPORT footers just as you would FORM footers. You can use the SUM ...
queries, multi criteria	12/9/2008
Q: I have a form with two list boxes, each box I have declared a global variable. But what I am trying ... A: Instead of using the Filter property, why not just change the Recordsource property to a valid SQL ...
creating a punch clock in ms access	12/9/2008
Q: i'm trying to create an in/out clock. i created a form with an in button and an out button when ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
VB Codes	12/9/2008
Q: I am trying to program to change the font bold and the message visible when the years in business is ... A: Text boxes store TEXT strings. If you want to check it against a numeric value, use the VAL() ...
Access Form	12/9/2008
Q: I set up a form to enter test data into an Access table. I want to be able to enter the studentID ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Code Source for Unbound Text field on a Report	12/9/2008
Q: I have a report called 2009 Management Report where all the fields are bound to a query that has a ... A: Yes, it sounds like you are either missing a parameter somewhere, OR you have something misspelled ...
Splitting an Access 2007 Runtime DB	12/8/2008
Q: I have and Access 2007 database that I share out over the network as a runtime. The backend of this ... A: I think you understand the concept just fine. You already have a "split" database in the fact that ...
Calculate time divided by number	12/8/2008
Q: i want to calculate a specific item, what code can be used to calculate one field which is a time ... A: I could convert the time into minutes by multiplying it by 24*60 and then divide that number by your ...
Using Values in a SubForm	12/7/2008
Q: My form (fMaintRequest) has a subform (sfJobSpareParts) that contains two data fields: Spare Part ... A: Don't include the form!field name inside the quotes. Put it outside the quotes, so you're sending ...
How Do I Make sure my form is NOT in continuous forms format	12/5/2008
Q: I have seen a lot of your videos on U-Tube. It's awesome to be able to ask you questions on this ... A: Robert, the EASIEST solution would be to simply make ZERO your default value for that form field. ...
Sum in an access query	12/5/2008
Q: I am trying to work with a field in a table called CALL DURATION which is a Date/Time field in the ... A: Left, Right, and Mid are TEXT functions. You can't use them on a time without converting the time to ...
QBF queries	12/5/2008
Q: I started playing around with QBF queries 2 days ago, and I can get the query to filter the results ... A: Make your query criteria: LIKE "" & Forms!FormName!FieldName & "" This way if the form field is ...
How to make columns use information from each other	12/5/2008
Q: I am really new to the Access world and am building my tables now. What I have is a column in a ... A: This is not something you can do in your table. You would use a QUERY or a FORM to do this. I would ...
Access hyperlink for Windows and Linux	12/4/2008
Q: Good day Mr. Rost, Thank you for taking your time to answer questions. I have a database used to ... A: Janet, I'm not a Linux user myself, so I really can't say for sure… but this would really depend on ...
Searching between dates on a link table in access	12/4/2008
Q: I have a link table in access from oracle db with date field. The values in the table in oracle are ... A: Are the fields from Oracle actual DATE fields or just text? That will make all of the difference. ...
Password protection by field in form	12/4/2008
Q: I have a median knowledge of access and have been asked by my employer to build a database that ... A: Joni, your first question is not that easy to answer. It's difficult to make a specific SECTION of a ...
getting median in place of subtotal in a MS Access Report	12/4/2008
Q: I have a table with 3 columns "Cost Centre", "Sales Orders" and "PR%". I need to find the median of ... A: A quick Google search turns up a MEDIAN function someone wrote: ...
Access Form	12/3/2008
Q: I set up a form to enter test data into an Access table. I want to be able to enter the studentID ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Inventory Database	12/3/2008
Q: I am currently working on a database in Access 2007 that we want to track retail store inventory at ... A: It is certainly possible to build a database with complete inventory management with Microsoft ...
Image control in a form	12/3/2008
Q: when i assign an image control with a text field showing path of the image, it works, but when i ... A: Is the image bound to a table, or are you just saving it in a form? Let me know if you have any ...
using option group in form to filter records	12/2/2008
Q: I have an option group with 3 buttons in my form. 1. Show all records 2. Show only closed records 3. ... A: The EASIEST way to do this would be to make 3 queries, one for each set of data. Then in your ...
Password protecting individual buttons in Access 2007 / Trend analysis on forms	12/2/2008
Q: 1. I have some sensitive data on certain forms inside of my database that I would like to password ... A: 1. You can use a simple INPUTBOX command to check a password. While I'm sure you could do this with ...
Access 2003, strange records behaviour!	12/2/2008
Q: I'm looking for some reassurance on an issue I've not seen before. Db = Access 2003 BE is in shared ... A: Yes, trying to build your database around users who don't follow the rules - and aren't always ...
Error " Index or Primary Key cannot contain a Null Value "	12/2/2008
Q: i am getting this error any time i ma trying to close my DB. Error " Index or Primary Key cannot ... A: Sounds like you have a table open with a record that's missing an ID field. Let me know if you have ...
Access random lock on a table	12/1/2008
Q: I am using a SQL insert statement to populate a temp table. Then I am using a subform in a Larger ... A: Is your subform bound directly to the table or a query? Is the query a compound query (more than one ...
Validation rule	12/1/2008
Q: I would like to set a validation rule in a text box where you can only enter a time value with an ... A: I'm pretty sure you can't do this with a basic validation rule. You're going to need your own custom ...
Increase the number of fields in a table`	12/1/2008
Q: I have a project which contains a sort of a master table with a lot of fields.Now if i want to add ... A: You can't. That's a physical limit in Access. You shouldn't ever need more than 100 fields in a ...
Problem recalculating totals	12/1/2008
Q: I have a subform that includes the purchased items as well as their price. I need to recalculate the ... A: You can use the SUM function in a text box in the FOOTER SECTION of a form or report to add up all ...
Forms in Access 2000	11/30/2008
Q: I have a main form for data entry linked to a query. This form is linked to a "Results" form also ... A: Alan, you can easily import data from Excel into Access, provided you format the information ...
acess	11/29/2008
Q: iam a new in access how can i make a reminder in access A: Create a reminder table with a text field and a date. Make a query to show all reminders before a ...
Copy record value forward	11/27/2008
Q: I need to copy a value to the next record until the value changes and then copy the last non null ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Sunday School Roster	11/26/2008
Q: I'm trying to build a Sunday School Roster for our church. I have a class table, that lists all ... A: I just recorded a video tutorial on something VERY similar to this: ...
Updating a subform from a form.	11/25/2008
Q: I am trying to create a booking system for a project and I have a Booking table and a journey ... A: You can access data on a subform like this: Forms!FormName!SubFormName.Form!FieldName Let me know ...
historical records	11/25/2008
Q: I'm creating a database that keeps track of maintenance on laboratory equipment. I need it to inform ... A: You can use an Append Query to add records from one table onto another. See this FREE tutorial on my ...
Access 2003, strange records behaviour!	11/25/2008
Q: I'm looking for some reassurance on an issue I've not seen before. Db = Access 2003 BE is in shared ... A: My only suggestion would be to disable the standard "close" button on your form and make your own. ...
problem with forms	11/25/2008
Q: I have a problem with MSaccess forms. to explain my problem better i will tell u what i did so far ... A: Make sure the NAMES of your FIELDS (not your labels) are StartDate and EndDate and then make your ...
record set	11/25/2008
Q: I ve tried your Answer : if e_id is a text field then your code should have single quotes around the ... A: Sorry, I couldn't tell you for sure without seeing your database. It's behaving as if E_ID is still ...
Audits Database - Need to Normalize Tables	11/24/2008
Q: I have a safety audit form that has over 200 fields on it. There is a header, then nine sections. In ... A: If your items are always going to include S, U, and N and those are numbers, then I would have a ...
Forms in Access 2000	11/21/2008
Q: I have a main form for data entry linked to a query. This form is linked to a "Results" form also ... A: How about: if IsNull(ID) then Msgbox "whatever" docmd.close "MyForm" Exit Sub end if Yes? ...
Inserting Time using a command button on a form	11/21/2008
Q: I need to select current time and view it in a textbox (Start_Activity) by clicking a command button ... A: Try this instead: Start_Activity = Now() Sometimes that will work when Date() or Time() will ...
Manipulatung photos	11/21/2008
Q: I have a database that displays linked photos. I need a software module (preferable linked to a form ... A: This is not something Access can do by itself. You'll need 3rd-party software to manipulate images. ...
Access Query Matching Field in Same Table	11/20/2008
Q: I have imported a spreadsheet into Access to create a single table. I need to know how to build a ... A: This is going to be a complicated answer, and it sounds like you're an Access novice. I would ...
Text Box Issues	11/20/2008
Q: Ok, so I'm new to MS Access and I've created a text box to enter information into but, it allows me ... A: Go into TABLE design mode and change the max length for the field - up to 255 char. Let me know if ...
Access 2003 Auto Populate	11/20/2008
Q: How do I autopopulate a field in access? I have a set of units that are in a battalion, in the ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Handling parameter prompts on a query	11/19/2008
Q: I've created a query which sums one field from a database. A second field checks that field for a ... A: Your query is too complex. Break it down into two smaller queries. The query can't find the field ...
Requery/Refresh a subform	11/19/2008
Q: I have an unbound form with a tab control on it. On one tab I have a dropdown list with client ... A: I would have to see your database to tell you for sure, but you can also try the.REQUERY method ...
Expressions with check boxes	11/19/2008
Q: I'm trying to build a complicated expression in using a number of control sources to calculate a ... A: You're going to need to know the IIF function and how to read values from a form in your query: ...
Access Query	11/19/2008
Q: I love challenges, and this one stumped me! I have a grade book I am trying to make in my spare time ... A: Joel, this sounds like a wonderful project, and I would love to help you with it, but I'm incredibly ...
How can I reset autonumber?	11/19/2008
Q: I would like to know how i can reset autonumber to be 1 again cheers, Boon A: Delete all of the records in the table. Compact and repair. Aside from that, there's nothing you can ...
Change Time format	11/19/2008
Q: I'm importing a table from a soucre and the time format comes with the date as well, I changed in ... A: If you want the TIME stored internally as a TIME then that's the way you WANT to keep it. Access can ...
transpose row to column or best method	11/18/2008
Q: I have constructed an evaluation tool in Access. I want to store and compare answers to questions. ... A: There are no rows and columns in a database. You have records and fields. This simple way of ...
apply filter with combo box	11/18/2008
Q: I have this code to apply a filter to a form Dim strFilter As Variant If Not IsNull(txtToDate) Then ... A: You'll have to put code in your filter sub that looks for the words "This Year" in your box and ...
Access 2007 Many to Many relationship	11/18/2008
Q: I am trying to create a junction table, for a many to many relationship. How do I make 2 primary ... A: You don't need them to be primary keys… but if that's how you want it, just click on the KEY button ...
Filtering using Combo boxes	11/17/2008
Q: I know this has been convered before, but I can't seem to find a definitive answer. I just want to ... A: Easiest solution: put an UNBOUND combo box in the form header or footer. Make the query that's under ...
CODE	11/17/2008
Q: I am writing a inventory database. I collected all my data and put them into their proper forms. ... A: Well, the first thing I would ask is why you have different forms for the different types of ...
Selection from drop down menu un-checks a check box	11/6/2008
Q: I had asked Manish a question on 8/15/08 (Using function in a form). He asked me to use IIF and it ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
IIf Statement in a Query	11/6/2008
Q: I would like some direction on how to replicate code from a Excel srpeadsheet into a MS Access Query ... A: You've got too many parameters in your IIF function. It only takes 3: =IIF(Condition, True, False) ...
adjusting one column's data in a report on another column's data size	11/6/2008
Q: I have two fields in a table/query. One field is the employee title. Another field is their ... A: Use basic string concatenation: http://599cd.com/tips/access/string-concatenation?key=AllExperts ...
Making a query in MS Access 2003	11/6/2008
Q: Richard, I was hoping you could give me some insight on a query I need to make in Microsoft Access ... A: Yes, you will need to get that into a standard date/time format like mm/dd/yy using some string ...
Forms	11/5/2008
Q: I have created a table and from that table have created a query with the fields from the table and ... A: Trish, sometimes depending on the relationships you build with your queries, you cannot EDIT the ...
pop up reminder	11/5/2008
Q: I am using access 2003 i would like a reminder window to pop-up when they open a form how would i do ... A: Put a: Docmd.Openform "FormName" in the OnOpen event for your first form. Let me know if you have ...
Using Count in 2 column report	11/5/2008
Q: I have a report that I modified to have two columns, instead of one, and now the =Count() in the ... A: It shouldn't matter how many columns you have - Count shouldn't be effected. Don't Count up all ...
Links to Files	11/5/2008
Q: I have written a database (currently running in 2002) with links to .pdf's that reside on my ... A: Setup a network share on your PC, or use the SUBST command to create your own drive letter. Then ...
Security for split database	11/4/2008
Q: Good afternoon Richard, I am trying to add user level password protection to the back end of a ... A: The EASIEST solution is to re-integrate your database back together into ONE MDB file and then run ...
SQL type mismatch error 13	11/4/2008
Q: .. I have been dealing with Scott and he is not available for a follow up This is the last code we ... A: Which line gives you the error? Let me know if you have any other questions or comments. Also, be ...
Comparing Data Between Columns	11/4/2008
Q: I am working on a project where I am trying to track over 150 participant's weight over time ... A: You should have one table for people, and then a separate table for your weight measurements. It ...
Report	11/4/2008
Q: Trying no to let the fields start on page that with no information. I have attached an example. I ... A: Use the KEEP TOGETHER property of the section to keep the header with the first data. Let me know ...
MS ACCESS2003	11/4/2008
Q: I am using 1 forms i.e.salaryinfo is the mainform and the salaryincrement is the sub form.While ... A: You could use DLOOKUP to find that information out. You can use the DLOOKUP function to look up a ...
MS Access	11/3/2008
Q: If I have a person's height in Inches Person_Height 67 how would I define as a MS Access Query ... A: You would need two calculated query fields. One would be just the feet: FeetOnly: INT(Height/12) ...
parameter queries	11/3/2008
Q: Is there a way to cause the results of a parameter query to display in a form as opposed to the ... A: I don't understand… if you build a form based on a query, then you just open that form up, and it ...
VBA	11/2/2008
Q: Let me explain my situation briefly, i have 8 excel workbooks with 50 worksheets each. My final goal ... A: Yes, I'm sure it's possible, but I would go the other way… write it from Access and pull in the data ...
Adding Columns	11/2/2008
Q: I need to add the records in each column for a total. So for one of my columns, I have a bunch of ... A: You can use the SUM function in a text box in the FOOTER SECTION of a form or report to add up all ...
Using treeview to open forms	11/1/2008
Q: I am trying to create a menu to open forms from treeview. I have a form on which I have placed a ... A: I think this is what you need. It's too hard to tell from your description. If not, let me know: ...
Adding columns in Microsoft Access	11/1/2008
Q: Sir, I run a polling company. I downloaded a large file of voters. I need to add a few columns to ... A: There is no "insert column" function in Access. Access is a database, not a spreadsheet. You need to ...
Data in a Report	10/31/2008
Q: I'll do my best to explain....... I currently have a report that displays Engine IDs under specific ... A: Your problem is the JOIN in your query. You need to create an OUTER JOIN so you see ALL records from ...
access tables	10/31/2008
Q: I have set up an Access database for our 4-H club (my first database). I have a table with all the ... A: Put both names together in a query using String Concatenation. I teach this in my tutorials. Here's ...
Open read only to edit from from another form	10/31/2008
Q: I want to open a form that I have made read only by using a command button and a form that asks for ... A: You should get a good tutorial on HOW forms work with VBA. I would open the form and then change ...
listbox editing	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: You can't edit data in a listbox. You might want to use a subform. Let me know if you have any ...
MS Access	10/31/2008
Q: Is it possible to select records in a random order within a table (like you select files by pressing ... A: Anything's possible. I've never done this before, but I'm sure it's possible. Perhaps use a random ...
Access 2002 Options	10/30/2008
Q: I am a relative beginner to Access (Using 2002 unfortunately) but I have searched all over and ... A: Just set the ENABLED property for your combo box to FALSE and then in the AfterUpdate event for your ...
Closing Balance to be Opening Balance in new record	10/30/2008
Q: I have asked this question and was told it is too long and involved but maybe I can try again.How ... A: I might have been the one who told you it was too complex to cover here. Explaining this PROPERLY ...
Linked tables across network not updatable	10/30/2008
Q: Greetings: I am attempting to create a database that links across the network (outside our building) ... A: I would need to know more about how you have configured your network connection. If you have a VPN ...
Finding Start Up Form Code?	10/29/2008
Q: I went to Tools > Start up to see which form it was loading, but nothing was in the drop down under ... A: Look for a macro named AutoExec. Let me know if you have any other questions or comments. Also, ...
Updating an Access read only report	10/28/2008
Q: We have a database that has several read-only computers hooked to it. Some changes were made to ... A: I would need to know more about your setup. What do you mean by "read-only" computers. How is your ...
Transfer text/ Copy 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. You could manually import the text file using FILE I/O reads (Open #1 For Input…) if you know VB. ...
Access	10/28/2008
Q: I have an access table for results with the fields: Student No, Module, AssessmentType, Result. ... A: Perhaps use two subforms? I'd have to see what you've got going on there to offer more help. If you ...
Query and Table Link	10/27/2008
Q: Hey Richard, I have a quick and hopefully simple question. I created a simple query from a table ... A: Queries are bound to one or more tables. When you add, edit or delete records, you're effecting the ...
Queries and forms	10/27/2008
Q: I have a form with the fields 'conferences', 'address' and 'city_state'. In this form, I want to ... A: You can specify criteria for running your queries, such as limiting between two dates, checking for ...
parameter queries	10/27/2008
Q: Is there a way to cause the results of a parameter query to display in a form as opposed to the ... A: Yes, there is. Just build a form based on that query. Let me know if you have any other questions ...
Totals Query - Criterea from 2nd table	10/26/2008
Q: An Access 2000, I have two tables tbl1 contains fields: LoggerId, RecordingIndex tbl2 contains ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
VB6 & Access Db	10/25/2008
Q: Are you familiar with VB6 & Access? If so, I am having a problem and was wanting to know if you ... A: Well, do you want to re-create the database EVERY time you open your software? Wouldn't you want to ...
Access Combo Box Add Too	10/25/2008
Q: I have added a Query to do what result i want. The existing has all selectors which comply of 1. ... A: I would recommend just re-creating the combo box using the wizard. It doesn't that that long. To ...
Importing/linking Web Tables to Access	10/24/2008
Q: 1. I have an access table linked to an excel spreadsheet. How can I refresh the excel table without ... A: 1. When you link to an Excel spreadsheet the data is saved each time you edit a record. 2. Instead ...
List or Combo Box as query criteria	10/24/2008
Q: Please help this should work but is driving me nuts. Access 97 Table - simple list of change ... A: This could be something as simple as a misspelled field name. Check them all. Review this tutorial: ...
vba code to open an excel file from access	10/24/2008
Q: Rather than using a hyperlink, I would like to know if there is code that I can attach to an On ... A: You could use the SHELL command to open Excel with your specific file: SHELL "C:\path\EXCEL.EXE ...
Combining Quarterly Data	10/23/2008
Q: I am creating an Access Database for a sales division which consists of about 500 reps. I need to ... A: It sounds like you're going to have to put the sales rep in EACH transaction instead of just ...
Go to web page based on data entered	10/23/2008
Q: I have developed an Access 2003 database for my company to track our extensive library collection. ... A: I can't explain all of this here, but I can tell you it's possible. You can add a WebBrowser control ...
Show Date parameters in graph	10/23/2008
Q: I would like requested date parameters to be visible at the top of Access graphs. My graphs are ... A: Don't ask for the parameters in your query. Ask for them on a different form. Then feed them into ...
Access 2007 - Filter a ComboBox related with selection to otrer table	10/23/2008
Q: Let me try to explain me.... I have this following tables in relations: - Customers - Products - ... A: Tabs really aren't used to form relationships between two tables - unless you have a subform on ...
Access 2003 -Unique Value Return Query w/ Parameters	10/23/2008
Q: How would I build a query to return unique values with parameters? I want to select January dates ... A: You can specify criteria for running your queries, such as limiting between two dates, checking for ...
Change All Text Boxes' Font Color Depending on Single Field Value	10/23/2008
Q: I have a report that I would like the color of the font (on all text boxes) to change depending on ... A: You would need to put code in the BUILD EVENT for the DETAIL SECTION of the report to change the ...
Combining 2 files into one	10/23/2008
Q: I have two access files that list the Miles driven by state and by quarter. The problem is that one ... A: OK, as I said before, just merge the results together somehow (copy and paste works fine) into a ...
Access Page Pop-Up Calendar	10/22/2008
Q: I have built an access page (and stored the HTML on a public server for our company so employees can ... A: I use the ActiveX calendar control on a separate popup form. When the form is closed, return the ...
Request for info	10/22/2008
Q: Dear Sir please tell me about balance brought forward formula in access or crystal reports my ... A: If you just want to see it in a report, use a RUNNING TOTAL. Otherwise, to actually get that value ...
query	10/22/2008
Q: i am retriving a record while filtering the query with like skills[coloumn] 'xyz' and want to ... A: Then say: select * from jobs where skills like 'java' Let me know if you have any other questions ...
dcount function	10/21/2008
Q: I have a question about dcount. Attached is a picture of what Im trying to do. I have a section ... A: Yes, DCOUNT would work fine, but without seeing how the rest of your form and table are set up, I ...
Inserting Form Value into a table	10/21/2008
Q: How do I use a button to add the value(s) of a selection list on a form into a table? This is not ... A: Do do this you would need a second table for your options, and then you could use a Form/Subform ...
Find Specific Record	10/20/2008
Q: I have a dynamic search form that returns records based on criteria selected. I created a command ... A: Is [Subject ID] the name of a field on your form? Is it bound to a field under the form in a table ...
Select a value based on user input	10/20/2008
Q: On a form (ConStarted_Form) I have a text box (1PropAward)and a Combo Box (2PropAward) with the ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Counting records (excluding duplicates)	10/20/2008
Q: Good Morning! Our DB contains job numbers associated with multiple government contracts. They all ... A: You would make a SECOND query to DCOUNT the records from the aggregate query. Let me know if you ...
Query returns Column based on two Columns matching	10/20/2008
Q: I have two tables. Table_1 Table_2 Table_1 has an District field and ID Field Table_2 has ... A: You can do this with a simple query and an INNER JOIN. This will show ALL records from Table 1 and ...
Form Label display on opening form	10/19/2008
Q: am using office 2007 and access form with a label. what i wanted to do is to make the label active ... A: What you have SHOULD work fine. If there is a "Y" in Text83, your labels should become visible. Let ...
4 Images in rpt and export to pdf	10/19/2008
Q: Using Access 2007. Report with 4 Images to display. See code at bottom. My problem is each jpg file ... A: You're never turning visible back ON if the picture exists. Once it gets turned off, that's it. It ...
Summing values between tables	10/17/2008
Q: I'm working on creating a database for service calls for the company I work for. I don't know much ... A: You could just make two calculated query fields: one for the labor total and one for the parts ...
Form filter	10/17/2008
Q: I want to thank you in advance for taking the time to help. I am on a new business adventure and in ... A: What you should set up is a Form / Subform relationship. When you select a parent record (category), ...
Adding data to an Outlook body email from Access	10/17/2008
Q: I have a system set up whereby I can send emails using Outlook based on data stored in a Access ... A: Well, I don't see how you're getting StrgBody into the email. You don't add it anywhere. Perhaps ...
Page Help	10/17/2008
Q: I am trying to design a detention database for the local high school, and am having trouble with ... A: Jared, see this tutorial. It's about tracking attendance, but it could equally be used for detention ...
Display a 'Farewell' Message	10/16/2008
Q: I have a command button on my Main Page to close and compact database. When clicked, a MsgBox ... A: Just put another button on your Goodbye form that performs your docmd.quit. Either that or use a ...
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: I would need to know how your dates are stored. If they're just NORMAL dates like 2/1/08 then simply ...
Access 2007 Database Search Issues	10/16/2008
Q: I've created an Access 2007 database that houses client contact information. The user can search a ... A: Dale, there is a maximum limit, but it's 2 GB (gigabytes) for a single database file, and that's far ...
using graphs	10/16/2008
Q: how do i create a graph in access after creating a table, by this i mean i want to place the ... A: Go to FORMS and use the Chart Wizard. That will put a graph on a form for you based on the data in ...
Access question	10/15/2008
Q: On a form, I have Date, Vendor, Purchase Order, Items Ordered, Quantity and Price per Item. I have ... A: You need to have two related tables… one for ORDERS and another for LINE ITEMS. It sounds to me ...
Counting records (excluding duplicates)	10/15/2008
Q: Good Morning! Our DB contains job numbers associated with multiple government contracts. They all ... A: You can create an aggregate query with UNIQUE contract numbers, and then use DCOUNT to count the ...
specific question	10/15/2008
Q: i have a table with like in the attached picture : there are three groups and a lot of clients. the ... A: Doing this at the TABLE level would be difficult and probably not what you want anyway. You CAN do ...
auto numbering	10/14/2008
Q: Below is a link that i search for. trying to achieve the exact results he is trying to get. I want ... A: You could use DMAX to figure out what the largest value for each ID is and then assign that. See ...
date range criteria in query (from a form)	10/14/2008
Q: I have a form that allows a user to pick certian criteria for a query. One of the options I would ... A: You would be best off designing a custom SEARCH FORM that has several different fields on it and you ...
Passing a variable's content from one form to another's SQL string	10/14/2008
Q: Access 2003. On one form is a text box and command button. I enter CO, for example in the text box ... A: Forget your variable. Why not just: Docmd.OpenForm "MyForm" Forms!MyForm.Recordsource = "SELECT ...
Access Forms	10/13/2008
Q: .I have one db with two forms. The first form is a sign in type form, a user would enter in a 4 ... A: In the button that opens your second form, after you open it with DoCmd.OpenForm you need to set the ...
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: You can do this with a form/subform. Just have the subform based on your Intersection (Junction) ...
Ms Access DateAdd field not in report	10/13/2008
Q: I am attempting to add a user specified number of days 9 loss of yard) to a date range (Hearing ... A: Don't use the DateAdd function in your FORM. Instead, base a calculated query field on it... ...
Calculating age in access	10/13/2008
Q: I'm hoping you can help me, I have been trying to calculate age in my employee database as well as ... A: I think you've got way too much there. All you need to get the difference in years is: ...
Using tab control on a form	10/12/2008
Q: I have a form and I have used form control to insert 2 tabs and the tabs show data from two ... A: Use a form/subform combination to show related records if you have a one-to-many relationship. If ...
Dcount problem	10/11/2008
Q: I am using access 2003. I have a table called main and in that table I have a field called Valid ... A: It looks like your string needs some help (too many quotes): ...
Auto fill	10/10/2008
Q: I have a database with several tables linked to a form with the purpose of entering only valid data. ... A: I'm assuming this is happening in your COMBO BOXES. If so, just change the AUTO EXPAND property to ...
Linking Tables	10/10/2008
Q: (I am an Existing customer of your 599 CD's-they are great!I am working on section 102. I plan to ... A: Harvey, I would never recommend linking tables together based on any kind of a text field. I would ...
Create queries	10/10/2008
Q: how do create queries form link excel file in mircrosoft Access A: Once you link an Excel file into Microsoft Access properly you can create queries from that file ...
UPdate Query not working	10/9/2008
Q: I have an update query which I am running Onclose of a form. Rather than updating the record as per ... A: Yea it's not easy… But if you do it enough it becomes second nature. Let me know if you have any ...
date range criteria in query (from a form)	10/9/2008
Q: I have a form that allows a user to pick certian criteria for a query. One of the options I would ... A: William, your first set of criteria should work just fine. I'm assuming that these are unbound text ...
Prompt...	10/9/2008
Q: I am creating a database to hold information for all my employees. There is one field in particular ... A: Just create a field in your table to store this probation date and your yes/no value that you ...
filter	10/9/2008
Q: i have a table with 4 columns the first column is ISBN i would like to know if i can filter all ... A: Sure. Just set your filter to: V Let me know if you have any other questions or comments. ...
Delete Duplicates In MS Access Query	10/9/2008
Q: I ran a find duplicates query with the below criteria: In (SELECT [Invoice Number] FROM [Credit ... A: You are absolutely correct. A find duplicates query does not delete data. In order to do this, you ...
MS Access (2003) Add the value of two columns	10/8/2008
Q: I have a database with nine (for now) columns. The first one identifies the household; the second ... A: Tomas, It sounds like you need a good tutorial in how relational databases work. If you have ...
Access View	10/8/2008
Q: Simple question. I just installed Access 2007. I much prefer a larger work area with the traditional ... A: Not that I'm aware of. I know, I really hate office 2007 too. I can tell you that if you hold down ...
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: Simon, I'm sorry but I don't understand your question. What exactly is it that you want me to ...
im sorta new, but..	10/8/2008
Q: to begin with i have a limited knowledge of access, but what i would like to do is create a data ... A: I don't understand your last question. Can you please be little more specific? Let me know if you ...
SELECT Statment	10/8/2008
Q: I have an SQL Statement that calls a table from a table. Basically, when you click on a person's ... A: If you don't have any training in Access or VB, then what you're trying to do is pretty difficult. ...
change the value of text box in report	10/8/2008
Q: .. Actually i want to manipulate the string of that object and then need to assign the result back ... A: You cannot change values in a report. You can only do that in the form. Let me know if you have ...
Insert/link data from one form to another	10/7/2008
Q: First, just wanted to say thanks because I have been able to use several of your tips and tricks ... A: See my last answer Let me know if you have any other questions or comments. Also, be sure to ...
Getting value from Combo Box	10/7/2008
Q: I have a comboBox "Village_Name" which is related to a table "tbl_village".I have retrived all the ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Expressions in Tables	10/7/2008
Q: I'm stumped on how to do something in Access and I'm hoping you can help. I have a field in a form ... A: Yes you can create a calculated field in a query that determines this age value and then use that ...
joins	10/6/2008
Q: There are 2 fields I need to join but none of the join types are applicable. I want to show all ... A: You can either use a union query or a make table query and two Append Queries. A union query pass ...
Insert/link data from one form to another	10/6/2008
Q: First, just wanted to say thanks because I have been able to use several of your tips and tricks ... A: Actually never mind what I said about the ongotfocus event. Create a popup modal form where you ...
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: Your question is too vague for me to give you a concrete answer. How exactly is this data set up? ...
Executing from 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: In Access you would say DoCmd.RunMacro "MacroName" Somewhere in your VBA (a form, a module, etc.) ...
access excel	10/4/2008
Q: I would like to import a complete database consisting of 12 to 14 tables into excel so that I can ... A: Again, I really don't understand why you are doing this. Perhaps if you explain to me what you want ...
Importing from Excel to Access	10/4/2008
Q: Our Foundation has a database of over 12,000 records. They received an Excel spreadsheet from the ... A: You would have to create a macro or some VBA code to compare each record, look it up in your table ...
SQL search queries and programming the form in VBA	10/3/2008
Q: I am a fledgling database programmer (my OFFICIAL title is "data entry clerk" but I guess they ... A: I'm not exactly sure how you've got your database set up, but I think this tutorial will help 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: Exactly that… in your query, for your date criteria, say: > Date()-7 Let me know if you have any ...
Access Report	10/2/2008
Q: I have a table (based on a query). I like to know if there is a way to creat a report based on that ... A: I haven't tested this, but what about using a query to convert your dates into numbers for the ...
Converting access 97 files to access 2000	10/2/2008
Q: How are you doing? I ran into this problem and I want your help. I have a file that was created ... A: Back up the file first. Then you don't have to worry about screwing it up. Open it in Access 2003, ...
Access Database on Shared Drive - No login screen for some users	10/1/2008
Q: I have an access database on a shared network drive. Multiple users can access it and only see ... A: You have to make sure you log each user into your WORKGROUP using the MS Access Workgroup ...
Validating two table with one invoice number	10/1/2008
Q: I'm building a finance database for my company. All of their invoices is currently on Excel. Since I ... A: Yes, it's certainly possible, but without knowing more about your setup it would be hard for me to ...
UPdate Query not working	9/30/2008
Q: I have an update query which I am running Onclose of a form. Rather than updating the record as per ... A: Jeff, I'm assuming that the query has to use some field on your form as a criteria for the where ...
MS Access 2007 Query calculation using check box field	9/30/2008
Q: I have a very simple database to manage registration for an upcoming event and I'm trying to create ... A: Melanie, it sounds like it could just be a problem with your criteria. Are you using the word "yes" ...
access	9/30/2008
Q: I have a text file I am importing into a table. There is a key word STOP PAY that identifies the ... A: Well I'm completely confused. Are you saying that there is no rhyme or reason to the file that ...
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: Jennifer, I don't see a question here. What exactly do you want me to tell you? If you're looking ...
Issuing a project number on the click of a button	9/30/2008
Q: Each project we deal with is issued a unique project number e.g. 160, 161 etc.. I'm trying to create ... A: Stephen, try this tutorial: ...
Importing from Excel Spreadsheet to Update Records in Access	9/30/2008
Q: Our Foundation has a database of over 12,000 records. They received an Excel spreadsheet from the ... A: Tammie, Importing records and selectively changing all the records in your database is not easy. ...
Query count of days	9/29/2008
Q: I have a report I am working on which is in the form of a letter, I need to generate letters that ... A: You can do this with an IIF function. =IIF(DueDate>30 AND DueDate<60,True,False) I cover this ...
Date add	9/29/2008
Q: I have a form created in a ms access 2000 database. There are 3 fields, Probation Period Start ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Individual records from tables	9/29/2008
Q: Richard, I'm fairly new to Access and I'm currently putting together a database for employee ... A: I'm still not perfectly clear as to what you want me to help you with. what do you mean by "pull ...
Importing DateTime	9/29/2008
Q: I'm trying to import a file. One of the fields is defined as a Date/Time. The date is formatted as: ... A: That's very strange. That looks like a valid the date format that Microsoft Access should be able ...
MS Access Queries	9/28/2008
Q: First of all I am a beginner at MS Access and am building my first database. Here goes . . . I ... A: Denise, you can create a search form that has your technician names in a combo box. See this ...
Query two identical tables	9/27/2008
Q: I would like to create a report that show all the accounts and details i.e. acct#, name, ... A: I'm not sure why you have two different tables with the same information. However you can use a ...
Future date calculation	9/26/2008
Q: I have a business form used for employee discipline. There is a field for entering a date of ... A: I get asked this question a lot. Excluding weekends is not hard. You'll need to loop from start ...
Multiple Users	9/26/2008
Q: I was wondering if multiple users(more than 4) can change the same database. We are tracking our ... A: Ten users should be just fine. Again, the amount of traffic in your database is more important than ...
access excel	9/26/2008
Q: I would like to import a complete database consisting of 12 to 14 tables into excel so that I can ... A: Why would you want to go from Microsoft Access to Microsoft Excel? That's going backwards. If ...
text wrap in headings	9/26/2008
Q: Sorry that this is probably a very basic question but I've been unable to get a solid yes/no answer ... A: No. But you can make your or one of labels as headers in a continuous form. This is better than ...
date calculation	9/26/2008
Q: I'm kind of in a bind when it comes to access, i have been asked to make some changes to a rather ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
importing ascii fixed length files	9/25/2008
Q: I read your question/answer from Date: 3/26/2008 -- Subject: importing ASCII fixed length files. ... A: I do cover importing fixed width text in my Microsoft Access 308 class. It's not a free tutorial, ...
Importing from Excel to Access	9/25/2008
Q: Our Foundation has a database of over 12,000 records. They received an Excel spreadsheet from the ... A: Yes you should be able to import the data into Microsoft Excel. Just go to File > Import Data and ...
Sorting part of a text field	9/25/2008
Q: How do I sort on the second word of a text field? I have an address field that I want to sort by ... A: You are going to have to isolate the street address from the rest of the address field. You can use ...
lock data?	9/25/2008
Q: I have a form where users can lookup and enter data (all the data is pulled from one table). Is ... A: Molly, you would need to change the AllowEdits property when you load the record, preferably in the ...
Insert/link data from one form to another	9/25/2008
Q: First, just wanted to say thanks because I have been able to use several of your tips and tricks ... A: Jennifer, thank you very much for your kind words. What I would do is open the address form modal ...
access reports	9/25/2008
Q: i have several variables in a table including hours and date in a report , i want to get access to ... A: You can specify criteria for running your queries, such as limiting between two dates, checking for ...
MS Access Label Wizard - number of lines per label	9/25/2008
Q: If I use Label Wizard and choose Avery L7162 as the label format and font size 10 the Wizard only ... A: The wizard will format the label based on what it thinks you can fit. If you want to squeeze more ...
Solving Ending Inventory issue	9/25/2008
Q: I'm using Access 2003. I have created a table, query, and a form. The form pulls information from ... A: Amy, this is not going to be easy. I assume you're going to want to do this manually, perhaps by ...
Multiple queries to one form	9/24/2008
Q: I just wanted to know if there was a way to have multiple queries point to one form. I have a ... A: Sure... just change the RecordSource of the form after you open it. Docmd.Openform "FormName" ...
table with large number of fields	9/24/2008
Q: I am creating a table that will go over 255 fields, and wanted to know how to break them out into ... A: Why do you need 255+ fields in your table? I've been building databases for 15 years and I've never ...
conditional formatting in Access	9/24/2008
Q: I am trying to make a field color change if another field is not blank If Field 1 has a number in ... A: You can use an AfterUpdate event to set values, change properties, run macros, and do lots of other ...
Counting number of items by building in Access	9/24/2008
Q: I am a creating a Access program for my company here and one of the reports that I am supposed to ... A: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little ...
Combo Box	9/24/2008
Q: I have an employee table that has a primary key that is autogen. I have a comp table that I want has ... A: First of all, you realize that you're breaking the rules of normalization by storing that data ...
hyperlink pdf in mc access	9/24/2008
Q: I use a folder full of pdf documents saved with a naming rule (yymmdd_logno.pdf). I have a db that ... A: Honestly, if you're a complete novice, this isn't going to be easy. You need to know how to program ...
Ms Access 2007 - clients with more than one delevery address	9/24/2008
Q: I have a MS access 2007 database which keeps track of orders made by clients. Some of this clients ( ... A: If you run into two addresses (let's say billing and shipping) or even maybe 3 (billing, ship1, ...
preventing importing of a database	9/24/2008
Q: I created a user group, and I am trying to set it up where no one can import the files into another ... A: Sorry, Dee, but Access security is extremely flimsy. The only way to make your data 100% safe is to ...
query	9/23/2008
Q: what makes a recordset from a query updateable vs not updateable? A: That all depends on the cursor and lock types. I almost NEVER base a recordset on a STORED query... ...
filter in combo box	9/23/2008
Q: I am using Access 2000. I have a combo box with list of 12 months. When got focus (automatic drop ... A: You're going to need a properly built combo box that has 2 columns. The first column is the number ...
Display Query of Check Boxes	9/23/2008
Q: ..I need help displaying only the fields that are checked in a check box. For example if there are 9 ... A: In the build event for that section of your report, just say: F1.visible = F1 F2.visible = F2 etc. ...
Searching 6 fields all optional	9/23/2008
Q: Could you possibly help with this... ? I have a second problem which I would love to get some help ... A: While you can use a single query with IIF statements and LIKE keywords and all that, you start ...
Ignoring parts of form with no criteria input	9/23/2008
Q: I am trying to build a query whereby someone can put parts of up to 4 diagnoses into a form. The ... A: While you can use a single query with IIF statements and LIKE keywords and all that, you start ...
relationships	9/23/2008
Q: What is the purpose of forming relationships between tables, what benefit does this provide, what ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
Count Days and Grouping	9/22/2008
Q: I need help in resolving my dilemma, In the query design I have to fields [OpenDate] and ... A: You can use the IIF function to group your dates in a query. LessThan30: ...
Query problem with dates	9/22/2008
Q: I have a very large table containing all material purchases along with a date received and quantity ... A: You can use DMAX to find the largest value (most recent date). MostRecentDate = ...
Order details	9/22/2008
Q: How would i create an order confirmation for each of our orders on access. Each order has perhaps ... A: You need a second, related table. It sounds to me like you need to learn a little more about the ...
Access Report	9/22/2008
Q: I have a table (based on a query). I like to know if there is a way to creat a report based on that ... A: When you create your report, create a SORTING AND GROUPING level and specify the INTERVAL as 100. ...
Attendance	9/22/2008
Q: What all fields i need for a school attendance, so as to calculate their result on that basis. A: That all depends on your needs. See this tutorial: ...
joins	9/22/2008
Q: There are 2 fields I need to join but none of the join types are applicable. I want to show all ... A: You've got me. I spent about half an hour on this one, and I can't figure it out. I tried all kinds ...
searching using drop down lists in a form	9/22/2008
Q: basically i have 3 drop down boxes. depending on what you select in the first ones affects what can ... A: Well, if you've built your database right, you should only need to search based on the LAST field - ...
MS Access : Make copy of Desigmaster	9/22/2008
Q: I asked this question to another expert but he did not understand what i want and is offline now. Am ... A: Just copy the database file (ACCDB or MDB file) to a different filename and use that copy for the ...
Club Membership Database	9/22/2008
Q: I'm new to MS access but have learnt the basics well. I'm designing a databse for a country club. ... A: It sounds to me like you need to learn a little more about the basic concepts of building a ...
auto complete fields	9/20/2008
Q: I am not sure if i am explaining this correctly but i will give it a go. i am no expert on access ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Linked forms	9/20/2008
Q: I have a main form which contains a field called Establishment. In the subform, I have a field ... A: You need to somehow relate Establishment with ContactPerson. I'm assuming each Establishment has a ...
Complicated Where Statement in SQL/VBA	9/19/2008
Q: I am trying to script a 'Where' statement to check the value chosen in a combo box against 4 fields ... A: Just use the WHERE condition of the DoCmd.OpenReport command. It works the SAME as the WHERE ...
Ms Access New data every time	9/19/2008
Q: I made a database form. But the problem is every time when I open the form, I see the datas (that I ... A: You can set the form's DataEntry property to YES and that will make it so you can only add records. ...
Table Conversion/Creation	9/19/2008
Q: I have a table with historical data that contains hire dates for employees. This table a record for ... A: First of all, I have to ask WHY you want to move backwards? The way you have the database built in ...
I need direction!	9/19/2008
Q: I am currently working with an simple database that contains mostly data about members (name, ... A: Yes, you certainly can do all of this with Access. Unfortunately, explaining how to do ALL of this ...
Access Database on Shared Drive - No login screen for some users	9/19/2008
Q: I have an access database on a shared network drive. Multiple users can access it and only see ... A: It sounds like the previous user is just not logging out properly. That's more of a TRAINING ...
Help with VBA and SQL statement	9/19/2008
Q: I have a form with a Combobox and a Listbox on it, and I would like the user to select a record in ... A: Build your SQL statement in a string and send it the VALUE of your ID: S = "SELECT ...
MS Access DB	9/19/2008
Q: I have a DB of words categorized by difficulty level for my dyslexic son, whom I tutor. I want to ... A: Yes, it's possible. Yes, it will require some VBA programming. You won't need a special app ...
Problem with criteria in calulated date field	9/18/2008
Q: I am struggling to understand where I have gone with select query. It is meant to use paramters to ... A: OK, take a look at these tutorials and then if you still can't figure it out, ask me again: ...
help with changing record source	9/18/2008
Q: The form is called EntryAll code: Private Sub ROZ_Click() Forms!EntryAll.RecordSource = ... A: Flip it around. You can't change the RecordSource if the form isn't open. Private Sub ROZ_Click() ...
Individual records from tables	9/18/2008
Q: Richard, I'm fairly new to Access and I'm currently putting together a database for employee ... A: Steve, I'd love to help you, but I have no idea what you want me to help you with. Is there a ...
show price for product	9/18/2008
Q: Hey there, I have a table called Products. It has the following fields: ProductID, Product, Price. ... A: You can use the DLOOKUP function to look up a specific value from a table or query. Please see the ...
Concurrent Data Reader	9/17/2008
Q: I have a programer creating a program that will read an MDB file with about 2,000 records approx 2MB ... A: I don't believe there is a PHYSICAL limit. Realistically it more depends on the activity than the ...
Access Database form	9/17/2008
Q: I have an inventory form and a subform, the main form has the my supply information and the subform ... A: You could do this a couple of ways. You could hop back to the LastRecord, get the value, and then ...
Control Button on Form	9/17/2008
Q: I have an order entry form with a control button to add new products. To get the order entry form ... A: So you click on an "add new product" button which takes you to another form. The user adds the new ...
Adding time(hours/minutes)	9/17/2008
Q: Is there a fairly easy way of adding HOURS/MINUTES and then formatting it in an HH:MM format without ... A: Well, see this tutorial. This might explain how times work in Access a little better for you: ...
HELP	9/17/2008
Q: I need help in access. Is it possible to make some type of application in access where I can ... A: Sure. You need a Products table and an Ingredients table, then a third table to link them together ...
Multiple Calculations	9/17/2008
Q: I need some help on figuring some calculations. Here is what I am working with…I am working on Work ... A: You can calculate the totals for each subform in a footer total of that subform. Then if you need to ...
Multiple Users	9/17/2008
Q: I was wondering if multiple users(more than 4) can change the same database. We are tracking our ... A: Four users should be able to work just fine provided that none of them has the database open for ...
MS Access	9/17/2008
Q: Can you convert databases directly from Access 2000 to Access 2007? A: Honestly, I'm not sure. I know you can go from XP/2003 to 2007, but I've never tried 2000 to 2007. ...
Expiration Date	9/17/2008
Q: I am building a database and have an inventory table. The database deals with flowers and one of ... A: Well, you could very easily make yourself a query that shows you all plants nearing 2 weeks old. ...
Trim	9/16/2008
Q: Sometimes when I enter data I might place an unwanted blank space before typing. Is there a way that ... A: You said it yourself in the subject... use the TRIM function in an AfterUpdate event: MyValue = ...
Designing Database Schemas	9/16/2008
Q: Richard, I am building a database to store packing list information for the products our company ... A: You're probably going to find that a lot of the products have a lot of fields in common. Make one ...
Assign a color to a number on a form	9/16/2008
Q: How can I assign a number to a color? I have 3 color coded status categories and I would like an ... A: Good point. You can add code to the OnCurrent event of the form to fire off your color change when ...
web based form	9/16/2008
Q: I have an access database that I really like. It's functional, does everything I need it to, etc. ... A: You could set up a DATA ACCESS PAGE but they would each have to have Access, and DAPs really don't ...
add 6 moths with a yes/no box	9/16/2008
Q: I am trying to create a data base to control the loan dates of plants. We have certain plant species ... A: Yes, you can use the DateAdd function in an AfterUpdate event. You can use the DateAdd function to ...
Public Varibles	9/16/2008
Q: Using Access 2003, I have a declared a varible public on one form. On this forms subform I have ... A: You can't access it that way, even if it's public. I would recommend creating a hidden TEXT BOX and ...
joins	9/16/2008
Q: There are 2 fields I need to join but none of the join types are applicable. I want to show all ... A: You have just completely blown my mind. In over 15 years of building databases, I don't think I've ...
searching using drop down lists in a form	9/16/2008
Q: basically i have 3 drop down boxes. depending on what you select in the first ones affects what can ... A: I get asked this question all the time. You've got two or more combo boxes and you want the first ...
requery main form	9/16/2008
Q: Access97. I have a main form that I double click a field to open up another form where I add ... A: That's a tough one. You could try Me.Refresh instead of Me.Requery. That MIGHT work. Otherwise, you ...
Filter and preview	9/16/2008
Q: It helped me a lot. My new question is... I have a database. It has 3 fields. ID NAME ... A: Sure... just make your report based on a query that is filtered by your combo box. I know that ...
Run a calucation on a single fourm	9/16/2008
Q: I am trying to make a digital time card but with some advanced reporting and options that I will ... A: Make a Startup form that opens with your database. Have the user pick his EmployeeID and that can ...
Foreign keys from the same table	9/16/2008
Q: I've been given the task to create a relatively simple database. In the one table I have the fields ... A: You can have multiple forign keys in one table. For example, you might have fields called ...
Fill data	9/15/2008
Q: I have a Access 2007 database and I am trying to get a form to look and work a specific way, but I ... A: I get asked something similar to this question a lot. You can look at this free tutorial for an idea ...
Launch Word from a form to populate a field...	9/15/2008
Q: In a Form, is there a way to launch MS Word from an event that will allow for a more user friendly ... A: It would be EXTREMELY difficult to do this. You might want to look into the Microsoft Rich Text ...
CALCULATING TOTAL MILES	9/15/2008
Q: I am creating a daily log database for our officers. This database will track the date, time, ... A: It sounds to me like you just need to learn how to properly create CALCULATED QUERY FIELDS. You can ...
Count IIf using multiple text string criteria	9/15/2008
Q: Richard, I am creating a total count in a Recruiting Human Resource Report. I want the expression ... A: IIF needs THREE things: condition, value if TRUE, value if FALSE. I'm surpised your individual ...
DLookup not working	9/15/2008
Q: I have a DB with 2 tables, one is just a lookup table with two fields (ShipperName, EIN)about 30 ... A: If Shipper is a TEXT field, you'll need to enclose it in quotes: AesPostUsspiEin = DLookup("EIN", ...
Msaccess	9/15/2008
Q: Sir, Is there any tool available to make blinker in forms in msaccess2000? I would like to setup ... A: You could program an OnTimer event and change the background color of the textbox, or place a ...
Date Validation	9/15/2008
Q: I have two fields, DateOfService and DateBilled. I need to set a validation Rule in DateBilled that ... A: Gary, you can use an AfterUpdate event for your fields that checks this... If DateBilled < ...
Database	9/14/2008
Q: I said to a friend that I would try to figure out his problem for him, and I am not having any luck ... A: That's not a problem. You just need a PRODUCTS table and an INGREDIENTS table. Now, you need to make ...
opening a directory from a form using form data	9/14/2008
Q: Don Darracq answered a question titled "open word files within access" on 10/20/2006 by using the ... A: That GetObject code is specific to a Microsoft Office object (Word Document, Excel Sheet, etc.) If ...
Filtered combo box on subform	9/13/2008
Q: I have a form with 2 combo boxes. The first is tied to a table with 2 fields - Service and Type - ... A: A combo box on a form is referred to as: Forms!MyForm!ComboBox If you put it on a subform, you ...
Use of Linked Table	9/13/2008
Q: Normally I work on table imported from excel or text file,When should i use linked table, what is ... A: If you need to keep the databases separate, LINK them instead of embedding them. Otherwise they ...
Text Box control	9/13/2008
Q: I am trying to make a form. It has 3 text boxes. Textbox1, Textbox2, Textbox3. I want to make the ... A: Make T2 and T3 disabled. In the AfterUpdate event for T1, check and make sure the data is valid. If ...
Relating feilds in seperate databases	9/12/2008
Q: Richard, I enjoy your site.. Although I am finding difficulty solving my problem. I have attached 2 ... A: First, you'll need to LINK a copy of your 2nd table into your database (File > Get External Data). ...
MS Access Form - Controlling the visibility property of a picture	9/12/2008
Q: I am using Access 2003. I have a simple checklist form that contains several YES/NO data type ... A: Anna, I would really have to see this to tell you for sure, but you could try using a DCOUNT ...
Start Time End Time	9/11/2008
Q: I own a company that requires my employees to review sales that were generated by telemarkeing ... A: Good question, Christopher. This wouldn't be too hard. I would create a form where the user can ...
Similar/Partial Name Match	9/11/2008
Q: To find similar/partial match names between two tables, (tblA has several thousand names tblB has ... A: Bonnie, it's possible... and you're not taking a BAD approach... but I would really need to see your ...
filled in fields on a table	9/11/2008
Q: On my table I am trying to create a lookup so that when I select one field some of the other fields ... A: Bev, you need an AfterUpdate event. See this tutorial: ...
time field	9/11/2008
Q: When I merge my database into a Word document, the time defined field inserts a date (12/30/1899) ... A: Well... it works for me. :) Without knowing exactly what you're doing I really can't fix the ...
List box on report auto-expand to meet data size	9/10/2008
Q: I have a report which has multiple unbound list boxes which use a row property which entails a ... A: Well, you could play around with the Height property of the listbox in VBA code. You'd need to count ...
inventory	9/10/2008
Q: from an inventory table, i need to a field named "stock balance" that will automatically give me the ... A: Sounds like you just need to know how to do basic query calculations. See this tutorial: ...
im sorta new, but..	9/10/2008
Q: to begin with i have a limited knowledge of access, but what i would like to do is create a data ... A: Lance, if you're new to Access, then I should tell you what you're trying to do is NOT easy and will ...
Duplicate records in query	9/10/2008
Q: I have 2 tables: invoices and payments. Each table has duplicate records, but they are legitimate. ... A: Lana, I don't understand why you have duplicate invoices in your table. If you're splitting invoices ...
Can I search for a square bracket "[" using a query?	9/10/2008
Q: I seem to get an error whenever I try to FIND a square bracket, yet my database is full of square ... A: You may have a problem using the FIND feature, however if you design a QUERY to do your search, you ...
access search	9/10/2008
Q: hey I was wondering how I would create a form that could have a few different properties like city ... A: You could do this with a query and multiple parameters. See this tutorial: ...
AUTO POPULATE FIELDS	9/10/2008
Q: I have two fields, one for a home address and one for a current address. I would like to know how ... A: Use an AfterUpdate event for each of your address1 fields to fill in address2. See this tutorial: ...
Multi User Access	9/10/2008
Q: Richard : I read a users question and your answer regarding multi-user Access front end and sql ... A: It's both a speed and maintenance issue. Having a local copy does speed up the database (not ...
inactivate the data	9/10/2008
Q: When a group of data is no used anymore, but i do not want to delete it, i want to inactivate it, by ... A: Just put an "IsActive" field in your table. You can check things on and off that way. Just make sure ...
DLookup	9/9/2008
Q: I am trying to build a form that using a DLookup goes and pulls a commodity code from a table when ... A: Michael, your DLOOKUP code looks perfect. I don't understand why it's only working once. I would ...
Format Date/Time - Year only	9/9/2008
Q: I am trying to create a field in my database (using Access 2000) that stores and displays only the ... A: If you only want to ENTER the year, then just make your field a NUMBER of type Integer. Don't bother ...
Check if a date range includes weekend	9/9/2008
Q: I'm very new to working with a database, but haven't been able to figure out how to check if a date ... A: Well, you would need to loop through all of the days between your two dates and see if any of those ...
On Current Event	9/9/2008
Q: I have the following set for the Current Event of form "frmOvertimeEntry" Private Sub ... A: No... "underneath" the form means that the QUERY is what the form gets its records from. Table > ...
Database Question	9/9/2008
Q: I have a query in my database and in one of my columns, if there is a date, I do not want the entire ... A: Make a query to show only records where that date "Is Null". That will effectively hide the ...
Subform	9/9/2008
Q: I've created a subform (from table 'logon') linked to a main form (from table 'bookings') using join ... A: Sounds like your link is messed up. Open up the properties for your subform and make sure both ...
Update query, Use value in text box to update with	9/9/2008
Q: I hope to provide you with all the information you require here. I have 2 tables, 1 called ... A: Why don't you try building the SQL statement as a text string and then instead of running the query, ...
Field copy macro	9/8/2008
Q: I have a database that contains client information. We store the physical location of the business ... A: Use an AfterUpdate event: http://599cd.com/tips/access/afterupdate-event-vba?key=AllExperts Let ...
Control Wizards stopped working Access 97	9/8/2008
Q: The control wizards for access stopped working. For example, if I add a subform to a form, the ... A: There is a button in your TOOLBOX that turns the wizards on and off. I don't have Access 97 ...
Changing a Caption in VB Access 97	9/8/2008
Q: I have a caption on a form that is not bound to a field. I change the caption in visual basic but ... A: Is there a way to PERMANENTLY change the caption in VB? Yes... but I don't know it offhand (I'd have ...
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: Well, you could manually import all of the data into one database. If it's the same table name and ...
Setting required information for a field in access	9/7/2008
Q: I am trying to set up a complex numbering system, at least for me, the number system looks like this ... A: Here's some code for converting to Julian dates: http://support.microsoft.com/kb/116281 Here's a ...
ms access ustonumber field skips increment	9/7/2008
Q: i notice that acces (2003) is skipping increments, for example it issues 12,13,15, skipping 14. can ... A: Access does not REUSE Autonumbers. If you add a record and then delete it, the number will not be ...
Sum Field	9/6/2008
Q: I am creating an order confirmation table that i will store customer orders and print and send order ... A: See these tutorials: http://599cd.com/tips/access/calculated-query-fields?key=AllExperts ...
Access user security	9/6/2008
Q: My computer crashed. My Access 2003 database had user security turned on (required logon to open ... A: Sounds like your backup copy might have gotten marked READ ONLY. Just copy the MDB file to your hard ...
Keeping records field together	9/5/2008
Q: I have a table called TableConference and in form, I have created a form. In the form, the fields ... A: I'm not quite sure I understand your problem. You want to pick an option from a combo box and then ...
Updating a NULL value	9/5/2008
Q: I have combined two tables Using "LEFT OUTER JOIN"..Now I want to Update the Null value of a field ... A: I don't think you could do this with a simple query. You might be able to do it with an Update Query ...
Running a query from a command button	9/5/2008
Q: I've inherited an ordering system (based on the Northwind free dbase I think) which I'm trying to ... A: Sure. See this tutorial: http://599cd.com/tips/access/incrementing-your-own-counter?key=AllExperts ...
if statement in a form	9/5/2008
Q: i have a form with several fields i.e consultant amount jobid john 1000 j1000 john ... A: I would suggest doing this with a REPORT and a SORTING & GROUPING LEVEL. You could group by ...
calculating numbers of days between 30, but less then 60	9/5/2008
Q: I have a date field which is manually entered. I am taking this field and adding 3 months to it. ... A: You can use the IIF function in a query criteria for your date field: ShowThisRecord: IIF((MyDate < ...
Assign criteria in "Update to" for update query	9/4/2008
Q: I have a situation where I have a table full of existing orders and I need to assign them on a ... A: Sure, just make a form that has the date on it in an unbound text box and use that to feed your ...
.csv to access using macro in access	9/4/2008
Q: I have a .csv file. Now I need to write a macro in Access which will fetch data from the .csv file ... A: You don't necessarily need a MACRO to do this... just use File > Get External Data and import the ...
Table holds info but form won't display	9/4/2008
Q: I have a couple of forms based on two separate tables. The form is supposed to take in the ... A: If the form's Record Source is set to your table, that's the way it should behave. Open up the ...
Unbound form SQL Backend Access Front End	9/4/2008
Q: I want to create a form in Microsoft Access with unbound form where I can search for a record by ID ... A: You could use the DLOOKUP function to find the record you want and set all of the unbound text boxes ...
Access form/subform	9/4/2008
Q: I added a checkbox field to a table. The table is the "many" in a one to many relationship. I want ... A: Did you close everything down and reopen it? If you have a form open in Design mode and add ...
WeekDay Function	9/4/2008
Q: I am trying to create a report that will give me follow up assessments for our clients at 30, 90, ... A: You could use an IIF function in your report to just SHOW a different date. ...
displaying results	9/3/2008
Q: I have built a parameter query to my table (1 table only) my question is how do i make the query and ... A: You could make a form that has parameter text boxes on it: ...
Tables	9/3/2008
Q: I am creating a database on mushroom picking for my new job, and I need to make it so that when I ... A: Sure, use the DLOOKUP function to get any data you need: ...
saving mulitple products in stock	9/3/2008
Q: i have created a stock database and have a form to book in stock, but i would like to have a button ... A: Yes, it's possible. The CHEEZEY way to do it would simply be to have a command button move to the ...
Auditing Tool	9/3/2008
Q: Is there any form of audit tool built into access in order that if any data in my tables have been ... A: Once the data has been saved to the table, there's nothing you can do really. If you catch the ...
Date format in access	9/3/2008
Q: Is this possible without inputting the whole date but using the date format. How does Access handle ...