Using MS Access - Business Software - Computing/Technology

	AllExperts > Using MS Access
	Search

About Geoff
(Top Expert on this page)

Expertise
I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.

See my website for example apps and downloads

Experience
I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.

Commercial database design and development work undertaken.

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

Questions Answered By Expert Geoff
In Category Using MS Access

Subject	Date Asked

Contact Database that matches contacts up	6/12/2009
Q: I tried to create this in Excel and the functionality was limited and now I am thinking that Access ... A: Too many general questions here to be specific. I suggest you have a table of people, a table of ...
ms access 2007	6/11/2009
Q: I am new to this.and im doing my internship in an info management dept.i was given a task to convert ... A: Shafinaz, hi 1. Normalise the raw Excel data into individual Access tables (see wikipedia about ...
Validating field with a table	6/10/2009
Q: I have a postcode field in one table that will be used for data entry and a list of postcode in ... A: Luke, hi, Open the table for data entry in design mode, select the postcode field. Now find the ...
Macros	6/9/2009
Q: Im looking to make a macro that will pull out all 5 entries for the week and pick out The particular ... A: Nick, A query could do it,but you don't give much information about you tables, so difficult to be ...
Ms access double data entry	6/8/2009
Q: First i want to thank you in advance for your help. My question is as follows. i have an Ms Access ... A: Charles, hi Open Form2 in design mode Open the Properties dialog (RH click & Properties) Ensure ...
MS Acces field Autofill based on Lookup	6/7/2009
Q: I am using MS Access 2007, and currently I have two tables in it, one with car information, and the ... A: Michael hi, 1. Remove the look up on the table - that field should be pure data only 2. On the ...
Random record selection	6/6/2009
Q: Case: My roommates & I want to have an open house, so we started making a list of the people we ... A: In specific answer to your 2 questions Yes & Yes... However I have worked through a solution as ...
MS Acces field Autofill based on Lookup	6/5/2009
Q: I am using MS Access 2007, and currently I have two tables in it, one with car information, and the ... A: Michael, hi, Firstly, I would encourage you to put such functionality into a form, as tables are ...
Labels	6/4/2009
Q: I have created an order entry form that will print out an invoice and a label for each order, on the ... A: The query may be used to feed the label report - as it aggregates the data at invoice level, you ...
Help using multi-select list box for form filter	6/4/2009
Q: Geoff, Thank you for your help. This is a follow up to “Help with list box” asked 6/2/09. Your ... A: Firstly the "$$$" is nothing other than a very uncommon string - I wanted to be pretty sure that ...
Searching	6/3/2009
Q: I have a few tables and I need to search for specific words such as a persons surname or age or ... A: 1. Open your form in design mode 2. Ensure the Toolbox toolbar is open - at the top left are 2 ...
Searching	6/3/2009
Q: I have a few tables and I need to search for specific words such as a persons surname or age or ... A: The 'under-the-Hood' of searching is not easy, I would say to program a form to provide a ...
Help with ListBox	6/2/2009
Q: I’m using MS Access 2003. The code below allows a user to browse to files and (multi) select ... A: Could try the following small change in the assertion: If IsNull(DLookup("[SlctdFls]", ...
Combining Invoice & Inventory in microsoft access	6/2/2009
Q: I have used Microsoft Access, but those times i used'em, still haven't found out about how to ... A: Adrian, hi, I don't know this application, so I can't offer any specific advice as such, however ...
MS Access	6/1/2009
Q: I am trying to design a travel requests app in MS Access. The program should be able to take ... A: Just had a quick look: If you remove the [ItineraryAccommodation] table, then it would seem that ...
Labels	5/29/2009
Q: I have created an order entry form that will print out an invoice and a label for each order, on the ... A: Peter, hi, From your description, it would appear that there is one box per order, one label per ...
MS Access 07 to mass email to outlook 07 (novice)	5/29/2009
Q: I'm starting a database for my small business called eclipse signs we have a basic database with ... A: Sean, hi To create some functionality to deliver this requirement will require knowledge of Access, ...
MS ACcess replace value by text	5/28/2009
Q: I'm an absolute newbie in MS Access although I'm learning fast. I've created a lookup table that's ... A: Use the DLookUp() doman function: - there are a whole family of these which you will find very ...
TransferSpreadsheet	5/27/2009
Q: I have a query I want to export out as an Excel file but I need the filename to be unique each time ... A: I have written a small function that returns an instantaneous file name with the date/time formatted ...
Query Parameters	5/27/2009
Q: I have a Access Database, and I was wondering is it possible to set the parameters in the queries ... A: You can control the SQL behind a query using the QueryDef object in VBA, ie. Public Function ...
summarising data records with VB	5/26/2009
Q: I am looking to produce an Excel report from an Access database table. The data contains numerous ... A: Neil It would be easiest to generate the report 'table' as a query in Access, which is based on the ...
open different access forms based on user groups	5/20/2009
Q: Here is what i am trying to achieve - Want to create two forms (A & B) Want to create two groups ... A: I will assume the logged in [UserName] is saved in a table called tblSession I would write a ...
access synchronization	5/18/2009
Q: i am using access 2007 I have a Access db that is used by multiple users, some of the users have ... A: One option you may consider is using the replicated database feature in MS Access: I know nothing ...
Calculations using dates	5/14/2009
Q: I am designing a database to record information about appointments for a charity. The first ... A: I assume you have 2 tables (at least), I'll assume these as called tblAssessment and tblReview I ...
MS Access report - numbers of rows	5/13/2009
Q: I have a report with details (shipments) separated by countries. For each country should be ... A: I built myself a table to construct the SQL. The table definition is: RecID -> PK unique record for ...
Forms	5/11/2009
Q: how do I create a warning message after I hit tab or enter when I have a duplicate value. A: Mellisa hi, Generally, you can use the BeforeUpdate event to do this sort of comparison / check, so ...
How to breakup records in one field	5/10/2009
Q: Hope you can help. I needed to create a table based on records for another table. What I wanted to ... A: A simple query will do that. The query below will list the records / data to be copied SELECT ...
Access 2003 tables	5/8/2009
Q: I have 2 tables in Access 2003 which are similar in structure. One has 11,012 records and the other ... A: Add a column to Table1 (11012 recs) called [xx] with datatype Numeric/Long 'Run the following query ...
dwg file attachment	5/8/2009
Q: I want to link the latest version of the technical drawing of a manufactured part available as a ... A: All I can suggest at this stage is that you hold a field in the 'part' table called ...
error 3134	5/7/2009
Q: Private Sub New_Invoice_Enter() Dim strSQL As String strSQL = "INSERT INTO Customers (FirstName, ... A: Try inserting a 'requery' command as follows: Kind regards Geoff :-) Private Sub ...
Printing labels - adding to existing mailing list	5/6/2009
Q: I have a mailing list of over 1700 names, just recently our post office informed us that we need to ... A: Sherry, hi, You should be able to amend the query that feeds the mailing list, with an extra ...
Access Report Calculation For Buy & Sell	5/6/2009
Q: I want to set a report from table (Action, Code, QTY, Price) Where Action is Buy or Sell & QTY for ... A: Mahendra, Hi, Try using the IIF() function to generate two totalising columns in a reporting query: ...
Wireless MS Access 2007	5/5/2009
Q: We have a small business and intend to add an integrated computer system with computers at each work ... A: Chris, I can only comment in a very strategic manner - details may say otherwise... Two issues: 1) ...
using if statements	5/5/2009
Q: I'm setting up an access database. I have two columns A and B. The names entered in A refer ... A: Dean, hi Not sure I understand your starting point: you currently have a single table, with two ...
Using Expressions in Tables	4/29/2009
Q: Is there any way possible to use expressions in a table. For instance if I have a table that has 2 ... A: It is very common to build a 'view' of one or more tables, including calculated fields etc. This way ...
Project Explorer	4/27/2009
Q: Vba code to set the value for a form control fails with an error message "Microsoft Access Can't ... A: Denis, Under Tools, Options, View - you can select Hidden and System tables - check these and see ...
Searching Partial Field	4/26/2009
Q: i have been using MS Access 2007 to build a database of clients. I ahve set up the queries so that i ... A: Definitely... The following expression will convert a date into a string of format DD/MM ...
Data Mismatch error	4/25/2009
Q: I have a expression below but it keeps coming up with an error that says "Data Mismatch error" all ... A: Try using the Nz() function to cover the Null scenario, as follows: ...
2 level query	4/24/2009
Q: I'm stuck on what seems would be fairly easy but its not quite working right. Basically, I just want ... A: An interesting challenge !! It would appear your data is structured correctly: the key to your ...
Auto Increment	4/24/2009
Q: I am designing a receipt as a report on Access 2003 and we keep track of the number of ... A: You will need a dedicated table and a function - the table holds the last value (persistence) and ...
Database management	4/22/2009
Q: I have three tables(CompanyInfo,History,and PositionStorage) and I want to create the one to many ... A: Sonita, hi If you want to update data, then you will have to on an individual table basis. ...
Crystal Reports Formula Field	4/21/2009
Q: Crystal Reports XI The assignment is to create a field called "Points" in a Crystal Report using ... A: John, 1. Create a lookup table of Grade points tblGradePoints Grade - String Points - Numeric - ...
Forms	4/21/2009
Q: How can i create a form with a plus sign on each record so that when i click on the + sign the ... A: Build your form and sub-form as per normal Take a note of the sub-form object ->> assume this is ...
Print report with data selected from Combo Box	4/20/2009
Q: Greetings, I have made a question here to another expert but the answer was not sufficient as i ... A: Ralf hi, I presume the issue is understanding the phrase:- Forms!formname!controlname Forms - ...
Access 2007 split database	4/20/2009
Q: I want to deploy a split database using the runtime version. How do I specify the file location ... A: All users will need full rights to the Access back-end database on the server. You can reference ...
Synchronizing two subforms	4/18/2009
Q: I tried what you suggested a few days ago and it worked partially. I was the one who was trying to ... A: Greg, The DataSheet view needs to have the Master Child link fields de-linked, then you will need ...
Outputting Access Tables as Viewed on Screen	4/17/2009
Q: Geoff - I ran the sample code (see end) you provided a while back and everything seemed to work ... A: Try the variant below: open the text file from Excel, select " as text qualifiers and ',' as data ...
Record Navigation in MS Access	4/15/2009
Q: I'm using Access 2002 I have two tabs set up in a form, "Form View" and "Datasheet View". Each tab ... A: Make a query of your table's primary key, save this and then make it the recordsource property of ...
multiple selections from a combo box to populate one field	4/9/2009
Q: i am creating a form to populate a table, the problem i am finding is i have a memo field for ... A: Try this Use the ComboBox AfterUpdate event: I will assume the memo field is called txtMemo ...
TIME ZONE	4/8/2009
Q: Could you please help me on the code that can be used to set the Database to a particular time zone ... A: The native date format is US MM/DD/YYYY, but the native time format is whatever the user's PC is ...
Split the table	4/5/2009
Q: I want to creat a table which contain about 600 field, in access cannot do thin one table, can ... A: Ensure you have a primary key, then you can build as many extension tables as necessary using the ...
Search for customer's last name - VBA	4/4/2009
Q: I am new to VBA with access, but I am learning. I am trying to find a way to search a customer's ... A: I would change the following block of code: XXXXXXXXXXXXXXXXXXXXxx 'create the rsPoint recordset ...
Access 2000 combo box source from another database	4/3/2009
Q: I have put my data and it produces a report exactly how I want it... so far so good! I now want to ... A: Just link the CivilRepairs tables required into the BOM database. From the Tables tab in the BOM ...
update error	4/3/2009
Q: i have this update code behind my button: sSql = (" UPDATE tblInterviewer " & _ " SET ... A: Enrico, hi, It may be in the last line of the SQL. Is the InterviewerID a number ? If so the sSQL ...
Command Button not Opening a Form	4/2/2009
Q: I have play around with access a few times creating a few databases. I run into a problem with on i ... A: Hummm - very confusing :-0 Are sure you are not playing with sub-forms ? - the criteria: looks like ...
Adding date when checking a checkbox	4/2/2009
Q: In question http://en.allexperts.com/q/Using-MS-Access-1440/2008/1/Date-update.htm#b in 2008 you ... A: If you want to update a date field in an associated table, you can use this approach, but you'll ...
Multiple	4/1/2009
Q: Geoff, You may remember I asked you a question last week, to do with Placing, Position and Honours ... A: John hi, 1 - to cover the sometimes 2 sometimes 1 scenario: a work around would be to have some ...
Multiple "If" statements	4/1/2009
Q: Geoff, You may remember I asked you a question last week, to do with Placing, Position and Honours ... A: John hi, You need the 4th parameter "BooPos3" to make the logic work When you call the function ...
Currency converted to words in access	3/31/2009
Q: I have 2 modules that convert currency into text. One is for dollars and one is for pesos. I have a ... A: In a block of code I would use something like.. .. Dim sCostText As String If [ClientCurrency] = ...
Access 2003 "If and" argument	3/25/2009
Q: I have a report that shows up to four results in a team competition, the number of results depending ... A: This is a real tease.... I think you need to pass another parameter, which is the HO status of ...
pl sql , retriving information from multiple table	3/24/2009
Q: in using pl sql for oracle 10g, how do i extract information from multiple fiel from multiple table A: Assume you have tables TAB1, TAB2 & TAB3 With Primary keys as follows AAA / BBB / CCC and Foreign ...
forms	3/23/2009
Q: I have been given the task of assigning specific codes to employees in my company. I want to use MS ... A: John, hi, This is a very broad scope for a question... Yes a solution could be built in MS Access ...
using the command button wizard	3/20/2009
Q: I created a form call New Payroll Managers, I am using the command button to open another form and ... A: Ann, hi, Please forward the table specification and some sample data and also how you want the ...
using the command button wizard	3/20/2009
Q: I created a form call New Payroll Managers, I am using the command button to open another form and ... A: This is a feature of MS Access - :-(( There were no records returned from the filter criteria ...
Sum Query	3/19/2009
Q: i have these datas on singel table: A1 = 50 A2 = 10 A3 = 30 B1 = 20 B2 = 30 C1 = 20 C2 = 20 and i ... A: I will assume your table (tblMyTable) has 2 column, "Code" and "Value". Use a query based on the ...
Var Percent	3/18/2009
Q: Is there a way to calculate this one records total divided by the prev records total. Not the total ... A: Provided there is a sequence column in your table, yes So lets say your table (tblMyTab) has 2 ...
Hyper-Link	3/16/2009
Q: If I want to sent and email using VBA with NO OBJECT what code do I put in to create a link as the ... A: Tyson, Hi, I don't understand your requirement of "with NO OBJECT" - in sending email using VBA you ...
Command button to exchange data	3/12/2009
Q: Tanks in advance for any help you can give me. I'm not even sure this is possible. I have a database ... A: Yes you can do this, but I need to know more about the structure of your data and the forms Do the ...
Form query	3/12/2009
Q: I've got a form which is based on a query(Query1). On this form I need three more text boxes to ... A: Tom hi, If you have data that is structured in a hierachical mode, set up queries for each level, ...
How to get report headers to repeat/show every row in report	3/11/2009
Q: I have a report that are created in column format (with across then down column layout). I want to ... A: I'm not sure of your report structure, so you'll have to work out the details, but you can code the ...
Comparing values in different tables	3/10/2009
Q: I have a form based on a table, and wish to be able to place a 'search button' on this form, that ... A: FYI - This is one of the most complicated bits of functionality to develop. I would first of all ...
Access question	3/9/2009
Q: I am new to access and Windows scripting. I have an existing access program that, on click of a ... A: Set up a shortcut on Access, then edit the 'target' box to include the access application, followed ...
Selecting a field in a Combo Box in a Form	3/7/2009
Q: Once again ill restate make my question clearer Im using Microsoft access. Currently when i try to ... A: Cory hi, Try something like this:- Snippet under the button on Form A Private Sub ...
help	3/7/2009
Q: "I am having a program wrote. In this program there are many dropdown menus. They have created a ... A: Steve, Hi, Of course its possible... its just amatter of making sure that the relevant data is ...
Report Grouping & Sorting	3/6/2009
Q: I am self taught on Access, so have patchy knowledge. I have created a database for quotations and ... A: The easiest way is to add a column to your Group table called "Sequence" as datatype 'LONG'. Now ...
Not in List Code	3/5/2009
Q: You helped me on part of my database and it worked thanks. It's the last part that I am trying to ... A: try removing the lines Response = xxx ie. ... MsgBox "New Item Added", vbInformation, ...
Not in List Code	3/4/2009
Q: You helped me on part of my database and it worked thanks. It's the last part that I am trying to ... A: I think you should test for a Null value at the IF.. stage.. Answer = MsgBox("Add Item to List?", ...
Import Query to Excel	3/3/2009
Q: The query is statistical info for 4 groups of employee "Crafts": Apprentice Electrician, ... A: The way I have handled this previously is to use a variable to retain the final row number, so lets ...
VBA - Coding	3/3/2009
Q: i have a problem with closing a form , as the original button was to open one form. The codes look ... A: Cory, hi, You could help by sending in your code, and perhaps a bit more detail on the error ...
count	3/2/2009
Q: if i have a table with fields lastname and address, how do i get the total count of lastnames in a ... A: Use the following query template: SELECT [City], Count(*) As NumberOfNames FROM tblmyAddresTable ...
inserting into linked table	2/25/2009
Q: I have a linked table which is accessed by VB from frontend. My problem is, VB can't insert into it ... A: Lawrence hi, I don't have direct experience using VB, but I have had a similar type of issue whilst ...
looping through all the year	2/24/2009
Q: I really got struck cuz I want to loop through all the dates from (01 to last date of the month) ... A: Just change the date range in the WHERE predicate. If you want to make it interactive, you can ...
VBA - Coding	2/23/2009
Q: i am in need of help in microsoft access Ok lets say i have discounts for products in the store ... A: I am going to answer what I think is the question !!!! - I'm only a simple guy... I assume you want ...
I need help regarding looping through all the dates of every month	2/21/2009
Q: I really got struck cuz I want to loop through all the dates from (01 to last date of the month) ... A: Why not write a query ??? SELECT [MyDate], Count(*) NumberOfRecords FROM ... GROUP BY [MyDate] ...
combining data in access.	2/20/2009
Q: I have an Access 2003 DB. I recently implemented record level loging using vba tips for AllenBrowns ... A: Don hi, To help, I need to have details of the table specifications and the queries that currently ...
Multiple Values and Reports	2/20/2009
Q: It's me again. You were so helpful last time that I thought I would pitch one more question your ... A: Multivalue columns is contra the doctrine of 3rd Normal Form (ie. Normalised data) so for me, the ...
MSAccess Security	2/19/2009
Q: I just finished developing a little application in MSAccess for 4 users to inquire data and update ... A: Janise, Hi, You can implement Access security, which is not for the faint-hearted - see the ...
Access search form	2/19/2009
Q: I have a small database in access in which I have created a search form using the following which I ... A: Ken, Bespoke search forms are about the most complicated functionality to develop, so if you are ...
Code for Opening PDF in Access	2/18/2009
Q: I currently have a button set that will open a word document. The code I am using for this ... A: Kami, hi, 1. Try locating the PDF file in the D:\Prog... folder and try running again... (it may be ...
Code for Opening PDF in Access	2/18/2009
Q: I currently have a button set that will open a word document. The code I am using for this ... A: Sub RunPDFWithExe() MyPath = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe" MyFile = ...
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: You need to review the design of your database to bring it into "3rd Normal Form" - this process is ...
VB code to load specific file from different locations	2/16/2009
Q: Hope you can help with this. On a daily basis I import a file (whose name never changes) into my ... A: Yes - use and If - Else construct, as per Dim sPath As String If Weekday(Date()) = 2 Then ' ...
Generating ID Numbers Through a Form	2/15/2009
Q: I am using Microsoft Access 2003 to compile a list of programs that my office sponsors, of which ... A: Create a table to hold the last index numbers, viz tblIndexNumbers =============== Level1 - numeric ...
access 2003 message box	2/14/2009
Q: I set up a text box on my form to show me the difference between due date and today date(aprox 500 ... A: 1. rename the macro to mcrOpenTop5 (say) 2. On your main form, in design mode, insert a new command ...
Database Design	2/13/2009
Q: I've designed a database to store results from a survey/questionnaire and provide results. I have ... A: The easiest way is to set up a default value in the table supporting the questionnaire responses, so ...
Access 2007 & Query Wizard	2/12/2009
Q: I work on Access 2007 and I noticed that through the "Simple Query Wizard" you can generate the ... A: George, hi, I rarely use the query wizard, so I am not the best person to ask: I do work mainly in ...
Replicating field	1/28/2009
Q: I have two tables with a field called ID. ID is the primary key for both tables. When I enter a ... A: Maria, Hi, Access does not support triggers so as such this is not possible. However, you could ...
cascading combo in access	1/27/2009
Q: I need to create a cascading combo for two bound control. The first bound control called fType is is ... A: I take it you have downloaded the cascading Combo demo application ... Also, have a look at the ...
Access 2007 Search	1/27/2009
Q: I know how to apply criteria to a single field, or apply multiple criteria to multiple fields, but ... A: John, hi There are probably at least 2 issues here: 1. Table design 2. Query design You don't ...
Import Excel data with varying names	1/26/2009
Q: I have set up a macro to import data from an excel spreadsheet by clicking a button. This appends ... A: Phil, hi There is much not defined which might impact the best solution!!! However, I would write ...
survey type system	1/26/2009
Q: i'm trying to create a simple survey system. for example, i have 5 questions with an agree, ... A: Enrico, hi As ever, providing an SQL solution depends totally on the table structure and the way ...
inserting a combobox	1/23/2009
Q: how do i insert a combobox field? i tried to do it same as inserting a textbox (e.g. "INSERT INTO ... A: Enrico, hi, It depends on the row source structure behind the combo, and also the structure of the ...
insert query error	1/23/2009
Q: i put these insert query on the event of my button: INSERT INTO tblHospital(Hospital, Address, ... A: Enrico, hi, See my previous answer, plus... Check your datatypes: All text columns need to be ...
Drop Existing Tables	1/22/2009
Q: Do you know how can I check for a linked table (always the same names) to see if they exist, and if ... A: The procedure below is from an application I developed. There is a table (tblTableLoad) that lists ...
updating a table	1/21/2009
Q: i have created a database for a doctors surgery. it has two interfaces,one for a receptionist and ... A: From your general description I can only give an indicative answer, anyway, here goes: I will ...
Preselecting elements in a Listbox	1/20/2009
Q: I have Three Tables Tbl_Names, Tbl_Hobbies, Tbl_NamesHobbies. Tbl_Names:ID,Firstname,Surname,Job ... A: My preferred way to handle this sort of scenario is the traditional Microsoft way, ie a modal form ...
Creating a 'Payment Plan' Report Using Access	1/19/2009
Q: and thanks for your help. I have a database with a list of charges that customers have accrued over ... A: What is MonthlyPayment, and what does it do? The MonthlyBalance would be the amount the customer ...
Creating a 'Payment Plan' Report Using Access	1/18/2009
Q: and thanks for your help. I have a database with a list of charges that customers have accrued over ... A: There may be a way of using nested queries to do just the report, but I can't come up with any ideas ...
MS Access lowest cost supplier problem.	1/17/2009
Q: I am a small retailer trying to work out how to identify the lowst cost of products that come from ... A: I understand your problem, but I can't interpret your data !!! In Access, you'll need several ...
Multi varriable find replace	1/16/2009
Q: I am currently working with a large database and i wanted to know if there is a possible way to do a ... A: I have a table "tblStudentMarks" and this update query achieved what you are in principle are trying ...
Generating a query based on data entered on an Unbound form and using check boxes	1/16/2009
Q: I'm trying to create an "interface" which allows the user to select which fields to include in a ... A: Probably the easiest way of managing it is based on an SQL statement as follows SELECT .... FROM ...
Simplifying Database for Survey Responses	1/14/2009
Q: I am creating a database to store data collected through a survey form. The survey consists of 9 ... A: You should have one table set up with all the questions: insert another column [Section] and use ...
MS Access City/County Table	1/12/2009
Q: I have seen your article to Leon in 2006 regarding City/County Table. I like how easy you explain ... A: I'm a little unsure of your table/form configuration, however, you may base a form on a table, a ...
network database	1/12/2009
Q: gud pm. is it possible for my system to be used by other person using a network? will it be ok if ... A: Basically YES ! As a generic rule of thumb, I would say Access is ok for up to 10+/- concurrent ...
Setfocus	1/10/2009
Q: can you please answer this: I have a form with 3 textboxes - Firstname, Lastname and Number. Then I ... A: Please send in the complete code behind the combo box. Do you select the number from the combo ? ...
MS Access City/County Table	1/9/2009
Q: I have seen your article to Leon in 2006 regarding City/County Table. I like how easy you explain ... A: David Hi I built a demo application called Cascading combos, which links Cities and Countries. Have ...
Sending filtered results via Outlook from Access	1/8/2009
Q: Geoff: This question was asked in 2005 and you helped the user privately, but I'd love an answer as ... A: Sheila, Sorry I don't have the email, but here's some VBA code that mails a load of recipients... ...
query	1/7/2009
Q: how to retrieve the records if particular field is empty i have one table which have cusid, invoice, ... A: Yea, not easy.... There are basically 2 options - Null data, and blank data To test and ...
MS Access sql query to count duplicate entries	1/7/2009
Q: I have written an MS Access query that will count the duplicate entries of a user. I am new to sql ... A: Could you try appending an ORDER BY phrase to the query as follows: SELECT User, text1, text2, ...
sorting and filtering	1/6/2009
Q: This is one of number columns in Access: Some with 4 digits, some with 5 and some with 6. How to ... A: Make a query on your table showing the data in the "unconditioned" format. Now go into design mode ...
sorting and filtering	1/5/2009
Q: This is one of number columns in Access: Some with 4 digits, some with 5 and some with 6. How to ... A: Probable the easiest way is to pack any empty LH character positions with "0" The expression below ...
MS Access 2003 SQL code for setting up a query	1/5/2009
Q: I'm working on MS Access 2003. I need to know what SQL code to use in order to write a query that ... A: All you need is a conversion function - all are well described in onboard help. However, if you ...
Excel duplicate finder	1/2/2009
Q: I want free software for checking duplicate words in Excel (Deduplication check Software). I have ... A: A simple query along the lines of the following will dow the job: SELECT [Name], [Address], ...
VBA Question	12/16/2008
Q: I can create a form to enter data into one table; however, I want the data to be inserted into ... A: By running some SQL you can INSERT records based in one table into another table, however, as you ...
Carry out a search using keywords	12/15/2008
Q: I am currently working on a new project, where as a business we have to quote on baskets of products ... A: As ever, there are many ways of skinning this cat (so to speak).. One way, which is fairly ...
Importing and normalizing data	12/14/2008
Q: I am importing part numbers from older databases and some excel spread sheets and the formats are ... A: BTW - I have changed this to Public, as I believe it will be of general interest to all. The ...
Recipe Database	12/14/2008
Q: i am willing to create a Recipe database but im having a trouble. I want to start form Raw ... A: Option 1 ======== Add another treatment 7, Chop Meat Add another tblMaterialTreatment record 10, 1, ...
Recipe Database	12/13/2008
Q: i am willing to create a Recipe database but im having a trouble. I want to start form Raw ... A: You'll need 2 tables to hold all the Raw Materials and all the different treatments eg tblMaterial ...
Code/Query	12/12/2008
Q: Have query where I get the result like (except "-------------------",put it to separate the two ... A: I built a procedure as below which reads from a table of Student/Subject and outputs in a text file ...
about access	12/11/2008
Q: I am using this access 2007 and i have a big table i am working with what i am trying to do is that ... A: This method works very well and is robust. All you need to do is apply the filter expression to your ...
about access	12/10/2008
Q: I am using this access 2007 and i have a big table i am working with what i am trying to do is that ... A: You would be better building a query based on your main table to provide the records for the look-up ...
Is there a way for ACCESS to recognize the box as ZERO if nothing is entered.	12/9/2008
Q: First of all I have read so much of your responses from old e-mail and you have given great advises. ... A: Open the form in design view, right click & open the properties dialog. Select the ALL tab, select ...
What is wrong with my expression?	12/9/2008
Q: Can you help me with the following expression - I'm trying to do the following: TABLE 1: Prices ... A: Try the following query: SELECT A.Abbrev, A.Desc, B.Price As Retail Price FROM TBL_QuoteWindows_ALI ...
ComboBox image column display (Access 2007)	12/8/2008
Q: Here is the scenario. I have developed a Sales Order Processing DB. In the Order Form there is a ... A: Bill, I don't think you can display an image inside a combo box. If you want to display the image ...
creating an ordering system and need to add multiple text boxes which link to the table	12/8/2008
Q: I am trying to create an ordering system which, on the main form, you can add more than one order at ... A: Julie, Hi, When you say "add more than one order at a time" do you mean "Order Lines" ? If you ...
MS Access Table Of Contents KB	12/7/2008
Q: Greetings, Using MS Access 2003. I am trying to implement: How to Create a Table of Contents or ... A: John, hi, Could you confirm a few things please: What is the longest report Header ? What is the ...
append query	12/6/2008
Q: I've got a single form wich is bound to query to populate it's fields. when the user makes changes ... A: Tom, hi, Build a new 'add record' only form - change the command button to open this form, the form ...
Simple addition	12/6/2008
Q: I am simply trying to display the sum of 2 texts boxes in another. When I use =[Text241]+[Text248] ... A: Tony, hi You need to remember about data types. The + operator adds numbers and concatenates text ...
Is there a way for ACCESS to recognize the box as ZERO if nothing is entered.	12/5/2008
Q: First of all I have read so much of your responses from old e-mail and you have given great advises. ... A: 1// There is an error with your formula: AFTERUPDATE EVENT and entered Me.[Text Box3] = ...
Importing Negatives from txt	12/5/2008
Q: I am trying to import a fixed width txt file and one of the columns contains numeric values - some ... A: Sue, hi, I have just constructed a model and it works fine. What steps are you running ? My Steps ...
append query	12/4/2008
Q: I've got a single form wich is bound to query to populate it's fields. when the user makes changes ... A: Open the form in design mode Right Click, open Properties. With the mouse, select the Top Left box ...
Building Recordsource for Loop VBA	12/4/2008
Q: I have some code which uses a query to build a recordsource to add some notes back into a table. ... A: I notice there are no object declarations, try Sub SendMailshot() Dim db as DAO.database, rs as ...
Use form button to change the value of a control	12/3/2008
Q: I have created a form to create a debit request. Users might open a request and then hold it for a ... A: Probably the best bet would be to set up a new button, and get my proposed code working ...
Use form button to change the value of a control	12/3/2008
Q: I have created a form to create a debit request. Users might open a request and then hold it for a ... A: Use the button's onclick event to set the value you want Let's say the text box on the form holding ...
Invoice Numbering	12/2/2008
Q: I want to create an invoice number field to portray the following format: FQO08/000001 "FQO" is ... A: I have built a function, that receives the incremental number and returns the formatted invoice ...
Parse multiple criteria to query definition parameter	12/1/2008
Q: Geoff, I'm executing a query definition from an Excel application using DAO. The query is faily ... A: If the query genuinely boils down to Select * from Table1 where FieldA IN ([ParameterValues]) as ...
VBA CODE HELP on boolean	11/29/2008
Q: eg. i have two booleans : irregular and popup when i search ONLY one of the terms products that has ... A: I think you need to wrap the If.. End If construct in a test for a valid selection for [popup] IE ...
Help with microsoft access	11/27/2008
Q: I am creating a booking system for a project and I have got a Booking table and a journey table. The ... A: Michael, hi, I'm afraid the logic on this doesn't appear to be all there. Try abstracting (moving ...
Updating parts of strings	11/25/2008
Q: I have to update an old database, it contains an "ID" field which is repeated throughout the db. At ... A: Try the following function.... Best regards Geoff :-) Public Function ParseAndReplace2(sItem As ...
Bespoke or Specialist?	11/25/2008
Q: I was wondering if you wouldnt mind telling what the disadvantages and advantages are of a ... A: By Specialist Software System (Database), I assume you mean a package bought off the shelf or via ...
Lookup List Management	11/14/2008
Q: I am working on a database to track college basketball standings for all divisions, NCAA & NAIA. I ... A: I have designed a similar DB for a cricket league in England, so I'll throw in my t'penny worth: ...
query	11/13/2008
Q: There are two tables and a form, when the user types in the number into the text box and choose the ... A: Zsolt hi, This is a "design" feature of Access ! No records = blank form. The only solution is to ...
How can i fetch data relative to data filled in the current form	11/13/2008
Q: I have two tables in my DB, one is main table and another one is lookup table. I have two fields in ... A: In the [Name] text box Control Source property enter an expression based on the following - you will ...
Multiple Entries in an Access table field	11/12/2008
Q: How can I allow several entries in an Access table field? I have a field called 'associated ... A: I would not advise this in a database application - of course if you want to manually type in ...
Obtaining next available Member ID	11/12/2008
Q: I am trying to determine how to best set my form up to obtain the next available member number. How ... A: Why don't you use the Autonumber data type for the MemberID - it was made for exactly this ...
Multi Field Search Function in Access	11/11/2008
Q: I'm new at using access, and really hope you can give me some detailed instructions. I have set up ... A: Set the subform based on a query, say qrySF - anything just to get going. Next under the search ...
Multi Field Search Function in Access	11/11/2008
Q: I'm new at using access, and really hope you can give me some detailed instructions. I have set up ... A: Proramming a search form is one of the most complicated tasks for any developer, so if you are new ...
Transfertext -can't export date w/o time component	11/10/2008
Q: I have a table with a date fld to export to CSV. Fld is formatted ShortDate. I have tried format & ... A: Could you manually perform the export of the query, and whilst the wizard is open, click on the ...
Updating parts of strings	11/10/2008
Q: I have to update an old database, it contains an "ID" field which is repeated throughout the db. At ... A: Is the ID (in the string) always preceded by " : " ? If so, you could use this to locate the 5 ...
Columns to Rows	11/9/2008
Q: In an earlier response on how to transpose columns to rows you wrote : "Using VBA - create a target ... A: The INSERT SQL command is for inserting new records to a predefined structure: it will not allow you ...
IIf and DateAdd problem	11/7/2008
Q: I have a form that I have created that has the following fields: Date Last Revised: date field ... A: I would create a Frame object with 2 radio buttons (with the wizard). Lets say the Frame object is ...
Transfertext -can't export date w/o time component	11/6/2008
Q: I have a table with a date fld to export to CSV. Fld is formatted ShortDate. I have tried format & ... A: Behind a date/time field is a real number: the LH part provides the date part, the RH part (from the ...
arrays and recordsets	11/6/2008
Q: I have an array ' validcodes' with a few values. i have a recordset with two columns id number and ... A: I have built a table tblArrayValidate, and in the following VBA procedure, I populate an Array ...
Import Object	11/5/2008
Q: I was wondering if it is posible to import a object like a form or module from another database ... A: Sam, Hi, I've found a way - its a bit messy, but it does work: I have to give credit to G Hudson at ...
Input forms and foreign key	11/5/2008
Q: I have two tables where Table1's primary key (RegNum) is a foreign key on Table2. I am now creating ... A: The easiest way is to make Form2 a sub-form inside Form1, with the sub-form container object linked ...
MS ACCESS QUERY GROUP SAME NAME INTO GROUPS >7	11/4/2008
Q: low to mid level with access use it for my hobbie I use a table to check past performances of ... A: if your table is called tblHorses and the column name with the HorseName is called that: the ...
Command Buttons and Subforms	11/4/2008
Q: I have a form, called FormA, with subforms: SubFormB1 and SubFormB2. Here is what I would like to ... A: Just toggle the 'Visible' property of the subform with the command button click event as per... Sub ...
MS Access - OrderDetails table problem	11/2/2008
Q: I am in the middle of creating a stock control system. A problem as strike but i just cant seem to ... A: Elizabeth, hi, You don't provide the code for the function GetListPrice(), however you could use ...
editing listbox	11/1/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: Instead of using List boxes, use 3 sub-forms - each uses the same view of the data, but with ...
editing listbox	10/31/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: I don't fully understand the set up of the form, the way the user works with the form, and what/how ...
Mass Email Shot VBA	10/29/2008
Q: A little while ago you sent me some code to help with me sending out mass mailshots by email. The ... A: I have moved the End With associeted with the objMail, inside the While/Wend construct... ...
Simple Class Choice Query	10/29/2008
Q: Basically I am trying to design a database that when data is submitted from a form which asks ... A: Are you on the right track ? Definitely, just a bit more trekking to do !!! If only more developers ...
Automating start date and end date	10/28/2008
Q: I have a form of which I added two combo boxes, (cboYear and cboMonth) and I also added the two ... A: The active code to deliver that functionality would be:- Public Sub cboMonth_AfterUpdate() Dim ...
Automating start date and end date	10/27/2008
Q: I have a form of which I added two combo boxes, (cboYear and cboMonth) and I also added the two ... A: Use the AfterUpdate event on the two combos, but you will need to test that the value in the other ...
Refresh linked table without opening the source	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. Ref updating Excel - you can do this using VBA as follows: Excel XLS Handling Dim xlObj As ...
query for comparision	10/24/2008
Q: THis is with regard to the solution u had given me Public Function RunReports() ' OBJECTIVE: Using ... A: Thiru, Hi This will be a bespoke solution for your application, which will require a full tech spec ...
query criteria from table	10/23/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: The following lines is the build-up of a single SQL based on the constraints held in tblCrit and to ...
Seperate Date from Program	10/23/2008
Q: I made a program on ms access for followup 6 peoples are using it through network, WHEN I need to ... A: He Sher, Your network strategy is fine. A separate MBD file containing the data tables, then a ...
VBA	10/22/2008
Q: I want to change the row source property of combo box through VBA. However when I use the command ... A: I have a form with a combo "cboFruit", and a button "cmdChangeCode" to change the RowSource ...
query criteria from table	10/22/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: Strategy: 1. Write a function to cycle through the criteria table, and for each criteria, run the ...
query criteria from table	10/21/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: You certainly can, though you will need to decide very clearly how it should work The tables from ...
Updating Tables Using A Command Button	10/21/2008
Q: I've set up a User Form that contains two fields from my primary table - 'ID' and 'End Date'. The ... A: Mark hi, If the End Date field on the form is called [End Date], then the code to place a date in ...
MS Access calculate GPAs in report	10/7/2008
Q: Greetings, We have a student/course registration database normalized to about 3rd level. I need to ... A: My inclination is to have two sub-reports - one for completed terms, in which the average is ...
Database changes	10/7/2008
Q: I completed the advanced ecdl a few months ago and now work has asked me to make changes to our ... A: Bob Hi, It sounds like you should do some design before you start bashing the keyboard: I normally ...
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: Thiru, hi You most definitely can link databases: in the Table tab of the DB dialog, right click, ...
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: Sorry for the delay, I have been away on vacation. Here is some sample code for calling a macro in ...
Email addresses from query to copy / past to mail merge.	10/4/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: Ref the recipient... 'Add TO Recipient With .Recipients.Add("abc@xyz.com") .Type = olTo ...
Email addresses from query to copy / past to mail merge.	10/3/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: Simon, hi, 1. use With .Recipients.Add(rs![Recipient]) .Type = olBCC ' Blind ...
utonum no deletion	10/3/2008
Q: I created an order database on Access 2003. The order number ist an autonum field in a table. It all ... A: Are you sure about this European law? In which directive is it contained ? It would be impossible to ...
Email addresses from query to copy / past to mail merge.	10/2/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: The 'Email All' button should launch a stand alone procedure that generates and sends out the email. ...
Import text file in Access	10/2/2008
Q: I want to import a text file into access. The problem I have is that this file has a new name ... A: The import specification does not store the input file name, but the mapping of input columns to ...
Import text file in Access	10/1/2008
Q: I want to import a text file into access. The problem I have is that this file has a new name ... A: Please see my answer to a similar question... I hope this helps Kind regards Geoff :-) Subject: ...
Update values in one table form another table	9/30/2008
Q: hope you can help. I have a small Access 03 database which records data about student targets and ... A: Adam, Using the Query builder, bring in the two tables, link the FirstName, Surname and DOB fields ...
Update Query	9/30/2008
Q: This is a SQL View of an update query that i created. Could you please help me convert this query to ... A: Here is a subroutine to run the SQL - you could make this the action of a button, by placing the ...
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: Use the 'after update' event of the Start Date textbox, as follows... With the form in design mode, ...
Issue with Ending Inventory using Access	9/24/2008
Q: The question is, How can I make the ending inventory value entered in the form to become the ... A: I am not sure how you have structured your transactional, audit and valuation data, and my answer ...
Multiple Selection Combo - need to select all.	9/23/2008
Q: I have a multiple selection combo on my form and I need a macro or VB code which when a cmd button ... A: Try this code under the command button's click event ... Public Sub MyCommandButton_Click() Dim ...
Deactivate	9/18/2008
Q: When a group of data is no used anymore, but i do not want to delete it, i want to deactivate it, by ... A: The best way to achieve this is to carry an additional Record Status column on the table of say ...
Multiple Count function	9/18/2008
Q: I have a table which includes names, accounts and the transaction date. I want to create a query ... A: Fez, hi, First, build a query that returns the Number of days. I'll call this Qry1 SELECT Name, ...
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: I will assume you have a command button to open the report. The code behind the button needs to be ...
Time limit use for access application	9/16/2008
Q: I have created a ms-access2003 application. Now I want this application to run for 15 days only. ... A: Here is a possible solution... You need a table to hold the date/time the application was first ...
Access	9/15/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: You will need 3 tables: 1. T_Ingredient 2. T_Product 3. T_ProductXIngredient Table 3 is the ...
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: As the user tabs or clicks into say Textbox2, after Textbox1, the following sequence of events ...
adding multiple queries to a form	9/12/2008
Q: My question is a little convoluted, at least I think so anyway. I was having an issue with a query ... A: Use multiple forms: one as the main form, perhaps controlling the primary table/query, then ...
=Date() function	9/11/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: Beth, hi Please check that the name of the textbox on the form is called [Date] If it is something ...
=Date() function	9/10/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: In the expression Me.Date, there must be NO spaces and you should put [] square brackets around Date ...
Textboxes In Report Empty When Printing	9/10/2008
Q: I have tried to get this to work for a while now. I have an Access report that holds several ... A: Angel, hi Sorry, I can't really help here: its Microsoft wizardry at work... My inclination is to ...
Validation in Ms Access	9/9/2008
Q: Sorry Geoff, I have to ask my question again because I didn’t give you enough details because the ... A: Method 1 - assume a new column 'Validated' in Table 1 UPDATE Table1 LEFT JOIN Table2 ON Table1.IDNo ...
=Date() function	9/9/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: Beth, hi Use the form's Current event to test for a blank/null date and only insert the default if ...
Sorting a form	9/8/2008
Q: We have a form based on a view. We want it to sort by our date field. We tried the OrderBy ... A: Look up 'OrderBy' property in Access onboard help: - it does not always apply itself (weird..) - you ...
Data validation using SQL	9/8/2008
Q: Geoff Can you please assist me with the following question in Access. I have two tables with ... A: You could use two approaches: 1. Append a 'Validated' column with a default of 0, then run update ...
Month Countdown	9/4/2008
Q: I need an expression that counts down months starting at 15. And i need it to work off of a date ... A: Mikel, Hi, Use the DateAdd() function, as follows: = DateAdd("m", -1, txtYourDate) to ...
Access Auto Email Addresssing	9/3/2008
Q: I have what appears to be a pretty simple request - but its got the better of me.... I’m trying to ... A: Nick hi, Try using the following code... ...
Exporting Info to Excel	9/3/2008
Q: I've put together a collection of forms that will essentially walk someone through a particular ... A: Brett, hi I don't fully understand your application requirement, but try using the following code ...
Access Database Connection via ODBC	8/25/2008
Q: I have developed numerous Access database projects where the database is either linked to the tables ... A: I am not sure I know the answer, but I am keen to find the reason for this behaviour as one of my ...
Forms and Parameters	8/16/2008
Q: I'm attempting to control the criteria of a query by using a form. The form will have multiple ... A: Brett hi, The only way to do this is using VBA. The strategy is as follows: 1. Build a configured ...
Anthony Warren's impossible report	8/15/2008
Q: Did your solution provide Anthony with a way to produce the report as he wished? It only took him ... A: The query(ies) behind the report are primarily important as these provide the data being reported ...
Access Reports	8/13/2008
Q: I have a label in a report in access that pulls a year from a specific table within my database. ... A: Katie, hi Sounds like a formating issue: you have 2 options. Option 1: With the report in design ...
Copying records in a form	8/13/2008
Q: While viewing my database in form view I am attempting to code a button to enable the user to add a ... A: 1. You need to be sure that the query is an updateable query, or the data is entered directly into ...
the impossible report?	8/12/2008
Q: I need to build a report to show student choices for work experience at our school. employers ... A: Behind the reports you want to build are queries that deliver the data to be published. Here are my ...
Inventory	8/12/2008
Q: It was very helpful. My original question I have an Inventory DB. SupplierTbl – SupplierID, ... A: Irit, hi, With only the tables you have indicated, you will not be able to provide the ...
Picture IDs	8/10/2008
Q: I work at a Prison with 2500 Inmates, I'm trying to create a roster using one table, this table is ... A: As I see it you really need 3 areas in your database:- 1) Interface table to handle updates from the ...
Rounding in MS Access	8/9/2008
Q: Geoff, Thanks for answering my earlier question. May I also ask your help in the following ... A: Nelson, hi, Try the following technique: ?Round((0.0135 - 0.0005),3) where, I assume, the value ...
access information on subform (datasheet view)	8/8/2008
Q: I am using Access 2003 (v.11) with windowsXP Professional, version 2002 with 1.99 GB of RAM. I have ... A: Janet, hi Might I suggest a clean start, very simple approach, then take a look at this and see ...
Parameter	8/8/2008
Q: "Sir, I want to accept parameter in form, for that I have written following code but I am facing a ... A: Try this:- Set RS = New ADODB.Recordset RS.CursorLocation = adUseClient RS.Open "SELECT ...
Updating records without WHERE clause for the remaining records in a table	8/7/2008
Q: I have four columns in a table. In one column say assessmentyear (date type), I want to change the ... A: Nelson, hi, You will need to use a WHERE predicate if you want to 'UPDATE' only a section of the ...
Showing only 1 of each record	8/7/2008
Q: i have a report on in my database taht shows all of the people interested in going to a certain ... A: Depending on the number of columns in your table, you could use:- 'DISTINCT' or 'GROUP BY' ...
Selecting the date	8/1/2008
Q: hey I am making a database for a company that needs to store data about a group of graduates and ... A: Change the SQL from SELECT ... TrainingRecordDate, DatePart("yyyy",TrainingRecordDate) * 100 + ...
Calculating a total	7/31/2008
Q: hey i have a database on access 97 and i need to calculate the total number of hours that someone ... A: Jaffa, hi Did you try setting up a "Group" on the report ? Assuming each individual has a number ...
databbase locking in MS-Access	7/31/2008
Q: How do I prevent Access from locking the database when I am testing a program that involves ... A: Bob, Go into Access Options (Windows Button), and set Row Lock - then try. Next, try opening the ...
Menu on Form	7/30/2008
Q: Sir, Can I make Menu on my form in Access. If possible how? I was using my software till now on ... A: With the form in design view, Menu / View / Toolbars / Customise New / Enter menu name -> an empty ...
disabling design view	7/30/2008
Q: I've read that i can disable the design view by converting the database to ACCDE format. however ... A: Does your MDB comprise both the data and the forms/queries etc ? If so, split the database: data ...
Inventory	7/29/2008
Q: I have an Inventory DB. SupplierTbl – SupplierID, SupplierName CustomerTBL – CustomerID, ... A: You need three additional tables: PartSNTbl --------- PartID - Numeric - long PartSN - Text ...
Keep a record of entires from multiple tables	7/29/2008
Q: I am writing a program which calls an Access database (.mdb) file to get product information from. ... A: Andy, hi, Could I suggest, a more productive way would be to interrogate the tables concerned, when ...
Conversion of Figure Amount in word	7/28/2008
Q: I am using Ms-Access 2003. Question 1:-I have a report in which I want to display my total amount ... A: Ref #2 You could not have entered the Input mask property correctly, OR your datatype for the ...
SQL and VBA	7/28/2008
Q: I'm trying to connect SQL server via VBA in MS Access. I'm trying to get the list of all databases ... A: I find that you will always need some tables in the application to manage the running of the ...
databbase locking in MS-Access	7/27/2008
Q: How do I prevent Access from locking the database when I am testing a program that involves ... A: This is surprisingly difficult to give you a succinct answer as this is a very broad & deep subject ...
Conversion of Figure Amount in word	7/26/2008
Q: I am using Ms-Access 2003. Question 1:-I have a report in which I want to display my total amount ... A: #1 Paste the following functions into a module and then in your query, call the ...
how to manage inventory inward & outward	7/26/2008
Q: i have these table tblDesign :- Design ID Design name , Shade Number , Pcs , meter i have INWARD ... A: You should enhance the database design a little first: Add a table tblTransaction, with the ...
Inserting JPGs into table using VBA	7/25/2008
Q: Is there a way, using VBA to insert a JPG photo file into a table OLE object field? A: Harold, hi, Access will only permit BMP images The recommended way to manage images, is to store ...
selecting dates from 1 year ago	7/25/2008
Q: Hey Geoff - its me again (hopefully for the last time :P) Still on Access 97 >.< in my database i ... A: Easy.. Use the DateDiff() function on the applicable date column. Lets call this column [StartDate] ...
SQL and VBA	7/25/2008
Q: I'm trying to connect SQL server via VBA in MS Access. I'm trying to get the list of all databases ... A: Shital, hi You need to build a 'Pass-through' query in Access that uses ODBC technology. Using ...
Querying on multiple fields to obtain the result on the third field	7/24/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, Hello, The RecordSource property for combo #1 is: SELECT DISTINCT product_name FROM ...
Enter Parameter Value	7/23/2008
Q: I have created a couple of queries for calculations. One calculation is adding then multiplying the ... A: The best approach is to test for a Null or zero divisor before attempting the division, then if all ...
Insert Into Statement	7/23/2008
Q: I am working on my database and I have added code to make a button in my form update my table, but ... A: Chris, hi, You need to check the [ActivitieType] data type: if it is numeric then you need a ...
Insert Into Statement	7/23/2008
Q: I am working on my database and I have added code to make a button in my form update my table, but ... A: Chris, hi, try... dbs.Execute "UPDATE [Contact Infromation] SET [Letter Sent]=-1;" This will up ...
Images	7/22/2008
Q: In this database there is a form for all the graduates and I want to be able to display a picture of ... A: This is a complex area: please refer to the following:- MS Access Help - "Store images in a ...
Querying on multiple fields to obtain the result on the third field	7/22/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, hi Sorry I missed that bit.... Use the AfterUpdate event of the 2nd combo to set the textbox ...
Images	7/21/2008
Q: In this database there is a form for all the graduates and I want to be able to display a picture of ... A: Sam, hi, Please describe exactly the problem, including the object types you are using to display ...
Querying on multiple fields to obtain the result on the third field	7/21/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, hi Ideally in your data base you should normalise the data, so the table above should comprise ...
database	7/20/2008
Q: I want to develope database for my metal trading business, can you pls help me about the tables, and ... A: Hanif, hi, Your question is very broad ranging and outside the scope of AllExperts.com which is ...
MS Access --> SQL Server	7/19/2008
Q: I currently support a front end MS Access application connecting to SQL Server. I compile a .MDE ... A: Karen hi, Have you tried setting up an ADP (Access Data Project). ADPs are different to the config ...
Access2000 data types	7/18/2008
Q: SIMPLE QUESTION! ( a bit embarassing) How do I format my field to display th ecorrect ... A: You have two options: #1 - Enter the fraction as a real number, ie: 0.095, or, #2 - View the data ...
Maintaining incremental row count	7/18/2008
Q: I have a multi-table, multi-form database that has a "control referencing a control on another form" ... A: Cindy, Hi It sounds like your table structure is not correctly set up for database operations. ...
Selecting the date	7/17/2008
Q: hey I am making a database for a company that needs to store data about a group of graduates and ... A: In your query, create a new column, with a field name of: ReportMonth: ...
Limiting a table to a set number of individual records	7/10/2008
Q: I am an IT Teacher primarily but this is my hobby, that I hope to make pay - moved from the UK to ... A: I think the way I would approach this would be via a time limit rather than number of records, and ...
asp update	7/10/2008
Q: I can't get the update statement to work, can u please help thanks. <% sSQL = "SELECT * FROM Detail ... A: Missing spaces.... sSQL = "Update Detail set" sSQL = sSQL & " Equipment= '" & ...
Update Form	7/9/2008
Q: I have a form to add data into a table. I also have a form to update this table. I need to make ... A: John, Why not add a 'Search' button to your form. Use the Form wizard to configure this for you. ...
comboBox form control	7/7/2008
Q: Geoff, I currently have a form that has a tab in it and (obviously) the tabs have subforms in them. ... A: Jasson, Absolutely - you need to edit the following to suit your application For all of these, ...
Update Queries	7/7/2008
Q: I am using a form that shows the user what the current values in a table are i then have a duplicate ... A: In the code behind the 'Save' button you need to build up the SQL step by step based on the values ...
comboBox form control	7/3/2008
Q: Geoff, I currently have a form that has a tab in it and (obviously) the tabs have subforms in them. ... A: Jasson, hi, I haven't worked a sample as such, but this would be my approach... Main form called ...
computing total	7/2/2008
Q: I want to add a 5% fuel charge on invoices. What formula do i use to compute this and add to ... A: Create a query based on the normal table or query that you'd use for your invoice, except for the ...
Logic vba	7/2/2008
Q: Ive been using the following code on button click to open an letter and run a query if ok is pressed ... A: Try this... Kind regards Geoff Private Sub cmd_Mailmerge_Missing_Info_Click() If MsgBox("Do you ...
Save button	6/25/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, You could have one modal form serving both the Voucher and the transaction, or one for ...
Save button	6/24/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, I think my strategy would be to make the main form/subform as read only. If the user ...
Save button	6/24/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, 1. You are opening Pandora's box as soon as you want to control human behaviour. 2. You ...
printing labels from form view	6/23/2008
Q: i have created a DBMS for stock levels at work and would like some of the information on one of the ... A: Chris, hi 1. Create a query to return the fields needed in the report. 2. Turn this into an ...
Access Query	6/6/2008
Q: I have made a simple crosstab query using columns Description, Expr1: Format([Date],"mmm"), ... A: Paul, hi Sorry to be late, but been v.busy... You can control the vertical sequence but to my ...
Microsoft Access 2000/2003	6/6/2008
Q: I hope you could help me in my problem. I am creating a program in Microsoft Access 2000. Part of ... A: The Combo (assumed to have a single column of [ITEMS]) needs an AfterUpdate event to populate the ...
Update a text field with the result of a query	6/5/2008
Q: I'm trying to get field to update depending on what is selected from a drop down list - namely the ... A: Cate, If you want to look up a value from a table, given a value in a Combo, use teh DLookUp() ...
Query question	6/4/2008
Q: I am trying to create a report to total the number of hours someone is working, AND total number of ... A: Jeff, hi, When you capture the employee times, you should do this using the Now() function, ...
Update a text field with the result of a query	6/4/2008
Q: I'm trying to get field to update depending on what is selected from a drop down list - namely the ... A: I don't understand what the top query does and how it is used, however, I think your after update ...
Imported information doubling up	6/2/2008
Q: I am saving information from my hotels property management system to excel. I am then importing it ... A: You can do a number of things: 1. Save your info in Access - ditch Excel, or export data out to ...
and statement in sql query	5/31/2008
Q: I am selecting a table field with 2 variables. code below. I am recieving a type mismatch error. ... A: The problem would appear to be in the datatypes between: MASTER1.TECH_NUMBER and Me.Technumber and ...
Query question	5/30/2008
Q: I am trying to create a report to total the number of hours someone is working, AND total number of ... A: Try using the Date functions DateDiff() to return the respective times involved, then the report ...
Setting report Page setup via VB in Access2003	5/30/2008
Q: I would like to be able to update the the page setting for a report via VB in Access2003. Update ... A: Michael, hi, 1) Could I refer you to a paper on MSDN ...
Using Combo Boxes to view a list of reports	5/29/2008
Q: Geoff, I'm trying to have a dropdown list (maybe using combo boxes) that displays a list of already ... A: Adam, Hi, Set up a table of report names, and use this as the basis of the combo. I assume the ...
Access Query	5/26/2008
Q: I've created an access.mdb with 5 tables, one of the table contain various usernames, I want to be ... A: If you are using the Access Query tool, by entering an expression as below, under the 'Recipient' ...
Using NotInList with multi-column tables	5/23/2008
Q: I am using MS Access 2003 on WindowsXP, and have been trying to adapt your NotInList code to use ... A: Try using the function: MyValue = InputBox(Message, Title, Default) to get a value on the fly (see ...
access	5/23/2008
Q: 1.how do i get an access database to overcome the limitation of 2 gb? 2.can u create forms in vb ... A: 1. It is usually data that results in hitting the 2GB limit. The way around this is to split out the ...
MS ACCESS 2007- sum two or more fields in a query	5/22/2008
Q: I need help in two things: 1. How can I sum the results of two or more fields in a query. I tried ... A: Elaine, If [field1] etc are calculated 'fields' then it will not work, if they are columns ...
24Hr sum function	5/22/2008
Q: I work in a call centre and deal with MI reporting. Im currently building a form that displays the ... A: Dan, The way to handle this in Access is as follows: Use the DateAdd() or DateDiff() functions to ...
Add data to combo box with table	5/21/2008
Q: Good day Geoff, I have a drop down combo box (Combo11) on a form (FrmLocate). It was created using ... A: Janet Hi, I have built a prototype and got it to work: My combo, called cboFruit, is based on a ...
Add data to combo box with table	5/15/2008
Q: Good day Geoff, I have a drop down combo box (Combo11) on a form (FrmLocate). It was created using ... A: try this... Private Sub Combo11_NotInList(NewData As String, Response As Integer) Dim ctl As ...
Moving from MS Access to ASP.NET & SQL Server Express	5/14/2008
Q: I have a fairly good grasp of MS Access database design with an intermediate level of VBA ... A: Try this URL for learning about SQL Server: http://www.microsoft.com/sql/techinfo/default.mspx ...
Open form by sub-form information	5/14/2008
Q: I'm working on a Tool information database which shouldn't be too hard to carry out. I have two ... A: Exactly as you have done for Tools-Product, but the other way round. Set your main form based on ...
SQL Query	5/3/2008
Q: Following is the table Div district amount 1 mandla 4000 1 Katni 2000 1 Jabalpur1000 ... A: Musharraf hi, Your result set does not tie up with the group by / order by you have stated This ...
Making a checkbox change state	5/2/2008
Q: I have a form with checkboxes associated with fields in a report. I want to have the user check ... A: Try this.. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If chkIndividual = ...
Access 2003	5/1/2008
Q: I am a newbie & have invested in Access 4 Dummies & numerous other books from the library (so doing ... A: Amanda, hi You will need to get into programming with VBA Check out the following tutorials ...
field updates	5/1/2008
Q: if additional new fields are added inbetween existing fields. how do you get access to tab through a ... A: Anthony hi, Open the form in design mode, select a textbox (field), right mouse button, select ...
CheckBox ComboBox Extending the ComboBox Class and Its Items	4/29/2008
Q: I want to create a combo box with multiple pull down check boxes and when the user clicks on one or ... A: Try using a subform approach. Build a form as 'Continuous Forms' with the check boxes etc linked to ...
name a query and loop	4/29/2008
Q: I have a query that lists several "plans". I am trying to run another query off of that one and ... A: I will try the high level approach, as I think you are fairly close to the solution. If you can't ...
Data Access Page editable query	4/28/2008
Q: I have a main webpage to enter data. Now I have created a query which has a parameter to find a ... A: Janell, hi, There is a simple rule... If you want to edit/add/delete data you need to access the ...
Running a function	4/28/2008
Q: Im quite new to VB so im still learning the basics Through extensive browsing of this forum and ... A: Try this: Private Sub cmd_Mailmerge_error_Click() Dim retVar as variant retVar = ...
MS access program accessibility	4/24/2008
Q: I want to make a MS access program for projects management. Plz I want to ask three questions : ... A: Hussein, hi, My recommendation is that you consider using MS Project Enterprise Edition supported ...
To get the username or ip address on load or click event	4/24/2008
Q: I created MS access program using on my office network with 5 computers and I don't want to create a ... A: Use the code below in your client program to pull the UserName and ComputerName. With that you can ...
Not Allowed to Edit - No reason why.	4/23/2008
Q: I am making quite a complex database (well it seems that way anyway) and i have come accross a ... A: The best way is to break out individual tables into sub-forms, nested on the main form with a linked ...
Not Allowed to Edit - No reason why.	4/23/2008
Q: I am making quite a complex database (well it seems that way anyway) and i have come accross a ... A: Chris, hi, Its probably because the query itself is unupdateable. Try opening the query directly, ...
Can't exit loop	4/21/2008
Q: I really appreciated it. I have another problem I am hoping you can help with. I have two ... A: Alan, hi Why are you using recordsets when you can do this easily with a query ???? (I doo need ...
Move Text Data To Next Empty Row In Column	4/20/2008
Q: I am importing a 23 character text string in to my Form text box (from a RFID scanner). I need to ... A: On your form you need a button to save the record: the easiest way is to open the form in design ...
File Transfer Message	4/18/2008
Q: After Access has completed a file transfer can I build in a message to advise that the action has ... A: Just wrap the FT into a macro or VBA procedure, and follow the FT command with a MsgBox() function ...
login form with access rights check	4/17/2008
Q: i have two forms, Form A to login and enter Form B (provided that the logging in credentials are ... A: I'll start 1/2 way down... 'Check value of password in tblEmployees to see if this matches value ...
changing delimiter	4/17/2008
Q: I hope all is well. I've tried several things but have hit a wall figuring this out. Your assistance ... A: I have lifted this off the Net - so if you can get it to work, brill: if not well, I tried.... 'Use ...
MS Access Query Help	4/16/2008
Q: I've designed a database and I need help creating a query for it. In the database, there are dates ... A: Lets say your starting query is as follows: SELECT aa, bb, MyDate FROM MyTable Notes: The table is ...
Question on pages linking to tables...	4/15/2008
Q: This one is simple i believe... I've read over many help topics but can't seem to figure it out. I ... A: You'll need to develop an interactive website using ASP (for instance) on a IIS Web Server that can ...
How to create a form with an insert record function button.	4/14/2008
Q: I need a method that will allow me to insert a blank record in the middle of a table through a ... A: Rami, Hi To inser a new record for data entry, either use the '*' button in the form navigational ...
Field box automatically adding other fields together...	4/14/2008
Q: I am building a db for a quality tracker. There will be say a set of fields, or questions if you ... A: Aaron Hi, Lets assume your 4 text boxes with values are called:- txtValue1 txtValue2 txtValue3 ...
VBA Code	4/6/2008
Q: I cannot quite find the right syntax for this code to append a new record in one table with data ... A: Why are you using recordsets for this basic append rather than SQL ??? Use the Access Query Builder ...
automatic changing date	4/5/2008
Q: I have a database with records in which I change things on a regular base. There are two dates in ... A: For each updateable object, you need an after update procedure along the lines of: Private Sub ...
change filename	4/5/2008
Q: I need to import a delimited file into access. The file name is 3-30.txt.err. I need to get rid of ... A: Alan, hi Use the FileSystemObject: Look this up in onboard help, but here is a snippet base on a ...
Filter Form Button on Switchboard	4/4/2008
Q: I'm still an Access Newbie but I'm managed to create a simple "contacts" database for my job. I'm ... A: Yes. Use the Wizard to generate the code for the button to open up your required form: open this up ...
Access, subform dependent combo box	4/4/2008
Q: I have read your advice regarding a combo box in a subform that is dependent on a combo box in the ... A: 1. Ensure that the sub-form holder object is named as "sfrmPOdetails", as it is this that is used in ...
Exporting to Txt and losing leading zeros	4/3/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: Try the following: = Right("000000000" & CStr(CLng([MyNum])),9) & Left(CStr(CDble([MyNum]) Mod 1) ...
recordset	4/3/2008
Q: I have a table with the foll fields; purchasedate,name(combo box),coupon(tea/lunch),amount. When a ... A: You could try a Dynamic recordset, which allows the MovePrevious action, or continue with Snapshot, ...
Exporting to Txt and losing leading zeros	4/2/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: Tina, hi, #1 - you need to drag it out from the header row. Use the mouse, click one side of a ...
Basic Delete Query	4/1/2008
Q: Sorry to bother you again, your response worked last time, so i figured you'd be the one to ask ... A: Peter, hi, You'll need to run the following queries: the last one needs to be run last (if you want ...
Exporting to Txt and losing leading zeros	4/1/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: You need to explicitly declare the "000000123450000" as a Text/String I am not sure how you are ...
Very Basic Query Question	3/31/2008
Q: I am new to access; I currently have a table within my database that contains a field called ... A: Two ways:- Method #1 There is a function called "Year(YourDate)" which returns the Year based on a ...
Copying a textbox value, adding it to a table	3/30/2008
Q: This is what i am trying to do: I am trying to copy the value in a textbox, possibly the first 10 ... A: Open the Query Builder in Access, in a new query. Right mouse button, select SQL view - a text area ...
Copying a textbox value, adding it to a table	3/29/2008
Q: This is what i am trying to do: I am trying to copy the value in a textbox, possibly the first 10 ... A: Chris, hi All you need to do is to run a query to do this UPDATE. I will assume the following table ...
separate Fname and LName in a field	3/28/2008
Q: I have a field in which the data has first and last name separated by a comma. How do I separate ... A: Let sTmp = "Smith, John" LName = Left(sTmp, InStr(sTmp, ",") - 1) FName = Right(sTmp, Len(sTmp) - ...
Forms	3/27/2008
Q: I have created an inventory program for the not-for-profit company that i do volunteer work for. I ... A: I assume you have two tables anyway (one for the order header and one for the Order Line) Build ...
ADP	3/27/2008
Q: I wonder how I can print data from a field of table in a row like value1,value2,value3..... not in a ... A: First I built a function to return a string comprising all the columns of a particular table. In my ...
Filters	3/26/2008
Q: I have got a products table that holds products for different customers, for example, ProductID 3000 ... A: Peter Hi, You need a look-up to define the relationship between ProductID and ClientID - this data ...
Forms	3/26/2008
Q: I have created an inventory program for the not-for-profit company that i do volunteer work for. I ... A: You could just have a textbox for each line item, into which you type in the account number for ...
Copy multiple records with vba/sql	3/25/2008
Q: Is it possible to copy more records by with a sql string and DoCmd.RunSql in one action? kind ... A: As long as you can define which records you want to 'SELECT', and your tables contain data in a ...
sql count	3/25/2008
Q: I have a form that simulates the generation of random numbers, so that evaluators can evaluate ... A: Simone, hi Your question is only about 60% clear... I can see there is something wrong with your ...
Linking pictures to access 2007	3/24/2008
Q: I am linking pictures to forms using the code: Private Sub Form_Current() Me![Photos].SetFocus If ... A: Peter hi, Try this: Private Sub Form_Current() If Len(Me![Photos]) > 0 Then ...
Beginner access question	3/21/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, Hi If I didn't say it before, I'll say it now - good database design is the foundation of ...
Beginner access question	3/21/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, hi, In the macro you will need two queries - the first one copies the records from the main ...
Beginner access question	3/20/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, hi, The functionality you mention should be located in a form (or other code) and NOT on the ...
Incremental count	3/20/2008
Q: I have an query with different people sort by name and by date. So it looks like this : John ... A: I built a table speced as follows tblNames Name - Text DoB - DateTime using your data, this query ...
Macros and SQL	3/19/2008
Q: This project using MS Access is based around recording attendance of members of a club, who meet on ... A: Andrew, Hi In relational database solutions, we design and build the tables first, then use forms ...
Access Query	3/19/2008
Q: I am currently working on setting up a simple Access database to hold data for a Bridge (the card ... A: Success will depend totally on your database design. SQL - the language that relational databases ...
Current date update	2/16/2008
Q: Geoff- First, this is my first time trying to work with Access. So, I'm an extreme access novice. ... A: For info here's the code: QUOTE Private Sub cbxSurvey_AfterUpdate() If Me.cbxSurvey= -1 Then ...
memeo field ms access byte variable	2/16/2008
Q: good day, sir i just want to ask you something, this has been my question to myself from the past ... A: Overhead space is storage space taken up by the application to manage itself. Access has its own ...
memeo field ms access byte variable	2/15/2008
Q: good day, sir i just want to ask you something, this has been my question to myself from the past ... A: Memo fields expand to the space required. So if you store 'ABC', only space for 'ABC' is consumed ...
query	2/15/2008
Q: I want to select a query which may return quite a few records. If I want to see only the last 5 ... A: What you are asking is for a report, not a query, and to deliver this, I would build a form ...
Text File > MS Access > Macro - All in Macro	2/14/2008
Q: I need to develop a excel macro, in which I would be posting the contents of a few text files to MS ... A: Gaurav Hi I enclose a reference I have that may help... Best regards Geoff :-) QUOTE Set ...
Expressions Builder	2/13/2008
Q: Q1:- I created a custom (global) function that isn't showing up in my expression builder. Any Ideas ... A: Q1 - Sorry no ideas... Q2 - Often it is a question of database design. From what you have ...
Text File > MS Access > Macro - All in Macro	2/13/2008
Q: I need to develop a excel macro, in which I would be posting the contents of a few text files to MS ... A: Gaurav, Hello, Strategy: I would build the procedure to upload from the text files into Access ...
Lookup on MS access	2/12/2008
Q: Im doing a data base on UCAS applications and i want to be able to create a student code. This has ... A: Probably most flexible to have a function where you send the FName and LName and it returns the ...
starting a database	2/12/2008
Q: I need to create a database for a valeting company and although i am okay with Excel, Access causes ... A: Paul, hi, Its all in the design of the database..... Relational databases, require that each type ...
Storing a 2D array into MS Access	2/10/2008
Q: I've this 2D array in Visual Studio 2005, I've created the connection to MS Access and now want to ... A: Why not set up a new table to take the data:- tblMatrix RowID Numeric - LONG ColID Numeric - LONG ...
Create Simple Report in Access2007	2/9/2008
Q: Create Simple Report in Access2007 Purpose: List ORDERS and ITEMS on the same line. ... A: I think the best way is via a function that returns the list as a string, then use this function in ...
percentage query	2/8/2008
Q: I am relatively new to MS Access and what seems to be a simple task is really annoying me because I ... A: Chris, You could use a feature of the Checkbox to your advantage here... Lets say your table PK is ...
MS ACCESS query	2/8/2008
Q: Q1 == have a table with 2 columns. I need a query to copy individual elements of both column into a ... A: Muhammad, hi Q1 When you say Column A has 1,2,3,6,8 - do you mean each record has a single number ...
QUERY	2/7/2008
Q: I have a table Reseller, Owner, and Manufacturer they are identical to the table Business Entity. ... A: Tana, hi I have 2 questions for you:- 1. Why are you copying data from one table to another ? The ...
Merge fields from Access to Word	2/7/2008
Q: We have recently updated our Access database to include new fields, but when I try to merge them ... A: Dave, This is off my area of expertise, but, I imagine you will need to reconfigure the Access ...
Import/Export solution needed	2/6/2008
Q: I revised an MS Access 2002 mdb that contains 541 records in a people table. A user inadvertently ... A: Kae, hi, You will not be able to control the Autonumber column of the master table (541 records). ...
Query	2/6/2008
Q: I am trying to Create a query to total all the entries on a table according to each inividual id. ... A: In query builder, you need to expose the 'aggregate' view - from the menu, select 'View', then ...
combine a table info with form input into to a new table	2/5/2008
Q: i have different(6)tables set up as test questions. i have a form 'template' that i can pull in the ... A: Justin, hi, It is not clear in your description how your data is structured - ie. the database ...
MS Access 2003 Field List	2/4/2008
Q: I created a form with an imbedded subform using the forms wizard. I have since added fields to the ... A: Al, hi Each form has a query behind it which has the 'fields' selected. So the fix is to extend ...
Embedded macros	1/31/2008
Q: I've created a db for a client using Access 2007, but saved it as .mdb format because they have ... A: My only suggetsion is to take the original Access 2007 version, and save it as Access 2003 (menu ...
Multiple search criteria	1/31/2008
Q: I have created a search form where i have placed four fields, namely: "Make", "Ref No", "Begin ... A: The professional way would be to build a filter string, then apply that to the form based on all the ...
Query within Access	1/30/2008
Q: I have a date range, [start_date] and [end_date], which sets up a resources allocation to a project. ... A: Your question is potentially not an insignificant challenge. Some questions: 1. Table RESOURCE does ...
Query button on switchboard	1/30/2008
Q: I need to place a new button on a switchboard to use it to open a form named "SEARCH." Unfortunately ... A: You need to enter some code on the button's "Click" event so that it opens the form. This is the ...
Forms	1/29/2008
Q: I have a table on my db called nationality which is built up of two fields; 1) nation_code 2) ... A: You can rebuild the combo to work with both the nation code as the "bound column" and the Nation ...
Unbound OLE object based on combo box	1/29/2008
Q: I have an Access 2003 db with tables for region, staff, and correspondence. tblRegion has a key and ... A: Try the following: 1. Use your query to power a header(main) form, then use a sub-form based purely ...
Variant multiple rows per record	1/28/2008
Q: I need to find a way to import a file that has multiple rows per record. The number of rows may ... A: Elaine, You will need to write a VBA script to read the text file, parse the contents (applying ...
Access 03 query	1/26/2008
Q: Upon entering payments received in a form, I am trying to make a query that will adjust the current ... A: Bill, To calculate totals on the fly, you can use the DSum() function - look this up in onboard ...
relationship between form and subform	1/25/2008
Q: I have a main form (stud_details) also I have a subform called (pay_det_sub). I want to create a ... A: Jamil Hi, I always recommend building the database using the in built referential integrity system ...
Access 03 query	1/25/2008
Q: Upon entering payments received in a form, I am trying to make a query that will adjust the current ... A: Bill hi, Depends how your system works... Most accounting systems use transactions, and the ...
Forms	1/24/2008
Q: I have two forms, main form is called stu_details and second form (look-up form) called ... A: Use the afterUpdate event of the combo on form #2 the active command is as follows:- ...
store form calculations into table	1/24/2008
Q: Good day, I have a simple but large database to calculate the trade and selling price as well as ... A: Joh hi, You dont need macros or VBA You build an update query and run that. Below is a query in ...
Forms	1/22/2008
Q: Hallo, I have a form (Qual_hist) with a field named Institution1. Also, I have a table with all ... A: Build a Combo Box control, based on the 'school' table. To hide the school_code from the drop-down, ...
Forms	1/21/2008
Q: hallo, I have two forms, from one form I can navigate to the other form. What I want to do is ... A: Jamil, Hi Insert some VBA on the 'Click' event of the form1 button to do the following: 1) Goto a ...
Date update	1/19/2008
Q: Forgive the novice question. I need to have the date inserted into a field on the check of a check ... A: I will assume that the respective controls are called cbxSurvey (Checkbox) and txtSurveyDate ...
counting characters in a query	1/4/2008
Q: Happy or should I say Hopeful New Year!! I have a looped qry that has "~" characters. I need to ... A: Alan, hi, I don't fully understand your code and how it relates to the question... You can use the ...
Generate e-mail from Access	1/3/2008
Q: I have the task of monitoring e-mail traffic. I use Microsoft's LogParser to extract the data ... A: Mike Hi, Below is a procedure for sending email - if you are working with Outlook2000 or later, ...
Form Data	1/3/2008
Q: I have a database which we are trying to access using forms. Basically allowing us to enter the ... A: Probably the easiest solution is to use the 'Binoculars' button from the form viewing toolbar, ...
Sorting records by date entered	12/19/2007
Q: when I enter the records into my Access database, will they sort out by the dates I enter or will ... A: If you have not entered a sequence number/string that defines the order of entry, then there is no ...
photo's linked in report	12/18/2007
Q: I have probably a very simple question, but I cant find the answer. I have a pretty extensive ... A: Emiel, hi, Save the images in a single folder, eg: C:\Photos\.. In your DB table have a column to ...
MS Access Button Function	12/17/2007
Q: I placed one text box and two buttons on a form, When i Click the first button it add the text of ... A: Sher, Hi, In the VBA code, just insert the following statement before the UPDATE.... SQL ...
locations in inventory control	12/16/2007
Q: i want to thank in advance for any help i have these tables tblproduct :- productID,ProductName , ... A: Exactly the same principle, except that you use a recordset to control the stepping through of each ...
locations in inventory control	12/15/2007
Q: i want to thank in advance for any help i have these tables tblproduct :- productID,ProductName , ... A: 1. PO Form ---------- You could have a button on your PO form with the following 'Click' event ...
Performing calculations	12/15/2007
Q: Hey, I have made a table to record my container details. Here it is: ... A: Jaap, Hi, I would tend to leave validation rules aside until some other day - I hardly ever use ...
Access Report	12/14/2007
Q: I have an inventory report that I have summed dollar amount by part number. If the sum is <1000 I ... A: Buid a query that computes the inventory sum (if required) then constrains only those parts where ...
Form Filtering	12/13/2007
Q: It all works well except for when a users selects a combination of filters that have no records. ... A: Try using the DCount() function and pass the filter string to it for determination. As an example, ...
Code modification	12/13/2007
Q: Hallo, I have a main form called StudentDetails1, and a subform called FeeDetails and a field ... A: Jamil, Hi Your method seems a bit 'different': why don't you have a combo box on ...
Re: Date/Time	12/5/2007
Q: Prior to the previous question, the date and time is now populated successfully when a field is ... A: Here is some code to validate a textbox change and restore the previous value if not accepted ...
TAB Order	12/3/2007
Q: A previous reader has the same problem which I tried your subjections and still having problems. I ... A: I have built many applications with nested form configurations, and can say I have never encountered ...
counting loops	12/2/2007
Q: Your expertise is greatly appreciated. I am about to lose my mind. I use the following code to loop ... A: Alan, hi, I am not sure what you are trying to achieve from your code, could you please supply some ...
Sorted by date	12/1/2007
Q: I am building a database in which I entered customer info in order of the dates I served them. Now, ... A: Sy, If its only 50, just retype them... If its 50,000 then use a query like.. UPDATE tblMyTable ...
Tab Order within a subform	11/30/2007
Q: I have a form with a subform embedded. No problem with the between master and child fields etc. ... A: Jill, hi, 2 things to check: 1// Open the form/subform in design mode. Select the form object (top ...
Access Import Specification - Macro	11/27/2007
Q: 1) Delete the old version of a table 2) Import a .txt file, tab delimited. I have created the ... A: Paul, hi, I appreciate your frustration - I have gone through this many times and sometimes you ...
changing control's forecolor using Timer	11/26/2007
Q: Good Morning Geoff, This time my question is: How to change the forecolor of text in a Label using ... A: Sub Form_Timer If Me.Label.Forecolor=0 Then 'Black Me.Label.ForeColor = ...
changing fields into records	11/24/2007
Q: Hi How would I change fields in table1 into records in a new table2? field1 field 2 field3 ... A: Just to confirm the conversion Current Table F1 F2 F3 F4 Abc Fgd Ytwg TWB ...
Problem with MS Access	11/23/2007
Q: I am using Access 2002. I have two tables. In the first table, among other fields there is a field ... A: Open the form in design mode select the listbox right mouse button select properties select the Data ...
help to write VBA code	11/23/2007
Q: i have a form(frm_progress) on which there is a label which says "Opening File. Please wait....". i ... A: Rajiv Hi In the click event of the button on the main form, just wrap the Excel commands with the ...
declaring variable in access having ref to excel pivot item	11/22/2007
Q: pls help me with a code by which i can declare a variable in Access having reference to a pivot ... A: Rajiv, hi, Herewith, code to open Excel and get a value Public sVar As String 'Declared in the ...
Help with Access Query	11/20/2007
Q: I am trying to set up a query that looks for the lowest price from 3 tables these are; Price File ... A: Christopher, Hi I will assume that each table has a column called ProductID a column called Price, ...
Transfer text specifications	11/19/2007
Q: How do i create the transfer text specifications.Also how to import the file manually so that i can ... A: Anshul, hi, Create import specifications by manually linking a template text file. In the Table ...
Last Update Date	11/18/2007
Q: I know there are past posts on this but the code I am using is not working and I have tried various ... A: Steve, I assume there is a textbox control on the form named 'update_date'. The form's ...
MS Access queries	11/18/2007
Q: Sometime ago I asked about deleting duplicate records. I havn't been able to get any success and ... A: Alan, hi, 1) the table needs to have a column or column combination that is unique - these ...
forms subforms and referential integrity	11/17/2007
Q: I made a Main form with subform link to 2 tables that have a relationship 1 to many with referential ... A: You need to create a formal relationship between the two tables. To do this - menu - Tools / ...
Date criteria	11/17/2007
Q: In a query, i want to insert a date criteria for after the 1st January 2000. I've been trying this ... A: Rahmat hi, Assuming the field is a 'Date' datatype, then >#01/01/2000# If you are using the ...
QBE and ROW&COLUMN data table	11/9/2007
Q: First of all i'd like to say thank you for this kind of ASK-ME-ACCESS webpage :) I'm creating a ... A: Wojciech hi, The problem rests in the fact that the data is not in the Third Normal Form. Until the ...
Access query	11/4/2007
Q: I am a basic Access designer and need a basic answer please: I have a table with tasks in it. Each ... A: Try a little smoke & mirrors !!! The function Date() - abbreviated by vba to Date returns the ...
calculated fields	11/3/2007
Q: Ok, i cant save the result from a calculated field into a table, then i have any other option ... A: I will choose the Form method Assuming your form is called frmMain and the seven textboxes called ...
calculated fields again	10/27/2007
Q: Ok, i cant save the result from a calculated field into a table, then i have any other option ... A: If you need to save calculated results, eg. a sale was made on a day at a specific price.. you can ...
msgbox	10/20/2007
Q: i want to add some code to the code below to display a msg box "Please Complete checklist" if ... A: Try the following snippet for a checkbox... If Not Me.Check325 Then MsgBox "Complete the ...
opening pdf with code	10/14/2007
Q: Wondering if you can help me. In Access, Excel and VB6 if I want to open a file I can use the ... A: Eric, Hi, You could make the Acrobat exe path/file a configured variable that is held in a table, ...
MS Access Combo Boxes	10/13/2007
Q: I'm stuck. I have a Main form which has at the top a combo box that reads a table. There are 6 ... A: Nearly there... cboTemplate_AfterUpdate() Select Case Me.CboTemplate.Value Case "Flash ...
Report filtering and referencing images	9/25/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: Lynda, hi, From your submission, I believe you need to base the report from a Query and not the ...
Generate Report Based On Input Criteria	9/24/2007
Q: The HR peeps here at work are having some trouble getting certain reports out of our Access ... A: Jeremy, hi, The basic command for opening a report from a button (say) is as follows: ...
Report filtering and referencing images	9/24/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: Lynda Hi, In your report, you need to place an 'Image' object in the detail section - in the ...
vba search and replace	9/23/2007
Q: Geoff: Would you mind giving me the vba for the following: I have an Access 2002 database which ... A: Rodney, Hi, You don't specify what is the source data for replacement: are the Btrieve tables the ...
Multiple updates in one Access SQL statement	9/23/2007
Q: How can I update a single field in an Access table based on testing two conditions; ie. Update ... A: Jerry, hi, In SQL Server you could use a CASE / WHEN construct inside an UPDATE SQL statement based ...
Year Format in Access	9/22/2007
Q: We in India use April to March as Financial year but the Access Database Sorts from January to ... A: Subhash, hi, It's the same problem world over - you need to set up fiscal period columns in a ...
Report filtering and referencing images	9/22/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: 1. open your form in design view - select the button - right click & select Properties. Select the ...
MS Access Security	9/21/2007
Q: how do I secure the MS Access database so that certain users can only read the information and not ... A: , Security is not an easy 1 line answer - so its all or nothing. If you want the 'All' then ...
Extracting the right price for stocking	9/21/2007
Q: I have a problem on extracting the right prices of a product. Hope you can help. The price of a ... A: Tiago, hi You need to maintain the "AveragePrice" per product, and when you sell product you should ...
Automatically Run MS ACCESS from desktop	9/20/2007
Q: I have developed a small application in MS acccess by which we we have to analyse the data(Tickets ... A: Saurabh, hi Two ways: One, use the auto run macro (on Access load) - Inside Access set up the ...
access query	9/18/2007
Q: "Create ONE query which will:- Ask for and accept a maximum price from the user, with the cue ... A: Ugur, hi Insert the question in square brackets [] in the Query tool on the Constraints row, under ...
importing into access	9/17/2007
Q: i'm trying to import a CSV file into a database but it has some lines that i need to delete before ... A: Rob, hi Here is some VBA code (procedure) to remove 2 lines from the top of a text file (=csv). ...
Printing	9/14/2007
Q: Certain reports that I have created are output to MS-DOS Text format and printed with the help of a ... A: A quick search on the internet revealed the following ActiveX component for sending Escape Codes to ...
doing a query with multiple results from table fields	9/14/2007
Q: How does one get multiple results for counts needed for values in a table? From what I toyed with ... A: Lynn, hi, Set up 29 separate queries for each column based on: SELECT RunID, Count([R1]) As ...
Transfer of tables	9/13/2007
Q: Can you please tell me how to transfer all the tables created in one computer system to another ... A: Sailaja, Just copy the Access mdb file over onto the new computer, then link the tables into the ...
access form search	9/12/2007
Q: In my database, each record can contain up to 10 Identification numbers. I would like to be able to ... A: Dave, hi I am not exactly sure of the structure of your form: I shall assume that the form is bound ...
fields caption	9/11/2007
Q: Can I got the count of fields in a record and its names and captions using VBA Code Thanks for ... A: Have a play with the following subroutine... You will also need a table to provide the field data ...
help counting how many values are within a group	9/11/2007
Q: I am trying to count/find the page count. or more like to find how many pages(N's) are within Y(each ... A: Alex, Access is a database product, not a spreadsheet. There is no such object as a page in ...
Selecting single row from access table	9/10/2007
Q: I have an access table with fields: ID, Name, Surname, Training. I would like to select every single ... A: Charles, hi, Two queries should do what you want... I assume the logic you wish to apply, is if ...
Creating a survey	9/9/2007
Q: I am creating a simple survey using ms access. I have done the foundation, and have the survey ... A: ALim, hi You don't say much abouthow your forms work.... If you have individual forms per ...
timed alert	9/8/2007
Q: I have made a database to record details about patients and their operations. I need to record ... A: You will need to design the complete process to ensure that only 30/120 day follow ups without a ...
Muliple IIF Statements	9/7/2007
Q: Wonder if you can help, I have a MS Access database, And I need a query that performs the ... A: Simon, hi I have built a function based on a SELECT CASE construct - this is far more efficient ...
comparing table entries	9/5/2007
Q: I have made a database to track all details of the cattle and other aspects of running my farm. The ... A: Yes, please convert the Weight column to Numeric - Double Below, two functions: paste these into a ...
comparing table entries	9/5/2007
Q: I have made a database to track all details of the cattle and other aspects of running my farm. The ... A: Les Hi, Access 97 is a fine version and many developer wish Microsoft had just fixed the '97 bugs ...
Updating a Query	9/4/2007
Q: .That one helped me alot. But now, i have another question for you. i have a query thats supposed ... A: Raddie, hi, What I am not sure of is: 1. Are you implying that the column structure is changing ...
Data Overflow	9/4/2007
Q: i am subtracting to variables (defined as byte) it is working well except when the result is ... A: Nagy, hello A byte is a reflection of 'state', not of 'value', and therefore as such cannot be ...
MS Access	9/3/2007
Q: I am using MS Access 2003. I would like to know whether I can write a SQL to show the table ... A: Swarna, hi, No is the short answer. To get the table structure you will need to use the Tables / ...
Referencing in VBA	9/3/2007
Q: Private Sub Command223_Click() DoCmd.GoToRecord , , acNewRec Dim rsRecord As ADODB.Recordset 'this ... A: If you are trying to populate the newly created record with the Milestones UserName, then the 3rd ...
Inventory stock tracking system(follow up)	9/2/2007
Q: thnx for the reply. Erm...sorry if this question sound stupid... can you please guide me on how to ... A: Open both tables and take the record count of each. Of the tables tblsales + tblstockreceive - ...
MS Access	9/1/2007
Q: !! I have created a MS Access Table with 4 fields. 1. Item 2. Order_time 3. Shipment_time 4. ... A: Ather hi, This is too complex for an IIF statement that delivers reasonable performance. I would ...
Problem with report and listbox values	8/31/2007
Q: I have a problem with report depending on listbox values. My reports code: SELECT tt.NAME, t.NAME, ... A: Charles, Hi, If you look at the template application MultiSelect.zip and ...
Access query	8/31/2007
Q: I want to create a form which has QnA Q: Who's the president of United States A: Laura Bush B: ... A: Use the tools wizard to build an option group of radio buttons. if the radio buttons are allocated ...
error message	8/30/2007
Q: Iam trying to build up a database in ms access. iam using vba programming in it cause i learned it ... A: Try the following SQL replacement... sql = " INSERT INTO Lieferanten Values (" For intCounter = 0 ...
Relationship Question	8/30/2007
Q: I have a question about relationship in Access. I'll try to explain my problem as brief as possible ... A: Raddle hi, Insert a new column in Table 2 for the Material Code and the relationship is thus based ...
Building tables/relationships	8/29/2007
Q: I'm no sure how to build a part of my database. I'm building an Issue database. I want to link an ... A: Robin, hi, Are Clients related to Products via Sales Order Lines ? or how ? Regarding the Product ...
Opening up a MS word doc from a form based on a query	8/29/2007
Q: hey I am trying to create a program for a client that will allow him to have all his clients in a ... A: Kritarth, hi Some links: http://www.jojo-zawawi.com/code-samples-pages/code-samples.htm ...
textboxes	8/23/2007
Q: The forms are connected by a 'Next' button.The first field on both forms is School Name.Now,I want ... A: Assuming the first form remains open (but perhaps hidden by the second form), then you can simply ...
Query Question	8/23/2007
Q: I am importing a text file of inventory for my ticket sales company into Access and running a query. ... A: Why don't you load the textfile into a new table in Access, then perform the cost update on the new ...
Keywords on Access	8/8/2007
Q: I am doing a work placement in Paris and struggling somewhat with Access. I have been asked to ... A: Chris hi, The Filter By Form method from the toolbar should enable you to include as many or as few ...
MS Access 2003	8/8/2007
Q: I have two fields in a table, how do I link the two. The one field is DATE CLOSED when I enter A ... A: Columns in tables are not linked - in a database in 3rd normal form, each column's data is ...
Find and Replace Function in Access	8/7/2007
Q: Access Expert: Micah Rousey (on vacation) had a great answer to a very similar question. ... A: Mark, hi, Why not build a parsing & replace function and use that in the SQL So if the function is ...
change table date in response to update	8/5/2007
Q: Please help me. I have a database in Access, It has a table with information about properties such ... A: Luke PLease see question asked by Terry... Kind regards Geoff Questioner: Terry Category: ...
control in a from	8/4/2007
Q: I'm very much a beginner. I can do basic stuff but have never worked with events or the code itself. ... A: Terry Hi, You are nearly there... 1. Use the 'After Update' event on controls that you want to ...
Counting question	8/4/2007
Q: In my database, I have a field for Age. I need to count how many people fall within a age range. ... A: Delores, Hi Just run a query against the table as follows: SELECT Count(*) As AgeCount FROM ...
Record Filter	8/3/2007
Q: I have a form built with a subform inserted that is used to create messages on each account. The ... A: Jill, Hi From the level of detail provided I can't be specific, other than you need to xx.Requery ...
database question	8/2/2007
Q: I have the following setup within my database, table fields Primary Key ... A: Martin, Hi, This is a classic Sales / Inventory ER (Entity-Relationship) model. Your database ...
Open a form and Close a Previous Form	8/2/2007
Q: I am grateful cos it worked. So I am here again. I have a form, having a command button.I want the ... A: Charles, Hi, I assume the requirement is to have a single button that opens a new form (lets call ...
Storing values in database using interface.	8/1/2007
Q: Just want to know how to store data in the main database in different tables and fields using a form ... A: Awan, Hi Use the Query Builder to do the update: In the matrix, just enter the formulas you have ...
Multiple Like Query Parameters from Form	8/1/2007
Q: I'm creating a form where the user will enter a series of text strings and then execute a query by ... A: Joe, hi There are many ways of cracking this, but you are going to need to get skilled up if this ...
merging 2 tables	7/31/2007
Q: my name is Raddie. I have a question in Access, and would be really greatful if you could help me on ... A: Raddie, hello, 1. Working in a database environment is very different to Excel. One of the key ...
Access 2007	7/31/2007
Q: I need to create a form to select selected data to print to a report ANSWER: Ian, Hi, 1. The ... A: The code applies to 2 buttons on a form - one Cancel, and the other 'Yes' The 'Yes' button depends ...
Access 2007	7/30/2007
Q: I need to create a form to select selected data to print to a report A: Ian, Hi, 1. The objective is to end up with a SQL statement that provides the data to feed the ...
Using checkbox to determine on what record to update comments	7/30/2007
Q: I'm not really familiar in access db and vba. I have created a form which one of the field required ... A: Mimi hello, In principal you can achieve your requirement by setting up a button that runs some VBA ...
SQL	7/29/2007
Q: i have this query and my teacher asked me to remove the joins because she didn't want to use joins. ... A: Devin, Start & end dates don't appear in the SQL However, the WHERE predicate should be corrected: ...
MS Access	7/27/2007
Q: I am not an advanced user but can design a simple database and can, usually, sort out my problems ... A: Loraine, Hi, We need to do some testing to see what the issue is. Take a backup copy and store it ...
Importing CSV into MS Access using SQL and import specification file	7/27/2007
Q: Some time back you answered a question about importing CSV files into MS Access and how to create ... A: To my knowledge you can't combine an ODBC call to a text file directly into SQL If the Access route ...
Access	7/26/2007
Q: Geoff i have a motel business and i am wanting to no if you can help me on setting a passowrd on a ... A: Yep - use the textbox 'After Update' event if your textbox is called 'txtPrice' then the code would ...
Access keyword search	7/25/2007
Q: I have a db of about 1100 DVDs. I would like to run a select query that will enable a user to input ... A: The following query will do that: SELECT [DvdTitle] FROM tblDvds WHERE InStr([DvdTitle],'Harry') ...
ACCESS	7/23/2007
Q: I got a ReservationForm and got these 2 control, txtFromDate and txtToDate. I want to ensure that ... A: Steven Hi, Use the Validation Rule & Text for txtFromDate and txtToDate Provided the StartDate and ...
amalgamation of databases	7/23/2007
Q: The net based tool seems to fall over when trying to run more than 30,000 records(as the business ... A: Access 97 and less can grow up to 1GB Access 2000+ up to 2GB You should always have your front end ...
MS Access 2003 - Working with Textboxes and Command Buttons with VBA	7/22/2007
Q: with the limitations of Micro, I am starting with VBA. I have a working knowledge of BASIC ... A: Charles Hello, If you want to add the values in two text boxes to a third text box on a form, by ...
confused relations/queries	7/22/2007
Q: Good Morning Geoff. I ask for expert advice. I was sure of what i know , now i ain't. i thought i ... A: You are right ! Products and Sales (Orders) is a M:M relationship. To break it you will need an ...
AcViewNormal Question	7/20/2007
Q: I am trying to print a current form as a report. Here is the code I am using: Private Sub ... A: Daryl, hi, You need to pass the Primary Key value to the report so that it knows which record to ...
Changing the name of a table	7/20/2007
Q: I need to make changes in the format of a table. I want to save the existing table under a new name ... A: Nancy Hi You could write a procedure to check the row source of each form, and similarly each ...
Changing date in Access	7/18/2007
Q: I am using Access 2002. I have a date field and I want it to automatically change the date to a ... A: On the form, your textbox linked to the Date needs to run some code triggered in the After Update ...
Creating totals	7/16/2007
Q: I have a problem that may be difficult to explain and have a difficult answer but would you be able ... A: You could use a nested query as follows, assuming a table name of tblResult SELECT a.PlayerID, ...
Access Critereia Query - with blank fields and search parameters	7/16/2007
Q: I'm a high school student working on an Access project for a summer internship. I needed your ... A: Gautam, Hi, Here is a similar question with solution, answered earlier this year. ...
Access	7/12/2007
Q: my question may be very simple, however I am very new to using access and hope you are able to help. ... A: Jasvinder Hi, You don't need a memo field if you are only storing a few characters - change this to ...
Epiration Date - Change Color	7/11/2007
Q: Before I start I just want to thank you for taking time to help answer my question. I am trying to ... A: Marcus, Hi, Use the Form Current event, which occurs each time you change record, and build the ...
Lists	7/10/2007
Q: Is there a way to have one list serving two columns in a table, with no duplicates allowed? How do ... A: Cassie, hi, It sounds complicated - how will your users know which selection goes into which column ...
SQL Queries stored in a table	7/10/2007
Q: I Hope you can help, this is driving me crazy.I have a table tblreports which contain sql syntax for ... A: Interesting question and here is a way to crack it... 1. In you table tblReports, column sqldata, ...
MS Access to MS Access migration	7/9/2007
Q: I have two *.mdb files i.e. Data1.mdb and data2.mdb. in data1.mdb (old database), there are 100 ... A: I have built a small subroutine to perform this task: Paste the code below into a new module in the ...
Help in many to many form	7/8/2007
Q: exam table: examid, class, section, subject ,examtype, examratio, exampoints. student table: ... A: Mueine, hi, As I see your logic.. Part 1 ------ Select Class, select Section -> two linked combos ...
Keeping a Unique ID Using Auto Number	7/8/2007
Q: my company is using a product from a third party that stores data in an .mdb file which we need to ... A: Daniel, Hi, I believe you have to: a) Manage 2 unique IDs per product, one the original on the 3rd ...
please help me....	7/7/2007
Q: Dim tempDatabase As Database Dim tempTableDef As TableDef ' Set a reference to the current ... A: 'All varibles need to be declared Dim tempDatabase As DAO.Database 'We are interested in the DAO ...
Nz Function	7/6/2007
Q: I have this query and have applied the Nz function to Weekly commission. When i run the query i ... A: Devin, Hi, First you will need a function, that given a start date and end date, returns the number ...
Nz Function	7/5/2007
Q: I have this query and have applied the Nz function to Weekly commission. When i run the query i ... A: Devin, Hi, I assume the retainer value is held in the Staff table? If correct, then your INNER ...
Inventory Database	7/3/2007
Q: Hey Geoff, I have Microsoft Access 2003 and I need to create a database that will store all of the ... A: This is more like a design and development project. How much time and money have you got ? There ...
Access 2007 for Sportmanufacturer	7/2/2007
Q: Greetings, TJ here. I am in the process of building up a database for a sportequipment manufacturer ... A: Suggest you get some training asap. You will need to learn how to design a database system (generic ...
How To Creat relations to Linked Tables	7/1/2007
Q: I have three Databases the first is the front-end one have just the forms I linked the other two ... A: In the previous code I sent, just change the line Set oDb = oWk.OpenDatabase(soDb) so that 'soDB' ...
Updating multiple records	7/1/2007
Q: I have a bit of a problem with an Access DB I am trying to make, I have created a table with the ... A: If the main form is called frmMain and the subform and its object on frmMain are called sfrmDetails, ...
IIf function	6/29/2007
Q: i have set up a relational database with several forms in Access 2000. When clicked, I want a ... A: Try this I assume the button is called cmdButton, and the related form frmRelatedForm ... Sub ...
Help on Queries	6/28/2007
Q: I have two databases containing simple numeric values and I want to combine both tables to create ... A: Lets say table 1 is called tbl1, table 2, tbl2 The common data between the two will be as follows:- ...
julian dates in Access	6/28/2007
Q: I need a "stand-a-lone" line of code that will generate a Julian date on a Report using a "Text Box" ... A: I found this function on the internet - please use the full script including the copyright '' ...
Access or SQL Question	6/26/2007
Q: I have a table that consists of user names with exchange permissions such as NYSE, AMEX and NASDAQ. ... A: RC, hi, Take a step by step approach... 1. I will assume a table spec as follows: tblUser ...
Query using VBA	6/25/2007
Q: I'm using Access 2000. I am trying to create/build a query using VBA. I'm having trouble figuring ... A: Chad, hi, To do this, alter the Query SQL property using DAO The principal, is to have a basic ...
reference Library	6/25/2007
Q: I would like to know which check box at VBA reference libraries related to which library if there is ... A: Nagy, hi The reason you might not see the ControlType property is possibly because you have late ...
Set Relations By VBA Code	6/23/2007
Q: I know how to link more than one database through VBA code can i set relation between my linked ... A: Nagy, Hi Peruse through the code below and adapt to your application: the code builds 2 tables with ...
Dynamic text box name	6/20/2007
Q: I am trying to write some lines of code to get the name of my form's text box dynamically and put ... A: Here is a procedure I have built to access all the textboxes on a form. To make this work, just edit ...
Message Box.	6/19/2007
Q: Goodafternoon. Please could you help me, how could I write a code in VBA to protect one table in my ... A: Ivan, hello, try this code... If UCase(InputBox("Enter the Access Code..")) = "ABC" Then ...
Access Calculations	6/9/2007
Q: I have the following problem: Three fields in a table, Hourly Rate, GST amount and Total Amount. ... A: Run an update query on the table. Here is a SQL template you can use - you will need to check the ...
Multiselection List Box	6/8/2007
Q: I have been trying to create an experts' database using multiselection list boxes for choosing the ... A: I have built a demo application and zipped it up as the following file ...
MS Access Quries	6/8/2007
Q: I have a Several MS Access file that has several date fields with a field type of number but they ... A: I have built a small function to convert the Number to a Date, please check the date format works OK ...
link access and excel	6/7/2007
Q: I have a creeping suspicion that the answer to this question will be no but here goes. I know there ... A: Al hi, I will assume there is a Primary Key (PK) in both tables called ContactID - with a datatype ...
link access and excel	6/7/2007
Q: I have a creeping suspicion that the answer to this question will be no but here goes. I know there ... A: Al hi, From Access you can update Excel - linked table From Excel you can only read Access - ODBC ...
Microsoft Access	6/6/2007
Q: What are the various built- in functions for doing calculations in MS Access? How can we sort Data ... A: In a regular SELECT query you can use all the access functions for the selected fields, and in an ...
rank (vba)	5/30/2007
Q: I would like to create a database that will contain a rank field on the form which allows me to rank ... A: The following procedure will interrogate the user for the number of ranked records. You will need to ...
MS Access forms	5/29/2007
Q: I had a question about forms in MS Access, the situation being that I have two tables: Members and ... A: Miklos, hi, You will need a 3rd Associative table containing the permutation records of MemberID ...
errorHelp with error object variable or with block variable not set	5/28/2007
Q: I am exporting records from a table to excel template based by arrival date search. I keep getting ... A: Try... 'Select Date ("mm/dd/yyyy") stSQL = " SELECT *" stSQL = stSQL & " FROM ...
Need Help... Access Forms Calculations	5/27/2007
Q: I'm a newb w/a question on form calcs. I have a form (frmLedger) that dumps data into a table ... A: Steve, hi, When you say """ based off of whats typed into the "Agent Emp ID" in the form """ I ...
How to change a tables data type through queries	5/27/2007
Q: Is it possible to change a tables data type (number to text) using a query or ???. I have a Field ... A: Steve, Hi If you use the Nz() function to perform Null replacement then this should work. If you ...
How to change a tables data type through queries	5/26/2007
Q: Is it possible to change a tables data type (number to text) using a query or ???. I have a Field ... A: Steve, hi, The easiest way is to do the datatype change in the query as follows: SELECT ...
linking Btrieve database with Access 2002	5/26/2007
Q: Geoff: I am using Microsoft Access 2002 to link tables in an old Btrieve database (Lytec Dental ... A: Rodney, Hi In a word, no I can't help. But I did try the following on Google and there were a fair ...
Need Help... Access Forms Calculations	5/25/2007
Q: I'm a newb w/a question on form calcs. I have a form (frmLedger) that dumps data into a table ... A: Steve, hi, Fortunately there are a series of in-built functions for exactly this purpose.... How ...
adding a rank field	5/24/2007
Q: I am building a database where i have a rank field on my form. The user is allow to select a rank ... A: Change your logic as follows: Before Update ------------- Rnew < Rold "UPDATE Table1 SET rank = ...
Viewing images in a form controlled by a combo box	5/16/2007
Q: Please could you help. I have created a table 'A' to store data on my pc. From this I have created a ... A: Andrew, Hi, In table B include a column which gives the drive path and filename of the image - I ...
pivot chart/query	5/15/2007
Q: i built a query in pivot chart which would show the pattern of electric consumption per flowrate of ... A: I have built a sample chart in a report and have the same problem - viz. the formatting in design ...
Acees forms paste appand stopped working	5/12/2007
Q: I have used Access since version 1 and have built a large database for more than 10 years; I have ... A: With the info provided I can't be very specific: however my approach would be as follows: Set up a ...
Access printing VBA code	5/9/2007
Q: I hate to bother you with simple questions but I have been scavanging the forums and cannot find a ... A: Lonnie Hi, Ref 1 - there is no simple way - only a complicated way and you will need to refer to ...
conditional query criteria	5/9/2007
Q: I having some issue's with query design is a database I manage. I have a database which hold ... A: 1. To edit a query in code use the following: .... Dim qdf as DAO.QueryDef, sSQL as String ...
ActiveX Calendars in Access	5/3/2007
Q: I am designing a database for a small technical school. We want to be able to create a form that ... A: Lori, hello, If your calendar object is called 'cal01' and your target textbox called txtDate, then ...
Combo Box - find by typing text	5/2/2007
Q: Geoff: I have 2 separate databases which essentially do the same thing, but for different ... A: Michael Hi, 1. I have set up a prototype and can't simulate the problem you describe. Two combo ...
copying a data from one column/field of one table into another without repitition of the value	4/30/2007
Q: Currently I am developing an application using MS-access and require some help. I have created a ... A: Jayasimha K. Rao, Hello, The Jet DB engine has a neat way of resolving this, but first you need to ...
ranking records	4/29/2007
Q: I was wondering if you can give me some tips on how to do this. I have a database and it has many ... A: AAAAA If you have a column that you want to use to determine rank, eg [SalesRevenue], and a primary ...
Date format in MS-Access in different locales	4/27/2007
Q: I am currently developing a web application using ASP and MS-Access. The problem I am having is that ... A: Jay, Hi Yep - its a pain in the ***** :-) The only safe way I have found is to break the entered ...
Access	4/26/2007
Q: I trying to create a Macro in access to export the main table to excel. A: Sam, hello Use the TransferSpreadsheet action with the following settings at the foot of the macro ...
Printing reports	4/25/2007
Q: I have a report with 6 subreports. The reported data is correct, but the report contains hundreds ... A: Wes, hi, Not sure what the question is ? But if it relates to more repeats than you wanted, you ...
Query	4/20/2007
Q: good am. i have three tables with it's respective fields: Table Patient: PatientID, Date, Lastname, ... A: First a comment on your tables: Re Medicine table: if 'Quantity' relates to the medicine generally, ...
Problems with DateAdd	4/19/2007
Q: I have a series of date-time based data on 5 minute intervals. I need to look for anomalies in the ... A: Thomas, Try using the Format() function in your conditional expression so that only date/times to ...
Date Issue in Access	4/18/2007
Q: I have a form in which a start date and an end date is entered in order to query transactions for ... A: Ulices, Hi In the background, all Date data is stored as real numbers - 12345.56789 where the left ...
search by date	4/18/2007
Q: i'm creating a simple access data retrieval program. in my main form i want to add a particular ... A: Insert the following code in the button click code given previously: sSQL = "SELECT RmBkID, ...
search by date	4/17/2007
Q: i'm creating a simple access data retrieval program. in my main form i want to add a particular ... A: Open the form in Design mode and ensure the Properties dialog is also open. Create two text boxes ...
er diagram	4/16/2007
Q: CAn you help me out with the er diagram. Here is the situation : I am working on an inventory ... A: From your description, I would assume the following entities: Equipment - PCs, Priters etc ...
search by date	4/16/2007
Q: i'm creating a simple access data retrieval program. in my main form i want to add a particular ... A: Enrico, Hello However you implement this, the 'nitty gritty' comes down to a constraint (WHERE ...
Reports	4/15/2007
Q: the tabs are Customer and Products. Now I would like to construct a report in the form of letter, ... A: Peter, Hi, The regular Tabbed Page object shares data from the same record source as the underlying ...
Send PDF emails	4/12/2007
Q: I found my problem discussed on your site at ... A: Lynn, You will need to use a VBA subroutine: below is a well tried and tested one. You will need ...
"Enter Parameter Value" error in MS-ACCESS	4/11/2007
Q: I have a form with 3 LIST BOXES and have written small queries to fetch data conditionally. Upon ... A: srivatsan hi, Difficult to diagnose from the info provided.. 1. Are the program versions identical ...
Hide Access Window	4/9/2007
Q: A few years back, you answered a question regarding Access window properties. See ... A: James, Good Morning, Use the DoCmd.OpenForm function to open the form in 'Hidden' mode, then when ...
automated email	4/7/2007
Q: At the moment im building a database for a driving school, I have a customer field, with email ... A: Based on what you have said and assuming Access 2000+ on Windows XP I would suggest the following ...
Order by clause	4/6/2007
Q: I created a table as below: Region District Territory Customer_Name 60 42 ... A: Anitha, Hello, All database systems store data in the order that the data was loaded, but even that ...
parse words in a phrase and use those words to compare	4/5/2007
Q: I'm trying to do the following: I have two tables each with a column that contains phrases. I need ... A: Michael, Good Morning, You have a good day plus of programming work for a competent programmer here ...
From Form Header to Detail section to field in DB	4/4/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, Hi Try the following code please: Sub zInOut_AfterUpdate() Dim sSQL As String, db As ...
saving data into table	4/4/2007
Q: i have two tables one is a temp table i use on my form for data entry purpose, and the other one the ... A: ATS, Why don't you scrap the temp table and use the proper invoice transaction table. If the tables ...
VB Code	4/3/2007
Q: I have my form set up to input all the data necessary to print out my Bill of Lading and my Packing ... A: Kerri, Try this Private Sub cmdBillofLading_Click() On Error GoTo Err_cmdBillofLading_Click ...
saving data into table	4/3/2007
Q: i have two tables one is a temp table i use on my form for data entry purpose, and the other one the ... A: ATS, Hi, If you are working at sub-form level and you want to pull say 'Invoice_No' from the main ...
saving data into table	4/2/2007
Q: i have two tables one is a temp table i use on my form for data entry purpose, and the other one ... A: ATS Hello, Assuming the names of the tables are correct, the sql looks OK. The code behind the ...
From Form Header to Detail section to field in DB	4/1/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, Here is the revised code: Sub zInOut_AfterUpdate() Me.InOutH = Me.zInOut End Sub ...
From Form Header to Detail section to field in DB	3/31/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, In pasting the code through, some basic errors are creeping in.... Try this Sub ...
From Form Header to Detail section to field in DB	3/30/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, Looks good I predict that, provided you have selected a record in the detailed section ...
From Form Header to Detail section to field in DB	3/30/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, hi, Try constraining the UPDATE by the primary key of the selected record, so if the PK ...
Will I use Access for this project?	3/29/2007
Q: I am an intermediate MS Access User and fairly understands basic SQL. I have done a few MS Access ... A: Erick, Good Morning, Access should be fine for everything you have stated, within the following ...
From Form Header to Detail section to field in DB	3/29/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, Private Sub OurCompanyID_AfterUpdate() Dim db As DAO.Database, sSQL As String Set db = ...
From Form Header to Detail section to field in DB	3/27/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul, Sorry I missed the continuous forms bit. Probably the best would be to execute some SQL ...
From Form Header to Detail section to field in DB	3/27/2007
Q: I have 3 unbound combo boxes in the Header of a Continuous Forms. I have them there and unbound ... A: Paul hi, Use the AfterUpdate event of the combo to update the text box. If lets say the text box ...
MS Access Report Calculations	3/26/2007
Q: I am working with calculations on a report. I am having trouble figuring out how to calculate a ... A: Carla, Good Morning, The trick is in the design of the database tables. Once the data is stored in ...
on querry	3/24/2007
Q: Goodafternoon I have the 2 following queries. The 1st one shows top 10 lines on the client who’s ... A: Sergei, Good Morning, SELECT TOP 10 client,[Date], Rashod, prihod, 0 As Minus, 0 As SortOrder FROM ...
Every Other Record	3/23/2007
Q: I want to be able to show every other record for a person within a day. Table Below: Name\|Time\| Jo ... A: Dave, Open a module in design mode. From the menu select tools / references Scroll down and ...
Every Other Record	3/23/2007
Q: I want to be able to show every other record for a person within a day. Table Below: Name\|Time\| Jo ... A: Dave, Hi In a database records are stored randomly, so the sorting has to be determinable from the ...
MS Access tutorial	3/21/2007
Q: I have been using MS Access for about a decade to load & query data. Typically my databases consist ... A: Bill, Concerning your full-blown application, I without reservation would recommend that you get a ...
Problem with Reports	3/19/2007
Q: I have a problem with my report. I created a table that keeps track of the monthly church expenses. ... A: First step is to create a query showing qualifying records with their appropriate quarter: the next ...
dynamic SQL statement too large	3/14/2007
Q: I have an Access 2000 application where the user can select a multiude of criteria to produce a ... A: I am not aware of any SQL length limit, though it would not surprise me if there was. That aside, ...
MS Access	3/14/2007
Q: Goodafternoon. Could I ask you one question concerning MS Access? I have two tables. A table with ... A: I'm not sure of how you are managing this task (ie form based or VBA or what) but you will need to ...
Running script in Access	3/13/2007
Q: I am using MS Access 2003. I made a VBScript to update a database table from a .csv file. It works ... A: Charles, Hi, I assume you mean that you have built a Procedure (Subroutine) in a module.... Macros ...
Moving Tables	3/8/2007
Q: Hoping you could help me out. I work for a health department and currently have a database that ... A: Kerry, hi One of the problems with Access is memory leakage (or more correctly, memory management, ...
Subforms Issue	3/7/2007
Q: ..back once again I have a Form with Subform in an ADP with SQL Tables. I have Parent & Child IDs ... A: In theory you should not have this problem, but it may be the cursor type that you are using ... I ...
Date calculation	3/5/2007
Q: Hey, I am trying to design a table with a date field that will always display the date of the next ... A: I have built the following function that will always return the next Friday, unless today is a ...
Access report output to PDF	2/19/2007
Q: Is it possible to output an access report to a PDF file? If so, how do I do that? I am using MS ... A: Use a free PDF Printer program, such as PrimoPDF ...
Access count query	2/18/2007
Q: Looks good. One moreIs it possible to build the query so that it will return results for each of the ... A: Use Query 3 with a WHERE predicate.. SELECT a.WorkCountry, a.CompanyHierarchyPoint, Count(b.EmpID) ...
Access count query	2/18/2007
Q: I am trying to generate a query in Access to give me results in a specific format that I can cut and ... A: From your description, I am not sure whether you have separate tables - ie one for employee details ...
Link tables with combox	2/17/2007
Q: How I can link combo box with different tables, eg.I have three tables with field name Code.I have ... A: Not sure I understand the 3 textboxes bit...., however, you can join 3 tables together as follows.. ...
Reports	2/16/2007
Q: Is this the kind of information you require? The top section is how I want the info to appear on my ... A: Peter, Hi, Try the following : =IIF(IsNull([TraceabilityCode]),"","Summary For : " & " " & " " & ...
Combo move does n't enter the value.	2/16/2007
Q: I did two boxes with single Combobox to fill up the textboxes, but I do not value updated in ... A: Try using the following syntax... Private Sub Text1_Click() Dim ctl As Control Combo1.Clear ...
Button to open correct record in form/subform	2/15/2007
Q: But the Link Child Field and Link Master Field on subform is set to [Event ID]. There are multiple ... A: Dave, Hello Depends on the structure of your data... If your ContactID is unique in your Universe, ...
Button to open correct record in form/subform	2/14/2007
Q: I have a continuous form that displays info from a Event/Contacts query. At the bottom of this form ... A: Dave, Hi, I hope I have understood your question... 1. Ref the Event form with subform Contacts. ...
Generay random list	2/13/2007
Q: I am building a database for a hospital. My employeer has told me he wants in the program a small ... A: Below is a function that will generate a random 6 digit number. You can modify/use this in selecting ...
Reports	2/12/2007
Q: Yes, its me again, on my report I have three tracabilities; traceability, traceability1 and ... A: Pater, As per my previous response - Please provide some sample data for all these variants - this ...
ms acess autnumber	2/12/2007
Q: How can i make a personal info form that uses id number or last name to search a record and then it ... A: Try creating a 'Search' button using the command button wizard - options are 'Record Navigation', ...
regarding research project	2/11/2007
Q: My name is Rahima Mohammed; I am a student of John Tyler community collage in VA. I am doing a ... A: Rahima, Access is a globally used desktop RDBMS providing highly cost effective data analysis ...
Text Box	2/8/2007
Q: Sorry to be a pain, but could you tell where abouts in this line of text I could place the ... A: Peter hello, Proving a sweat hey.... :-) My only issue is understanding your data. Could you ...
Backend and Field Size of a linked table	2/7/2007
Q: I am very new to Access here. I have two files, one has a title with the name BACKEND in it. What ... A: It is common in Access (and other) databases to split the database application into 2 files, viz. ...
audit trail	2/7/2007
Q: it is working; however, the user must press the "save" command button - here is the code: Option ... A: Carla, Hi, My approach would be a little different to the way this product works.. I would use the ...
AUDIT TRAIL	2/6/2007
Q: MS ACCESS at my company is a desktop software located on a network server) I received this site from ... A: Carla, Hello, I have had a look at the program: the app I downloaded was missing the table tblAudit ...
Max & Count aggregate functions in query	2/6/2007
Q: I'm using the query design view to take a list of buyers and using total, to count the number of ... A: A nested solution is elegant from an SQL aspect, but from a performance view point in Access, a ...
Text Box	2/5/2007
Q: Maybe I did not explain myself very well. What this text box does is it shows a summary for the ... A: Peter, Hi The basic format command is of the form Format(Xxxxxxx, "Yyyyyy") and this converts ...
Multiple entries into 1 row per ID	2/5/2007
Q: I have a table with 7 fileds. Reference, ID, Second_ID, Rubric, Code, Text, Date. I have multiple ... A: Have you tried using a 'Cross Tab' query, built with the wizard ? Otherwise we are talking ...
Access Form from a command button	2/3/2007
Q: I am afraid I don't understand the reply. It shows my limited knowledge of forms and its controls. ... A: Talat, Hi The WHERE predicate is part of a SQL select statement. If you build a query in Query ...
Help setting up an Option group	2/2/2007
Q: The Option group now works great except for the "All Status" selection. My Lookup Table is as ... A: Alex, Modify your table slightly as follows: 1 'Open' 2 'In Process' 3 'Closed' 4 'Open', 'In ...
Access Form from a command button	2/2/2007
Q: I have a form which queries a table as pivottable. Standaone, the form works OK. But I want to open ... A: Talat, Good Afternoon, You cas pass a 'WHERE predicate' to the form on opening as follows: ...
Help setting up an Option group	2/1/2007
Q: I am trying to set the criteria for a Query column, named "Status", to read the selection from an ... A: Alex, Hi I understand.... Lets start with a model query to work this through. I will assume the ...
Oracle2Access	1/31/2007
Q: In Access I am linked to Tables in an Oracle db. When I perform an Access query using the linked ... A: Russ, Sorry, I can't account for Bill Gates and his vagaries... What you could try - Open the ...
ms acess autnumber	1/31/2007
Q: how will i append my old data that is not an autonumber to an autonumber filed? how can i change a ... A: In effect you have to provide an additional 'Number' column (of data type Long) that is NOT ...
Access Oracle Regular Migration	1/29/2007
Q: I developed an application using VB.NET 2003 and MS Access XP. This app. is running in low config' ... A: I would write an Access VBA module to perform the merge: The primary steps would be: 1. Establish ...
Access question	1/27/2007
Q: However, I am not sure how to do what you are suggesting. Account code is one of the fields in a ... A: Talat, Hi You should have a master Accounts table - that contains the master list of account codes. ...
Access question	1/27/2007
Q: I have a query which is grouping records under several account codes; eg 22201, 52201, 52202 etc. ... A: Talat Create a description column in the table of Accounts codes. Now design your query to join ...
Associated labels	1/26/2007
Q: I have built an access database for some surveys with 65 plus questions; to make data input easier I ... A: You need to build a table relating the check-boxes and the question. But to populate this ...
Report field Display	1/26/2007
Q: Thanx Geoff! I really appriciate ur help. and one more thing Here's a template query: SELECT ... A: Create a new query - use SQL view and paste in the template: SELECT MakeString() As NameString FROM ...
issues with the querydef object	1/24/2007
Q: I have an application where I am creating a query dynamically - on the fly. To do this I am using ... A: Brad, hello, For all your requirements use the QueryDef SQL property, and in each instance, just ...
Report field Display	1/24/2007
Q: I have a report, in the Detail area i draged the one fieled ("Name"), it displaying as list as abc ... A: Use a function to step through the table and build a conrinuous string. Now build a query based on ...
Access file not opening	1/23/2007
Q: I am working on an MS Access file on my laptop. I am limited with the page setup details as I am ... A: Mike Hi, Why not set up a default printer for your laptop ? Even if its a hoax, it would probably ...
Handling multiple Ifs and Null Controls	1/23/2007
Q: My database has two tables. Table1 and Table2. Table2 is a copy of Table1. Both tables have 50 ... A: I would use a FOR..NEXT construct to combine the column value length for Field(3)-Field(49) - NB ...
hyperlinking to form	1/19/2007
Q: I have a continous form (listing last names). Can I have Access automatically hyperlink each last ... A: Michael Hi, Set the 'Click' event of the textbox to 'Event Procedure' and paste the following code ...
Field Protection	1/13/2007
Q: I am trying to prevent accidental field changes to Last Name and First Name. I am using code as ... A: Try using the AfterUpdate event This is how I would code the same... Private Sub ...
If no record....message	1/11/2007
Q: I have developed a search form with a txtbox and a cmdbutton to open a form based on a query. The ... A: Polo Hello, You need to use a recordset approach, as follows: Dim db as DAO.Database, rs as ...
Email using Access	1/11/2007
Q: Happy new year to you too! I think I missed something on your explanation...(or I didn´t make ... A: Sorry - I am not quite sure what you want to put in the body of the email... however if you need to ...
Email using Access	1/10/2007
Q: I would like to use Access to send an email (the address is on a table) with a query, but not as an ... A: Happy New Year Cesar, I answered a question on emailing from Access recently - here is my reply & I ...
Creating Report based on crosstab query	1/10/2007
Q: 1.The name of my crosstab query is qryStatus. 2.The name of my report based on the query "qryStatus" ... A: I will assume there is a starting form, say frmMain. On frmMain there is a button which is titled ...
MS-Access capturing record history	1/9/2007
Q: I am building a database and one of this that I am interested in doing is capturing the date and ... A: Tana, hello, For the CreateDate field - in the table, in design mode, set the default value ...
Working with 2 surveys	1/8/2007
Q: Is there an easy way to have a button from one survey to call another database, answer questions and ... A: Kathy, hi, Yes - you can use the following commands to open up a different DB, then execute a macro ...
Replication	1/7/2007
Q: Can you tell me how to do replication from oracle to Ms Access in detail pls. Thanks A: Sorry I can't. This is a specialist and detailed area and would suggest you find a suitable ...
tables with similar fields	1/7/2007
Q: Geoff, I have a database to track job assignments. Each assignment has an employee or contractor. ... A: Derrick, Hello, It would probably be a good idea to take a step back and ask yourself some ...
Linking to MULTIPLE Excel Spreadsheets	1/5/2007
Q: Geoff, I work with multiple protected Excel spreadsheets every day that are generated by another ... A: Sounds fine - just need to sort out the logical stuff!! If for each 'XLS call' you are pulling a ...
transferspreadsheet using list box	1/4/2007
Q: G'day I am building an application where the user needs to import multiple spreadsheets into the ... A: Mike, hi, First - use a combo box rather than list box to ensure only one selection may be made at ...
proper formula syntax	1/4/2007
Q: I need to add a formula into a text box on a field that multiplies a field in the primary table by a ... A: Kenneth, Hi Probably your best bet is to build a function that you call in the result text box. You ...
Problem opening forms	12/21/2006
Q: Hey again, I am having a problem with opening some forms. I have several forms. The forms are ... A: If each Question/Answer form sbf work OK, then the issue I believe relates to how you are ...
how to create query based report	12/21/2006
Q: I have created a report in MS-Access based on a query where I am using 2 fields - "Start date" and ... A: Praveen, hello, I fail to understand the problem: if the query is working correctly, and the ...
subform combobox dependent on main form combobox value	12/20/2006
Q: I think I wasn't clear with my second question. As far as I can tell the dependent combobox in my ... A: How about if you extend the cboTeacher options as follows: 0 -> Select a Teacher 1 -> Add a new ...
subform combobox dependent on main form combobox value	12/19/2006
Q: With your help I've figured it out. As you said, it is just a matter of language. Using the same ... A: Franklin, hello If you look in the Cascading combos example, you will see that the rowsource ...
Sending data from Access via Email	12/19/2006
Q: I should have mentioned that I'm not very experienced on the programming side. When I run the code ... A: Kerry, hi, It will be references... Open any module in design mode. From the tool bar, select ...
subform combobox dependent on main form combobox value	12/18/2006
Q: I have 4 tables: tblEvent EventID (pk-autonumber) SchoolName other fields tblTeachers TeacherID ... A: Franklin, Hello, 1. Could you go to my website and download the sample application called Cascading ...
Sending data from Access via Email	12/18/2006
Q: I'm having a bit of difficulty sending reports/queries via email from Access. I'm able to hard code ... A: Kerry, hi Email.email is not a valid construct. This command is for a single email recipient in ...
Access Queries	12/17/2006
Q: How can we delete the records in from one table to another through query A: Suhail Hi, Split this into 2 operations 1. Append new records based on source table into the ...
Date Formats and Masks	12/16/2006
Q: I am newish to MSAccess and have to maintain someone else's work. I have a form with start and end ... A: Bryan hi, Onboard help has quite a bit on input mask formatting - have you tried that ? I am not a ...
Parameter Queries and Reports	12/16/2006
Q: Geoff, I have developed a pretty good database for my company considering I have very little ... A: Mike, Hi Think of a query as a specified filter. You can drive a form directly from the table - ...
Query	12/15/2006
Q: i have a query i need the criteria to find the value between value1 and value2. Help. A: In the criteria row of the Query By Example tool, in the column of the 'Value' insert >=123 And ...
Retrieving data from Forms and Subforms	12/14/2006
Q: I have created 2 tables, One called Projects and another called Project tasks. I have set up the ... A: Open the form in design view Select the sub-form object Open the properties dialog Find two ...
Query - Performing Calculations from 2 fields	12/12/2006
Q: I have been working on creating the structure of a DB and now I am trying to create some queries ... A: The RDBMS way would be to introduce 'groups' in addition to 'Client' etc, so a simple query would ...
entering data into tables using MS Access	12/12/2006
Q: I have created a table called contact_table in Microsoft Access 2002, i have also created a form ... A: 1. With your table in design mode, set the [TelephoneNumber] field to a max length of 11 in the ...
Form - Date Entry Issue	11/22/2006
Q: I have a 2 part question for you. First here’s some background. I have 3 tables, tblClient, ... A: 1. Change sbfQA from 'data entry' to add/edit/delete in the properties. 2 Insert the Client ID ...
Data Correction	11/22/2006
Q: I have an Access data base in which I am trying to migrate to SQL Server. Now when I tried to inport ... A: Mo, hello, 1. I would sort the offending Date columns (both Ascending then Descending) in the ...
Update and Append of text file	11/21/2006
Q: I have a database that is used as Single App in multiple sites and also a copy of the database app ... A: Your process is exactly like many that I have developed over the years and it should work fine. One ...
data import from excell	11/21/2006
Q: sir I have a excel data base (my stamp collection )I tried to transfer it to access not to much ... A: The first step is to prepare the Excel spreadsheet ready for linking into Access. Preparation ...
Documentation	11/20/2006
Q: I am in the process of taking several Identical(in structure not data) access databases and ... A: I take a fairly practical approach to documentation. Who is it for and what benefit will they get ...
event procedures in ms access	11/20/2006
Q: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If Lang = "P" Then Text15 = ... A: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If Lang = "P" Then me.Text15 = ...
VBA to append a query	11/18/2006
Q: Geoff, I want to write a simple docmd.runsql to append a query that I've already written. The ... A: Assuming that the queries work when run manually, the problem relates to the control code. You have ...
combo box	11/17/2006
Q: Could you tell me how to change the information (record)displayed on a form when a combo box on the ... A: Charissa Use the combo's AfterUpdate event, so typically... Sub cboMyCombo_AfterUpdate() If ...
vba parse through recordset to update or insert	11/17/2006
Q: As part of an onclick event, I have created a recordset from a temp table. I want to parse through ... A: Mary, Hi You need to build the SQL for each case on the fly as follows... If ...
counting records/nullrecordsets	11/9/2006
Q: will exactly will this function to .Hello . I have A subform that is bound to a query. And I have ... A: replace it for the one you tried... =Count(Fname or Lname) Check out the IIF() and IsNull() ...
creating a report from logic "yes/no" check boxes	11/8/2006
Q: Can you help me create a report in Access? Here is the deal. I just sent out a company-wide survey ... A: I would make a query for each category, summing up the results for each in the way you want, then I ...
Lookup problem	11/7/2006
Q: just cannot make it work. Is it perhaps because I erroneously wrote in my question that the field on ... A: Paolo Hi, I have just done a simple check function on a table of cities and it works fine, viz: ...
flagging a date	11/6/2006
Q: I'm looking at flagging a date, eg 25/12/2006 so the database would tell me when this date arrives. ... A: Erol Hi Use the form's Current event, which fires each time a new record is displayed. Assume the ...
Lookup problem	11/6/2006
Q: I have a form on which I have a cmdbutton to copy a couple of fields into another table (MASTER) ... A: Paolo, Hi Dim MyDb As DAO.Database, MyRs As DAO.Recordset Dim strCode As String Dim strFilter As ...
Hide Database Window	11/4/2006
Q: My database is used by a number of persons. I have hidden the database window so that others will ... A: You can 'disable' the F11 function key by assigning it to the 'AutoKey' macro. To set this up, ...
pop-up form or pop-up reminder	11/3/2006
Q: I want to create a pop up reminder. it must open by it self--when the date of my computers clock ... A: You can use a simple Do-Loop construct to achieve this, however this is perhaps too simplistic and ...
Access to Excel VBA +++++	11/3/2006
Q: Geoff Thanks for your reply, but I dont think it would work. 1. Re:- Your suggestion of using ... A: Woz, Hi If you want to make the table you can build the SQL on the fly at the time of creation, run ...
Query - Selecting Records	11/1/2006
Q: I have a database that has a query with multiple records. I am trying to set up a second query ... A: I didn't fully understand you 'mangle' of queries, however, if you want users to select a value when ...
Hiding then showing toggle buttons	10/28/2006
Q: Many thanks for your exceptionaly quick reply to my question, but I don't seem to have explained it ... A: Now I'm really confused :-) ! Could we go back a stage please. Could you fully describe the data ...
Hiding then showing toggle buttons	10/28/2006
Q: I have created a simple form which has a tab control containing 40 toggle buttons which I use as a ... A: Hamish, Good Morning, Use the form's 'Current' event to trigger the following code snippet. Each ...
Creating a table out of two Excel Worksheets	10/27/2006
Q: I am in the process of designing / building an Access 2003 database. A problem that I am having is ... A: The process is simple: 1. in Access Table tab - link the 2 excel sheets to the Access DB (RH mouse, ...
option group	10/27/2006
Q: i have challenge on option button .I HAVE 3 OPTION IN THE GROUP AND EACH OPTION HAS 4 FIELS I WANT ... A: Olumide Hi, Could you explain further please ? How can 3 option groups support 4 fields ? When ...
Show tables rows on form for update and edit	10/25/2006
Q: I have three tables ,I use combobox to select.How I can do that?I want all fields of table to show ... A: First build 3 single forms for each table - I will call these frmTab1, frmTab2 & frmTab3 Next, set ...
Grouping	10/24/2006
Q: I need to create a report,which has a grouping level on a certain range of numbers, i.e., 1 - 30 ... A: TJ Hi, I would build a small look-up table so that you can provide each range with a name, that may ...
simple Access question: auto complete state names	10/23/2006
Q: My Access database is fairly simple and used to have a spiffy feature that would automatically ... A: You need a table with a single column, containing all the State names. Now on your form create a ...
previous record	10/23/2006
Q: Is it possible to set the default value of a field (Number) same as 'the previous record'. The idea ... A: It of course would be possible to build any number of bespoke auto-numbering systems, however, most ...
Pass value from Excell to Access	10/22/2006
Q: I have a process that I have done in excel that uses data in MSAccess due to the large quantity that ... A: Try... .DoCmd.OpenForm "Source Path" 'Opens the form .Forms![Source Path]![DatabaseString] = ...
Access	10/22/2006
Q: Here is my issue: Imagine a polar plot. Lets say we devide it up in to cells. The cells are ... A: The trick is to build a lookup table of cells, describing each with: StartAzimuth EndAzimuth ...
Record Counting	10/20/2006
Q: Hey My friend its me again. I tried that and it did not work. I did notice that the count thats ... A: 1) Try this.. if szpSzdPrprtySysKey is a number: =DCount("[itdItemDispoSysKey]", ...
open word files within access	10/20/2006
Q: I have a form (Access 2003) which brings up a clients details on screen (name, phone number, etc ... A: Jeff, Hi In the code behind the button that opens the Word document, you can use all form values in ...
Creating Excel files from an Access Report	10/17/2006
Q: Sorry but the code is still blowing up in the same line. Now the debugger is highlighting the ... A: Al, HI, Please validate that ALL fields, such as [Supplier Number] are ACTUAL TABLE or QUERY COLUMN ...
Counting Records	10/16/2006
Q: I have a form that has a sub form. The record source for both forms is created dynamically in vb. ... A: Mo Hi, Here's some functionality I use for a " Current Record of Total Recods" text box On the ...
Creating Excel files from an Access Report	10/15/2006
Q: This is exactly what I was looking for and I do appreciate your help. I am having problems with ... A: If you have objects named with included spaces, you need to enclose the entire object name in square ...
database design - adding a new field to a query?	10/14/2006
Q: I know I can't add a field to a query that I can edit, but that's how far I've gotten in my mind... ... A: Justine hello, You are nearly there, just maybe a couple of mods... tblWorkshop has a 1-M ...
How to change data type in a Table using VBA?	10/14/2006
Q: I have been trying to contact you. I tried this function in SQL of a querry, and worked well. I ... A: Shary, Hi To my knowledge there is no Cxxx() function to convert a Text field into a Hyperlink ...
Newbie question: Setting initial data in a make table query	10/13/2006
Q: I have a table which is created by using existing fields from another table and addition of new ... A: Sanchita, Hi Use the Cxxx functions for this, so CInt("01234") = 1234 CDbl("0234.560") = 234.56 ...
Creating Excel files from an Access Report	10/13/2006
Q: I have an Access 2003 report that contains data related to multiple suppliers. I want to create and ... A: You can use Macros and the TransferSpreadsheet command to do exactly that - potentially each XLS ...
Survey with complex answers	10/12/2006
Q: I'm fairly new with Access Database. I have only taken a class and I am now trying to create a ... A: Here are the main tables I would set up tblPerson PersonID (PK) - this means primary key - could be ...
error message	10/12/2006
Q: I am getting the error message this form or report is based on a query that exceeds the limit for ... A: Split the form and perhaps the table vertically. This means, limit the form to a smaller range of ...
Linking forms	10/10/2006
Q: I have several different tables, each with a correspondinf form. Each table has a FK which is a ... A: Probably the neatest solution would be as follows: 1. Set a large (area) main form based on your ...
Access Form	10/10/2006
Q: I Have a main form in access and I have created a pop up form, that pops up when a certain command ... A: On the pop-up form you can refer to objects on the main from (say its called frmMain) in the ...
Access to PowerPoint Question	10/3/2006
Q: I want to create a Power Point presentation that includes "snapshots" of various Access 97 forms ... A: If you are talking about a one off presentation, then I use Snag-It from Tech-Smith software to ...
Updating 2 tables on one form	9/29/2006
Q: I'm an access Rookie. I have 2 tables with Employee ID (both tables) and Total budget hours (which ... A: Ray, Hi I assume that you have budget detail in one table and budget total in the other table. I ...
Currency to Text	9/29/2006
Q: i have created a report for Bill in which i would like to have the amount displyed in words also. ... A: //www.ozgrid.com/VBA/CurrencyToWords.htm Kind regards Geoff Type the following code into the ...
Access to Sql(long Question)	9/28/2006
Q: I am new to this stuff so explain as much as you can.I am working on a project in which I have 40 ... A: Using 'LocationID' as a unique key is a good plan. IE each Access Application (client) will have a ...
Linking outside pictures to database	9/28/2006
Q: I forgot to mention that I'm a begginer... 1. How can I set a textbox to a folder in my hardrive? 2. ... A: 1. How can I set a textbox to a folder in my hardrive? Just type 'C:\Temp' for example into the ...
Linking outside pictures to database	9/27/2006
Q: "Hello my friend, Hope you can help. I have a very simple database of products in a stock. We have ... A: Store the filename in the database as a text data type. On your form set a textbox to the drive and ...
Append Query to current active records	9/27/2006
Q: I'm trying to use an append query to copy data to be further modified leaving the original tables in ... A: I think, following your description, that you should use an update query instead of an append query ...
To fully computerise any inventory or not?	9/26/2006
Q: Our previous inventory system makes use of cards, record books to record items purchased, serviced ... A: The million Dollar question hey.... Some of the aspects you might want to consider: 1. No history ...
IIF and Access	9/1/2006
Q: I am working on a project with over 200,000 observations. Someone suggests I use Access but I don’t ... A: Paul, hi, Build a new table with each age range, then use this in a query as I demonstrate below. ...
Undo function	8/31/2006
Q: hey Geoff, At the moment im using a form to input data into a table. I want to be able to stop ... A: In lieu of: Me.Range.Undo Me.Fanily.Undo Me.Name.Undo .....etc ...
Ms access 2000 update query	8/30/2006
Q: I have a Database with a course, employee and a link between them tables. I would like for every ... A: Arturo Hi, This may be as a result of your database design: From your description, I would suggest ...
calculations using access (table/form/query)	8/30/2006
Q: I have a similar one in xl but am having difficulty in access. What my intention is, is to have a ... A: Daniel Hello I assume that you will have a table with columns relating to the Question numbers, ...
List numbers	8/28/2006
Q: My name is Ivan - I am a student from Russia. I have a table of about 300 rows – a database of ... A: Good Morning Ivan, I will assume your table is called 'tblCars' and it has a primary key (PK) of ...
Access	8/26/2006
Q: please refer to the table below SALE PURCHASE BALANCE 1) 20,000 ... A: You have not provided any detail of your source table, so I have had to improvise: My table is as ...
query	8/26/2006
Q: I have created a database for the evaluation of marks of students in our school. A query has been ... A: Prasad, Hi Sorry you have had to wait, however I have a solution for you: I started with a table ...
Connect Access?	8/25/2006
Q: Is it possible to connect Ms access with Max, so we can have updated information in Ms access from ... A: Dhaval Hello, I don't know what Max is, but you can connect to external Oracle and MS SQL systems ...
Access countif in a query	8/24/2006
Q: may I ask you for your help. In my Access database I have a table which looks like this ... A: The following query will do that for the first 3 Questions: copy it and paste it into a new query in ...
find name match comparing a table with fullname to just lastname and vice versa	8/24/2006
Q: I have a datbase with just last names in it and want ot find matches from an excel table with ... A: I named the steve.name table 'tblName' This query returns all hits SELECT Customers.ContactName ...
Counting the Check Box and other integration	8/23/2006
Q: A brief description of what I'm trying to achieve - I am working for a company that warehouses ... A: Duncan Hello, Sorry for being tardy, but I have been excommunicado for the last week. Build ...
find name match comparing a table with fullname to just lastname and vice versa	8/23/2006
Q: I have a datbase with just last names in it and want ot find matches from an excel table with ... A: Link the Excel sheet into your Access DB, Create a query based on the joined tables as follows ...
VBA code for popup option group	8/22/2006
Q: I'm trying to get the value of a popup option group, radio buttons, passed to the calling "Public ... A: Dave, Hi Use the frame 'After Update' event to pass the chosen value to the parent form (frmMain) ...
formula using excel 2000	8/22/2006
Q: How can I make a formula using Access same as Excel? I want the ending inventory to be ... A: Jan Carlo, Hello, Bring the three tables together in a query, join them on the part number. Then ...
Date calculations	8/21/2006
Q: Iam not getting the desired results using DateAdd["m",2,[Date]] the error #Name? is returned. The ... A: Paul, Hi, You are doing yourself no favours pursuing this approach. I don't hesitate to recommend ...
ms access	8/21/2006
Q: how can i add a photo to data base field and how can i make it appeares just in a form or querry ... A: 1. Store the Drive, Path & image filename (DPF) as data in the DB 2. Include the combined DPF in ...
Deleting records	8/20/2006
Q: I am trying to delete a series of records from an Access table using the "additional column method" ... A: The 'default' value will only apply for new records inserted after it was set. To set the default ...
Queries	8/19/2006
Q: This is more of an inquiry than a question, When you construct a query and you need to set the query ... A: Peter, hi, You can integrate user specified values using square brackets [] inside the SQL. Eg. ...
Two list box	8/17/2006
Q: I have two list boxes. I have the code that uses both the list boxes and have to select from both ... A: Good Morning Pinto, 1. Remove the first test on length of Criteria, viz: If Len(Criteria) = 0 ...
pavement management	8/16/2006
Q: I work for Exeter NH DPW and I'm building a pavement management database. So far I have a form that ... A: Great project... A simplistic solution, might be to add a new table, called tblStrategy which has ...
calculated form field	8/16/2006
Q: I'm using MS Access 2002 and trying to build a recipe costing database where all recipes are linked ... A: Tommy Hi, To enter a value on a from you need a single function approach, like =function() ...
after append UPDATE QUERY	8/15/2006
Q: I need an append query to also update a yes/no box or a text box with "yes" "no" in the field. I ... A: Ryan, hi 2 points: 1. A query can only perform one action at a time, so you will need 2 queries - ...
Creating a field based on another field	8/15/2006
Q: I am trying to create a crosstab query with the date as a row heading, and a row heading called ... A: Rupert, hi Take [channeldata]![StatusFlag] out of the aggregation - use the MAX function to choose ...
Using a form to change the parameters of a query	8/14/2006
Q: I have created a database for a trade company that has accumalated information of various companies. ... A: No, its a good question.. :-) Lets assume your form is called frmMain and the combo keyword ...
Update Query	8/13/2006
Q: Like Maryam when I update a form I want it to update a table in another Access database. I've tried ... A: I can see some syntax issues - if that was me, then excuse me for that... Sub txtBB_OnExit Dim ...
password witout using a macro	7/31/2006
Q: Is it possible to ask for two passwords without me using the macro. One password opens the form in ... A: Try this.. Sub SelectForm() Dim sPwd as String, strMsg as string strMsg = "Please Enter Your ...
problems with password code	7/30/2006
Q: I have re done the this code and its nearly working how I want, however I seem to have something in ... A: Try insert an 'Exit Sup after the first If construct.. If UCase(strInput) = "ReadOnly" Then ...
ER diagram - Mricrosoft Access Relationship Help	7/30/2006
Q: I soo hope you can help me. I would really like to accomplish the following, but I am new to ER ... A: LeeAnn, Hello, You are embarking on a significant development project - consider it equivalent to ...
form issue	7/28/2006
Q: I have a form where I enter data. I then have a comman dbutton that opens a second form for data ... A: Robert, hello, The reason is that you have created a new record that is stored in Screen/Ram memory ...
Image scrolling	7/28/2006
Q: Using Access 2000, i have an image path stored on a form. The form uses the "on current" and "after ... A: Simon, hi, You could use the form's Timer event to slow down the loading of the imagae, so that ...
compact and repair	7/26/2006
Q: Are their any issues that arise if you continuosly compact and repair your database? Such as files ... A: Robert Hi, When you compact and repair, a number of actions are happening: 1. All delete records ...
queries	7/26/2006
Q: Here's a basic query question. When you enter data into forms, queries automatically update the new ... A: Robert, hello, QUOTE Microsoft Access automatically saves the record you are adding or editing as ...
Query/Report	7/25/2006
Q: I got a Query and report listing all the shops with their weekly sales. My query and report asks ... A: 1. You need to add an attribute into the source data that specifies which records are collated into ...
Print current report	7/25/2006
Q: =============================================== Hi, I made a form to print letters, and I want to ... A: 1. The report data source needs to be either a query, which contains a constraint based on the ...
Security feature	7/24/2006
Q: I am using MS Access 2003. Currently, i can only create password for the database as MS Access's ... A: You have two primary options: 1. Develop you own security functionality, or 2. Use Access ...
Microsoft Acccess reports	7/23/2006
Q: How can I print a report in a common readable format (word or pdf) to a CDR Will that report be ... A: Cathy, hi, What is a CDR ? You can print an Access report to Word (RTF) format using the toolbar ...
problems with password code	7/23/2006
Q: G'day Can you have a look at this code to tell me why it will not work for me, I'm trying to use a ... A: If you sub in the following code: ..... If UCase(strInput) = "ReadOnly" Then MsgBox "Well done, ...
update query	7/22/2006
Q: hello, i wanna create an update query, but i have a problem. i have a data base that i fill some ... A: You can use either a 'Save' button - OnClick event or a textbox OnExit event as you say. the code ...
How can I do it in	7/21/2006
Q: I have a table in ''MS Access", where each row has 5 fields (boxes). Let's call them "A", "B", "C", ... A: In your form all you need to do is set the 'AfterUpdate' event for say textbox 'C' to set focus in ...
autofill from another table	7/20/2006
Q: I'm creating a helpdesk/inventory system for the school system I work for. I have a form for ... A: Steve, Hi, Try using the DLookUp() function So in your form in one of the associated textboxes, ...
Updating 2 tables with one SQL statement	7/20/2006
Q: I have the code below, which is actually working thanks to all the great help from people here. One ... A: Tom Just add another SQL clause to the procedure.. Private Sub ...
adding multiple lines in access	7/17/2006
Q: To make things precise it goes like this.. in the box where the client keys in, its perfectly ... A: Then your application must be web application with an Access DB behind it... An Access Memo field ...
PDF format	7/16/2006
Q: Is there a way where i could open up a word document in PDF format in MS Access 2003 after i had ... A: Felicia, Hello, Once a document is in PDF format, provided you have Adobe Acrobat installed, you ...
Deleting duplicate records	7/14/2006
Q: I have two situations: 1. I want to eliminate duplicate records from an Access (2002) table. All ... A: Claude, Hello, Three practical ways to delete records AAA - Additional column method 1. Add a new ...
sendobject command	7/14/2006
Q: I created a sendobject macro to email a form with text in the subject line and the body of the ... A: There certainly is... 1. Create a Function in a module that returns the data you want EG: Public ...
If / Then Statement	7/13/2006
Q: I have a soccer tournament database. When scores are entered, I would like teams to advance. So for ... A: Assuming this is all via a form and that your form textbox objects are named as you say, then try: ...
list box coding for ascending or descending numbers in the box	7/13/2006
Q: I have the following code that creates a list box of numbers. How do I change the code so that the ... A: Elaine, Hello, Try setting the record source for the listbox from a table direct (eg tblQuotes) to ...
Access DB to BIG	7/12/2006
Q: Geoff, I have created an Access DB with 3.5 million records. I am now getting an error that the DB ... A: Richard, Hi, Try opening Access application with NO database. Then from the Menu bar, select ...
system date in a text box on form load event	7/12/2006
Q: Goodday Geoff, Well i have a form and a text box in that... while opening the form i need the ... A: Mitran Hello, The functions available are : Now() returns the date and time and Date() returns ...
Images on forms linked to Local Network	7/11/2006
Q: I am going to use images in my forms using VBA. In the tables there will be a text field for the ... A: As long as each user has access to the central network folder where the images are stored, they will ...
Updating Fields with SQL	7/7/2006
Q: Currently I am using Access 2002. I have two tables, one named MachineInfo which contains ... A: From your descriptive, the Machine table is the 'Parent' and the Monitor table the 'Child' table ...
Basic Subtotals for Reporting	7/6/2006
Q: I wanted to design a report that utilizes quarter of the year, department head, and applications ... A: Ralph, Hello, First build a query that pulls the data together correctly, then use the query as the ...
Serial Numbering	7/6/2006
Q: I created a Supplier List on MS Access, There is many products and suppliers, I deleted some ... A: Sher, Hi, You will not be able to change the existing Autonumber column/field, or insert new ...
Updating subforms within a form	7/5/2006
Q: hope you can help. I'm using a form to update multiple tables which is working well. The main part ... A: Jack Hello Use the AfterUpdate of your textbox with the order value Run the ReQuery action on the ...
Counting Records with MS Access	7/5/2006
Q: I would like to know how I can do the following. I have a form that I use to make bookings at ... A: Frans, Hi, Please provide a detailed table specification and indicate which column contains the ...
Calculation on report & subreport	7/3/2006
Q: I have a database that contains a CUSTOMER table, an INVOICE table, an EQUIPMENT table and a LABOR ... A: Maureen, hi, You need to ensure that the query generates the Area 3 'zero' data records, so that ...
delete row	6/30/2006
Q: I hav this data: ... A: Shah Hello, You have an impossible task for Access or any other RDMS as the data has not been ...
Can't view every feilds in a form	6/30/2006
Q: Sir,i have one form which takes values form a table.My problem is that when i navigate through the ... A: Three possible problems: 1. There are blank records in the table which cause the form to go blank - ...
Adding records from a table to a form	6/29/2006
Q: This question maybe a little confusing, but I will try to explain it the best I can. I have a ... A: In the SQL statement that INSERTS the 'other' table records to the sub-form table, you need to pass ...
Export to word check box problem	6/8/2006
Q: Tried but still nothing happens.hello, I am having problems in exporting data from my Access db to ... A: Paolo, I have checked with Microsoft and here is some code... QUOTE This example clears the check ...
Trying to get a total with out creating another colum	6/6/2006
Q: This would only happen when there are credits in the colum. I am running this of a table [Number ... A: The question was jumbled so I repeat..I have two totals in a Table, running a Query I want to take ...
Access Pivot Table-Followup Question	6/5/2006
Q: Answer - Hi Linda, Right,,,, I have found out what you're up to :) however, I am sorry, I know ... A: Linda, hi, I don't know your business process, so I am not sure of what is possible or not... To ...
Getting data from tables using sql	6/2/2006
Q: I am developing a database for guest houses, the database has a bookings table and a room table. I ... A: Adrian Hello, I assume the Bookings table (I will call tblB) has the following columns - RoomID ...
Access Pivot Table-Followup Question	6/1/2006
Q: Followup To Ques - Ques is this: I cant figure out how to add or rename a fld in the access rec ... A: Right,,,, I have found out what you're up to :) however, I am sorry, I know absolutely zilch about ...
Access Pivot Table	5/31/2006
Q: I have used the Access Pivot Table wizard to create a pivot table that opens up in Excel. The ... A: Linda, Hello, I assume you have a crosstab query in Access that you are linking into Excel via ...
VBA	5/29/2006
Q: The purpose in this tring of code if to allow for my clients id# to be unique in a sense that it ... A: Aaron, hello, Access unfortunately does not feature triggers (as does SQL Server for instance), ...
Need Help Printing Labels	5/26/2006
Q: Does anyone know of a good resource for info on printing inventory labels. I need to create a label ... A: Tom, Use MS Word as the main program, with a datasource of the Access table or query. Word has ...
accidentally created 2 different versions of MS Access	5/25/2006
Q: Win XP Pro MS Access from Office 2000 [Premium SR-1] Today I appear to have accidentally ... A: Roark Hello, Windows allows single file names per folder, so somehow you must have inadvertently ...
Totals for colums	5/25/2006
Q: I have colums but I want one total per colum at the end of the report. How do I do this. Second I ... A: Troy, hello, Totals ------ 1. insert a Group footer (or Header), and select the field that you wish ...
MS Access	5/21/2006
Q: What are the advantages and the disadvantages of using MS Access over MS Excel from the viewpoint of ... A: Greg, hi, Consider using Access when storage, processing and retrieval of quality data is a primary ...
MS Access Calendar-driven report	5/19/2006
Q: My data: Name OffSun OffMon OffTue OffWed OffThu OffFri OffSat AdamM Y N N N ... A: Firstly, I have stored the data in a normalised DB table, viz: tblMechanicsDuty Mechanic ...
Row and column	5/18/2006
Q: How can I change row into column? A: Sinisa Hi, Using VBA - create a target table with the correct new field structure. Create a ...
fixed lenght text file import for Access	5/16/2006
Q: Hey Geoff, I'm hoping you can help me out here. I am trying to import a text file of fixed record ... A: John, hi, How about two radio buttons to make the choice, the you can use the value of the radio ...
Creating Unique Reference	5/11/2006
Q: I have a database in which the three important fields (for this purpose) are: RecordID ... A: A small subroutine could be used to do that retrospectively using the Autonumber as a base: Sub ...
Validation Rule & Validation Text	5/10/2006
Q: Geoff, I am using 2003 Access. My database has a form for entering contact info. I have my ... A: Use the afterupdate event of the textbox, and enter CBF along the following lines: Sub ...
searching?	5/10/2006
Q: I hope i explain this correctly. Sample - I have created an address form with: Name, company, ... A: SHaron hi, The easiest solution is to place a button onto your form, using the button wizard: ...
Date format	5/9/2006
Q: I have recently developed an MS Access based database. The records in the primary table on based on ... A: Treasa, hi, Text searching works from left to right, so your text field of mm/yyyy will not deliver ...
Confict Message	5/8/2006
Q: I have 2 access forms linked to the same Query (which links to 2 MySQL tables), one form is a list ... A: Chris, FYI - I have seen the same sort of behaviour with SQL Server back end tables. Have you ...
What do I need to learn?	5/6/2006
Q: I have a numerical analysis project for which I don't know how to proceed. I have made a database ... A: Access 2000+ has a size limit of 2GB, so depending on your final record size you may breach this. I ...
Access test selection	5/5/2006
Q: I'm trying to build a form that will let the user; Select how many questions they want from the test ... A: Jeff Hi, This is a serious bit of development outside the scope of this site; if you want to ...
Counting records	5/4/2006
Q: 1. I have a database with N1, N2, N3, N4, N5, AND N6 columns, each one has numbres from 1 to 51. I ... A: I will assume your table is called tblMyTable Build a base query (qryMyQuery) such as SELECT ...
Where condition on Open Report on Macro	4/27/2006
Q: Hey Geoff So I have a form that the user is entering data with. Once they have entered the data I ... A: Ennis Hi, My approach would be to make the query get all its information for the WHERE predicate ...
Year Planner	4/25/2006
Q: I have an access form which I need to add another page to (not a problem), but I need this page to ... A: Lynne, Hi, You could use a second table to hold the user_id, date/time and log entry. Now build a ...
Change data type	4/23/2006
Q: Geoff: I have been searching the internet for how to change the data type of a field in an MS ... A: Two things... 1. Change Exit Sub in the 14th line to Exit Function 2. In Module design, from the ...
Tabel normalization in Access	4/18/2006
Q: I want to set up a database in which each person can have mutltiple workouts on the same day, each ... A: Table 1 - Person - PK = PersonID Attribute data - Name, Addresss... Table 2 - ...
access 2000 query design limitations	4/18/2006
Q: 1. Specs: yes I checked that already and I am far from a violation. I.e. suspected offending field ... A: Teddy, Hello, Could this be a limit imposed by Access QBE in the Win2000 environment ? I don't ...
access 2000 query design limitations	4/17/2006
Q: GEOFF - MYSTERY IN BRIEF: I have designed a complex query [all aliases of the same table] in access ... A: Teddy, hi You can retrieve the Access specification from the onboard help - select the help / Index ...
Reservations	4/16/2006
Q: Perhaps my question is much too simply for someone of your expertise... However, I'm trying to use ... A: Mike, hi, Its all in the data modelling.. You will need a table for rooms - hold all the attribute ...
Microsoft Access	4/13/2006
Q: I saw your code below, and would like to know how to modify it for comparing two tables. For ... A: I have made some mods as requested, assuming that you have 3 columns - ContractID, Aaaa, Bbbb in ...
PDA + MS Access	4/13/2006
Q: I run a retail catering outlet, and I have for the last year kept a 'book' entering names of ... A: Paul, hi, PDAs are a new area to me: however... You may use Access as your database (ie. the ...
Database Report Generators	4/12/2006
Q: Do you know of any inexpensive REALLY SIMPLE to use database report generator programs? We have ... A: Bruce, How about exporting the spreadsheets in CSV format from Lotus then link the csv file (or in ...
Linked Tables	4/12/2006
Q: I have an Access 2003 db which has the tables in a separate db - linked. (Our Terminal Server is ... A: Karen, If the relationships exist on the back-end database, then you are protected and fine - no ...
Drop Down Boxes and Queries	4/8/2006
Q: I have 3 drop down boxes. the first 2 are just lists of values, but the third I want to customize ... A: Use the After-Update event on both combos 1 & 2, and reset the rowsource propoerty of the 3rd combo ...
Moving Average on MS. Access	4/4/2006
Q: I have a data series on prices and would like to make moving averages from those data. Eq. Price ... A: Arianto, hi, One approach is via a recordset. You will need a column in your table for populating ...
Access Epression	4/3/2006
Q: How can I pull, at random, 500 records from a query of 1000 records, each record having a unique ID ... A: It would take me a couple of hours to develop a solution, which is beyond the scope of this service, ...
Setting up the date format at a prompt to only require a year	3/31/2006
Q: Simple question? I have setup a query and I set the query to prompt and ask for a year, yet the ... A: Delores Hi, Access expects as a minimum, a day, month and year to furmulate a date, so the trick is ...
Moving data from an subform to mainform bound field	3/31/2006
Q: I have a Form/Subform setup. On the subform I have an unbond field "EstCostTotal" which calc the ... A: Place a button on the Main form, and then place the following code inside the click event of the ...
Multiple selections from list/drop down	3/30/2006
Q: Using Access 2000. Have a table with list of names (called [Employee]) and a table consiting of ... A: For the 'Click' event of the button, after selecting you will need code along the lins of the ...
Append data to different tables	3/28/2006
Q: long time I have not asked for your help and based on passed experience I am sure you are the only ... A: Paolo, hi, Could you send your email to gmjohns@fastmail.fm - I will answer this on Friday. Kind ...
Table size	3/27/2006
Q: How can I determine the size of a table in MS Access? A: Orlando, Hi, Tricky question.... I couldn't find anything on the internet after a brief search, ...
Form Calculation	3/25/2006
Q: I am fairly new to Access and have developed a database to keep track of my farming activities. On ... A: Tim, First a little advice - use combos to look up static data and make selections; use text boxes ...
Database Design help Needed	3/25/2006
Q: What I require is help on ERD for the database design ( Entity relationship diagrams) we run a ... A: Michael hello, There can be a primary entity, lets say 'Project' The immediate secondary entities ...
Type Mismatch in Expression	3/24/2006
Q: i have a linked table (to an excel spreadsheet). i have a query that is using that linked table ... A: Christie Hi, This is a well known problem for Access developers. The best solution, is to append ...
Deleting dupliate records	3/24/2006
Q: I have a main table in which new excel data is imported into periodically with new information. ... A: Herman, hi, How about bringing all the data into a working table (#1) in Access, then condition ...
Sorting in a specific order	3/23/2006
Q: Geoff, I have a database that one of my managers wants sorted in a very specific and unusual order. ... A: Bobbie, hi, A trick that I have used when faced with this sort of data is to incorporate some of ...
Forms	3/23/2006
Q: I'm trying to build a Rental Management Database for a property holdings company. The difficulty I'm ... A: Marelle hello, First build the form(s)for editing each specific table, ensuring that the Tenant_ID ...
Import .csv	3/22/2006
Q: I am importing a .csv file exported from an AS400 system. I am trying to automate the import. I need ... A: You need to set up an import specification, then use this in the TransferText command. To build a ...
Lead Zeros in a Graph	3/22/2006
Q: I am doing a graph in the report section. My X-axis is a text field that contains numbers such as ... A: Yes, I agree, this is a real problem.... I have also gone into the Chart and changed the X-Axis ...
MS Access 2003	3/21/2006
Q: Actually, I probably didn't explain it good enough. My objective is to group all senior students ... A: You may use queries in a nested set of recordsets as follows: dim db as DAO.Database, rs1 as ...
MS Access 2003	3/21/2006
Q: I'm working in an Access DB with contains record for terms in college. Each record has fields (among ... A: Treasa hi, Two queries should do it. Query #1 'Q1' SELECT StudentID, Sum(CreditHours) TotCredHrs ...
working in access with forms/tables	3/20/2006
Q: I am creating a 'time tracking' system that will take user data entry and drop the data into a ... A: Bev hi, With the form in design view, check that 1. Navigation controls are enabled 2. Additions ...
Protecting my database from my boss	3/10/2006
Q: I want everyone to have access to the databases I have created (with your help) however, my boss who ... A: Karen hi, There is a white paper on Access security on my website - www.gmjohns.fastmail.fm, ...
Query information that may be Null	3/9/2006
Q: I am having a problem pulling data from a query. Here is the problem, I need to assign a text box ... A: If "qrytblSort" is a query then it sure won't work - you'll need to use a recordset such as: Dim db ...
Query information that may be Null	3/8/2006
Q: I am having a problem pulling data from a query. Here is the problem, I need to assign a text box ... A: Brian hi, Try this Dim REV As String REV = Nz(DLookup("[Revision]", "qrytblSort")) If ...
Forms Filtering For	3/7/2006
Q: I have build a form that builds a table that is used to keep up with information that needs to be ... A: Use the form's filer property To create a filter expression, construct a WHERE predicate constraint ...
Adding a Form's Comment Ssections to a Rreport	3/7/2006
Q: Geoff do you know of a way I can operationally add information to a report from a database comment ... A: If you build a query, based on the main table and joining in the look up tables for the drop-downs, ...
Access 2000 DateAdd Function	3/6/2006
Q: First of all, I'm a dedicated beginner with access and appreciate your patience. My database.mdb is ... A: Here is a SQL query that will extract those plates that will expire within 2 months of today. I will ...
Quarterly Reports	3/4/2006
Q: I have a monthly report that tracks the number of workorders by month, and category. I would like to ... A: Karen Hi, Build a table that defines the quarters, with dates that relate to the work-order date, ...
Linking to Excel workbook what is password protected	2/28/2006
Q: From MS Access how do I import or link to an Excel workbook (specifically a named range in the ... A: Sharon, hi, How about... 1. Set up a new workbook 2. Use Excel macro recorder to record the VBA to ...
sql statement	2/28/2006
Q: tblparticipants contains all info regarding participants. There are approx 13,000 records. Along ... A: Elaine, You need to run 2 queries, one for each update: #1 IN Tracking UPDATE tblparticipants As ...
MS Access 2000, Rounding and Formatting	2/27/2006
Q: I download data from mainframe. Cost are stored as 0000075 or 0000120, where the first is 75 cents ... A: Mike hi, Regarding converting your raw data - you may use the CDbl() function with CLng() so the ...
sql statement	2/27/2006
Q: I have two tables tblparticipants with about 15 fields and tbltrackingIn with 2 fields and ... A: Elaine, Some questions first... 1. Do you want a view (in a form, for example) showing the summary ...
Linking to Excel workbook what is password protected	2/27/2006
Q: From MS Access how do I import or link to an Excel workbook (specifically a named range in the ... A: Sharon, hi, I think you should be able to link an XLS sheet in the normal way, in Tables tab, right ...
Access2000	2/25/2006
Q: I have about 5 years of Trainers Habits converted to TRUE FALSE numbers In Excel 2000. I would like ... A: Gene Hi, I don't understand what Trainers Habits are, but that's an aside.. If the primary key is ...
City/County Table	2/24/2006
Q: I would like to be able to enter the city name in one field and have the next field automatically ... A: You will need a table listing City and County, I will call this 'tblCityCounty' I will assume on ...
Dos Copy file renaming with Query field.	2/23/2006
Q: I have a situation where I need to copy an exported text file to a network drive and rename it using ... A: Brian Hi, You can use the regular file handling commands for copying, deleting (Kill) and renaming ...
MS ACCESS Forms with dates	2/22/2006
Q: Geoff: I'm confused about using dates with forms & date calculations. I have a test DB I am using to ... A: Andrew, hi 1. You only need to declare a variable if you need a new variable in memory. If the data ...
MS Access	2/19/2006
Q: I have 2 tables - Journeys and Zones (a suedo taxi service). Relationship is 1 zone to many ... A: You can use a LEFT Join between the Zone table on the left and the Journeys table on the right, ...
Access 2000 queries	2/18/2006
Q: I asked another expert but her solution does not work. Access 2000 queriesHow would I make a query ... A: Raymond Hi, Julie's method works for appending new records but not updating, for the reason you ...
Recordset	1/29/2006
Q: How to creat a recordset in MS Access A: You can choose either DAO (old style - but still excellent) or new style ADO... Here are both.. ...
Let me re-phrase the question	1/27/2006
Q: I have a database of seven tables and the first table has a one-to-many relationship to the second ... A: Rich hi OK.. So you will have a main form for your top level, then successive nested subforms to ...
I am creating a database that...	1/25/2006
Q: I am creating a database that has a pyramid of one-to-many relationships. To enter my data, I have ... A: Rich Hi You will need to use VBA to manipulate forms and data, and this is therefore very much a ...
Random Percentage	1/25/2006
Q: I have the following SQL statement to return a random number of records (30 per this example). ... A: Natasha, hi I think you will need to build a process comprising several steps, viz: 1. create a ...
MsgBox() Function and return results	1/24/2006
Q: I have been loking at the MsgBOx() function in Access and have been wanting to put a message box to ... A: You can use the MsgBox() function as you require. I tend to integrate an IF statement with the ...
Access Database repair/recovery	1/24/2006
Q: I have an access database that was created in an earlier version of access. I have Access 2003 and ... A: Dave, Hi, Open Access 2003 - no db From the menu, Tools, Database Utilities / Convert Database ...
Report footer section in VBA	1/22/2006
Q: Mr. Geoff: Good afternoon. Is there a way I can access all the sections of a report at runtime? I ... A: David hi, I haven't any experience in controlling reports as you suggest, so this is really my ...
Access 2003	1/20/2006
Q: Object: to connect, via ODBC, to SQL Issue reporting database and view data in Access. I have ... A: You need to check the system times on your PC and SQL Server: For the PC, you will see this ...
Reports in MS Access	1/19/2006
Q: I have recently been designing a database, but I am having one small problem with the reports ... A: Craig hi, You probably have either: 1. A report width plus margin set up that extends beyond the ...
MS Access DDL	1/18/2006
Q: I am developing a program in Visual Basic where i need to know the Field Names and there Datatypes ... A: In DAO you can exploit a set of collections covering: Tables Fields Properties amongs, many others ...
Access 2000; XP Windows, 2000 Excel	1/17/2006
Q: I have 5 years data in Excel that is converted to TRUE FALSE, numbers, letters ( OR,JA ) There is 7 ... A: Gene Hello, Your problem is that Excel permits different data types in the same column. To succeed ...
Table design	1/17/2006
Q: I am having trouble getting my database started. Here is my situation. I have a client who has ... A: You could start with a number of base tables tblPractice Practice_ID Practice_Name Speciality ... ...
Forms/Reports	1/12/2006
Q: Please find enclosed SQL as requested. SELECT Products.ProductID, Products.ProductName, ... A: Peter Hi, Please try the following SQL: SELECT a.ProductID, a.ProductName, a.Units, ...
Need Help Getting Started	1/11/2006
Q: Do you have or can you suggest a standard scope or database design worksheet that I can use to get ... A: Joyce Hi, I recommend that you borrow or invest in a good Access Developer Handbook, such as that ...
Using visual basic to move to a subform	1/10/2006
Q: Could you help me with an issue. I have a Access form which also has a subform within it. What I'd ... A: Ray Hi, Use the combo Change event to trigger the action, then use the .SetFocus action to effect ...
DoCMD syntax	1/9/2006
Q: I am sorry, but Dennis Cassøe wasn't able to answer your question First - thank you for any ... A: Andrew Hi, 1. The 'filter' is equivalent to the WHERE predicate in an SQL statement, so NO, ...
Forms/Reports	1/8/2006
Q: The information on the main form is all correct, i.e. when stock levels get to zero or less, the ... A: Peter, You said " The report gets it information from a query based on 2 tables Products and ...
Autofill a field based on another field	1/6/2006
Q: I am very new to Access and any help would be appreciated. I have been through the entire Access ... A: Robert hi, One of the fundamental precepts of database applications is that data is stored ...
Autofill a field based on another field	1/6/2006
Q: I am very new to Access and any help would be appreciated. I have been through the entire Access ... A: For completing the form, you may use DLookUp() The syntax would be somthing like the following in ...
Apply filter to chart	1/4/2006
Q: I have generated a chart and now want to view this be an additional category. The chart displays ... A: Sorry, add a parameter clause at the head of the query, as below: PARAMETERS [Enter Division] Text; ...
Apply filter to chart	1/3/2006
Q: I have generated a chart and now want to view this be an additional category. The chart displays ... A: Julia, You need to effectively develop a new chart, but first develop the query so that the data is ...
Append	1/2/2006
Q: I have 4 files, 3 excel files and one .MDB i created. If I had an email address I can send these ... A: Tim Here is a simple solution, you can adapt it as you will: Paste the following code into a ...
Microsoft Access	12/2/2005
Q: How can I trap events from a graph object on a form? For example, to find which point on the graph ... A: Mark hi, Quite a reasonable request - I think an insolvable one in Access alone... Some years ago ...
requery	12/2/2005
Q: Many thanks for your previous tips. In Myform consisting of 3 ... A: Just change the oncurrent event to the same as the afterupdate ebent for the control, viz: If ...
Error Msg	12/1/2005
Q: I have forms built on MS access 2003, it contains subforms , it works in multiple computers wok on " ... A: It looks like you have a Referencing problem - the ActiveX on one PC is different to that on the ...
Compareing row dates	11/28/2005
Q: I have a query that returns a field that contains a date. I would like to compare how many days ... A: I have used a nested query, which may be a real durge to run unless you have few records and a ...
EER Diagram	11/27/2005
Q: You said you specialise in Database Design. Can you to tell me if I'm going in the right direction ... A: I have had a quick look, and offer the following advice based on the info provided... In building ...
Forms/Subforms	11/23/2005
Q: I have got a form with a subform attached, I need to get one field of information from the main form ... A: Peter, Please try entering =Parent![Product Name] in the control source of the textbox on the ...
CONDITIONAL CRITERIA IN QUERY	11/23/2005
Q: Firstly I thank you for the help you have given me till date.I appreciate it. My accounting year is ... A: Patrick Hi, In these situations, I find it easiest to solve it by writing a function or two, then ...
Forms/Subforms	11/22/2005
Q: I have got a form with a subform attached, I need to get one field of information from the main form ... A: Peter, In the textbox on the subform, enter the following in the control source property.. ...
Percentage field	11/21/2005
Q: I just created a db where I have a field for customer discounts. When I type in .3 I am expecting ... A: I have set up a table / column and can't replicate the problem. FYI my table / column settings were: ...
VBA (syntax error)	11/18/2005
Q: Greetings. I am just beginning to learn VBA in MS-Access and it reported a syntax error when it ... A: Generally, a function that returns a value will use brackets, and a function that is a direct ...
MS Access 2002 - Display Fields	11/18/2005
Q: I am a long time COBOL programmer trying to create a form in Access that would display a ... A: You are very close.... There is family of functions for this sort of requirement, which you can ...
User Name	11/14/2005
Q: Sorry but not able to let it work. This is what I have done. I have copied your Function into a ... A: Paolo, hi, fGetUserName() returns your network login name: I am not sure what the PIN number is. ...
User Name	11/10/2005
Q: I would like to store a control on my database menu form named WELCOME, a control (possibly a text ... A: ACCESS - Get the logged on user name ==================================== Add the following ...
Using Excel as an Interface to Access	11/9/2005
Q: I understand that Excel can be used to as interface to Access. In other words, Access will update ... A: Michael, You need to set up an ODBC DNS for the Access database, however you can do this as part of ...
Looking up data in a table	11/8/2005
Q: I have a data set that has two aspects, the size of a pipe and the size of a nozzle. Specify the ... A: Pat Hi, You will have two tables supporting the look up of Pipe size and Nozzle size. These ...
crosstab/parameter queries	11/7/2005
Q: But it did not help. I created a crosstab query without date field.eg; transactionID,property, ... A: I have done a sample last night and got it to work fine - I can't recall your data structure ...
many to many relationship	11/6/2005
Q: Hi, I have got a database consists of four tables 1.Workshops 2.Lecturers 3.Participants (I have got ... A: Where you have a Many:Many relationship between entities (tables), you need to break this with an ...
Hiding or Displaying Controls on a Form	11/4/2005
Q: I'm trying to write an Event Procedure that will hide or display certain controls on a form but it ... A: Kim, hi, 1. you need to enable txtCreditCardType, ie un lock it, 2. you need to set the sequence ...
union/crosstab/parameter query	11/4/2005
Q: I thank you for your advise dated 27/10/05 for preparing a union query to populate values under ... A: Patrick, hi, Could you try entering the dates in US format, MM/DD/YY As a point of interest, SQL ...
Running an Update Query Based on Completion of a Control on a Form	10/31/2005
Q: On a form, I need to have a yes/no control (check box) be checked automatically when a user ... A: You could update all existing records that weren't checked by running a query like this... UPDATE ...
Data Model	10/30/2005
Q: Develop a simple data model for the members of a student clubwhere each row is a student. for each ... A: Virinox hi, In the table you will have a primary key (PK) which may be [StudentID], any foreign ...
MS Access Database Query	10/28/2005
Q: I have around 24 tables each displaying a list of costs for a certain month in the past 2 years. So ... A: Tom Hi, I would suggest that you structure your data as follows: 1. Use a new single table to hold ...
Access	10/28/2005
Q: I'm a police officer in Indiana and just completed a database with information that officers can use ... A: John, Your application needs a reference to that file. You will need to open up the Modules ...
store computed value in table	10/27/2005
Q: I am desperate for a way to sum a (large) number of variables from one table and either store the ... A: Larry, Its no problem to store variables into a table - all I need to know is where the variables ...
crosstab query versus reports	10/26/2005
Q: Geoff, I have created a database to enter details of expenses incurred for vaious properties (A, B, ... A: You could use a default Table to populate each ABC.. option, then UNION query this to your ...
automating my report	10/25/2005
Q: Windows XP, Access 2003 SP2 A brief synopsis: I'm quite good at excel (not an expert) but have ... A: Try this... Private Sub cmdok_Click() DoCmd.OpenReport "rptcontactdata", acViewNormal ...
Print files using a list box	10/25/2005
Q: I have a list box on a form which lists excel files on my PC. Is there a way I can print these files ... A: Paolo, First a function to establish if excel is currently running: Function IsExcelRunning() As ...
Multiple Selections in an Option Group	10/21/2005
Q: Geoff, I am creating a rather simple database to track customer complaints. I am somewhat new to ... A: Ronnie Hi, Decide how many different categories you need to cut your analysis by: for instance, ...
Show the student's Homeroom teacher on Form.	10/21/2005
Q: I am a teacher and I am building a database for myself. I have a table for HomeRoom Teacher ... A: If the table where you store the Homeroom Teacher to Student relationship is called tblHRxS, with a ...
Automatically Create Records - 1 for each student, etc	10/21/2005
Q: I am a teacher and I am building a database for myself. I have a table tbl_Attend and I want to ... A: I'll provide some overall guidance on how to approach your requirements. You can add records to a ...
ACCESS Form Objects	10/15/2005
Q: I have been trying to figure out how to send a combo box select to a report from a Dialog Box which ... A: Referencing objects in nested forms for any purpose (reports, queries etc) is not the easiest task ...
Access Fields	10/14/2005
Q: I'm running ME and Access 2000 and entering fields in the design view. I understand the total ... A: Here are the MS Access Table/Query limits - it will be one or more of these. For further ...
Calendar control	10/13/2005
Q: Can you help me to solve this problem? I have a access data base with several tables. From some ... A: Leandro, buenos dias Could you describe how the combo box is meant to inter-react with the calendar ...
Avg. Report	10/8/2005
Q: I have a table with these fields: Item, Date,WC,QPPH,ActPPH,QSU,ActSU,Scrap "Item" is a field that ... A: Russ, hi You will need two queries, one to work out the averages and the other to display the last ...
Creating a Boolean(Yes/No) field in VBA	10/5/2005
Q: I need to create a Boolean (Yes/no)field in VBA requirement Table name :Test Field name : Supplier ... A: Raman Hi You need to set the field DisplayControl property. Here are the values and ...
Adding DateModified and TimeModified to Access Record	10/4/2005
Q: I want to add DateModified and TimeModified to Access Record to my records in Access. I followed the ... A: Tom Hi, All I would do would be as follows: 1. insert a field in your table called [LASTUPDATE] as ...
Access printing reports	10/3/2005
Q: I need to be able to cycle through every report in a project print them all out and repeat the ... A: You're nearly there.... Public Sub tyr() Dim db as Database, rpt As Report , y AS Integer Set ...
ADJUSTING REPORTS	9/29/2005
Q: I have created a report and now I need to add a column with page totals and grand totals. I have ... A: 1. Open the report on design mode. 2. From the toolbars, select the button to show all Fields - ...
access	9/23/2005
Q: i was wondering if it's possible that you can help me with an access program that i've created. I ... A: Sue, What is your question concerning Access ? FYI - Via this forum, I can only answer specific ...
Passing multi-selection from ListBox to table fields	9/22/2005
Q: I am using Access 2003. Summary: I want to pass one or more selections from a ListBox to a table ... A: Richard, hi, Its all do-able, just need to be clear on exactly what you are trying to achieve... ...
Access 2003 Database-viewing who has DB open	9/20/2005
Q: Geoff, Is there a way for me (the Admin/Owner)to see which person in my workgroup has a shared DB ... A: A starting point would be to open the locking file (xxxx.ldb) and view the entries... This will ...
open report in vba	9/5/2005
Q: I have a table (tblbills) with fields billno: primary key billdate: shortdate ( british date) ... A: Patrick Hi, The fact that it works on the PK is encouraging and leads me to believe this may be a ...
can't open in a access data base file	9/4/2005
Q: I can't open in a access data base file. When I try to open the file an error message displayed ... A: Thomas Hi, The file is probably an Access 2000+ version and you are using Access 97- version. ...
MS Access 2003 help needed	8/31/2005
Q: Geoff, I am not very familiar with advanced database design and don't know anything about VBA. I ... A: I would suggest that you do the following: 1. Build a table for your 'preferred' Subcontractors. ...
I have created a combo box...	8/29/2005
Q: I have created a combo box that lists 4 columns of information and once a description is picked I ... A: You cane achieve this very simply, with a little VBA code behind the form I will assume the combo ...
SQL question!	8/29/2005
Q: My name is Assaf and I have a problem building an sql statement: I have a table called tblSched ... A: Assaf Hello, I have prepared a solution to help you, but I am not able to upload it right now. I ...
Filter on lookup table	8/27/2005
Q: I have a large table of info I have built up over a period of time but when I try to apply a filter ... A: Mike, hi First, how do they work.... The structure of a Look-Up table field is as follows: The ...
adding up fields	8/27/2005
Q: I am using ms access 2000. I have a table which has the fields: Product Cost, Shipping Cost, and ... A: Joel Hi Assuming the User is working via a form. I will assume the names of the 3 textboxes are: ...
Form - Several Users	8/26/2005
Q: I am looking to create a form where the first person enters data, then perhaps emails it or a ... A: You are designing a workflow system, which is fine, and you should be aware that this will be ...
Question about Access function	8/25/2005
Q: I would like any web page or reference resource to use MS Access function for example how to ... A: 1. Access onboard help has full documentation of its functions. If you want help off the web, I ...
Random selection in a query	8/24/2005
Q: I have a data set which contains x-y location coordinates, date and time for an animal. There are up ... A: I will assume your table is structured as follows: Name: tblStats #1 - RecID - PK #2 - Location - ...
comparing fields in two data bases	8/24/2005
Q: I have two data bases in Access that have a similar field Primary key number. One dbase is the ... A: I will assume the two tables are as follows tblMaster MasterID - PK - LONG tblCopy CopyID - PK - ...
storing planned and actual data	8/23/2005
Q: My access db is about event management. I have been able to complete the part where all "planned ... A: Usually in commercial planning systems, you would do as you have suggested, ie. have a planned table ...
One of my forms is not working as expected.	8/22/2005
Q: Geoff, I have been developing a database for my company to be able to track their project ... A: I normally open forms with the command DoCmd.OpenForm "frmYourFormName" and this never fails ...
Connection with SQL Server	8/22/2005
Q: There are 2 computer named WJB1 and WJB2. SQL Server is installed on WJB1. Now i want do make ms ... A: 1. Set up your ADP on WJB2 as you said. 2. Create a DSN (to allow ODBC) on the SQL Server on WJB1. ...
Memorial Database	8/20/2005
Q: Access 2000 - I need a database to track golfers, sponsors, single donations, people having dinner ... A: I would suggest, based on the info provided, that you will need the following tables: tblContact - ...
Crystal Report Cross Tab	8/19/2005
Q: I have a problem with a crystal reports cross tab report.... I have made a usual cross tab report ... A: I have no experience of Crystal Reports, however, assuming the underlying database is Access, then ...
Access 2000 - problem handling a large number of fields in a table	8/18/2005
Q: This is my problem: I have a report I need to prepare monthly. This report has 124 line items, ... A: Delores Hi, Why can't you use a single table with possibly the following columns AccountType - ...
Date Add problems	8/3/2005
Q: Hey I am trying to pull info from a flat file and then store it in an Access table called ... A: 1. Variables 'a' & 'b' need to be of Date/Time data type for a start 2. You will need another ...
Date calculation in MS access 97	8/2/2005
Q: I am looking for the number of days between [start date] and [end date] with out counting the ... A: Paste the function below into a module, then call it where you need the number of weekdays like ...
Information from a listbox or combobox	8/1/2005
Q: -This works great but once the color is chosen, there are other answrs that I want to go into other ... A: Use the control 'After Update' event to do these sort of actions. Eg. If the combo is called ...
Information from a listbox or combobox	8/1/2005
Q: I am using Access 2003. What is the best way to approach this: User has a form with several ... A: 1. Set up your table ready to store the values you want to store. 2. Build the form based on the ...
Checkboxes or MultiSelect	7/28/2005
Q: I have beent trying to puzzle out how to go about this. Here's what I am trying to do: For each ... A: Try this... I will assume the listbox is called lbxNames and the target with the comma separation ...
Good morning, I am attempting...	7/26/2005
Q: Good morning, I am attempting to autofill a text box form by using two pieces of data from two ... A: You probably need to use the 'AfterUpdate()' event of the Materials combo: ie. after you make a ...
MS Access query display	7/18/2005
Q: When I query my database table, it displays as this. ID Colour 1 Red 1 Blue ... A: Solution 1. Add a Sequence column to the table 2. Build a Cross-Tabbe query as follows: TRANSFORM ...
Serial number	7/15/2005
Q: Hallo! I am using MS Access to develop a data base for serial numbers. These serial numbers are ... A: Buenos Dias Esteban, Hablo poco poco Espagnol... We can use text manipulation directly in the ...
Using VBA to pass listbox selection as query parameter	7/14/2005
Q: I have a list box that allows the user to choose many different items. I want to use whichever is ... A: The solution I would adopt, is to generate a string of values to be selected from, based on the ...
Access Report Table of Contents	7/13/2005
Q: I have created a report in access 2000 that is about 320 pages long and broken in to categories, ... A: Its not totally obvious - but it all looks fairly straight forward.. How are you activating the ...
Programming access to perform a keystroke in code	7/12/2005
Q: Using Access2003. I am trying to set up a database that will get data from a serial device and ... A: Sounds really interesting :-) In a few words - I haven't a clue, but Can you try using the Timer ...
Relational Data Base	7/11/2005
Q: I am new to SQL. For example, a large relational data base is created in Access XP - normalised. My ... A: Hock Hello, I don't fully understand your question / problem, but I will try none the less... A ...
calculating specific dates within a date range	7/8/2005
Q: My DB stores data about programmes(events). My DB is normalised. I store the StartDate, EndDate and ... A: Ahmed Hello, It should be feasible... If you have 3 columns to define StartDate EndDate DayString ...
Crosstab Queries using parameters	7/6/2005
Q: When using a crosstab query an error message is returned, "Property Not Found", if using parameters ... A: I have used the following crosstab query, with a parameter, with success. Could you check your query ...
access formula	7/5/2005
Q: I have a table of numbers and in the fields I have numbers like the following: 12345\0000056789, I ... A: You cane create a query, building new columns for each segment required, of the original field (I ...
Automatic preferences	7/4/2005
Q: I wonder, is there a way to set some preferences from a code on a database start up? Example: I ... A: 1. You can use the expression DoCmd.SetWarnings False to prevent the query ...
A database design approach	7/2/2005
Q: I'm hoping you can give me advice in yielding a set of results from 3 different tables 1. Table 1: ... A: You will need a UNION query compbining two sub queries - the first will be those cust/areas from ...
forms for entering report criteria	6/20/2005
Q: I have to make a form to enter report criteria that has two text boxes one for a start date and one ... A: I will assume the 2nd list box relates to a second code, CapSumReportCode2 - I similarly use xxxx2 ...
Access-VBA	6/19/2005
Q: I am an intermediate level programmer at best. I am currently running Windows XP with ACCESS and ... A: Sure its possible - though MOD and ROUND are already on board. You may care to trawl the net... I ...
sql update state	6/17/2005
Q: I'm using the .execute UPDATE tbl1 SET fld3, fld4 WHERE fld1 = me.text1 AND fld2 = me.text2 to ... A: Try the following syntax .execute "UPDATE tbl1 SET fld3='" & Xxxx & "', fld4='" & Yyyy & "' WHERE ...
macro to email report filtered by salesman	6/7/2005
Q: I have a large query which I must e-mail to approximately 60 salesmen, each filtered out to their ... A: I have plenty of sample code on a memory stick, but nowhere to plug the thing in .... Grrrrr. I am ...
Access collect data into one new row	6/7/2005
Q: The following situation in a Access DB (a little bit changed to make it easier to explain): Table ... A: Try this 1. Open the table in design mode and append the new field - this is miles easier than ...
Access XP	6/6/2005
Q: Geoff, I created an Access database in NT. In NT, I was able to shift/enter into the database and ... A: There is a database property that needs to be set. I am away from home and unable to access my ...
Microsoft Access Reports	6/6/2005
Q: First of all, Thank you for your last suggestion, it worked great. This question is about summing ... A: It should work... Could you check that the names of the fields as they are held in the report ...
Access 2003: How to auto-resize an Image Control based on the image to be displayed	6/2/2005
Q: I have developed an Access 2003 database that has a form in it with the following controls: ... A: Sheree, hi, Of course you can easily adjust the dimensions of the image control on the form - what ...
Access 2003 - how to add file browse control to a form	6/2/2005
Q: I have developed a form in an Access 2003 database where I have a text field that the user types in ... A: Sheree, Hi I answered a similar question recently - hope this helps... Regards Geoff :-) ...
Access output ptinting message	6/1/2005
Q: I have created an application that builds and displays html pages by recalling code stored in ... A: The command phrase should be structured as follows: DoCmd.OutputTo acOutputReport, ...
Numbering	5/31/2005
Q: I've got an assignment which includes making invoices. These invoices have got to be numbered ... A: I would create a table with one field and one record that stores the last invoice number. Then ...
Dates in MS Access	5/30/2005
Q: Here is myI have a report that contains these four fields: Course Name: which is the name of the ... A: In the Query builder, build a query based on the table with all the fields.. Now in the ...
Access Report	5/29/2005
Q: Geoff: This is a very trivial question for you, I'm sure. I'm new to Access, and am unable to print ... A: Nick Hi, Its difficult for me, with the info provided, to help find out what's gone wrong, but can ...
address labels with access	5/29/2005
Q: I AM TRYING TO PRINT TWO LABELS, ON ONE PRINTOUT, FOR EACH SELECTED ADDRESS WITHOUT HAVE TO RUN A ... A: Hank Hi, This is a bit of a cheat, but it works... :-) Lets say your table with the addresses is ...
Controls	5/27/2005
Q: I am sure this is a simple question but I need a little extra help. Is there a control on MS access ... A: The MS Office dll - MSOxx.dll where xx depends on the version you are in - 97/9/10 etc., contains ...
Room Nos	5/27/2005
Q: Geoff, I have residents in a table: Name.......\|RmNo Mrs Perkins\|1 Mr Perkins-\|1 Mr Allen---\|2 I ... A: I built an example table called tblRooms, with 2 columns, RoomNo and Name I then built a function, ...
Access Database Setup	5/5/2005
Q: Access 2000 I distribute Access databases to many users within my company and use a "setup" ... A: Josh Hi, Concerning the first point, this question should be targetted at an expert in Windows XP ...
To retrive from the table 3rd Max	4/20/2005
Q: My table is that : Emp_Salary ( emproll, name, salary) i want to built a query third maximum salary ... A: I would do this using two queries: The first query returns the top 3 salaries, then the second ...
Query	4/19/2005
Q: I have built one of my best databases ever, even using VBA. However I am stuck on setting up one ... A: Your basic query, producing ALL records, will look something like... SELECT T1.Xx, ... T2.Yy, ... ...
No of forms and queries	4/16/2005
Q: I am using Ms-Access 2003. It is not allowing me to add more than 34 forms. My problem : I have ... A: Did you try building a Data Sheet form besed on a query ? Then assuming you launch this from a ...
Display Only Top 10 Values in a record	4/15/2005
Q: I keep a database that tracks overtime. I can display the sum of overtime of all employees,for a ... A: One way is to use a nested query: So the outer query is the query for the actual report you are ...
Ms Access Cross-Tab	4/15/2005
Q: Sorry I didn't explain myself properley. Someone has given me a Cross-Tab report which they have put ... A: The values inside the crosstab data will be aggregated (ie sum, avg, count, max, min etc) - if you ...
Attachments	4/13/2005
Q: I was asked if I could extend the database I have with the possibility of adding attachments. It is ... A: Marc Hi, It is possible to add images as ole objects, however... 1. they are very voluminous and ...
Automatically update the second combo box	4/12/2005
Q: I developed a form with two combo boxes (topic and subtopic1), I want to update the subtopic1 ... A: Grace hi, I have built a sample application of related combos because I get that many questions ...
Truncate rightmost two digits	4/8/2005
Q: How do I truncate the rightmost two digits in every data field in my Access table? A: I have built a VBA subroutine to perform this task - please be aware that each field datatype will ...
Table Properties	4/7/2005
Q: I am using Access 2003 and I was wondering if you know of a way to change the properties of a table ... A: Here is a code subroutine that copies a table, then creates two indexes, the primary key, and ...
Max capacity of MS Access	4/6/2005
Q: My laptop crashed, and with it my Access 2000 program. In addition, an upcoming project will ... A: Access 2000+ can go to 2GB, however I would be loath to use it at that level. I generally like to ...
Parameterquery	4/4/2005
Q: I would like to give a parameter to a parameterquery in a VB script (in Access) so you don't need a ... A: Here is how you would handle a parameter query in VBA: Create a new procedure in a module... ...
Parameterquery	4/3/2005
Q: I would like to give a parameter to a parameterquery in a VB script (in Access) so you don't need a ... A: If you are working with VBA then there is no need to worry about parameters, just use the WHERE ...
Capture of image	4/1/2005
Q: I want to capture image of the my webcam inside of database (access), is this possible? I tried ... A: Ronaldo Hi, I would advise that you: 1. Store the image as a gif or png format file in an images ...
MS Access ADO	3/30/2005
Q: Scenario on using Microsoft Access: Fields in "Student" table: 1) StudentID (Primary key and Auto ... A: The strategy is 3 steps - insert the new record - break the connection and remake it - ...
Question	3/29/2005
Q: Sir, I have a question that is Please explain the distributed database, with the help of an ... A: Distributed versus Centralised: In concept you will always default to a central database on grounds ...
MS Access Transfer Text	3/28/2005
Q: I use Transfertext macro to export a query to text file.IN the file name field i use this ... A: You need to use a specification to change the default export format. To create a specification: 1. ...
Security in split database	3/25/2005
Q: I've set up security via Access security wizard. Then I split it into appl and data MDB. Now the ... A: I think you will need to: 1. Build a new MDB backend using the security wmf file, then, 2. Import ...
Query to update customer info	3/24/2005
Q: I have two tables: * master customer table * customer updates. The Customer Updates table can link ... A: Bill Hi, The SQL structure would be as follows: UPDATE [Customer Updates] SET [ContactName] = ...
VBA question	3/24/2005
Q: I have two tables, one called cave-ins, the other work orders. Cave-ins: caveID – autonumber ... A: If you use a form and sub form structure you can achieve this quite easily. Assuming a Cave-in ...
VBA SQL Append query	3/15/2005
Q: I'm trying to create an append query in VBA to update only records which are new to an existing ... A: Revise sub.... All the best - you were very nearly there.... Public Sub RecordSetFromSQL() On ...
Import Data into Access	3/15/2005
Q: I have a database in SQL Server. I want to import it into MSAccess(2000 or XP). can you help me ... A: 1. Create an empty Access MDB file in the location you want. 2. Use Data Transfer Services in SQL ...
MS Access Pivot Table wizard	3/14/2005
Q: I can see the option for Pivot Table when I start a new form. However, I can not add anything to ... A: Virginia Hi The table is where the raw data is stored, and a 'Pivot Table' is a respresentation or ...
Access Linking Tables	3/11/2005
Q: Geoff, I am new to access and am in the process of developing some databases for my company. The ... A: Concerning the link table between Company and Work Category, I would remove the autonumber and make ...
DateTime in database	3/9/2005
Q: I am developing a Access db for my website and I needa a date and a time of the article added or ... A: In the case of ADDED records, set the date field default value to Now() In the case of UPDATED ...
printing two copies	3/7/2005
Q: I'm sorry, forgot to tell you that there is a parameter attached to the query that runs this report. ... A: Elaine, If you are trying to control queries from VBA, I would be inclined to use the DAO QueryDef ...
printing two copies	3/4/2005
Q: How do I get this code to print two copies. This is on the command button's click event Private ... A: Just repeat the DoCmd.OpenReport... Private Sub cmdPrintTrayReport_Click() On Error GoTo ...
Number format & alignment in list box	3/1/2005
Q: How can I format numbers in a list box? Is it possible to align them to the right? A: Antony Hi, I think not... You can adjust the column widths, and you can add virtual columns to ...
MS Access INSERT INTO	2/27/2005
Q: I have problem with Union Query. I want to insert one table data to another table but it is not ... A: On the face of it your SQL is correct. A UNION query is not use for appending records from one ...
converting reports to Word or other e-mailable formats	2/25/2005
Q: I have an MSACCESS 2002 database and have created several different reports. I am a relative new ... A: This is a short question with a long and wide ranging answer - you are about to open Pandora's box ! ...
Crystal Reports Formula	2/25/2005
Q: I am trying to use a formula based on a field which has a value or not in Crystal Reports. I have ... A: Wouter Hi I haven't a clue about Crystal reports, but if this is VBA, then you need to amend it as ...
Conditional macros on number of records returned in query	2/24/2005
Q: I have a datbase for booking rooms. I have a query that runs that then checks a user's input to see ... A: Macros get you so far, then there comes a point where VBA is the logical way to go: To start you on ...
(Error 3734)	2/24/2005
Q: Hope you can help. We have 3 users' DB and very often we get the message: "The database has been ... A: Ensure all are logged out, then open the db. From the Menu select Tools / Security / User & Work ...
Pass thru query to Oracle db	2/22/2005
Q: WHERE LN_SERVR_VNDR.DDA_VNDR_ID = '16386' AND REO_CASE.DISPN_DATE BETWEEN '01-jan-2004' AND ... A: Let assume the form name is frmMain and you have 3 text boxes, named: txtID, txtDateIn & txtDateOut. ...
check box problem	2/22/2005
Q: Geoff- I'm creating a health care database to keep track of patient information. One of the fields ... A: The data structure for this part of your DB will include 3 tables; the patient, the full list of ...
Change Data Type	2/21/2005
Q: How do I create a MS Access macro that will change a field's data type from text to a date/time ... A: I have written a small function which will do this: to run this from a macro, simply paste this into ...
MS Access Help	2/20/2005
Q: was wondering if you could help, The problem is very simple, I have a Simple table that I am using, ... A: The query below should do what you want: you will need to substitute in the values where I have put ...
Access adherence scheduler	2/20/2005
Q: I am facing some problem related to scheduling the people in shift. We have 3 shifts coming in our ... A: This is an involved piece of work, and I suggest that if you are not comfortable programming in VBA, ...
Queries in a video rental database	2/18/2005
Q: Hey Geoff, I'm creating a database system for a fictional video rental company. I've got tables, ... A: Ian Hi Could I suggest that you take some time to peruse and absorb some excellently prepared ...
MS Access	2/17/2005
Q: For compiling the Code in PHP or ASP we need a server…… What about MS Access? Or MS Access don´t ... A: Jose Hi All VBA is interpreted - it is not compiled into machine code, but gets converted in real ...
union query in Access97 misses data	2/16/2005
Q: I have an Access 97 database that has gotten too large. I split it into multiple databases, link ... A: MS Access does have an overall memory limit - Access 97 is 1GB (Access 2K - 2GB), so it may well be ...
Access 2003: how to print table design info	2/16/2005
Q: Is there a way in Access 2003 to print what I visually can see on the screen in Table, Design Mode? ... A: I did something on this a few years ago, but couldn't find it, so I built a new module... You will ...
Password Protected Button	2/13/2005
Q: this is me again and I need your help! How can I add a password to a button, i.e. whenever someone ... A: If you add the following code behind the button click event the required functionality should be ...
query	2/12/2005
Q: i am a rookie at access and im just learning how use the program with some help here and ther if ... A: I found this an interesting challenge and built a little App to demonstrate filtering. Send me your ...
Access 97: how to extract unique values in a column	2/11/2005
Q: I have an Access 97 database. In one of the tables, I would like to do 2 things: 1. For a specific ... A: You could use the find duplicates wizard to generate this, however the SQL will be along the lines ...
Why error? "Cannot find a record...with key matching fields..."	2/11/2005
Q: "The Microsoft Jet database engine cannot find a record in the table 'tblOrgs' with key matching ... A: It would appear that your have two tables: tblDocs is your main table and tblOrgs a look-up table or ...
MS ACCESS database	2/9/2005
Q: I build a database using MS Access, When I delete a record, My Numbers get out of squence order. ... A: Databases in general and tables in particular are not really designed for keeping numbers in ...
MySql ASCII merger	1/29/2005
Q: Geoff, First off, thank you for your time! I have an ACSII (132 column...fixed length) file that I ... A: If you link the text file directly as a table in Access, you will be lead by the wizard into ...
Query Results To A String	1/25/2005
Q: What is the easiest way to get the results of a query into a text box? Thanks... A: Try using the DLookup() function. Use it as follows in the 'control source' property of the text ...
Access print button	1/24/2005
Q: I have a table with 6800 dealerships and a table containing participants who receive awards. I have ... A: Build a query that shows all the fields required in the report, and apply constraints to limit only ...
canned control?	1/24/2005
Q: Geoff--Thanks again for your help last week. I am good to go with my new found SQL knowledge. ... A: I know exactly what you mean - I have not come across such a control. My inclination is to build a ...
Query design	1/21/2005
Q: I have 2 database tables (exported from a text file) with a course ID and a student username. One ... A: Try this:- SELECT T1.[Course ID], T1.[Student Username] FROM tblFirstEnrollment AS T1 LEFT JOIN ...
complex query	1/20/2005
Q: Geoff-- You're the man! Believe it or not, yesterday I actually came close to what you have shown ... A: I built an initial query using an alias T1 to determine from an aggregated query, the MaxDate for ...
complex query	1/20/2005
Q: Geoff, I'm trying to build ONE Access query that returns a donor's most recent gift w/ additional, ... A: Try this... SELECT T2.Donor, T2.Date, T2.Gift,T2.Note FROM Donations AS T2, ( SELECT T1.Donor, ...
VBA Double Click Event	1/19/2005
Q: In VBA of Microsoft Access, for listbox, if I want to use the double click event, why does it need a ... A: Well I didn't know either, until now :-), so.... But basically it is a property of a command button ...
Geoff, Thank you for your...	1/18/2005
Q: Geoff, Thank you for your answer. I have a followup at the bottom of this message.I have two ... A: I have looked through the code and see nothing that looks offensive... Could you send me a mini ...
I have two problems with subforms...	1/14/2005
Q: I have two problems with subforms in MS-Access. I have a subform (continuous forms) for entering ... A: Humm ! 1. This sounds like a Me.Requery somewhere: Requery, refreshes the form from the table and ...
How to compare two tables?	1/13/2005
Q: Is there a straightforward way to compare two tables and write out the records that don't match to a ... A: Bill Hi Yes & No ! Yes - all you need to do is write two queries that generate the non-matching ...
List tables in Database in a listbox	1/11/2005
Q: I have a database that imports an excel file, combines it with data from our mainframe, then ... A: I would include in the table (that holds the XLS file name) a selector (Yes/No) field, that is ...
Chosing database software	1/7/2005
Q: I'm new to databases. I am involved in collecting an collating information about one hundred or so ... A: As a fellow yachtsman, I'm on your side ! If you are simply going to have two lists, one of entrant ...
Delete record from multiple tables	1/5/2005
Q: Good morning Geoff, I would like to delete records from multiple tables using VBA. I now use delete ... A: Create yourself a procedure (or function if you want to execute from a macro) in a module as ...
Using MakeTable query for export ?	12/17/2004
Q: I have been searching my head off on the internet for finding an answer to my question. Hope you can ... A: You will need to firstly link the table, then copy the table, then populate it with the new data. ...
ms access forms	12/17/2004
Q: i have developed a databse in ms access.the database opens by doubleclicking an icon on the ... A: Patrick Hi I had a squint around the internet and found this... Prevent users from disabling ...
How to show all dates in a subform for a timesheet	12/16/2004
Q: I have a timesheet layout with a detailed subform where users can enter data. They want to see all ... A: If you set the join properties between the Dates table and your timesheet table to 'All records from ...
Data in multiple tables....	12/15/2004
Q: Good morning. I have three tables with with a common StateName and StateId fields. I am trying to ... A: Hommy, I dont fully understand the problem. Have you built a query joining the 3 tables together ? ...
sort order of form in datasheet view	12/13/2004
Q: I am using Access97. I have a form which has a subform which opens in datasheet view. I created a ... A: Steve Hi, Sorry for the delay - been v busy and been struggling to work out how to do it.. My best ...
Setting up criteria dates from different tables	12/12/2004
Q: I'm trying to create a query using two tables of different databases: Both are related to sales and ... A: 1. Concerning tables in two different databases - link the tables into your current Access ...
Queries with date field	12/10/2004
Q: Do you know of any way in Access XP or 2003 to create a query that will select a date range. I have ... A: The query would be: SELECT T1.Empl#, T1.[Last Name], T1.Status, T2.[Hours], T2.OT, T3.Vac, T3.Dis, ...
Would like to create a macro to search a string (field1) for the text in another field from a different table and write to a new table	12/9/2004
Q: Using Access 2000 I need to cross-ref a complex text string against a simple text string to find ... A: Katie Hi You could probably achieve most of what you want using query builder with some functions ...
Reservation Availability	12/8/2004
Q: I have been working on a reservation database for a small Bed & Breakfast. I am almost done. ... A: If you have request start date, and a request end date, then you can write a query that tests these ...
listbox items selected	12/7/2004
Q: I'm using Access 97. I have a form with two listboxes. The listbox on the leftside has a list of ... A: Interesting... If you have a single table of names, and a column called 'SelectName' which is a ...
Query with dates	12/2/2004
Q: I went into the SQL view of the query and pasted your suggestion. I have the following error come ... A: Sorry, could you try: SELECT strNonConformity, strType, Sum(numQuantity) AS SumOfnumQuantity, ...
Stock control	12/2/2004
Q: I wanted to transform the Null values into "0" in the query that performed the stock control. I have ... A: Use the Nz() function as follows: if the variable is Xxx - and it could have a value or Null, and ...
Access and Query	12/1/2004
Q: I have used access for the past 7 years and have a problem that I have never encountered up until ... A: I would look at the SQL script to see that that includes all the columns (as opposed to the asterisk ...
VIEWING REPORT	11/30/2004
Q: I need some help and I do not know almost anything about access, I am only data entry. I have given ... A: Sonia Hi, It sounds like the problem relates to the report not finding or only partly finding the ...
Query with dates	11/29/2004
Q: I have a query for which I wish to retrieve records between 2 specified dates using "Between [Type ... A: Try this.. SELECT strNonConformity, strType, Sum(numQuantity) AS SumOfnumQuantity, strSupplier, ...
ADO	11/29/2004
Q: I'm stuck in the middle of a project. I have an Access database and I need to update or add data to ... A: Try something along the lines of the following.... Sub UpLoadData() Dim Cn As ADODB.Connection ...
PrimaryKey & ForeignKey	11/27/2004
Q: Give me briefly with example Primarykey and Foreignkey. A: Relational databases (RDB) comprise tables of data, each table is designed to hold only data relted ...
organigram	11/24/2004
Q: i would like to make an organigram in my database to show the roles of some people. i tried for two ... A: Joey Hi Database tools such as Access do not lend themselves very well to hierarchical data, so ...
Display Record Horizentally in Access 97 Report	11/23/2004
Q: I am Sanjib I am making a project, which have a report thT displaying the employee monthly ... A: Sanjib Hi, You should build up a final reporting query using a sequence of queries, each of which ...
ER Diagram	11/22/2004
Q: I want to draw an entity relationship diagram to represent a hotal room reservation database system. ... A: Daniel Hi, The primary Entities are going to be: 1 Room: - RoomID, Room Num, type, number of ...
ER Diagram	11/22/2004
Q: I want to draw an entity relationship diagram to represent a hotal room reservation database system. ... A: Daniel Hi, Are you looking for a tool ?, or Are you looking for help with the systems analysis ? ...
Access 2000	11/18/2004
Q: I am creating a make-table query where I want to make some fields that will have the currency data ... A: If you use the expression CCur(123.456) this will create a number into a currency data type so in ...
Run-time error on form	11/16/2004
Q: I have set up a database for monitoring users of our financial system. with the user ID being ... A: Change the code to the following.. Sub Combo103_AfterUpdate() ' Find the record that matches ...
refreshing data on a child one-many form	11/12/2004
Q: I have a one to many relationship on a form/subform. I have buttons on the subform that basically ... A: You need to use the .RecordsetClone property. Easiest is to look it up in onboard help, but the ...
pop up	11/10/2004
Q: Good morning Geoff, Is it possible to have a form pop-up after a date has expired? thanks for your ... A: Eric Hello, Yes - use the Form Current() event, and test the required date against the system date, ...
differentiating records in an access subform	11/10/2004
Q: I work for a small charity that delivers PC training to the over 50s. We use MS Access 2003 to ... A: Roy Hi This is not one of Access's strong areas - basically, assuming your subform shows several ...
Mask for ZIP	11/9/2004
Q: I'm just learning Access on my own, and something has me puzzled about masks. If I want the ZIP ... A: Michael Hello, I have tried playing with a few ideas... If you are concerned about presentation ...
Access Macro problem	11/9/2004
Q: It is an Access 2000 DB and we're using a Win 98 OS. We had a database that got corrupted and it ... A: Landon Hi, The problem may be any of the following: 1. A table has lost a column 2. A table has had ...
Access Programing	11/8/2004
Q: Access 2000 I am trying to convert numbers to text. Example 954.25 to Nine Hundred fifty four ... A: Try this... Paste the functions below into a module Run the function SpellNumber() Kind regards ...
Combo box and View	11/6/2004
Q: I am just the friend of the previous person who asked you the same question. As you said we need ... A: Concerning Cascading Combos: The value of the Cities combo is not cleared when you change country - ...
Combo box and View	11/4/2004
Q: I'm interested on how to clear the selected value of combo box when i go back to design view or ... A: You can set the 'Default Value' property of the combo box to an initial value as per the data in the ...
Books	11/3/2004
Q: I find it immesely useful. I now request your suggestion for books on the following subjects so that ... A: I can thorouhly recommend perusing www.wrox.com - there is much to choose from, and I think you ...
MS Access 2002 sp2, SQL, setting output variable for a union query	10/19/2004
Q: I have been trying to get a memo field returned from a SQL union. The query returns a 255 character ... A: I am not sure from your description whether Exp3 is a text or memo data type. In any event the ...
tables	10/18/2004
Q: Hope you are fine. I have 3 tabels : tblTrainers, tblTrainees, and tblCompanies Each record of ... A: If you set up the table tblAddress with an autonumber primary key (PK), then you can have a 'foreign ...
Absent MS Access driver	10/17/2004
Q: While trying to add a user data source in ODBC,I can't find MS Access Driver choice in the list ... A: Omer Hello I would try the Office 2000 set-up, and select the options relating to ODBC, and allow ...
DlookUp Error	10/12/2004
Q: What is wrong with this expression? (Access 2002) VoyageExists = Nz(DLookup("[ID]", "Delivery", ... A: Try.. VoyageExists = Nz(DLookup("[ID]", "Delivery", "[Delivery.VesselName] ='" _ & ...
print problems	10/8/2004
Q: I have got a problem with printing a rapport. This is the problem: I have made a search Form, when ... A: Gooie middag Theo Try changing the equation round, viz: Macro Rapportopenen Rapportnaam: ...
Relational	10/6/2004
Q: I have four tables' 1. the Clients details (One set of details) 2. The Communication Aids details ... A: If you go for a forms solution, this will probably resolve the issue: Your are going to build 4 ...
saving images in Access	9/30/2004
Q: .i noticed that u don't accept beginners questions .but i'm not beginer ..but it is my first time to ... A: dig your handle.... :-) Could I persuade you to NOT store the images in the database, but store ...
Running Access queries with Time fields	9/29/2004
Q: I hope you can help (at least with the Access part of this question) I have written a software app ... A: There are one or two functions that may prove useful to you, you can look up the full syntax etc in ...
Access and Excel 2000	9/27/2004
Q: This is something I know you kind of answered for me already but I'm running into a similar problem. ... A: In Excel you can use the =VALUE(A1) function to convert text to a number - where cell A1 contains ...
Database design for photography site	9/26/2004
Q: I wish to build searchable database using Access 2000.In 'IMAGE TABLE' I have following fields : ... A: In my experience it is best to segregate types of attribute with a view to simplifying future ...
Multiple criteria query	9/23/2004
Q: I have a form with 5 textboxes that need to query three tables. The user will type in the criteria ... A: My approach would be to build the WHERE predicate inside a function, based on the values in the 5 ...
Calculating Inventory Balance	9/22/2004
Q: I was making a database in Access XP , a sample for inventory control financial base I need to ... A: If you are looking for spreadsheet style presentation, then it would be best to use Excel from the ...
Keyword query	9/21/2004
Q: I have been trying to put together a database to store my digital images. I think I have got the ... A: I will assume that there are 3 tables concerned, viz: tImage: ImageID(PK), ..., ..., [SelectIm] ...
Grouping in Blocked	9/20/2004
Q: Suppose I have Cargo and the cargo has boxes inside. So it is like Cargo: ID , Weight Box : ... A: Krystel Hello, You need to group by cargo ID, then in the report set the Cargo ID visible property ...
Database design - migration to SQL Server	9/9/2004
Q: Geoff, Thank you for being with All Experts. (1) I have built a database in MS Access-VBA ... A: Good to hear from you again.. 1. The easiest way forward is to introduce a new entity into your ...
automatically importing data into database searching automatically different databases	9/8/2004
Q: sir, i'm an engineering(I.T.) student doing a project on website development for iit-kgp library. ... A: You can use OLE DB to access a remote database with ADO, so assuming you have a link to the ...
ping command from ms access	9/7/2004
Q: I have devloped a small database of PCs in MS Access 2002. I used VBA to write codes for automation ... A: I would control the ping command and any related functionality in a batch file (DOS) and then launch ...
Database Design	9/7/2004
Q: I am going to try to describe my design dilema the best I can. I am creating an XP database that ... A: From your brief description, I think you will need a few more tables to build a 3rd Normal form ...
Regarding Dataentry through Forms!	9/3/2004
Q: I have developed a database for billing of salaries based on hours or days the employees worked. ... A: The neatest solution to this, is: 1. Use a single table (ie. add the fields in table 2 to table 1 ...
Date Query in MS Access	8/26/2004
Q: I am trying to create a very simple query that gives me a person's age. I have a Date of Birth ... A: In this sort of situation I build a bespoke function to calculate the persons age for any given DoB, ...
split database	8/26/2004
Q: I am working on an splitted Access 2002 database and need to add a new table. I have created a new ... A: Paolo Hi 1. Build the table in the 'data' MDB file as you require... 2. In the Application or ...
Link files with MS Access	8/24/2004
Q: I have relatively good knowledge of MS Access. I have a made a form linked to a database table. I ... A: 1. Linked Photo (jpg) I have set up a form using two textboxes for two fields (PhPath & PhFile) in ...
Checked?	8/24/2004
Q: I was surprised that Check boxes has no Checked Property! am I right???! I want to write some VBA ... A: They certainly do.... The 'value' property contains -1 = true = yes, or 0 = false = no You do not ...
Graphics on OutputTo rtf	8/23/2004
Q: Geoff, I have an app written in Access 2002 and I have reports that need to be output to Word. The ... A: Jerry Hi I think the way forward is to use Automation together with Word. I have limited ...
Data Access Page	8/19/2004
Q: have a database that tracks part number requests. These requests need to be signed off by 4 ... A: Jon Hi, I'm shooting from the hip here, but I have a gut feeling it could work... 1. Open the ...
store photos in tables	8/19/2004
Q: How to store photos in a database using acess 2000 or 2003. for example, making a table for persons ... A: The way this is normally done is to store the server / path / filename as a data fieldin the DB ...
redundant entries in report	8/18/2004
Q: Ok I have a report that consists of job task, steps, job type, hazard, safety etc. When entering ... A: Shane Hi, With your report in Design mode, select 'Sorting and Grouping'. With the dialog open, ...
Access query	8/18/2004
Q: I am building a database for grant tracking and I would like to make a query that asks the user to ... A: First tool is the wildcard symbol - *, which together with 'Like' enable you to perform a fuzzy ...
combo and list boxes as query criteria	8/17/2004
Q: In my Access database, I have created a form named WELCOME on which I have added a combo box which ... A: A trick that I use quite frequently for this sort of functionality is as follows: 1. Use the ...
copy reports	8/17/2004
Q: I copied and pasted a report, but i need to change the new one to read from a different query, how ... A: Select the report, open it in design mode open the properties dialog box ensure that the 'whole ...
Query and Data page	8/16/2004
Q: The % sign is only used for SQL Server Access doesn't accept that sign, also I have tried using the ... A: I have searched through the help system and conclude that the only 'Filtering' can be done on a user ...
Error in sub-sub form	8/16/2004
Q: I am building a client database, i have a table with general data, one with accounts and one with ... A: I would have a separate table for Company information that is independent of year, then create a new ...
Urgently	8/14/2004
Q: 1) I want some help in Microsoft access .I have some question in access. I made reports in ... A: Mohamed Hello, 1. I use Access 2000 on Win 2000 and Win XP and have also experienced this problem: ...
query issue...	8/9/2004
Q: I have a table that has four fields that may or may not contain null values - depending on the ... A: Could you try something along the lines of the following... SELECT .... FROM .... WHERE (Field1 Is ...
Report Print Options	8/8/2004
Q: Please excuse my ignorance but does this code print only the current record or does Access / VB ... A: Steve Hi, Sorry, I missed that bit - :-) The code would not select only the current form record. To ...
Report Print Options	8/7/2004
Q: I have a form that is completed by the user to produce a certificate. The report is usually ... A: The code behind the button might look like this... Sub cmdPrintReport_Click() Dim I as Integer ...
Masks	8/6/2004
Q: I'm using Access to create a management DB. I created a submask which has disappeared and I don't ... A: Andrea Hello, I don't understand what you mean by 'submask' & 'picture' ? Do you mean textbox and ...
Append Query	8/5/2004
Q: Geoff, I have 2 tables: 1)'Residents Details' 2)'Care Plan' At the moment I am using an append query ... A: Option #1 Add a WHERE constraint to the query, as below, but suitably edited... INSERT INTO [Care ...
Link tables with Schema.ini in a loop	8/5/2004
Q: I'm writing to you because this is my last resort. I have been everywhere with Google and asked in ... A: Raul Hi Try using the attached code that I use for running the Shell() command - it sounds like the ...
help, i'm stuck	8/4/2004
Q: I'm building a data base for an Air Force program and i have thought of some things i would like to ... A: Daniel Hi, Thank you for your question. Firstly the extent of your requirement is well beyond the ...
parent->child->grandchild relationships on same form	8/3/2004
Q: I currently have a form on which I have a parent child relationship. ie.. the subform contains child ... A: Yes this is doable :-) You proceed exactly as you have for the main form / child form but between ...
Sorting in a report	8/3/2004
Q: I work for a school and my MS Access database was set up for me. I've always sorted - either ... A: I don't understand what you mean by: P3, P4, KA, KB... If you have a table open, you can sort on ...
Follow-up	8/2/2004
Q: I asked you a question about a week ago about printing labels for a friend's wine cellar. Thanks SO ... A: Open up the code module (design). Fom the menu, select Tools / References. From the dialog, scroll ...
Form accessing particular record	7/31/2004
Q: I have a table I have set up for followup of problems. This table is updated from another larger ... A: Russ Hi 1. Design and save your parameter query to work independently of the form, to contain all ...
Linking Forms	7/30/2004
Q: I have an access database for a telephone directory. We are currently gathering phone records for ... A: Yes you can link a second form by using the 'After Update' event of the 'tick box'. The code is ...
Last date	7/29/2004
Q: Geoff, I have a report based on 2 tables in a one-to-many relationship: -------------------- ... A: Sorry for the delay.. Try the following query: SELECT T1.NameOfResident, T2.Medicine, ...
MsAccess 2000	7/28/2004
Q: Can I prevent additional records when a record is deleting and the database is set to a certain ... A: You can write a controlling VBA procedure that is called by the form's 'BeforeInsert' event to make ...
Developing a printer billing system	7/28/2004
Q: I want to develop a printer billing system which can be used at a college,whereby the students can ... A: This potentially is a substantial system.... :-) Some questions: 1. Is the printing to be done ...
Preventing Records from being deleted in MsAccess 2000	7/27/2004
Q: I tried it, and it locks correctly with the exception I cannot add new record, which is something I ... A: Please check that you have changed the Enabled and Locked properties for only the text-box concerned ...
Preventing Records from being deleted in MsAccess 2000	7/27/2004
Q: I am trying to prevent certain areas in a record from being changed once information has been ... A: 1. Ensure that edit is managed via a form, even if datasheet view. 2. For fields that should appear ...
Creating multiple records?	7/26/2004
Q: Please be patient with me - I have an involved question that (I think) requires that I give you a ... A: Build yourself a little VBA procedure using a recordset with a nested For/Next loop as follows: ...
Insufficient Key Information	7/26/2004
Q: I am using VB 6.0, Access 2000, TrueGrid 7.0, and Win 98R2. An ADO recordset in my project is ... A: You will find it difficult to delete rows from a table whilst directly joined in a query to other ...
Access 2000 Date Problem	7/7/2004
Q: Have been creating a database in Access 2000 for a while on and off now. Runs quieries from drop ... A: Andrew Hi The problem lies in a missing reference - VBA extensions for applications Open a module ...
Inserting Edit Text Date	7/6/2004
Q: I have a table with "Name", "Addrees","Phone","Status" fields. I have form which is bound to this ... A: If you harness the 'After-Update' event of each of the textboxes described, then use the following ...
Access query format & report	7/6/2004
Q: I have a table which I want to pull data from to a report by using a query. The three fields I want ... A: You need to build 6 queries, the first 4 return the counts for each of the 3 Nonconformance sources, ...
Dlookup	7/5/2004
Q: but I still cant find the how can i do it, I have to find a way for example that gives me the record ... A: How about using DMax() to look up the ID of the record concerned nested inside a Dlookup() to get ...
Access 2000	7/2/2004
Q: I have a field on a form that currently has a validation rule on it to not allow the user to input ... A: Cristie Hi Probably your easiest solution is to have two forms - one for regular users and one for ...
Relating a calculated field back to the table.	7/1/2004
Q: I'm using MsAccess 2000..I've created a table to calculate salaries. I input an amount into the ... A: The way I would do it in the situation you have described, would be to have all textboxes on the ...
Manipulating data layout using MS Access 2000	7/1/2004
Q: I have a table that I need to flatten using MS Access 2000. The table consists of two columns. The ... A: I believe you will need to write a VBA procedure to populate a working table. You will need to ...
Zip code search	7/1/2004
Q: Geoff, i have a zip code tabel with customers and want to search on a zip code and show the ... A: Dennis Hi How about searching on zip with wildcard as there may be several zip codes with no ...
NEW DATABASE (ACCESS)	6/29/2004
Q: IF I WERE TO CHANGE A MANUAL PROCESS TO AN AUTOMATED PROCESS USING A DATABASE SUCH AS ACCESS WOULD I ... A: Brigette Hi You haven't given me much info to help with... Access will run on a standalone PC - ...
Relationships	6/29/2004
Q: In Access 2000, I would like to make a relationship between two different numbers so when I type in ... A: Stephen Hi 1. I believe you need four tables, with the primary control keys as follows, viz: ...
Records distributed by date	6/27/2004
Q: Geoff, I have a list of records in one table: Date entered 01/05/04 17:58 04/05/04 19:58 05/05/04 ... A: Lets call these tables tab1 and tab2 respectively You will need a left join on tab1 to return all ...
Running Macros from Other Databases	6/25/2004
Q: Geoff - I inserted that in the event procedure on click event for my command button but it is ... A: Melissa Hi The code is well used - there must be a formating / typo issue. The target database ...
Running Macros from Other Databases	6/24/2004
Q: I don't know VBA, so just tried to paste the code below into the code on a command button as an ... A: To start with just put the following code behind the 'OnCick' event of your command button: Set ...
Running Macros from Other Databases	6/23/2004
Q: I have multiple Access databases that will need to be updated daily by running one macro in each ... A: Certainly ! Below is code to do that - All of this is VBA in modules - if you are not sure of how ...
Generating a Report	6/22/2004
Q: I am actually using Microsoft Access for Windows 95. I hope you might still be able to help me. I ... A: You undoubtedly have two tables, at least, connected using an INNER JOIN - this type of join shows ...
Database Design	6/21/2004
Q: Database Insert Anomaly I'm designing a database with a one to many relationship, here is the list ... A: If you open the table Contact in design mode, then select the FK field, say, Location_ID. At the ...
Report on mutiple criteria	6/21/2004
Q: In the statement suggested by is asking user to enter the science marks or DOB etc. can one can use ... A: You could increase the level of complexity as follows: SELECT * FROM tblYourTable WHERE [date of ...
HTML within Access	6/20/2004
Q: I am trying to create an html script like the one below BUT I would like the seperate field in the ... A: You will need a module and procedure in VBA to do this, plus of course, two related tables, #1, the ...
Printing DB in Colums	6/18/2004
Q: Geoff! Okay. I have 11,000 records in table format, in both Excel and Access (2000)formats. The ... A: You need to use the Access reporting feature. Build a query that provides the information you need ...
SQL	6/18/2004
Q: me working in a IT company and i need to write a SQL statement to retrieve the records from the ... A: You can reference the combo box directly in the query as follows: SELECT ..... FROM ...... WHERE ...
crosstab queries	6/17/2004
Q: I have a table with 5 columns which are test scores ( e.g. test1, test2...test5 ) with values from 1 ... A: If you are trying to transpose data, then I would use Excel and the Paste Special feature, which ...
canceled previous operation	6/15/2004
Q: ------------- the code: ========== Dim freeDays As Integer Dim selectedRule As String selectedRule = ... A: Your DLookup looks fine. I'd like to question your system logic.. You have declared the var ...
SQL	6/14/2004
Q: i hope u will help me out with this question also. i have 2 table which do not have primary keys. ... A: You will need to use separate queries for each task you are trying to achieve: In the first case ...
select query	6/14/2004
Q: This doesn't seem to work for me at all. I need to display each instance of duplicates, and and I ... A: The only way I can productively interact with you is by passing SQL scripts - you can view the SQL ...
Parameters to a query	6/13/2004
Q: I have a query with parameters but would like to send parameter value from a form. I can't say that ... A: Using SQL, your existing query will look something like: PARAMETERS Abc Text ( 255 ); SELECT * FROM ...
wording numbers	6/13/2004
Q: I have created an access database that produces an invoice. I now need to be able to produce the ... A: You will need a special function that performs this translation. However this would take an amount ...
queries	6/11/2004
Q: i want to construct queries but they are not appropriate. i have 2 tables. forecast and actual. ... A: You need to use a LEFT JOIN between the two tables, with 'Forecast' on the left, and 'Actual' on the ...
reports	6/11/2004
Q: i like the explaining appraoach. very good. but then SELECT * FROM tblSalesOrders WHERE [Period] = ... A: If you need to build in more criteria, then it is the 'WHERE...' predicate in the select statement ...
creating records	6/10/2004
Q: i have created a database which has taken me forever anyway i am quite proud of it and at work we ... A: Could you take some time to describe more fully, the database and what you do (want to do) in plain ...
reports	6/10/2004
Q: i want to construct a report by using queries. example, in my form i have let the user to choose the ... A: We need to put the period constraint into the query used by the report. 1. So, lets say the basic ...
Duplicating the primary key Error	6/9/2004
Q: If the primary key is duplicated, an error will have occurred from which a unique error message is ... A: I ran a little test and found the error number to be 3283 Here's the code I used - please check ...
Splitting the data base	6/9/2004
Q: I have a lot of append queries that append tables in another database. I want put this both these ... A: I assume you will be working with linked tables between your application MDB and your data MDB. ...
wat r these codes?	6/8/2004
Q: i found these codes on the web about opening the MS Access form form the destop itsseld. but im not ... A: Place this all in a new code module. You can then use the function in your code as per the examples ...
Left and Top Properties	6/7/2004
Q: how do i move a textbox some steps to the right using VBA? I tried this: Me!txt1.Left=... but it ... A: 1// FORM My system is set up with centimetres as default. and to change this setting I would use... ...
Report on mutiple criteria	6/7/2004
Q: i generated report through a form where i give criteria for date of birth between "date" AND "date". ... A: The way I would tackle this is to base the report on a query, then using DAO and the QueryDef ...
codes to extracts information	6/3/2004
Q: i have a few forms to go about doing the report. this is like a wizard. in form 1 i have 3 radio ... A: To open a form from a button, you can use the control (button) wizard to generate the code: ...
access 2000 - crosstab query totals?	6/2/2004
Q: Is there a way to get subtotals (as well as a grand total) to appear for each row and column of a ... A: A query is a flat 'view' of data and so the composite inclusion of sub and grand totals is not ...
checkboxes	5/31/2004
Q: i have a problem here. ive got a checkbox on my form called "By Customer" and i have a button called ... A: 1. The following code needs to be inserted into the button's click event If Me.[By Customer] ...
data normalization	5/31/2004
Q: Please send advice for explaining normalization in reference to breaking large table into smaller ... A: The primary objective of normalisation is to breakdown information into a series of tables that ...
rounding off in query	5/27/2004
Q: I WANT TO CALCULATE THE NET AFTER DEDUCTING IT @2% (ROUNDED OFF TO THE NEXT HIGHER INEGER. EXAMPLE ... A: Access (VB) has totally different functions and behavior to Excel. To round up in Access / VB use ...
Query Design & Relationships	5/26/2004
Q: Setup There are two tables, table A and table B. Table A has a customer list(name & adress), table ... A: Norman Hello I need to better understand the structure and data held in table B. The way I read ...
You canceled the previous operation follow up	5/26/2004
Q: xxx = DCount("[ContainerID]", "Containers", " [ContainerDemurrage]= " & Me.txtDemurrageID & ")" did ... A: 1. Ref: but this works : number = DCount("[ContainerID]", "Containers", " [ContainerDemurrage]=" & ...
You canceled the previous operation	5/25/2004
Q: DCount("[ContainerID]", "Containers", " [ContainerDemurrage]= Me.txtDemurrageID ") It says : “You ... A: Try the following syntax: xxx = DCount("[ContainerID]", "Containers", " [ContainerDemurrage]= " & ...
reports and subs in VB	5/19/2004
Q: I have a report "A" and two sub reports "B" and "C" "A" has a total field 'txtTotal' "B" and "C" ... A: 1. You should use the 'Print' event not the 'Format' event as the data is only present during the ...
using a form field as parameter	5/18/2004
Q: I would like to use a form field as parameter of a query that's started from the form, is this ... A: Yes, but the form will need to be open. You will need to refer to the form object in its full ...
Access 2000	5/17/2004
Q: I have a table that has information in it that I don't want removed. This is my "master" table. I ... A: Adding the field manually is fine ! You obviously have some constraints on the master table that ...
showing multiple selection on form without vb	5/17/2004
Q: i am using pure ACCESS no VB i have three tables [patients]->srno[PK],name,phone,etc... ... A: 1. To avoid multiples in any table, apply an index to the fields concerned and make the index ...
hide and show	5/15/2004
Q: I have a report A and three sub report B, C and D. Reports B and C each has a filter that let them ... A: I would use the GroupHeader or Detail section (according to where these subreports are located ...
Track Access Users	5/14/2004
Q: I would like to be able to track the users of access db's that i create to tell if the usage is ... A: I enclose a module that I use occassionally when I need this functionality - I hope it is self ...
TreeView Control	5/13/2004
Q: There is a control in MS Access and VB, treeview control. I want to use it to view my data in the ... A: You will need ..windows\system32\comctl32.ocx installed & registered, then in Access, with a code ...
Access Databases	5/12/2004
Q: I have a number of tables spread throughout a variety of databases (.mdb) which are in a number of ... A: One easy way would be to link the tables from the remote dbs to the central one - shouldn't be a ...
Changing ChartTitle using VB	5/12/2004
Q: my form, in ACCESS, contains a chart (MSgraph.chart.8) I do need to change the chart Title and the ... A: Antonia Hi I found the Access chart tool weak and complicated, and for several years have ALWAYS ...
DateAdd	5/11/2004
Q: Not even sure I know how to ask this! I have been asked to modify a db that I didn't create which is ... A: How about setting up another column, say, Year2: Year(DateValue([EffFrom]))" with the criteria ...
I need help with this error message	5/11/2004
Q: I am having problems with my access database. It has been up and running for over 2 years and all ... A: Cynthia Hi There are potentially many issues at core here. Firstly ensure that the data is in a ...
Populating a Report	5/10/2004
Q: I have designed a report and I need it to be populated with just the current record from the ... A: Use the button to change the underlying SQL of the query. You will need to use DAO. If this ...
MS Access report error	5/8/2004
Q: I'm new to Access and have run into a wall. I have a linked Excel table and have created several ... A: It is important that in your third step, ie. bringing the data together, that you have a common ...
Report and Text Fields Sql	5/7/2004
Q: Infos about me ______________ Access 2002 computer science graduate C# but never VB and hate how it ... A: Jaqueline Hi Q1 - Set the visible property to False (and then back to True) using VBA snippet under ...
tables in memory & diff. between versions	5/5/2004
Q: 1. Is there a major differrence between access97 and access2000/2002/2003, in the database behavior ... A: There are very major differences between Access 97 and 2000+, including file format, as well as use ...
Report from one Record	5/3/2004
Q: I want to create a preview report button from a form where the reulsted report has to show only the ... A: Sam Hi If you base the report on a query, then we can rebuild the SQL behind the query on the fly ...
Access 2000	5/2/2004
Q: Ok...I'm happy to do that. For future reference, are you basically going to create a select query? ... A: Sorry I spelt your name wrong :-( Could you send me a zipped up version of the following please: ...
Access 2000	5/1/2004
Q: I am super embarrassed to have to ask such a stupid question...but here goes. I have VERY LITTLE ... A: Firstly what you want to do is not easy, so dont feel bad about it... Basically, a table stores ...
two tables to a form	5/1/2004
Q: I created a form using the wizard and I added the fields where I want the form to get linked to ... A: Method 1 - Build a query comprising all tables then make the query the control source of the form. ...
After I finish the application, what??	4/30/2004
Q: ... I just finished my database. OK but I didn't take care of anything else, like security, network, ... A: I can only help you with the first part. With the database dialog active, select menu, tools, ...
cascading combo boxes	4/27/2004
Q: Followup To Question - Hi Geoff, I really hope you can help me with this problem (which I don't ... A: Send me your email address and I will send you a simple example - you will then need to look at the ...
cascading combo boxes	4/27/2004
Q: I really hope you can help me with this problem (which I don't think is really too difficult) ... A: Gavin Hi In the code above cboRec is your country combo, I will assume that when you make a ...
using Access databases though the internet	4/24/2004
Q: Is there a way to tell Access to import tables from a remote computer on the internet? I want to ... A: Yes you can connect to databases over IP (internet) If you have an IP address of the web server / ...
MS Access formula in table design	4/24/2004
Q: I hace designed a mileage table using milein and milesout as two of the fields and want to make a ... A: Use a query. In the QBE, select the table and the columns you want, then find a free column and ...
List box to open files	4/22/2004
Q: I would like to add a list or combo box on an Access form to view and open (with the double click or ... A: I would suggest using the Common Dialog Control (Form Design - Toolbox) however I have no experience ...
UPDATE QUERY	4/21/2004
Q: I am trying to update a field named "CODE" with three values, based on another field "ENROLLMENT"; ... A: With the constraints you have shown you will need to do this in 3 separate queries, though, I am not ...
Table Comparison	4/19/2004
Q: Geoff, I use both SQL Navigor (direct to Oracle 8) and Access. In Oracle I can display the ... A: Ian Hi Access doesn't have these sort of power functions and the Access query is the simple end of ... <