	AllExperts > Experts
	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

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 ... A: It might be easier just to enter the year as a number (integer) such as 2008, and then if you need ...
calculate time difference in Access 2007	9/3/2008
Q: My question may not meet your guidelines, but I'm going to try anyway. I am trying to put together ... A: Here's a tutorial that will help you: ...
Table Required Property	9/2/2008
Q: I have created a database to enter the marks of students I have set the default value to 0 and ... A: If the REQUIRED property is set to Yes, then they shouldn't be able to leave it blank. You could ...
time field	8/30/2008
Q: When I merge my database into a Word document, the time defined field inserts a date (12/30/1899) ... A: In Access, make a QUERY and use the FORMAT command to display the date/time exactly as you want it. ...
Multi User Access	8/30/2008
Q: Richard : I read a users question and your answer regarding multi-user Access front end and sql ... A: Assuming JUST an Access database without a SQL Server backend... I don't know if there is a ...
Time delay	8/30/2008
Q: I was wondering if you could tell me, is there anyway to have a modal form pop up on a particular ... A: Sure... just make it your startup form. If you want to use a single password for everyone that's ...
Access in network	8/29/2008
Q: I've already developed a VB.net application with ms access as backend locally i.e. not in a network. ... A: You shouldn't have any problems putting your Access database on a network server - or even a ...
Report Using A Combo Box	8/29/2008
Q: Can i do the above to do a report of what is on the form and what is on another form. e.g. on the ... A: You would need to make a query to limit your results based on the business type - then feed those ...
Preventing Duplicate Field Entry per Record	8/28/2008
Q: I am trying to prevent duplicate entries of the exact same job order per employer. The employer is ... A: Honestly, I have NO idea what you're trying to do with that statement. If you want to see if a ...
Calculating processing time	8/28/2008
Q: My department tracks requests received from clients. I have established an Access database to track ... A: To start with, your query expression should read: Lagtime: DateDiff("d",[Date Received],[Completed ...
Counting Specific Fields and adding to a counter	8/28/2008
Q: I do not have a ton of experience with access as I am learning as I go, so any information would be ... A: You could make an AGGREGATE query with the COUNT function to determine how many times each number ...
insert statement	8/28/2008
Q: i have two combobox on form. Every one from diferent table. when i select combobox value from both, ... A: You can use an AfterUpdate event to insert those values into the table... or just a Command Button ...
avg in access	8/28/2008
Q: I have a database that is tracking calls, what type of call and how long the call is. how would I ... A: To calculate call time just subtract the TimeStarted from the TimeFinished. That will give you the ...
Synchronizing tables in access	8/28/2008
Q: i am very new to ms access and need some ideas. i have one access database which contains some ... A: Yes, you CAN do it in Access, but there is no EASY way to do it. The only way I can think of to do ...
Access 2000 help learning	8/27/2008
Q: I would like to do an MS Access data base for my company, it is so hard to find any one to help me ... A: I think you should take a look at my Access video tutorial series. Below is a link to try the first ...
On Current Event	8/27/2008
Q: I have the following set for the Current Event of form "frmOvertimeEntry" Private Sub ... A: Instead of putting this in a form event, make it a field in a calculated query underneath the form. ...
"Conditionally" Requiring a Field	8/27/2008
Q: In field properties, I know how to make a field required (or not). But is it possible to make that ... A: First, there is