About Experts Sitemap - Group 54 - Page 16 2014-12-16

Using MS Access: sharing database, record locks, open mode
record locks, open mode, access security: Access security is set via use of an MDW file. When Access opens it looks for a filename.mdw (same filename as mdb). If it doesn t find it it looks for a system.mdw and takes its security from that. If you want to remove the security delete the filename.mdw....

Using MS Access: sharing a database, utter access, simultaneous users
utter access, simultaneous users, ms works: Access is designed as a multi-user database. It has multi-user features (like record-locking) built in. Its designed for and works well with small workgroups (up to 20 simultaneous users). You should be able to find info about this at microsoft.com/access....

Using MS Access: sharing ms access with excel linked table issue, ms access databases, sharing ms
ms access databases, sharing ms, database excel: First, I don t recommend using mapped drives. Instead you should use UNC coding: (\servernamesharenamefoldernamefilename) I think this is what you are erroneously calling SQL coded. You CAN use Linked table Manager to create links in UNC coding. You...

Using MS Access: How do I show two columns in an Access combo box?, frist name, combo box
frist name, combo box, personnel database: If you want it to show in the box after it s been selected, the only way to that is to change the query to only have two columns. Make a new column in your query like this: FullName: [FirstName] & & [LastName]. Get rid of the other columns in your...

Using MS Access: How to show dates in forms by the order of time, question pool, recordsource
question pool, recordsource, order of time: I think I understand your question... and if I am reading it right all you need to do is open the recordsource for each of these forms and sort the date field ASCENDING. This is the Sort row under the field name. Just change it to Ascending or Descending,...

Using MS Access: How to show all dates in a subform for a timesheet, dates table, period number
dates table, period number, employee id: Hi Lynda, If you set the join properties between the Dates table and your timesheet table to All records from Dates table this should sort it. What you are actually doing is creating a LEFT JOIN (or RIGHT JOIN depending on the orientation of your tables)...

Using MS Access: how to not show empty spots of fields on report, blank lines, thanks for your help
blank lines, thanks for your help, empty spots: Well it depends on how you have your report setup. But what you need to look at is the Can shrink and Can Grow properties of the control. Your comments control should be one line high, by turning the Can Grow property on, the control will grow to accomodate...

Using MS Access: show fields base on selection options, preferred price, query report
preferred price, query report, selection options: KC, Many thanks for the question. I am not sure I understand it fully, but if I do, then the answer is Yes. To achieve this, in the criteria row of the query add - [PeferredPrice] Now each time the use ones the query/report they will be prompted...

Using MS Access: show and hide, good database design, text boxes
good database design, text boxes, control source: You should be able to add code to the On Format or On Print events of the subreport that changes the properties of controls on the report based on a condition. It should be hard. Would look something like this: If condition Then Me!controlname.property...

Using MS Access: How to show parentheses around text, nickname field, relative newbie
nickname field, relative newbie, focus event: Certainly not a dumb question but no question is dumb when you are taking the time to learn. Any way for this to work you must place the following code in the on_got_focus event of the control that has the tab order immediately after the control for nick_name...

Using MS Access: show records at unbound form in A97, data validation, retrieval functions
data validation, retrieval functions, haiphong: With unbound forms you have to write your own record save and retrieval functions. There are several ways to do this. Without knowing how you have done it, there is no way I can answer. Frankly, if you know enough VBA to work with unbound forms, you should...

Using MS Access: showing multiple selection on form without vb, tab control, three tables
tab control, three tables, foreign key: I almost always design my subforms ahead of time, then use the Subform tool on the toolbox to place the sub on my main form. If you have so many sub tables and you want them all on one form I would suggest using a Tab control and placing each subform on...

Using MS Access: showing multiple selection on form without vb, database dialog, visible property
database dialog, visible property, index property: Hi Nitin 1. To avoid multiples in any table, apply an index to the fields concerned and make the index unique . So table [patient_consult_reason] will have a normal unique index on the pK, plus a unique index on the combined fields reasonsid, srno. To...

Using MS Access: showing users in a shared database, database option, exit function
database option, exit function, alias: That code doesn t do what you think it does. What the code does is get the network login ID of the current user. I use that function all the time to identify the current user. To check who has the app open I use the following function: -------------------------------------------------------------------------------------...

Using MS Access: simple Access question: auto complete state names, recordsource, control source
recordsource, control source, state names: Hello Lorna, You need a table with a single column, containing all the State names. Now on your form create a new ListBox object: set the State table as its recordsource, and set its control source as the [State] field on your form. Now run it & voila......

Using MS Access: simple multi-user environment, inbuilt security, intranet environment
inbuilt security, intranet environment, shelf system: Hi There, Best idea to start with is to split the database and that involves having the tables in one db and the queries/forms/reports etc in another database. My first idea with the users would be to use the inbuilt security but with 100+ users this...

Using MS Access: simple personal info database, button wizard, photo field
button wizard, photo field, record reference: What I would do is create 2 forms. One form in continuous form mode that displays the picture and a name. The other is your info form which contains the detailed info. Let me first explain that the best way to include photos in an Access database is to...

Using MS Access: Very simple questions!, simple questions, incoming calls
simple questions, incoming calls, suburb: Jodie - It sounds like the problem probably lies in the Relationships between the Tables. First thing before we have to go deeper, check your Relationships and make sure you have dedicated Primary and Foreign Keys, and that the relationships are basic...

Using MS Access: simple/single list box selection, correct column, correct names
correct column, correct names, foreign key: First, with list boxes, you have to actively select a value. Just scrolling until the selection is highlighted is not enough, you have to actually click on the selection. Second, are you sure you are using the correct names? If your form named frm_List?...

Using MS Access: size compare to picture, database screen, document name
database screen, document name, screen shots: There are a few ways to deal with this. One way would be to create a parameters table. this would be a one record table that would store info used in processing. You would then store the base path there. But the way I would do it is to use the function...

Using MS Access: size does not reduse, access file, mdb file
access file, mdb file, microsoft access: Under Tools Database select Compact and Repair. Access does not autmatically free space when you delete records or objects. It just marks them as deleted. The Compact function frees up the space. By the way 92M is not very large. Also data takes up a small...

Using MS Access: what software should I be using?, networked pcs, database builder
networked pcs, database builder, opertunity: Dear Erik it seems you need a real database you can do it using Access or any other database builder. you have databases based on files and databases based on servers please contact me by e-mail to give you furhter directions my e-mail hersain@cesea.org...

Using MS Access: sort order of form in datasheet view, menu window, column width
menu window, column width, elegant solution: Steve Hi, Sorry for the delay - been v busy and been struggling to work out how to do it.. My best offering (after having looked briefly at datasheet properties) is to use sendkeys I set up a datasheet form and a control form (not a parent/child)...

Using MS Access: how to sort ???, tabels, xp sp2
tabels, xp sp2, uing: Its not clear exactly what you are trying to do. But if you want to sort the records, that can be done using the Sort icons. There are two on your toolbar that show a-z with an arrow showing the direction of the sort. Just put the cursor in the field you want...

Using MS Access: sorting by dates, february group, dobs
february group, dobs, special dates: First some background that might help you. Access stores dates as a serial number counting the days from 12/30/1899. How the date is displayed on forms and reports is a function of formatting. There are several date functions that you can use to manipulate...

Using MS Access: sorting problem, query expression, query design
query expression, query design, syntax errors: Hi Difficult to say really. Have you tried just summing using the calculation row rather than manually (big E in the toolbar in query design) Does that make any difference? Also I m not sure $ is the best thing to use here as I think Access may...

Using MS Access: sorting in a query, statuses, fiscal years
statuses, fiscal years, hth: If I follow you, 0001 represents Fiscal year 2000-2001 and so on. The easiest way to sort this is to create a table like so: tluFiscalYr YrID (PK) YrCode The table would look like this: YrID YrCode 1....9798 2....9899 3....9900 etc. You would...

Using MS Access: sorting within a report, arabic, expression
arabic, expression: Check the field that is storing those numbers. It sounds like its a text field, not a number field. Text fields sort character by character as you describe. Assuming it is a text field, you have 2 options. You can convert the field to a number field or...

Using MS Access: spelling correction, foreign key, spelling correction
foreign key, spelling correction, microsoft access: Probably not. Since you are not dealing with absolutes you can t match directly. You can try things like searching for LIKE *sim* to get all that have sim in the name, but when you have no real pattern to match on, its going to be hit and miss. Hope...

Using MS Access: splash screen, timer interval, database in access
timer interval, database in access, simultaniously: Hi If your splash form is the first to open, why not put a timer interval on it? If you set the timerinterval to 2000 then ontimer event to DoCmd.Close acForm, Me.Name this will close the splash screen (as long as the form which your executing the...

Using MS Access: split column, field column, fourth column
field column, fourth column, column type: shah, Many thanks for the quesiton. To do what you ask, open a query in design view and add the table that has the above field (column). I am presuming you know how to do this. In the first column type the following in the first row (Note: Chane...

Using MS Access: split column, textfile, f1
textfile, f1: Dear Zeeshah, There is a simular function in Access, but it only work on Text Imports. I suggest you export this column to a textfile and then import the textfile into access. Access will then give you the option of deciding where to split the columns etc....

Using MS Access: split column, instr function, query design
instr function, query design, rousey: There isn t a Text to columns function in Access like there is in Excel. If the column has less than 64,000 rows, I would consider copying it into excel, running the text to columns wizard, and then pasting it back into access. If you have more than 64,000...

Using MS Access: split column, reimport, hth
reimport, hth: No, which is why it may be easier to export to Excel, split and reimport. But, if the pattern is as clear as you indicate, you can use the Left, Right and MID functions to do it. 1st field: Val(Left([textfield],2) 2nd field: Mid([textfield],3,1) 3rd...

Using MS Access: split currency, currency field, converting currency
currency field, converting currency, field numbers: Well, I understand why you want to do it, but unfortunately it doesn t change my answer. What you are asking is not possible with an assurance of data integrity. You can do it in a table, with a series of queries, but they just cannot be text boxes....

Using MS Access: split database, split database, database dialog
split database, database dialog, table tab: Paolo Hi 1. Build the table in the data MDB file as you require... 2. In the Application or Interface MDB file, select the table tab in the database dialog. Right click, select link table. 3. The common file dialog opens, search for and select...

Using MS Access: spreadsheet to database, spreadsheet program, database program
spreadsheet program, database program, table structure: A spreadsheet is different from a database. Data that works in a speadsheet table may not work in a relational database. So its possible, that you will need to redesign the table structure rather than just copy the data. From there you would APPEND the data...

Using MS Access: sql, combo box, textboxes
combo box, textboxes, field names: I m assuming there is no date field in your record. The best way I can see this is to fudge a date in your query. Add a column to your query using something like the following expression: TestDate: DateSerial([Yearfield], [Monthfield], 1) You need to...

Using MS Access: sql, query window, sql syntax
query window, sql syntax, combo box: Hi If you design the query and hard code the dates into the query window, then select SQL view from the menu - you ll see the SQL syntax you can use. You will then have to replace the hard coded dates with what the user selects. Something like ...

Using MS Access: sql, datevalue, cdate
datevalue, cdate, date1: You can use the Cdate or DateValue functions to convert the string to a date for querying purposes. But frankly I would use a date field and then convert the data back to a string when needed. SELECT BellID, Cstr([Belldate]) As CheckDate, BellAmount WHERE...

Using MS Access: sql server back end VS MS Access back end??, sql server tables, server back
sql server tables, server back, customer table: Might help to know what version of Access and SQL Server you are working with. However, if you link SQL Server tables then the linked tables should work the same way as Access. Rather then using linked forms, why not use a mainform/subform. Set your mainform...

Using MS Access: sql statement, ms access forum, jtextfield
ms access forum, jtextfield, fname: Ahh this is Java code. I m not a Java coder, you asked this under MS Access forum. However, my advice still stands the proper SQL for Access would be: SELECT empNo,Fname FROM Emp WHERE Designation = Driver ; There is no need for the Trim since you...

Using MS Access: sql statement, strsql, correct syntax
strsql, correct syntax, command button: First, you don t lookup from another form, you look up from a table or query. If you have a subform, then your main form should have a combo to select the supplier. That control would be bound to the SupplierID FK. When you select the supplier, the subform...

Using MS Access: sql statement, several records, two tables
several records, two tables, batches: Elaine, You need to run 2 queries, one for each update: #1 IN Tracking UPDATE tblparticipants As A INNER JOIN tblTrackingIn As B ON A.ID = B.ID SET A.trackingIN = B.trackingNo #2 OUT Tracking UPDATE tblparticipants As A INNER JOIN tblTrackingOUT...

Using MS Access: sql update state, reccord, syntax error
reccord, syntax error, yyyy: Hi Lisa, Try the following syntax .execute UPDATE tbl1 SET fld3= & Xxxx & , fld4= & Yyyy & WHERE fld1 = & me.text1 & AND fld2 = & me.text2 & ; In the above, I assume all variables are text data type - if numeric, then omit the...

Using MS Access: sql, problem dates, exact text
problem dates, exact text, error message: Try: stsql = INSERT INTO List of Service Calls(Serial #,PRSO#,Service Date,Comments) VALUES ( & [Forms]![UnitSerialNo]![Serial #] & , & [Forms]![UnitSerialNo]![CSO] & , # & [Forms]![UnitSerialNo]![Next Service Date] & #, & [Forms]![UnitSerialNo]![Problem]...

Using MS Access: How to start with a new record, frm, wizard
frm, wizard: Hello Tania - Place the following code in the On_load event of the form: DoCmd.GoToRecord acDataForm, frmMyForm , acNewRec Where frmMyForm is the name of your form. Don Follow-up Tania I am very sorry; I guess I was being a bit presumptuous....

Using MS Access: A start off..., schedual, work flow
schedual, work flow, well thanks: I agree. The scenarion you are working with would be MUCH better off in Access. However, how to move it is a very broad question. What you need to do is work out on paper what info you want to capture and how your work flow moves. You then need to design...

Using MS Access: About starting with ACCESS, microsoft access, ms access
microsoft access, ms access, mvp: These are questions that I really can t answer. I don t know you or your abilities. I can tell you that, to learn how to use Access as an end user, should only take a couple of weeks. To learn how to use it as a developer can take several months. To really...

Using MS Access: IF statement, object names, hth
object names, hth, calc: Ok First, since you have date field, you do NOT need a months field. What you DO need is a Payperiods table like this: tluPayPeriods PayPeriodID (PK autonumber) PayMonth PayYear Payperiods (or PayHours) From there you will need to use some interim...

Using MS Access: If statement, isnull, travel request
isnull, travel request, bloat: First, Its actually not recommended that you use OLE Objects. That can cause the database to bloat in size. The better method is to store the path to the file, then link it in code. The next problem is how to trigger the test. I think you would need to...

Using MS Access: if then statement, dateadd, isnull
dateadd, isnull, microsoft access: Access doesn t have cells, it has fields. The expression you want is: =IIF(IsNull([fieldname]),[fieldname],DateAdd( d ,30,[fieldname])) You can use that expression as the controlsource of a control on a form or report. Or you can use it in a query like...

Using MS Access: if then statement, option group, zero points
option group, zero points, letter x: Yes that helps, but it sounds like your database is not normalized properly. It sounds you have a field in a single record for each of the 36 Arrow points and that would not be correct design. Your table should look like this: tblSession SessionID (PK...

Using MS Access: If statement in Access, yellow numbers, daisy chain
yellow numbers, daisy chain, category column: There are a couple of ways to do this. If the situation is as you describe where one range of numbers belongs to one category and the rest to another, then its fairly easy. Add a column to a query with the expression: Category: IIF([Field1] 4, Green ,...

Using MS Access: If statement in access, detailed explanation
detailed explanation: You need to describe this in greater detail. Where do you want the result to appear? How are you determining where the data is from? What is the process by which the data is being accessed? With a completely detailed explanation I might be able to help....

Using MS Access: If statement within query, many thanks
many thanks: George, Many thanks for the question. Try not having the talking marks , outside Expr1 and 4 If this does not work try putting the full calculation in, not just the name of the field. Finally, the IIF statement evaluates all answers, it does...

Using MS Access: if statement, condition thanks, yvette
condition thanks, yvette, melissa: Add a Column to your query with the following expression: PickMonth: Month([Datefield]) Then set the criteria for that column to the month number. If you have more than one year s worth of data you would add a year column in the same way. Hope this...

Using MS Access: If then statements within a query, e mail address, boolean field
e mail address, boolean field, text values: It doesn t work because a Yes/No (Boolean) field does not accept text values. Yes and No are VB constants that represent -1 and 0. yes and No are text strings. Btw your database is not properly normalized. Use of a lot of Yes/No fields constitutes...

Using MS Access: still trying to get this resolved, but people keep getting maxed out or going on vacation.., access macro, case references
access macro, case references, report thanks: I have some suspicions here. The fact that its printing the same info on the odd number pages with blank even pages indicates 2 things. First the blank pages are the result of some object on the report exceeding the margins. This forces Access to add the blank...

Using MS Access: stock take database and recording, database example, access report
database example, access report, stock info: Make a table with the 4 fields add a Inventory_ID field with the data type of Autonumber, and add a yes/no field called In_Inventory (Default value is true) When you no longer have the item you would have the field set to false. A simple query and Access...

Using MS Access: stock imamge library, stock image library, atached
stock image library, atached, datebase: First, you don t want to link the image to Access. All you want to do is store the location of the image file in a text field in the record. To display the image you use an UNBOUND Image control. You use the On Current event to set the Picture property of...

Using MS Access: validation rule, paracetamol tablets, inventory query
paracetamol tablets, inventory query, drugid: First, if you have a new question please start a new thread, don t keep adding to an existing thread. However, this goes back to your initial question. Reorder level is an attribute of the drug, NOT a transaction. It should be a field in the Drug table. ...

Using MS Access: how to stop write conflict msg?, message dialog, action queries
message dialog, action queries, action query: Is this a multi-user app? What exactly are you doing when you get the message? What I suspect is the issue is that you need to committ your changes before proceeding to some further action. But I would need to know the exact flow to figure out where. ...

Using MS Access: store computed value in table, automation object, analyst programmer
automation object, analyst programmer, docmd: Larry, Its no problem to store variables into a table - all I need to know is where the variables are being used ? Form, VBA ? If you have a table with ONE record, and a number of fields, each relating to a variable, then you can use the following code...

Using MS Access: store photos in database, acess 2000, artwork catalog
acess 2000, artwork catalog, store photos: The recommended solution is that you don t sotre them in the database. This just causes the database to bloat. What you do is to store the path to the image file in your record and then use a Picture control to display the image. I have an artwork catalog...

Using MS Access: store photos in tables, acess 2000, store photos
acess 2000, store photos, db table: Hello, The way this is normally done is to store the server / path / filename as a data fieldin the DB table, then use this information to display the photo on your form. On your form, insert a picture, selecting any handy picture file, then with the...

Using MS Access: How to store & save an unbound label on a form, strsql, switchboard
strsql, switchboard, button code: The only way is to add a parameters table. A parameters table is usually a ONE record table that has various pieces of data uses during processing. Create the parameters table and in the On Load event of the form use: Me.lblUpdate.Caption = DLookup( [LastUpdate]...

Using MS Access: store sum value, breakfast lunch dinner, sum value
breakfast lunch dinner, sum value, ms access: Well you have two problems here. First your database is not normalized properly. Your structure should be something like this: tblMeals MealID (Primary Key Autonumber) MealTypeID (Foreign Key) Meal Date Amount tluMealTypes MealTypeID (PK Autonumber)...

Using MS Access: storing computed value in table, automation object, analyst programmer
automation object, analyst programmer, control source: Hi Larry, My suggestion would be to have a form with 5 bound fields to the table. Fields V1, V2, V3, V4, Total. Also on the form have one unbound form which has the control source as = V1+V2+V3+V4 . When you click your button to close/save...

Using MS Access: storing planned and actual data, minute hitches, step basis
minute hitches, step basis, planning systems: Hi Ahmed, Usually in commercial planning systems, you would do as you have suggested, ie. have a planned table and an actual table. All these systems depend on rigorous processes to make them work, so you may need to take 5 and write down your business...

Using MS Access: storing a range of values, interest field, strsql
interest field, strsql, rate of interest: Hi If your form field was called [txtAmount] and interest field was called [interest] - a select statement like the following would retireive your values... Dim strSQL As String strSQL= SELECT [interest] FROM yourtablename WHERE & Forms!NameofYourForm!txtAmount.Value...

Using MS Access: string comapare, database table, hth
database table, hth, access database: Create a query on each table and add a column like this: MfgMatch: Left([mfgnamefield],5) Now create a third query that joins the 2 on that MfgMatch column. You can then turn it inot an Update query to update the field in one table from the other. ...

Using MS Access: stuck with wildcards, miscommunication, country code
miscommunication, country code, cdr: Well lets take the 001 for example. To select all numbers that start with 001 in the T1 table, I would place the following criteria in T1 s Field1: Like 001 & * This will work if the data type of the field is text. However, if the data type of this...

Using MS Access: stupid question, default account, security password
default account, security password, admin account: Hi Unfortunetly I am not a fan of Access security - overkill in my opinion. I have locked myself out of a database before - no way back in. I eventually had to revert to a copy. Did you make a back up? There are companies that will break the Access...

Using MS Access: sub procedures, windows xp, stock
windows xp, stock: Umm no. That is not the way to setup an inventory app. Units on Hand should be calculated not stored. An inventory app centers around a transactions table that records all movement of stock in and out. Your subform should be bound to that transactions table....

Using MS Access: sub to sub, access 2002, subreports
access 2002, subreports: You need to be very specific in the way you reference the control on the subform. You might be better off trying to duplicate the value from the underlying data. Hope this helps, Scott Please don t forget to provide feedback for this response (either...

Using MS Access: sub of subreport causes crash, craches, chapter1
craches, chapter1, chapter4: The structure you show shouldn t require subreports but grouping. You should have a query that returns the data you want to report on, then set grouping levels for Shelf and Booktitle. Why its crashing I couldn t say from here. Could be you are trying...

Using MS Access: subdatasheet problems, sort buttons, collumn
sort buttons, collumn, message dialog: Oh!!! No form huh? Yikes!! (Build a form then!!!) Just kidding. Well, ok... not really. But anyway... Hmmmm... Well, to be honest with you, I ve never seen that error message before, and really don t know why it would come up. Functionality like...

Using MS Access: subform, parameter value, microsoft access
parameter value, microsoft access, misspelled: If you are getting a parameter prompt that means that you are referencing a field that Access can t find so it assumes you want to prompt for the value. This could be due to the reference being misspelled, referencing the wrong control name or referencing...

Using MS Access: subform combobox dependent on main form combobox value, source record, source table
source record, source table, question thanks: Hi Franklin, How about if you extend the cboTeacher options as follows: 0 - Select a Teacher 1 - Add a new Teacher 2 - Jo Smith 2 - Rita Hayward 2 - etc etc your default is still 0, which means that you should never have a failing scenario...

Using MS Access: subform entries into a comma-list, stay at home mom, childrens names
stay at home mom, childrens names, kids name: The way I would handle this is to use columns in your report instead of trying to combine the child records. Depending on your layout you can probably fit the children on one line underneath the parent s info. However, if you want to combine them, you...

Using MS Access: update..., ongoing support, customer info
ongoing support, customer info, deliveries: This is pretty standard stuff. You would need tables for drivers, customers, orders and schedules. The Schedules table would look something like this: tblDailySched DailySchedID (PK Autonumber) DriverID (FK) SchedDate OrderID (FK) Delivered (Yes/No)...

Using MS Access: A subform with a requery method, requery method, cmd button
requery method, cmd button, db design: You are making more work for yourself than necessary. You don t need to base the borrowers form on a query. Base it on the table and use a Search combo (Combo wizard, 3rd option) to create a combo that will allow you to select a borrower. You don t need...

Using MS Access: subform slow, parrent, rule of thumb
parrent, rule of thumb, single file: Hi Is the sub form bound to a full table table recordset? Also, is the primary key set on a number? Generally, the rule of thumb here is to bring back only the data you need. Also, what is the set up? Is the data file sitting on a server, is it a...

Using MS Access: subform values not visible between main form records, query statement, combobox
query statement, combobox, sql query: Let me see if I follow this. When you change a record on the main form and you then pull down cboTeacher, it doesn t reflect the current School listed on the main form? The After Update event doesn t kick off unless you actually make a change inside the...

Using MS Access: subform, sorry for the confusion, long integer
sorry for the confusion, long integer, data entry: There are two ways you might work this. I m not sure if the first will work as it may produce an uneditable query. 1) Create a query joining GoodParts to MasterJobList on JobID. Then use that query as the RecordSouce for your form. Now the Partnumber will...

Using MS Access: subforms and queries, normalization rules, record source
normalization rules, record source, fname: It sounds like you have problems with normalization of your database. All that will do is cause you problems later on. You really need to redesign this databse according to normalization rules. You need to realign your 5 tables into only two: Individual:...

Using MS Access: subforms, related records, ms access
related records, ms access, tabs: Hi In design view select the subform and then view the forms property sheet. You ll see an option for setting the parent/ child fields - this should contain the linking information between the data. I.e. The piece of data that allows Access to show only...

Using MS Access: form to find by form, option boxes, combo boxes
option boxes, combo boxes, filter string: Dear John, Comboboxes, Textboxes, tickboxes and option boxes can only have one value so these types i handle a little different from the Listboxes. I Normally have a function that create this Where or Filter string. Example: Function CreateFilter() as...

Using MS Access: subReport.HasNoDataBecauseOfFilter, visible property, endless questions
visible property, endless questions, text boxes: There are a few ways to do this. If it works except where B has no records, then try the Nz Function to substitute a 0 for Null. Something like: =Nz(txt1,0)+Nz(txt2,0)+ etc. If the control contains a null, then the formual will add 0. You can also...

Using MS Access: Is there something like a subroutine in access, fortran subroutine, global module
fortran subroutine, global module, microsoft access: Pretty much. What you need to do is place the subroutine (referred to as a procedure) in a Global module and make it a Public routine. For example: Public Sub MyRoutine() your code End Sub This is then called using the statement: CALL Myroutine()...

Using MS Access: subtotal query, dmax, eem
dmax, eem, max price: The Min and Max don t work well in a Group By query because they function against the whole recordset not by group. The best suggestion to get around this is to create a query that gives you the unique list if Items. Then use that query to join with your...

Using MS Access: how to subtract one time to another, three boxes, toam
three boxes, toam, discrepencies: Hi You must store the whole date. I.e. 07.06.04 23:00 to 08.06.04 01:00 If you compare the above using DateDiff, you ll get 2 hours. It seems to me that you are not calculating against the whole date (including time). There can be discrepencies...

Using MS Access: subtration function, table calculations, hth
table calculations, hth, subtraction: First, Access is not a spreadsheet. It doesn t have cells . Second, as a general rule, we don t store calculations in a table. Calculations are done on the fly for display when needed. If you want to show a subtraction expression, you can set the Controlsource...

Using MS Access: sum a field in a subform, line fashion, recordsource
line fashion, recordsource, database window: Call when you can. The #name is probably because the subform name is probably not the name you see in the main database window, but the one you see when viewing the mainform and subform in design view. It s touch, not seeing it, myself. One way to have Access...

Using MS Access: switchboard, switchboard, andrea
switchboard, andrea: MJ - The Switchboard doesn t open unless you add it to the Startup routine. Startup is located in the Tools menu. In the pop-up box that appears, look at the upper right-hand corner for the Display Form/Page drop-down. Find your Switchboard on this drop-down...

Using MS Access: switchboard, swithchboard, switchboard
swithchboard, switchboard, design mode: MJ, Many thanks for the question. Check your start up option. With access open in design mode, on the menu bar click on Tools Start Up Check the Swithchboard is selected in the Display Form/Page section. Hope this is of some help. Regards...

Using MS Access: switchbord, one access, switchbord
one access, switchbord, switchboard: Rename the old switchboard table to switchboardold. Then go to Tools Switchboard Manager and create a new switchboard, but don t add anything to it. Next, create an append query that appends the records from switchboardold to the new switchboard table that...

Using MS Access: & symbol display in label caption, ascii value, microsoft access
ascii value, microsoft access, johnson johnson: There are two ways. One is simply to double up on the character: Johnson && Johnson will work. The other is to use the Chr() function. Johnson & Chr(123) & Johnson where 123 is the ASCII value of the ampersand. Hope this helps, Scott...

Using MS Access: synchronising replica-databases, many different things, ms state
many different things, ms state, exit function: Hi Kim I ve just done a search on this and found the following: http://home.clara.net/tkwickenden/problems.htm#synchronise Although this is only one comment is seems to be very common that there s problems with this. It seems to me that synchronising...

Using MS Access: synchronize tables, programming knowledge, customer number
programming knowledge, customer number, customer info: Hi Should the second form not be a subform in a one to one relationship? Then the data would have a relationship. Saying that I m not sure this is beneficial in your case. In essence are you trying to... Open a form Complete some data Open a...

Using MS Access: syntax error using text search, run time error, findfirst
run time error, findfirst, qbid: rstSearch.FindFirst [QBID] = & Nz(Me.txtQBIDSearch, ) That may work. If not, use: Private Sub txtQBIDSearch_AfterUpdate() Dim rstSearch As DAO.Recordset Set rstSearch = Me.RecordsetClone If Len(Me.txtQBIDSearch) = 0 Then MsgBox You...

Using MS Access: system date in a text box on form load event, onload event, goodday
onload event, goodday, default property: Mitran Hello, The functions available are : Now() returns the date and time and Date() returns the system date There are a number of options: I would try first setting the textbox Default property to: =Now() If not, you could use the...

Using MS Access: TAB Order, currency field, previous records
currency field, previous records, data input: Hi Brian, I have built many applications with nested form configurations, and can say I have never encountered this behaviour. Could you zip up a SMALL version of this problem mdb and email it to me please, to gmjohns@fastmail.fm Kind regards Geoff...

Using MS Access: TABLE LOOKUP, potential confusion, normalization rules
potential confusion, normalization rules, combo box: Sorry, but what you want can be accomplished VERY easily using a query. Create a query joining the 2 tables on CourseID and and list all the fields you need in the query. Then use the query when you want to see all the data together. I understand what...

Using MS Access: Tab Control, page1 page2 page3, page3 page4
page1 page2 page3, page3 page4, proper syntax: Steven, Many thanks for the question. To do what you ask, the syntax you are looking for is (this would be added to the on click event for each of the command buttons) Me!TabCtlGUI.Visible = True Me!Page1.Visible = True Me!Page2.Visible = False...

Using MS Access: Tab Order within a subform, tab sequence, property options
tab sequence, property options, index property: Jill, hi, 2 things to check: 1// Open the form/subform in design mode. Select the form object (top left square). Open the properties dialog (right Mouse button). Select the Other tab. Look at the Cycle property options & choose your favoured option....

Using MS Access: Tabbed Forms & Subforms, question concerns, column widths
question concerns, column widths, boundry: Hi Todd Have you tried to set the tab order on the forms? Once you have set the tab order for the form, select the tab control to get the focus next. From that, select which control (fields) you wish to move to. On the subform issue - have you tried...

Using MS Access: Tabel normalization in Access, attribute data, fks
attribute data, fks, thanks in advance: Hello Charles, Table 1 - Person - PK = PersonID Attribute data - Name, Addresss... Table 2 - Workout - PK = WKID FK = PersonID Attribute data = Date/Time - Use Now() Table 3 - Exercise...

Using MS Access: Table - 1 record of information only, database window, address state
database window, address state, deletions: Its not 100% possible, but there are a number of things you can do. First, users should not have access to the the Database Window or tables. All interaction should be done with forms. So setting the form to not allow Additions or Deletions accomplishes your...

Using MS Access: Table Comparison, vba procedure, rigorous solution
vba procedure, rigorous solution, fastmail: Ian Hi Access doesn t have these sort of power functions and the Access query is the simple end of your options. Options that I use, both of which require a planned approach (as opposed to ad-hoc)... 1. Append a date field, then as you update a field...

Using MS Access: Table of Contents and Kool Reports Sample, ourworld compuserve, moving in the right direction
ourworld compuserve, moving in the right direction, page number references: Hi Brian Downloaded the db - haven t seen it before. From what I can gather it s a TOC for report content. I m not familiar with the Kb article either. You mentioned that I had a solution to this - are you sure you have the right person? I m really...

Using MS Access: Table compareing for matches, access tutorials, president ceo
access tutorials, president ceo, 599cd: Easiest way to do it: create a new field with a query to put first and last name together into one field, then join the two tables together in a query - all the like records will show up. If you don t know how to do this, let me know and I ll elaborate. ...

Using MS Access: Table Design, foreign key, x block
foreign key, x block, three tables: I see at least three data tables here. First you need a Properties table and a Property Attributes table: tblProperties PropertyID (PK Autonumber) StreetNr Direction StreetName Lot Block Zoned Notes tblPropAttributes PropAttributesID (PK Autonumber)...

Using MS Access: Table Design for Energy Database, database design issues, database novice
database design issues, database novice, energy database: Hi I see this as two tables. One for your schools and one for your utilities in a one to many relationship. One school can have one or more utilities. You can then add as many schools as you want without creating new tables. Does this help?...

Using MS Access: Table design, hourly pay rates, salary information
hourly pay rates, salary information, time kevin: A lookup table is a table like any other. The difference is that its purpose is to provide standardized input to a field in a data table. Generally, lookup tables provide the rowsource for a combobox. Salary info is part of your data. Queries are used to...

Using MS Access: Table design, hourly pay rates, position titles
hourly pay rates, position titles, employee position: Kevin, Many thanks for the question The #Physician and total number of employees relate to the the practice - therefore these need to be in the practice table. The second table for the employees would contain the following: tblEmployees EmployeeID...

Using MS Access: Table design and relationships, good database design, autonumber field
good database design, autonumber field, necessary fields: Dear Seth, If you create a Autonumber field in you main table this can be the key that all four other tables can use to relate to the main table, the fields in the 4 other tables must not be a autonumber field but a normal number field Long Integer These...

Using MS Access: Table design, hourly compensation, physician name
hourly compensation, physician name, base tables: Hi Kevin, You could start with a number of base tables tblPractice Practice_ID Practice_Name Speciality ... tblPhysician Physician_ID Practice_ID Physician_Name Position Annual_Pay ... With these two tables you could prepare a query...

Using MS Access: Table Fields, ms access 2000, visible control
ms access 2000, visible control, five tables: Most likely you have not designed your database properly. There is a design technique called normalization that is essential to a well run database. You need at least five tables: tblEmployees EmployeeID (Primary key Autonumber) FirstName LastName...

Using MS Access: Table to be incorporated into Access DB, nozzle size, row headings
nozzle size, row headings, pat mcgraw: Pat, Many thanks for the question. I have had a bit of a think about this. Microsoft Access does not work well with the table set out in the format described above; as it will interpret the column heading values as field names and the row headings...

Using MS Access: Table Lookup, last selection, data integrity
last selection, data integrity, combo box: OK, it s a little vague what you want to do AFTER, which does make a difference in how you approach this, but try this solution: Make a form using the wizard based on that table. Use all of the fields EXCEPT for City. Choose Columnar for the type (so...

Using MS Access: Table looking up itself, product bundles, index field
product bundles, index field, table keeps track: Don, First of all my apologies in my delay in answering this. I am sure I can help you, if you could send what you have done so far. Send it to: jmisson@@iprimus . com . au (remove the extra @ and the spaces, trying to prevent spammers picking...

Using MS Access: Table looking up itself, table keeps track, product bundles
table keeps track, product bundles, index field: That s what the last sentence was saying. Calculated values should not be stored, they should be done on the fly. Stock on hand is a calculation. It represents the total of incoming and outgoing transactions. As for bundling of products, you can include...

Using MS Access: Table Names, etc, microsoft visual studio, sony hi
microsoft visual studio, sony hi, explicit function: Hi Sony, I m not too sure but you may check the following excerpt of code. I got it from the link below - http://www.dotnetspider.com/Technology/QA/ViewQuestion.aspx?QuestionId=1281 You must use TableDef object to see the tables in a access database.....

Using MS Access: Table Properties, runsql, longfield
runsql, longfield, primary key: Hi Sam, Here is a code subroutine that copies a table, then creates two indexes, the primary key, and another. I am slightly cheating here in that I am using SQL to do the Data Definition as opposed to VBA, but I find this more eloquent and it doesn t...

Using MS Access: Table Property with index YES (no duplicates), kind solution, customer names
kind solution, customer names, field column: I have some problems with your structure here. First, it seems you are going backwards in your tasks. You should have started with a table of senders where each sender had a unique record. You would then have used an Autonumber as a Primary Key for that table....

Using MS Access: Make Table Query format, four digits, query format
four digits, query format, numeric format: Access is analyzing the data and if it sees a numeric format its creating a numeric field. Not sure if you can control that. What you can do is add a dummy record that becomes the first record that contains text data so it read it correctly. Another thing...

Using MS Access: Make Table Query using multiple linked tables, entity table, blank fields
entity table, blank fields, separate tables: Yes, but my question is why. Sounds like you have a normalized database and are trying to create a denormalized flat file out of it. I don t see a lot of reason for that, which is why I ask for a reason. If you want to create a query to use to export a...

Using MS Access: Table Relationships, appointment data, table relationships
appointment data, table relationships, 123456: Hello Ed - instead of a 6 page explanation, lets give you something to look at. Go to www.dropboks.com. Log into the website using donalddarracq@yahoo.com as the username and 123456 as the Password. Locate Ed.mdb, right click and download it to your computer....

Using MS Access: Table row protection, autonumber field, data tables
autonumber field, data tables, multiple users: Access handles the first situation with record locking. You can read more about that in Access Help. The second situation should not be an issue. Use an autonumber field as your PK. Also a multi user app NEEDS to be split. The tables go in a back end file...

Using MS Access: Table Shrinkage, shrinkage, window area
shrinkage, window area: Actually I have the same problem. When you open a table or query in datasheet mode, the window area is larger even when the number of fields is smaller. You can size the window, just as you would size any window. Then close the window and it will ask if you...

Using MS Access: Table as a SubForm, left hand corner, format tab
left hand corner, format tab, kenly: Kenly, Well done with the database thus far. My apologies for the delay in getting back to you. First of all make the subform as large as possible. To do what you ask, with the form in Design View open the properties of the subform i.e. right click...

Using MS Access: Table security, payroll application, troble
payroll application, troble, real security: Yes there IS an easier way. Personally I do not use Access security since I find it cumbersome to implement. I usually build in my own security. Secondly, Access is just not a totally secure environment. Anyone who knows a decent amount about Access can get...

Using MS Access: Table size, mdb file, tricky question
mdb file, tricky question, number of bytes: Orlando, Hi, Tricky question.... I couldn t find anything on the internet after a brief search, however... AA - If you want to know the impact on the size of the mdb file in relation to growing a specific table - I suggest you do the following. ...

Using MS Access: Table structure manipulation, structure manipulation, table definitions
structure manipulation, table definitions, sql statement: Well, You ll need to learn a little VBA to get this done, and I m afraid this venue isn t the best for teaching it. Basically what you ll need to do is write some code where you read from the table definitions the column names, and Loop through them all...

Using MS Access: Table, stock control system, inventory transaction
stock control system, inventory transaction, microsoft access: I would think you need a table that lists the medication, the dosage, the frequency and the length of time it will be prescribed. Ther would be one record per med per patient. The transactions table would track when the prescription was actually administered....

Using MS Access: Tables, input table, combobox
input table, combobox, microsoft access: Yes, you do it on a form, You only need the employee number in your input table, so you can link to the name table. So you only want to display the name. Look at the Northwind sample that comes with Access and you can see samples of what I m referring to....

Using MS Access: Tables and Data, increment, data entry
increment, data entry, many thanks: Monika, Many thanks for the question. Just to clarify - you want idea 101 to increment automatically from 2006, so if idea 101 was put in a second time it would be assigned 2007, and if entered a third time 2008. But if you entered 102, you want to...

Using MS Access: Tables design problem, correct relationships, infinity sign
correct relationships, infinity sign, autonumber field: I suspect the reason you can t enforce referentail integrity is your field isn t a primary key and therefore may have duplicate values. Just about every table should have a primary key. In the vast majority of cases, that primary key should be an autonumber...

Using MS Access: Tables & Forms, john corrigan, dear scott
john corrigan, dear scott, three letters: Yes its possible, but you neither want nor should you do this. Essentially this is a calculated value. As a General rule we do NOT store calculations. These can be calculated on the fly when necessary. The expression: =UCase(Left(MembLname,3) & Left(MembLname,2))...

Using MS Access: Tables: Long Integer Property, integer data types, futute
integer data types, futute, long integer: Dear Robert, Normally if you using numbers more than 9 digits, the way i will do it is using a text field, text fields can easily be modified and can be primary indexed as well. The only thing it can t do is run a autonumber on it. You can however write...

Using MS Access: Tables-Relationships-Duplication error, combo boxes, side tables
combo boxes, side tables, tab control: I don t understand using comboboxes. If you want to use a single form to enter data, use a main form bound to the table on the ONE side of the relation and subforms bound to the many side tables. You could use a tab control for each subform. If you need...

Using MS Access: Tables, thanks norman, data integrity
thanks norman, data integrity, futhermore: It usually works the other way around. You enter data in Table 1 (the one side) before you enter data in Table 2 (the many side). You can easily work it that using a main form (on Table 1) and a subform on Table 2. If you want to do it the other way, you...

Using MS Access: Taking it to the next level, access courses, formal education
access courses, formal education, code samples: Well it depends on what level you are at. Most Access courses I ve seen deal at a pretty low level. You might try finding a course or book on VBA to take you further. I also suggest using sites like utterAccess.com, checking code samples and reading on the...

Using MS Access: Tax Calculation, tax percentage, query field
tax percentage, query field, design mode: Open the query in design mode. There should be a column that look like: Tax: some formula Right click on that box and select Zoom, then highlight and copy the entire expression and paste it into a note here. Hope this helps, Scott Microsoft Access...

Using MS Access: Templates - General Ledger, free ms access, datebase
free ms access, datebase, page hope: Frankly I would recommend getting Quickbooks. Quickbooks is an easier to use full fledged accountng system and it will work with Access and Excel. A full G/L system is not the easiest thing to create and I doubt if there would be an free templates for one....

Using MS Access: Test Bank, random function, temp table
random function, temp table, selection button: This is doable. You would use a multi-select list box to select the topics. You can then use the Random function to generate a number to select QuestionIDs and add them to a temp table. Then use that table as the basis for a form to ask the questions. ...

Using MS Access: Test Dates, metal test, dateadd
metal test, dateadd, metal thickness: Don t know why they shouldn t work. I would have to see the file to check it. I suggest using askmehelpdesk.com. You can post a note in the Database forum and attached a zipped copy of your file so I can take a look. Hope this helps, Scott Microsoft...

Using MS Access: Text Box, detail records, detail record
detail records, detail record, record detail: Peter hello, Proving a sweat hey.... :-) My only issue is understanding your data. Could you provide 5 sets of sample raw data for each of [ProductName] and [TraceabilityCode] with your desired styling alongside... In the mean time, here s my interpretation......

Using MS Access: Text box on form, foreground color, default color
foreground color, default color, highlight: You need to change the back color. Even if you set it to transparent. When you are actually entering data it defaults to normal with a default color of white. If you change the back color, then reset the BackStyle, it will be transparent except when the control...

Using MS Access: Text box to store in table, combo box, microsoft access
combo box, microsoft access, mvp: You don t want to do that. Data should exist in ONE place only. Since the value already exists in the table the combo is based on there is no reason to include it as a field in the table. You can retrieve the value anytime you need it using a join. Hope...

Using MS Access: Text box value linked to combo box, absolute novice, combobox
absolute novice, combobox, combo box: Richard, Many thanks for the question. To do this you will need to write a bit of code. Right click on the Combo box, and select Properties from the drop down list. When the properties window opens, click on the Event tab and place your cursor...

Using MS Access: Text to Columns in Access?, vb scripts, empty columns
vb scripts, empty columns, c parker: Shows how much I work with Excel. Never knew that was there. Just tried it out and its pretty neat. I ll have to remember that. Anyway if its only first and last names that s simple: First: Left([namefield],Instr(1,[namefield], ) Last: Right([namefield],...

Using MS Access: Text Files, quick brown fox, filesystem object
quick brown fox, filesystem object, opentextfile: Hi, The code samples below will give you an idea of how text files are read and written to. You need to referene the FileSystem object for this to work. Function ReadLineTextFile Const ForReading = 1, ForWriting = 2 Dim fso, MyFile Set...

Using MS Access: Text file import, transfertext, api section
transfertext, api section, open dialog: Frankly, I almost never use macros they are very limiting. This is one of the places. You might try using an Inputbox within the macro, but I would recommend using VBA. To do a simple TransferText method is very easy. Instead of filling in the boxes on the...

Using MS Access: Textbook recommendation, access 2003 programming weekend crash course, weekend crash course
access 2003 programming weekend crash course, weekend crash course, punched cards: What you would have to do is insert a Do While loop that looped thru table1 while the key value remained the same. But I m even more convinced that there are better ways to do this. Maybe a query like this. INSERT INTO table2(field1, field2) SELECT Rent...

Using MS Access: Textbox Formatting, query builder, data entry
query builder, data entry, formatting: Dear Gersh, A small VB statement behind the Textbox can do the trick, change the textbox property After update event to [Event Procedure] and between the two lines add the following statements: If nz(me.activecontrol) 1 then me.activecontrol...

Using MS Access: Thanks so much for the link..., allowbypasskey, wise installer
allowbypasskey, wise installer, paste method: I thought I answered this. First its not MY code. I just pointed you to someone else s code. A) The PropName and values are what you designate. They refer to the Bypass key property. You run the code from a module. the module should be kicked off when...

Using MS Access: One More Thing, datediff, parenthesis
datediff, parenthesis, current date: No, you are declaring the arguments as Date types. You are declaring the returned result as Integer. What you are returning is the number of months elapsed. That s an integer. Access does drop the parentheses from the Date function in some expressions. I m...

Using MS Access: One More Thing, dear scott, record navigation
dear scott, record navigation, docmd: Also just so you know the setting of Data Entry in the actual form is set to YES. That s your problem. Put the cursor in the Date Entry property and press F1. This will bring up the Help explaining what it does. When set to Yes, it only allws you to enter...

Using MS Access: One More Thing, dear scott, record navigation
dear scott, record navigation, ok button: See that s what has me confused. Definitely setting Data Entry to Yes will prevent reviewing existing records. But I don t know what else you are doing that would cause that. Can you send me a copy of the database to dafiles@optonline.net and I ll take...

Using MS Access: One More Thing, dear scott, record navigation
dear scott, record navigation, delicate matter: Got the file, sent you an e=mail about issues with structure. OK, Several issues here. The reasons you aren t seeing older records is because you are basing your form on a query and no recordfs are being returned by that query. Basing a data entry form...

Using MS Access: One More Thing, ltl rate, storage charge
ltl rate, storage charge, rate charges: Sandy, First let me apologize. I did get your sample file, and it slipped thru the cracks. I m glad you got it solved. 1. I think you need more than an expression can give you here. I would use a custom function. something like this: Public Function...

Using MS Access: Tick Boxes in MS Access, ms access 2000, local youth group
ms access 2000, local youth group, hi thanks: First, the fact that there is only one table of information indicates that it isn t normalized. The POINT of normalization is to make data entry and reporting more efficient. Without knowing the scope of this app, you need at least three tables. One table...

Using MS Access: Time over 24 hours, care asst, decimal portion
care asst, decimal portion, integer portion: Its definitely possible to do, but you will need to change your table a bit as well as use some coding. The first issue is that you should store the date and time in a single field not two separate fields. Access stores a date/time as a double precision number...

Using MS Access: Time Calculations, time calculations, select properties
time calculations, select properties, thanks john: Add a column with this expression: TimeDiff: [Time]-[Time1] Right click on the column and select properties then Format the column for Shorttime display. Hope this helps, Scott Please don t forget to provide feedback for this response (either...

Using MS Access: Time calculations using MS Access, dim rs, difference in time
dim rs, difference in time, time calculations: Dear Andrew, The only way to do this is to run a little VB code to do this. Function CalcMins() Dim Rs As Recordset, PrevDate As Date Set Rs = CurrentDb.OpenRecordset( MyTableQry , dbOpenDynaset) While Rs.EOF = False If Nz(PrevDate) 0 Then...

Using MS Access: Time / Date Function, d1 d2, date function
d1 d2, date function: Dear Jay, I can t seem to replicate the problem you experienced, i use the function DateDiff as follow d = Datediff( d ,[D1],[D2]) D1= 23 May 2006 01:00 D2= 25 May 2006 05:00 Result d=2 D1= 23 May 2006 01:00 D2= 25 May 2006 23:00 Result d=2 ...

Using MS Access: Time difference calculation, datediff function, time span
datediff function, time span, control source: First you should NOT be storing Time worked. As a general rule we do not store calculated values. If the time span will always be less than 24 hours, you can just use subtraction. Set the Control Source of a text box to: =[txtEndtime]-[txtStartTime]...

Using MS Access: Time duration, time duration, datediff function
time duration, datediff function, d days: Hi This function works for dates in general. The difference in how it reports is the first argument, in the example above h = hours. d=days n=minutes Look up Access help on DateDiff as there is a very good explanation and several examples. ...

Using MS Access: Time format over 24 hours, decimal portion, integer portion
decimal portion, integer portion, time notation: You can t, nor is this a problem . You need to understand how Access (and Excel) store and format time values. A date/time datatype is stored as a double precision number where the integer portion represents the number of days from 12/30/1899 and the decimal...

Using MS Access: Time queries in Access, queries in access, return time
queries in access, return time, logue: Hi Joseph, If you know how to use Datediff it should be a piece of cake for you to do hours. You still use Datediff but with a different value in the first argument ( h instead of d ). If you have the dates in 2 fields, you would use this formula...

Using MS Access: Time Range Overlaps, time fields, time range
time fields, time range, microsoft access: This needs to be done in VBA code. You will have to use a recordset that returns the records for that day. You will then need to test each record to see if the end time from the previous record is later that the start time of the next record. Hope this...

Using MS Access: Time Start and Finish, game duration, chess games
game duration, chess games, kasp: Hi Dave An easy way would be to think of 3 queries performing the 3 logical steps: 1. A view of player / game / start-times 2. A view of player / game / finish-times 3. A view based on the above two giving the time differences Query1.. SELECT Left([Name],...

Using MS Access: Time Tracking Attendance, timein, reception desk
timein, reception desk, timestamp: First Date is a reserved word in Access and shouldn t be used as an object name. Second, I gather you want to scan a barcoded MemberID and automatically record the Time In and Out. If so, you will need to make some changes. Your table should look like...

Using MS Access: Time tracking, signout, select properties
signout, select properties, signin: Steve, Many thanks for the question. To do what you ask, create a table that will keep this information. It probably only needs two fields - SignIn and SignOut both of text format Then build a form based on this table by following the wizard (adding...

Using MS Access: Toggle for printing single form information, command buttons, command button
command buttons, command button, format tab: Hi Try the following code in your command button click event pre If MsgBox( Do you wish to oprint the Notes field? , vbQuestion + vbDefaultButton2 + vbYesNo, _ Print Notes? ) = vbYes Then Always ...

Using MS Access: Left and Top Properties, report thanks, thanks in advance
report thanks, thanks in advance, cancel: Hi Krystel 1// FORM My system is set up with centimetres as default. and to change this setting I would use... Me.txt1.Left=2.6cm You can also use inches, viz: Me.txt1.Left=1.05in 2// REPORT Use the report open event, viz Private Sub...

Using MS Access: Totaling up queries, dual core processor, dell dimension e521
dual core processor, dell dimension e521, amd athlon 64: You can reference a value on a control on an open form using the syntax: =Forms!formname!controlname So wnter the criteria on your form and use that expression (substituting the correct form and control names) as your criteria. Depends on how you...

Using MS Access: Totaling yes and no questions, base query, fabulous website
base query, fabulous website, cross tab: Hi This sounds like you need to build a base query which gives you the data (let s call it QUERY1). Then you need to build a new query based on QUERY1 whcih gives you the totals, etc. There is a calculation row in Access, switched off by default,...

Using MS Access: Totalling Checked Boxes, example values, triple option
example values, triple option, quandry: I think you are on the right track with: IIf(IsNull([Forklift]),0,[ForkliftPrice]) but it should be: IIf([Forklift]=0,0,[ForkliftPrice]) or: IIf([Forklift]=-1,[ForkliftPrice], 0) Use that for all the options, sum the totals, and that should...

Using MS Access: Totals for colums, blank text, textbox control
blank text, textbox control, colums: Troy, hello, Totals ------ 1. insert a Group footer (or Header), and select the field that you wish to group on. 2. Insert a textbox control in the newly created footer 3. Open the properties for the new textbox control 4. On the ControlSource property...

Using MS Access: Track Access Users, fastmail, database option
fastmail, database option, loging: Hi t I enclose a module that I use occassionally when I need this functionality - I hope it is self explanatory. Please keep my copyright reference - thanks. Kind regards Geoff basUserID Purpose: Called from main application on loadup...

Using MS Access: Track any changes, colums, primary keys
colums, primary keys, requestor: First, you have some problems with your database structure. Access is a relational database and you aren t using it as such. You might as well just use a spreadsheet. I would strongly suggest you look at the Northwinds sample, which is an order entry database....

Using MS Access: Tracking Historical Data or "AKA" data, strsql, housing developments
strsql, housing developments, primary key: Use an Autonumber as the primary key when you first create the development. Then add a table called tblAKA like this: AKAID (PK Autonumber) DevelopmentID (Foreign Key) AKA ChangeDate You can automate the recording by putting code like the following...

Using MS Access: Tracking Quarterly, and Semi-annual commissions, agent commissions, table structure
agent commissions, table structure, zero value: Hi There, I should be able to help i just need some more info. How is your data sorted, like table structure? How is the dbase meant to know when the payment is due? Which method are you planning on using, report or query as they are different?...

Using MS Access: Transaction reciept, subscriber info, recordsource
subscriber info, recordsource, money transactions: I understand the issue, but I don t know if I can explain it more clearly. You need to create a query that returns just the data you want. If you want a receipt for a single transaction then you want to set the criteria to that transaction s PK. From there...

Using MS Access: Transfer data from Excel to Access, office automation, exact code
office automation, exact code, spreadsheet: If we are talking about columns in a table then the best way is to link to that table and run an append or update query to bring the data into your table. If we are talking about isolated cells, you can use Office Automation to to read cells in a spreadsheet,...

Using MS Access: Transfer of tables, ascii file, new computer
ascii file, new computer, computer system: Sailaja, Just copy the Access mdb file over onto the new computer, then link the tables into the new Access mdb application To create a permanent table in the new Access application, use the wizard to build a Make Table query from the linked table....

Using MS Access: Transfer tables, ascii file, transfer tables
ascii file, transfer tables, microsoft access: The ASCII file can be in any location that the machine can access. If you want to use the same ASCII file on different PCs, then it needs to be on a shared network drive. If you change the location of the files, you may need to relink to the ASCII file using...

Using MS Access: Transfer text specifications, mapping specification, docmd
mapping specification, docmd, template text: Anshul, hi, Create import specifications by manually linking a template text file. In the Table section of the db window, right click, link table, the windows file locator dialog opens - select a file type of csv or txt, find the file, click OK. Follow...

Using MS Access: Transfering data tables from .mdb to excel, export tables, transfering data
export tables, transfering data, access office: Hi Brock, Sory for replying late. I will have to blame the weekend for that. It is a painful process to program Excel formatting in Access. Normally, we export the data to an unformatted excel file, then use a formatted Excel sheet in another Excel file...

Using MS Access: Transferring information from one form to another in Access, normalization rules, key field
normalization rules, key field, foreign key: OK, If I follow you, you have a form that is in Continuous form (or Datasheet) mode. You want the user to be able to select one record in that form and open another form that displays the record in columnar form. You wouldn t be transferring info from one...

Using MS Access: Transferring to another table, recordsource, source properties
recordsource, source properties, control source: If the tables are identical, just change the Recordsource of the form. As long as the controls on the form are bound to the a field that exists in the new Recordsource then you will have no problem. Changing the Datasource won t do anything unless the Recordsource...

Using MS Access: TransferSpreadsheet to excel, insurance data, excel spreadsheet
insurance data, excel spreadsheet, mortgage insurance: Why are you exporting to a spreadsheet? Why are you exporting the queries separately? You could have one query without the open/closed criteria then sort on that field to group the records that way. If you have to export to a spreadsheet, that requires only...

Using MS Access: Trasnsfer the data in a field in a Combo box from one form to subform of another form, student id field, combo box
student id field, combo box, combobox: I would use the OpenArgs property in this case: Set the OpenArga property to the combobox, then in the form being opened use the On Open event to run code like: If Not IsNull(Me.OpenArgs) Then Me.txtStudentID = Me.OpenArgs End If Hope this helps,...

Using MS Access: TreeView Control, vba programmer, ms access
vba programmer, ms access: Hi Yes - it s completely possible to bring data into the treeview. This is arguably the most difficult control to work with in MS Access. Are you familiar with VBA? If not - then the treeview is a no go as the only way to get data into the structure...

Using MS Access: TreeView Control, windows common controls, access programmers
windows common controls, access programmers, design mode: Hi Wasim You will need ..windowssystem32comctl32.ocx installed & registered, then in Access, with a code module in design mode you will need to make a reference to MS Windows Common Controls. If you download either the 97 or 2000 sample zip file from...

Using MS Access: Trigerring multiple events with a single button, tab control, bound form
tab control, bound form, employee id: I would do it differently. I would have a single form with a tab control. Put the common controls above the tab control and have the specific controls for Register and Settlement on respective tabs. Data entered into a bound form is automatically saved...

Using MS Access: Triggering an event in a subform from new record, scott expert, darracq
scott expert, darracq, requery: Ok, let me see if I follow this. You have a form where new calls are entered. You have another form, used by the people who resolve the calls, that lists open calls. On that second form, you run a refresh periodically so any new records are added. I have...

Using MS Access: Triggering an event in a subform from new record, requery, workable solution
requery, workable solution, sample database: Will - Access has always handled triggers differently then expected on nested sub-forms. It is not you so don t worry. Unfortunately I m am about to leave. I will begin on the solution first thing tomorrow morning. In the meanwhile, send me an e-mail to Rocknd@hotmail.com....

Using MS Access: Trim Blanks From Text Fields In Reports, microsoft office 2000, field definitions
microsoft office 2000, field definitions, apparent effect: Well first of all just because a field has a maximum length of 50 doesn t mean there are trailing blanks. If you set the Controlsource in a report to: =[Lastname] & , & [Firstname] That should work. But =Trim([Lastname]) & , & Trim([Firstname])...

Using MS Access: Trouble importing a text file, manageable chunks, database size
manageable chunks, database size, message file: Hi Yeah it sounds like the file is too large. Despite Access stating that it can handle 2gb - that s in total (including your internal Access objects that make the db run). Have you reied a file slicer to reduce your data to more manageable chunks?...

Using MS Access: Trouble importing a text files, comma separated values, wordpad screen
comma separated values, wordpad screen, database size: hmm, I knew it was too big for Notepad which is why I suggested Wordpad. there shouldbe no limit for Wordpad, but you can use any word processor like Word, WordPerfect etc. If you prefer to use a text editor, there are a number of freebie text editors...

Using MS Access: Trouble w/checkbox code on subform for single record change, several records, checkbox
several records, checkbox, thanks in advance: First I would use the After Update event of the checkbox, not the click event. Next you don t need the Value property. Also, since you are hardcoding the vTax in the module, no need to Dim it. Finally you need use the Me qualifier for the Tax control. ...

Using MS Access: Truncate rightmost two digits, sql view, hth
sql view, hth, digits: Hi Create an update query and paste the following SQL into the SQL view of the query. UPDATE Table1 SET Table1.table_value = Left([table_value],Len([table_value])-2); Replace Table1 with your table name and replace table_value with your field name....

Using MS Access: Truncate rightmost two digits, fld, currentdb
fld, currentdb, menu tools: Hello Bill, I have built a VBA subroutine to perform this task - please be aware that each field datatype will need to be managed by a separate element in the FOR..NEXT structure. Public Sub TruncateFields() Dim db As DAO.Database, tdf As DAO.TableDef,...

Using MS Access: Trying to count values within a group, correct answer, many thanks
correct answer, many thanks, punt: Alexander, Many thanks for the question. I am not sure if I fully understand your question, but I am going to take a punt, that you are trying to count Yes and No s. A yes = -1 and a no = 0. Rather than counting them, as you are correct and all will...

Using MS Access: Trying to figure out how to count values within a group, lisa sims, crosstab query
lisa sims, crosstab query, query html: Hi Alexander, You may want to consider using a crosstab query to produce the result that you are looking for. Here s a link with step by step instructions on how to create a crosstab query. http://www.ehow.com/how_13577_create-crosstab-query.html ...

Using MS Access: Trying to run a Date query by "MM", birth month, birth data
birth month, birth data, table columns: Add a column to your query: CurrBD: DateSerial(Year(Date()),Month([DOB]), Day([DOB])) This will give you the birthdate in the current year. From there you can query for a date range. Alternative you can add two columns to your query: BirthMonth:...

Using MS Access: Trying to sum dates in a access query linked to an excel spreasheet., customer part number, access store
customer part number, access store, excel2002: Both Excel and Access store dates as a serial number starting at 12/31/1899. Formatting in either Access or Excel does not change the underlying value, just the way its displayed. If you want to do date arithmetic, you need to leave it as a Date field (or...

Using MS Access: Trying to get a total with out creating another colum, colum, possable
colum, possable, expression: Hello Mary, The question was jumbled so I repeat.. Question: I have two totals in a Table, running a Query I want to take one which is a credit and have it autmatiacally be subtracted. [Number of Shipments] - [Credit Number of Bills] = [Number of...

Using MS Access: Turning off the mouse scroll button function when using forms, button function, mouse wheel
button function, mouse wheel, mouse scroll: I ve heard others reporting this problem, but I m not sure of how to disable it. I ve heard there may be a utility that might help. Try doing a web search on Microsoft Access Mouse Wheel and see what you can find. Hope this helps, Scott Please don...

Using MS Access: Type Mismatch in Expression, numeric field, type mismatch
numeric field, type mismatch, alpha numeric: Christie Hi, This is a well known problem for Access developers. The best solution, is to append an extra column in the excel spreadsheet using the TEXT function to convert the column in consideration - then in Access, use the function column, not the...

Using MS Access: Type mismatch error, type mismatch error, contractor name
type mismatch error, contractor name, control source: The subform and the combobox are TWO separate things. The combobox is used to populate the ContractorID field tblBaseContracts. The Subform is used to display data about the contractor. The subform is linked to mainform on ContractorID. You don t need to...

Using MS Access: Can't get my chart to work right, fk link, peace and tranquility
fk link, peace and tranquility, attribute data: Hi Jet I hate Access charts with a passion ! Some things to check.. 1. Is you query behind the report generating the correct data, including the PrimaryKey/ForeignKey fields used for sorting/grouping by customer and period ? 2. When dealing with...

Using MS Access: I can't open in a access data base file, unrecognized data, access data
unrecognized data, access data, access database: Hi Thomas, This is a hard question to answer with out further file information. My only ideas at this stage is that the file is not a Access database file or that it was created in a earlier or later version of MS Access that you are trying to use it in....

Using MS Access: can't open in a access data base file, antony thomas, unrecognized data
antony thomas, unrecognized data, database utilities: Thomas Hi, The file is probably an Access 2000+ version and you are using Access 97- version. Solution is to use a computer with Access 2000+ to convert the file to Access 97, then use the converted file in Access 97. To convert an Access 2000 file...

Using MS Access: Can't open access data base file, unrecognized data, blank database
unrecognized data, blank database, mdb file: Hi Tom, Sounds like the MDB file that you are trying to open has a problem. Did you try creating a blank database and import the tables from the file? If you can import the tables, you can have the data. If this is an MDB file, your Access should open it...

Using MS Access: Can't open access file, member roster, access file
member roster, access file, office documents: Hi Yeah - I remember it. The response I gave was to create a shortcut to the file and prefix the shortcut will the full path to the Access.EXE I.E. C:Program FilesOfficeAccess.EXE C:FOP ameofyourdb.mdb I think I was waiting for you to...

Using MS Access: Can't open a Microsoft Access File, mdb extension, access document
mdb extension, access document, member roster: If I recall the answer it had to do with naming the icon properly. The target of your icon didn t have the MDB extension that tells Windows what to use to open the file. If you right click on the icon and go into Properties, you can correct this. HTH Scott...

Using MS Access: Can't print report in landscape, landscape view, landscape format
landscape view, landscape format, speedy reply: Hi I m not sure if this is a bug related to the SP or not. Have you tried opening the report by using VBA? Did it make any difference? (I know you said you had no code in the db but just want to eliminate any possibilities and also know if it works using...

Using MS Access: Can't update field list in a report, recordsource property, select statement
recordsource property, select statement, access database: What you meed to check is the RecordSource property of the report. This should be either a table, query or SQL Select statement. Its probably NOT your table if the fields aren t showing. So its either a query or a Select statement. In either case you have...

Using MS Access: Can't view every feilds in a form, blank records, visible property
blank records, visible property, tab keys: Hello Umaimath, Three possible problems: 1. There are blank records in the table which cause the form to go blank - solution delete the blank records 2. Form objects (eg. Text boxes) are missing for some of the table columns - solution open the form...

Using MS Access: tab controls, customer table, multiple records
customer table, multiple records, tab control: No, nor would I. A tab control is used to display additional info about a single record, or a subform with related records, not multiple records. What you should be using is a subform. Sounds like you have a one to many relation between your customer table...

Using MS Access: tab controls, tab controls, tab control
tab controls, tab control, hth: I m not clear how you want to accomplish this. Each Tab in a Tab Control has a name. This is a direct reference on the form. So if the name of the tab is page2, then the code should work. What s not clear is what state you are testing for page1. HTH Scott...

Using MS Access: tabbed control "click" event problems..., filter data, tab control
filter data, tab control, child tables: Where are you clicking? I just tried a test and when I click on the tab itself, nothing happens. But when I click on the page, the event is kicked off. Hope this helps, Scott P.S. I m going on vacation tmmw so may not be able to get back to you for...

Using MS Access: A tabel in alphabetical order, colums, combo box
colums, combo box, noordwijk: You need to set the RowSource of the combo to a query on the table. You can then set the sort order in that query. RowSource should look like: SELECT DistanceNumber, LeavingFrom, HeadingFor, Distance FROM table ORDER BY LeavingFrom, HeadingFor; HTH...

Using MS Access: table, medicine name, patient id
medicine name, patient id, creating a table: Hi loko! I think if I am reading your question correctly you are going to need more than one table. The first thing you will need to do is add a column to the table you already have. Call it Patient ID or whatever you want to call it. You can also make...

Using MS Access: table border, blank space, sure what this means
blank space, sure what this means, field names: The border of your table? I m not sure what this means. You have a table displaying it s columns and rows and you want to resize the fields so they fit the text? That is done by double-clicking on the line between the two field names at the top of each...

Using MS Access: table,field>other table,field, combobox, three tables
combobox, three tables, database project: Christian, Many thanks for the question. What you need is a sub form instead of a combo box. The subform wizard can be found on the toolbox wizard, just add to your form, the same as for the combobox, then follow the wizard through. Choosing the...

Using MS Access: table lookup, source types, combobox
source types, combobox, family thanks: A combobox can use one of three source types. Either a Value List, a Query or a Field List. Field List doesn t apply here. Since you only have three choices and its unlikely you would add more, I would tend to use a value list here. You can use the Combobox...

Using MS Access: What table do I need for my database, foreign key, company software
foreign key, company software, hth: I would start with tables like so: tblTitles TitleID (Primary Key Autonumber) Title tblMedia MediaID (PK Autonumber) TitleID (Foreign Key) LicenseID tblUsers UserID (PK Autonumber) First Last Location tblUserSW UserSWID (PK Autonumber)...

Using MS Access: table setup - relationships, relational database management, seperate tables
relational database management, seperate tables, subsidiary company: Well first off, you need to make sure relationships are set up in the relations section of Access to make sure the integrity of the data is set. This is the basics of Relational Database Management Theory. Once that is done, you can use the forms and queries...

Using MS Access: table, field medicine, microsoft access
field medicine, microsoft access, mvp: First you would need Access 2007 to do that. It has a new, multi-value data type. However, that is NOT good design. If you need to note multiple values for a record, then you should be creating a subtable. HTH Scott Microsoft Access MVP 2007 ...

Using MS Access: tables, expression
expression: Here s another aspect of incorrect structure. I think I ve mentioned that, as a general rule, we do not store calculated values. And this is a calculated value. An expression like: =Dcount( [BuildingID] , tblBuildings , [ClientID] = & [txtClientID])...

Using MS Access: one to many tables, coloumns, colomn
coloumns, colomn, cross tab: That s not the point. Your relation is a many to many, you can t get around that. You only other option is to use code like that found here: http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=228834&Zf=&Zw=concatenate&Zg=0&Zl=a&Main=228834&Search=true&where=&Zu=66&Zd=l&Zn=&Zt=1&Zs=b&Zy=#Post228834&Zp=...

Using MS Access: two tables to a form, one tables, control source
one tables, control source, text boxes: Hi Sami Method 1 - Build a query comprising all tables then make the query the control source of the form. Method 2 - Build a second form based on the second table and bring it into the first form as a sub-form, and link the data using the Master/Child...

Using MS Access: tables in memory & diff. between versions, visible property, application load
visible property, application load, access2000: Hi Joel There are very major differences between Access 97 and 2000+, including file format, as well as use of ADODB in 2000+ and DAO in 97. However, apart from these primary diffs, the VBA is similar / identical. To achieve your second point, you would...

Using MS Access: tables (probably simple), competition league, league id
competition league, league id, auto land: Hi Jack, On the third table, for the row source for the field League, you will need to add WHERE... criteria. I am sure you already know how to use WHERE criteria in a SELECT statement. If I can help you on that please let me know. I hope this helps. ...

Using MS Access: tables with similar fields, contractor table, assignment table
contractor table, assignment table, seperate tables: Derrick, Hello, It would probably be a good idea to take a step back and ask yourself some questions: 1. Can a Contractor be an Employee? and vica versa ? 2. Could your have many Contractors per assignment ? 3. Are Contractors and Employees related...

Using MS Access: tables, share one, thanks in advance
share one, thanks in advance, pk: Hi Kedaish If you set up the table tblAddress with an autonumber primary key (PK), then you can have a foreign key (FK) field in each of the tblTrainers, tblTrainees and tblCompanies tables that carries the appropriate address ID number. To bring...

Using MS Access: How to tell which queries are referenced, table structures, access database
table structures, access database, access 2000: I ve had the problem myself, and do not know of any code that can do this. What I did, was creating a new access database, and copy the table structures over into it, and then the form and reports. Then i start the program, and get it to crash each time...

Using MS Access: text box moving left using VBA, run time error, left hand side
run time error, left hand side, dear mrs: Hi Krystal, Many thanks for the question. I think the code you are looking for is the following: Me!Text0.Top = 255 Me!Text0.Left = 2773 Change the numbers (twips) to suit your own scenario. The Top property positions the text box X twips from...

Using MS Access: text box, combo box, many thanks
combo box, many thanks, wizard: Jeff, Many thanks for the question. As easy as this seems to be it isn t. You will need to do some coding for this to occur. A way around this is to create an unbound combo box, by following the wizard, adding the table and field you wish to check...

Using MS Access: text boxes, combo boxes, exact record
combo boxes, exact record, text boxes: Dear Epuser, MSAccess refused to edit or delete anything if it can t join at least one of the two table s to a Uniq primary key. I always test this in the qeury to see if i can still change anything. I think access is a little concerned about the data integrity...

Using MS Access: text to column, time field, microsoft access
time field, microsoft access, time data: Actually, no. As long as the field is a Date/Time field there is no reason to split it. There are several Date functions that you can use. Year(), Month() and Day() will extract that part of a date. So, if you wanted to show all December events regardless...

Using MS Access: text to columns in Access, datevalue, decimal portion
datevalue, decimal portion, integer portion: You are going to need to add a category for holiday period. You might do this using a custom function like: Public Function Holidayperiod(intMth As Integer) As String SELECT Case intMth Case 1 Holidayperiod = New years Case 2 Holidayperiod =...

Using MS Access: text field to the left using VBA, run time error, moving objects
run time error, moving objects, dear mr: Can you give me a little background on why you need to do this using code? It looks to me like the problem is that you re treating move as a property when it s not. If you re in the VBA editor and you type in: me.txtUSONLY. It should automatically...

Using MS Access: text format, input masks, input mask
input masks, input mask, patient letters: Didn t I answer this earlier today? Scott Yeah I did. In case you can t find it: Subject: text formating Question: Scott: my problem is getting clerks to enter names properly to be used later in patient letters - Is there any simple way to...

Using MS Access: text formating, input masks, input mask
input masks, input mask, patient letters: It doesn t matter how they enter it, its how YOU store it or format it for display. The first thing I would do is read up on Input masks in Access Help. I would then set an Input mask to force all upper or lower case. I would then use the STRCONV function...

Using MS Access: text input limitations, table definitions, text input
table definitions, text input, len: You can t define a minimum number of charaters in the table definitions.The only way to do that is by putting some code in the form. For example: in the OnExit of the textbox: If Len(Me.TextBoxName) 12 Then MsgBox The field size must be 12 characters....

Using MS Access: textboxes, control source, textbox control
control source, textbox control, object names: Hi MOT, Assuming the first form remains open (but perhaps hidden by the second form), then you can simply set the textbox Control Source property to: =Forms!frmFirstFormName.Textboxname.Value You will need to edit the frmFirstFormName and Textboxname...

Using MS Access: time in access 2000, time fields, access 2000
time fields, access 2000, many thanks: Johnn, Many thanks for the question. Try one of these (Change the [TimeExample] fields to the name of your fields): This first one is adding of two time fields in a query. SumTime: Format(([TimeExamples]+[TimeExamples1])*24, #.0 & Hours ) ...

Using MS Access: time calculation, decimal portion, integer portion
decimal portion, integer portion, control source: The only way I see how to do this is to calculate the elapsed time between start and end in 3 segments. The time between start and 8PM, the time between 8PM and 6AM and the time between 6AM and end. Use a DMax function to add a sequential numbering. ...

Using MS Access: time problem, time problem, time field
time problem, time field, time value: You need to give me or detail here. If you have a time field that stores a time value, you can set criteria to list any time after a specified time. But if you want it only to show times that were on the hour, you have to do something diffferent. Or if you...

Using MS Access: timed alert, filter buttons, patient id
filter buttons, patient id, filter criteria: Hi Boyd, You will need to design the complete process to ensure that only 30/120 day follow ups without a form are reported One way.... Have two Yes/No (CheckBoxes) datatypes in the PatientOperation master record - one when checked(True) will mean...

Using MS Access: How many times each record on the many side is found, query design, sql statement
query design, sql statement, design mode: Use a Group By (Totals) query. In Query Design mode, select the table and add the Carton and Bottle ID fields in that order. Press the Sigma icon on the toolbar which adds a Totals row to the query. Set the CartonID as Group By and the BottleID as Count. ...

Using MS Access: title, upper beam, caption bar
upper beam, caption bar, translater: There are several properties assocaited with the caption bar and borders. If you look at the form in design view and select the property sheet you can get at properties like Borders, Max/Min buttons, Close, etc. Play around with them and you ll get what...

Using MS Access: toggle units, diving logbook, depth column
diving logbook, depth column, conversion formula: Add a button with an initial caption of Show Meters. Behind the button put code like: IF Me.cmdMeasure.Caption = Show Meters THEN Me.txtDepth = Me.txtDepth * x Else Me.txtDepth = Me.txtDepth * x End If x would be the appropriate conversion formula...

Using MS Access: top 5 records of query, query design, optin
query design, optin, access 2000: Hi Apologies, I picked you up wrong. Sounds like the query is still editible. Set the data recordset type in the query property sheet to... Recordset Type = Snapshot I think you may be getting the above because you have a default value in one...

Using MS Access: top 5 rows of query, data access page, access 2000
data access page, access 2000: Hi In the query property sheet you ll see an entry for TOP VALUES - insert 5 into this. This will then restrict the query to return only 5 records - subsequently, anything that is based on this query - your report, data access page, etc. will only return...

Using MS Access: How to total a column of hours, decimal portion, datediff function
decimal portion, datediff function, integer portion: You may not be calculating the differences correctly. The problem lies in the way Access stores date/time values. The data is stored as Double precision number with the integer value representing the number of days from 12/30/1899. The decimal portion represents...

Using MS Access: Getting totals in a database, william dwyer, report wizard
william dwyer, report wizard, followup question: First, when asking a followup question please use the followup options. I answer dozenhjs of questions daily and can t always remember the original question and answer. In this case it would definitely have helped to see your original question and my answer....

Using MS Access: tough form question, command buttons, related records
command buttons, related records, two tables: Dear Robert, You need to split these two tables and create a form together with a subform. The form bound will be Clients and the subform bound will be Subsidiaries . This way you will only see one client with none or more subsidiaries in the subform....

Using MS Access: transferspreadsheet using list box, column headings, drive path
column headings, drive path, spath: Mike, hi, First - use a combo box rather than list box to ensure only one selection may be made at a time Second use multiple columns in the recordset behind the combo box to store the folder and other download variable data In the AFterUpdate event...

Using MS Access: troubleshooting, weird thing, odbc
weird thing, odbc, double click: Dear Dusan, I don t really know what the problem is, but i know certain fields, like bit fields cause problems in access, typical Yes/No fields. To overcome this problem i changed the bit fields in sql to short Integer and the problem disappeared. Hope...

Using MS Access: truncate, column count, combo box
column count, combo box, question thanks: Your RowSource is wrong. Assuming Title is the field you want to truncate it should be: SELECT [Projects].[Project_ID], Left([Title],50) & IIF(Len([Title]) 50, ..... , ) AS AdjTitle FROM Projects; You needed to use the actual Fieldname in the expression....

Using MS Access: truncate, blank grid, drop down list
blank grid, drop down list, text string: Hello Adam - very good question! The best way I found to do this was as follows; 1) Design a query based on all the fields that are on the form, that holds the Drop down list 2)While in the design view of the query you just built, got to the very next...

Using MS Access: I am trying to figure out..., seperate category, summary option
seperate category, summary option, wizard: Hi HQ, Normally we use the Sorting Grouping menu item under the View menu while you are in the design view for the report. Sometimes this method works the best for me: When you create a new report, if you use the wizard you will have the opportunity...

Using MS Access: Unbinding a control, competency levels, combo box
competency levels, combo box, primary key: You have to have two related tables with the dield SpecialityId, the first table is the mother table where specialityId is Autonumber and the primary key, de child table has SpecialityId field as Integer You can bound a form to the mather table, an place...

Using MS Access: Unbound Form Data into Table, dim rs, addnew
dim rs, addnew, vb code: Dear Howard, VB Code like this can append data to a table: Dim Rs as Recordset Set Rs = Currentdb.OpenRecordset( MyTable ,dbopendynaset) Rs.Addnew Rs![My Field1] = me.FormField1 Rs![Another Field] = me.MyField2 etc...... Rs.Update Rs.close ...

Using MS Access: Unbound subform, textbox control source, place cursor, control source
place cursor, control source, text boxes: Referencing a control on a subform requires the full syntax which includes the Main form. Forms![mainformname]![subformNameAsYouSeeItInMainFormDesign]![Name OfControlWho sControlSourceYouWantToChange] One way to have Access write this syntax for you is...

Using MS Access: Under question sql or access, ms sql server, microsoft databases
ms sql server, microsoft databases, clinical survey: Hi I only use microsoft databases such as sql server and access :) so can t send screenshots for mySql. You didn t state, what you need to do, so it is hard to recommend a database. But here are a couple of example recommendations :) If you re system...

Using MS Access: Undo function, input data, error message
input data, error message, scope: Hi Greg, In lieu of: Me.Range.Undo Me.Fanily.Undo Me.Name.Undo .....etc (its a long list) try: DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 Hope this helps Kind regards Geoff...

Using MS Access: Unicode Compression, ucode, google
ucode, google, workload: Hi, You can do this via VBA, unforuntately its quite an involved process as you will need to enumerate the tabledefs collection which is above the scope that I can help you with due to my current workload. I would suggest doing some searches via google...

Using MS Access: Unique Key, share identification, welcome thanks
share identification, welcome thanks, school id: Not in the same field you can t. (At least not easily.) It sounds to me like that would be two separate pieces of data (the letter, then the number), and those should be in Two separate fields. You can have a tables primary key be a concatenation...

Using MS Access: Getting Unique records, unique records, query design
unique records, query design, ms access: Dear Rachel, You need to create a query that is sometimes called a Grouped query. In query design view, click on View/Totals . This open a line called Totals . Drag the four unique given fields down and make sure all four has a Group by entry in...

Using MS Access: UniqueValue in query not working as expected., auto increment, long integer
auto increment, long integer, duplicate records: Dear Sanjay, The keyword DISTINCT mean it will take the selected fields list them, and only remove the records that is identical. So in your example all the SrNo s has a new number, so by listing this field with the others will list everything. In your...

Using MS Access: Unsecuring a database, user level security, corruption problems
user level security, corruption problems, security setup: You would run the security setup to remove security. Frankly I never use Access security so not sure how. Splitting a database means that the tables go into a back end file that is on your network share. Everything else goes into a front end that should...

Using MS Access: Unwanted blank lines in Access Reports, mailing labels, format tab
mailing labels, format tab, text boxes: Leanne, Many thanks for the question. To get rid of blanks lines in a report do the following: Right click on the textbox in question and select Properties from the drop down list. Click on the Format tab change the Can Shrink property from...

Using MS Access: UPDATE QUERY, code 61, criterias
code 61, criterias, single action: Hi Luther With the constraints you have shown you will need to do this in 3 separate queries, though, I am not sure if you have made a typo in stating the constraint for 1 & 2. The only way I know of to turn this into a single action would be to load...

Using MS Access: UPDATING AGE, birthdate field, initial input
birthdate field, initial input, yyyy: Hi, Many thanks for the question. I am presuming you are using this in a form? I am also presuming [birthdate] is a field on your form and when you change this date, the age does not change in the age field. If I am correct, then try the following: ...

Using MS Access: UPDATING TABLE WITH CALCULATED VALUE, expression builder, database programmer
expression builder, database programmer, training wheels: Hi Robert Appreciate your situation. Have a question for you. In database design - the model would not normally contain a calculation. I.e. We wouldn t store the calculated value. Any time we needed to know this - we would just multiply the values...

Using MS Access: How Do I Upate a Form with information from another form?, ms acess, room location
ms acess, room location: You should just be able to run an Update query. Create an Update query to update the recordset that drives the Equipment form. Include the RoomLocation field in the query, and use the builder (the elipses (...) button) to find the field in the removal form...

Using MS Access: Update Access Web Page, data access page, page hi
data access page, page hi, daps: DAPs are not my strong suit, but I can give you some feedback. First, if the DAP is bound to a query, then you need to add fields to the query before you can add them to the page. Second, You should use UNC coding whenever possible. UNC coding follows the...

Using MS Access: Update and Append of text file, head office location, operational attributes
head office location, operational attributes, docmd: Hello David, Your process is exactly like many that I have developed over the years and it should work fine. One important item to resolve at the outset is what/how you will identify records, and differentiate new recs from existing recs. This needs...

Using MS Access: Last Update Date, textbox control, yyyy
textbox control, yyyy, dd: Steve, I assume there is a textbox control on the form named update_date . The form s AfterUpdate event relates to form changes, such as new record, save record, next record etc., and not changes to data in controls on the form. Try the following...

Using MS Access: Update date in subform, run time error, validation problems
run time error, validation problems, docmd: I think I need to see this to track the problem. I would suggest you zip a copy of your database and attach it to a post at askmehelpdesk.com or utteraccess.com. Someone can then check it out and see where the problem is. Make sure you indicate what form...

Using MS Access: Update a date, due date, interval
due date, interval, update control: You need to kick this off in the After Update event of the Last Update control. That s the only way to determine when that changes. However, you might consider putting this all in a separate table and maintaining a history of updates. What you would do...

Using MS Access: Update a field in a form using another form, record source, form1
record source, form1, isnull: OK, first things first... OMG, stop using spaces and reserved characters in the names of your objects. It ll create problems later. Anyway, add to the code on the click or double click or whereever you have it: Forms![frmVendor Inventory Tracking...

Using MS Access: Update field from a text file..., command button, vba code
command button, vba code, line input: Moe, My code won t help you since it based on the layout of the file I have to import. The example I posted in that answer is what you need. Open TESTFILE For Input As #1 Open file. Do While Not EOF(1) Loop until end of file. Line Input #1, TextLine...

Using MS Access: Update multiple records with one button, referenced questions, product ship
referenced questions, product ship, multiple records: First the situation is not at all similar to the referenced answer. Second, if you are stuck a denormalized structure like this because you are getting data from another source, then you methodolofy is reasonable, though unnecessary. I would do it differently....

Using MS Access: Update Querry MS Access, latitude d600, query design
latitude d600, query design, relevant table: Mike, Many thanks for the question. To do what you ask: NOTE: make a copy of your table first in case you make a mistake you cannot undo the update. (Right click on the table copy, then right click on a blank section paste) Choose to create...

Using MS Access: Update Query, ms access 2000, inventory database
ms access 2000, inventory database, pallets: Hi First of all create a query that supplies your data. I.e. The pallets used each day. Then create a new query - straight forward SELECT query - save it - then convert it using the menu to an update query. Then set the fields you wish to update...

Using MS Access: Update Query, maryam, access database
maryam, access database, error message: Hi Kevin I can see some syntax issues - if that was me, then excuse me for that... Sub txtBB_OnExit Dim sSQL as String sSQL = UPDATE tblBBB SET & _ [AA] = & Me.txtAA & , & _ [BB] = & Me.txtBB & & _ WHERE...

Using MS Access: Update Query, query design, command button
query design, command button, school database: Hi, Many thanks for the question. To do what you ask, the easiest thing would be to build an update query, to do this: Create a query in Design view, add the table with the field you wish to update. Close the table window when you have done this....

Using MS Access: Update through Query, recordsource, bottom bar
recordsource, bottom bar, microsoft access: First users should never update data through tables or queries. You should always use a form for that. If you query is multiple table, then it will often be not updateable. What you should be doing is using forms with the Recordsource a single table or single...

Using MS Access: Update Query Based On Two Tables, temp info, two tables
temp info, two tables, occurrences: At risk of a poor review, I will give this a try. The only way to join the two tables is by a common field. Once that is established, you can compare values between both sides and then create a derived column with DerivedColumn:iif(table1[xxx] table2[yyy],...

Using MS Access: Update Query For A Grand Total, aggregate function, inventory database
aggregate function, inventory database, us navy: I m not real strong on SQL...but I m wondering why you need to update anything? If you want a grand total, why aren t you calculating it in a report? Is TotalAmount a field in your table, and you re wanting to update every record with your total in that...

Using MS Access: Update Query Question, sql
sql: Are you sure that s the message? An Update query would not replace the table. It would only update values in the table. What is the SQL for the query? As for suppressing messages, you can use CurrentD.Execute queryname in a code module and the messages...

Using MS Access: Update Query, algebra teacher, datediff
algebra teacher, datediff, long integer: The first thing I noticed was your data type. You re reviving the algebra teacher in me from days long ago. An integer, in layman s terms, is a whole number. So by definition, it has zero decimal places. Your display may be set for 2, but if you re generating...

Using MS Access: Update query, query wizard, cross tab
query wizard, cross tab, query in access: Hi Steven, Many thanks for the question. I think what you need is a query, rather than a table. I also think what you probably need is a cross tab query, but first you will need to create a query to base the cross tab query on. Build a new query...

Using MS Access: Update query, customer orders, andrea
customer orders, andrea, relationship: Carol - Your update query did something, just not what you wanted it to do. I d look at the data in your CustomerOrders table carefully to see what happened. As for your Update Query - It should have both the CustomerOrders table and the CustomerFile...

Using MS Access: Update query, lord answer, employee id
lord answer, employee id, naming convention: First, If the employee ID is named Employee No in qryLasttrained, then you need to surround it with brackets. Second, if its a text data type then you need to use single quotes like so: [Employee No] = & me.txtEmployeeID & ) The ME qualifer refers...

Using MS Access: Update records, ssn field, many thanks
ssn field, many thanks, second time: Hi Paolo, Many thanks for the question. You will need to use the left, right and mid functions to achieve this. In the Update to row enter the following code: Left([SSN],3) & - & Mid([SSN],4,2) & - & Right([SSN],4) NOTE: Make a copy of the...

Using MS Access: Update from Subform, access tutorials, access tutorial
access tutorials, access tutorial, president ceo: This would involve quite a bit of VBA code. There is no easy solution, and I wouldn t be able to answer it in a few paragraphs here. If you d like to send me your database and I can look it over, contact me via my TechHelp site (see below). Let me know...

Using MS Access: Update the stock field in a table, stock field, inventory application
stock field, inventory application, foreign key: The answer is you don t. Stock on hand is a CALCULATION and as a general rule we do not store calculated values. The correct way of dealing with an inventory application is to have a transactions table that records all movement of stock. You then calculate...

Using MS Access: Update table from a calculated field on a form, current event, joshua
current event, joshua, field calculation: If you need to insert the result into a table other than the one the form is based on, I would use an append query. If you are actually inserting it into a field on the main form, I would use code (maybe behind the On Current event of the form?) to update...

Using MS Access: Update one table with values from another, warrant number, temp table
warrant number, temp table, twh: Dear Randy, I see this way of coding work ok in SQL but in Access i could not get it right. The only way that work is to first create a temp table with the result of Max(StatusDate) with a primary key warrantnumber. Then use a update query joining on the...

Using MS Access: Last Updated Column in MS Access, ms access, many thanks
ms access, many thanks, novice: Avril, Many thanks for the question. You can only update this field, via a form, you cannot do it from within the table itself - is there a form based on this table? If so, let me know and I will send you some instructions. Cheers Julie www.simply-access.co...

Using MS Access: Last Updated Field, data tables, matter of course
data tables, matter of course, distinction: First, you don t have fields on forms, you have controls that may or may not be bound to a field in a table. This is a subtle but important distinction. Second, I do this as a matter of course. I usually add the fields UpdatedBy and UpdatedWhen to my...

Using MS Access: Updating 2 tables on one form, budget detail, employee id
budget detail, employee id, column names: Ray, Hi I assume that you have budget detail in one table and budget total in the other table. I will assume tblDetail contains the detail (BudgetHrs) and tblTotal conatins the totals (TotBudgetHrs), and the common key is EmployeeID The queries below...

Using MS Access: Updating 2 tables with one SQL statement, strsql, assignment data
strsql, assignment data, combo box: Tom Just add another SQL clause to the procedure.. Private Sub btn_Assignment_Folloup_Submit_Click() Dim strSQL As String strSQL = INSERT INTO tbl_Followup (AssignmentNumber, FollowupDate, FollowupNotes); strSQL = strSQL & VALUES( & Me.Combo24...

Using MS Access: Updating an Access 2002 DB, access 2002 bible, field pointer
access 2002 bible, field pointer, relational db: Hi Martin There are a few typos.. Try.. Dim CurConn As ADODB.Connection Dim rst As New ADODB.RecordSet Set CurConn = CurrentProject.Connection rst.Open tbl1 , CurConn, adOpenKeySet, adLockOptimistic, options:=adCmdTable With rst .AddNew !tbl1IndexField...

Using MS Access: Updating a calculated field on an access form, text boxes, combobox
text boxes, combobox, question question: Ibukun, Many thanks for the question. Try the following: Right click on the combobox and select Properties from the drop down list. Select the Event tab and place your cursor next to After Update , then click on the ... button that should...

Using MS Access: Updating A Database, database link, cheers
database link, cheers, queries: Hi Karen, The easiest way to do this would be to split the database into two files. Have your tables in one database and your queries, forms and reports in a second database. In your second database link the tables to the first one. This way you can...

Using MS Access: Updating SOME data in a table, relational databases, key field
relational databases, key field, principle: First, I must question why you are doing this. A principle of relational databases is that data should exist in ONE place only. Since you have a key field in both tables, you can simply use that to join them and then pull the data you need from the respective...

Using MS Access: Updating a date field in Access automatically with current Date, tiome, client pcs
tiome, client pcs, best bet: I wouldn t know. ASP is a way of creating interactive web pages. If the front end to your Access database is done thru ASP pages, then I would guess its possible, by changing the the coding, to change the date being picked up. But that could be violating the...

Using MS Access: Updating Field Values On another Table, circular relationship, blank string
circular relationship, blank string, primary keys: On your MonitorInfo Form use the command button wizard to create a Delete Record button. Then open the On Click event of that button and add code like the following before the delete command: Dim strSQL As String strSQL = UPDATE MachineInfo SET MonitorAssetNumber...

Using MS Access: Updating Fields with SQL, docmd, strsql
docmd, strsql, string changes: Hello Sunny, From your descriptive, the Machine table is the Parent and the Monitor table the Child table You should be able to delete the monitor directly using the following SQL: strSQL = DELETE * FROM MonitorInfo WHERE MonitorAssetNumber =...

Using MS Access: Updating one field from nother, database tables, two tables
database tables, two tables, number 3: Craig, Many thanks for the question You are almost there Try the following (ie replace the + with &) Left([tblEquipment].[Location],3) & Left([tblEquipment].[Department],3)& Left([tblEquipment].[Number],3) If you could give me a bit more detail...

Using MS Access: Updating a field, message field, propertyid
message field, propertyid, boxes: Looks to me like the query you wrote creates a recordset that cannot be updated, and you re trying to update it through code. You ll have to change your query. PS: Take the SellerID field out of the tblSales table. It s redundant (since there is that...

Using MS Access: Updating form information, volunteer hours, time field
volunteer hours, time field, foreign key: You shouldn t have a form for each person. You should have at least 2 tables here. One for the Volunteers and another for their hours, something like this: tblVolunteers VolunteerID (Primary Key Autonumber) Firstname Lastname other info about each volunteer...

Using MS Access: Updating forms and/or content., customer data base, export option
customer data base, export option, check boxes: Hello Mark - I was browsing through the question pool and came across your question. As far as executing some sort of deployment package to install your changes - IT IS JUST NOT POSSIBLE. As far as adding remote records to the main database with an append...

Using MS Access: Updating linked fields from a form, value model, foreign key
value model, foreign key, microsoft access: What is the relationship between the two tables? Is it one to many or 1:1? If its one to many then the ONLY value from table1 that needs to be in table2 is the ProdNum as a foreign key (not the primary key). Hope this helps, Scott Microsoft Access...

Using MS Access: Updating multiple records, docmd, multiple records
docmd, multiple records, sql code: Hi Jamie If the main form is called frmMain and the subform and its object on frmMain are called sfrmDetails, and the checkbox object is called cbxRec, and the URN textbox is called txtURN, and the table called tblDetail Code for frmMain!cbxRec Sub...

Using MS Access: Updating multiple records, multiple records, e mail
multiple records, e mail, dbase: You need to create an Append query. Something like this: INSERT INTO Calls (ContactID, ContactDate, ContactType) SELECT ContactID, #5/10/06# AS ContactDate, E-Mail AS ContactType FROM Contacts WHERE .... The Where clause is what you used to select...

Using MS Access: Updating multiple tables from 1 form, model location, supplier address
model location, supplier address, record source: open the database window open Suppliers table in design view and add the follow field: IdSupplier declare it as Primary Key and set it as Autonumbered type. Add an IdSupplier field in the other two tables. Set them as Numeric Long Integer. Open the relationships...

Using MS Access: Updating a Query, vba procedure, redundant data
vba procedure, redundant data, qbe: Raddie, hi, What I am not sure of is: 1. Are you implying that the column structure is changing from week to week, or 2. that the structure is identical, but the data contained has moved on and needs to be refreshed. If its #1, then you need to organise...

Using MS Access: Updating Stock, quantity sales, stock products
quantity sales, stock products, quantity purchase: Wrong approach. Stock is a CALCULATED amount, not a stored one. You calculate stock by adding all your purchases and subtracting all your sales. For example you could use DSUM formulas, like so: DSUM( [Quantity] , Purchase , [ProductID] = xxx ) - DSUM(...

Using MS Access: Updating select records, normalization rules, select records
normalization rules, select records, record source: First, it sounds like your database is not constructed properly. It also sounds like you are making things a lot more complex then you need it to be. If you have checkboxes for each Group with corresponding fields in your table, then you have a Repeating...

Using MS Access: Updating stock, stock field, quantity field
stock field, quantity field, details table: I think you may have misunderstood my answer. Since the answer was that you shouldn t have a stock field in the Products table. Inventory tracking is a specialized function. It centers around a transactions table that records all movement of stock in or...

Using MS Access: Updating subform within a form, control source, question thanks
control source, question thanks, account profile: Dear Jack, As far as i see a requery will not solve this, you need to update this value. It is very unusual to update a subform out of a main form. It s normally the other way around. Here is the code to update the value of the subform. Private Sub Order_Value_AfterUpdate()...

Using MS Access: Updating subforms within a form, account profile, human error
account profile, human error, saving time: Jack Hello Use the AfterUpdate of your textbox with the order value Run the ReQuery action on the subform object Eg: Sub txtValue.AfterUpdate() Forms!frmMainform!sfrmSubformObjectName.Form.Requery End Sub Change the textbox, form and...

Using MS Access: Updating Tables., composite keys, primary key
composite keys, primary key, microsoft access: Well first, I don t recommend having multiple key fields. Composite keys cause more problems then they are worth, especially when joins are required. I d need to know more about your data sructures to help further. Hope this helps, Scott Microsoft...

Using MS Access: Updating table fields with formulas, dateadd, dsum
dateadd, dsum, isnull: Ahh I see at least partially. If your external system provides the days delinquent then what more do you need? The only thing that makes sense to me is that you might need to calculate a date billed to determine what records to include. You cna do that with...

Using MS Access: Updating a table with SQL, vba code, primary keys
vba code, primary keys, two tables: Dear Sunny, Tables that are linked must be set up properly before a update can take place: Your statement should look like this: UPDATE MonitorInfo INNER JOIN MachineInfo ON MonitorInfo.MonitorAssetNumber = MachineInfo.HasMonitor SET MonitorInfo.Attached...

Using MS Access: Updating my table, expression builder, control source
expression builder, control source, dear james: Dear James, On a form the result of a formula/expression builder does not update a field because the formula/expression is inserted in the place of the field. So to update the field together with the form you need to write a small Event Procedure to achieve...

Using MS Access: Updating tables via subforms, ms access 2000, bound form
ms access 2000, bound form, windows xp: You need to be more specific about what you are trying to do and how you are using subforms. Generally a subform is used to display and/or enter data in RELATED tables on one form. For example, you might have a main form based on an orders table and a...

Using MS Access: Updating "Last Updated" Field Automatically, scott brooks, data tables
scott brooks, data tables, current date: I do this as SOP on most of my apps. I generally add 4 fields to my data tables: CreatedWhen, CreatedBy, UpdatedWhen and UpdatedBy. On my data entry forms I set the CreatedWhen and By controls to default to Now() and fOSuserName() (You can find this function...

Using MS Access: Upgrade and automatic backups, mdb files, mdb file
mdb files, mdb file, file database: Again you need to have the database split. So you are only upgrading the front end. How you upgrade the front end depends on where its accessed from. If the front ends are stroed locally, then you need to replace the front end on each local PC. If its accessed...

Using MS Access: Upgrading to Office 2003, microsoft web site, vba code
microsoft web site, vba code, access 2: My company also recently went from Office 97 to Office XP. And I had to convert over a dozen Access apps. The conversion has gone very smoothly with almost no modification needed. There were a few instances of miscellaneous errors after the converted databases...

Using MS Access: Upgrading to Office 2003, microsoft web site, vba code
microsoft web site, vba code, default library: Hi Aidan Yes this can be very tricky. The basic difference are... Pre 97 - 16 bit code base Post 2000 - New default library (ADO) I would not believe what MS say about 2003 running your databases - Office 2003 has the ability but you re...

Using MS Access: Upload a file to server via Access form, command button, testit
command button, testit, microsoft access: 1) is it always the same place or does the user have to select it? If its the same place, you can use the COPYFILE method to copy the selected file. 2) the new location can then be saved as a value in a record. 3) Depends on the type of file. Hope...

Using MS Access: Upload image, open dialog, access api
open dialog, access api, member database: You can use the code found here: http://www.mvps.org/access/api/api0001.htm to open the standard Windows File/Open dialog so the user can select a file. You can then use the CopyFile method (see VBA Help) to copy the selected file into your images folder....

Using MS Access: Uploading an image from MS Access, access api, website documents
access api, website documents, heschel: Hi, Many thanks for the question. The first 3 are all possible, the last - uploading to a website I am not sure if you could do this automatically, it will depend of the website - i.e. if you have access to the drive where the website documents are kept...

Using MS Access: Upsize, ms access 2000, time formats
ms access 2000, time formats, asp files: Hello, I have always found MS Access uses the current system time and format by default, so if you are using Windows, go to your control panel and selected regional and language settings. This is where the time / date formats are stored for windows and...

Using MS Access: URGENT: Autopopulate and change, field boxes, unbound controls
field boxes, unbound controls, followup questions: The CustomerID control is bound to the CustomerID field in the Orders table. Forms don t have ControlSources, they have RecordSources. If the RecordSource for a form is a multi-table query then the form may not be editable because you have an uneditable query....

Using MS Access: URGENT HELP!, entity relationship, pk
entity relationship, pk, book store: Hi Aqeel You need an Associative table between the Author and Publication tables to break the Many:Many relationship into two One:Many relationships. This table will normally comprise only two columns, the primary key (PK) from Author and Publication,...

Using MS Access: URGENT HELP - Query, staff id, staff table
staff id, staff table, code group: Right click on the Join line in Query Design mode and selkect Join Properties. There are three options. The first is an Inner join returning only matching records. The other two are Outer joins taking all records from one table and matching records from the...

Using MS Access: URGENT HELP!, bridge table, entity relationship
bridge table, entity relationship, book store: Who says it isn t appropriate? Since publications can have multiple authors, a many to may would be appropriate in this case. What you need is a bridge table. This table would have 2 fields; AuthorID and PublicationID. You would add a record to this table...

Using MS Access: Ur View in my DB + Updating info in DB, rented apartments, apartment description
rented apartments, apartment description, apartment building: 1. tlu is part of a naming convention that many developers use to differentiate the purpose of tables. It refers to lookup tables as opposed to regular data tables. 2. PaymentTypeID is a Primary Key in tluPaymentTypes. In the other tables its a foreign...

Using MS Access: Urgently, portrait orientation, aggregate function
portrait orientation, aggregate function, landscape design: Mohamed Hello, 1. I use Access 2000 on Win 2000 and Win XP and have also experienced this problem: I assume you have opened the report in design mode, set the page properties and saved the report. I don t have an answer for this unfortunately - this is...

Using MS Access: USING SUB-QUERIES IN ACCESS 97, french transl, french interpreter
french transl, french interpreter, queries in access: If multiple interpreters can be assigned to ameeting, then you should have an assignments table with just MeetingID and Interpreter ID. You can setup a subform that will list the qualfied interpretors, then use the form double click event or a button that...

Using MS Access: Use an Excel form to enter data in MS Access, combo boxes, input data
combo boxes, input data, microsoft access: You could create an Excel form that would populate data into a table within the spreadsheet. You can then link to that table from Access. But I strongly disagree with the manager. Unless there is an issue with employee having access to your Access database,...

Using MS Access: Use of On LostFocus, improper entry, msgbox
improper entry, msgbox, microsoft access: Try: If IsNull(Me.txtDate) Then MsgBox( Date Required , vbOKOnly) Me.txtDate.SetFocus Else Me.AcctPrd.SetFocus End If Date is a reserved word in Access so shouldn t be used as an object name. Also its not a good practice to name your controls...

Using MS Access: User-friendly search box, search boxes, table database
search boxes, table database, form tool: Almost ANYTHING is possible with Access. Personally, I have my users use Filter by Form. I created a little help form that shows exactly how to use it and then put a help button on the data form to open it as a popup. Here are some links to examples of...

Using MS Access: User friendly searching in Access, microsoft access, mvp
microsoft access, mvp, parameters: I would recommend showing your users how to use the Filter By Form feature. This allows them ot enter criteria in any one or more controls to filter the resulting record set. You can also read up on Filters. Another possibility is useing a query and passing...

Using MS Access: User Id Stamp, security wizard, microsoft access
security wizard, microsoft access, data tables: There are several ways. The way I prefer is to pick up their network login ID which can be done with an API call. You can also use your own login form and hide the form instead of closing it after login, so you can pick up the login ID that way. Another alternative...

Using MS Access: User interface, red delicious, microsoft access
red delicious, microsoft access, support microsoft: What are talking about is cascading or synchronized comboboxes. Its also referred to as drilling down for data. You can find specific instructions at support.microsoft.com by searching for synchronized comboboxes. Generally, you have to construct your...

Using MS Access: User log on, network workstations, vb coding
network workstations, vb coding, access api: I handle this using the custom function found here: http://www.mvps.org/access/api/api0008.htm This function returns the user s network login ID. So there is no need for the user to log into the application, just onto their workstation. I would create...

Using MS Access: User login and password, dim rs, text boxes
dim rs, text boxes, command button: Dear Haneesh, You created a table bound to the user/passw table, this will not work. Just create a form without reference to any table and add the objects as explain. Change the line Set Rs = CurrentDb.OpenRecordset( Users , dbopendyanset) Users to...

Using MS Access: User login and password, password thanks, microsoft access
password thanks, microsoft access, help file: Haneesh, Many thanks for the question. The easiest way would be to use Microsoft Access s built in security. Just type security in the help file. If though you specifically wish to use code let me know and I will put something together for you....

Using MS Access: User Name, database menu, textbox control
database menu, textbox control, computer name: Paolo, hi, fGetUserName() returns your network login name: I am not sure what the PIN number is. Check syntax of ActualName / Actual Name and User ID. Check that you have the user network login in the UserID column. f in fGetUserName() is a naming...

Using MS Access: User Perms, backup version, command buttons
backup version, command buttons, switchboard: May, I am not sure why your switchboard stopped working. You could try to reset the permissions in the new (backed up version) just make another copy before you do so. That was a wise move. Do not add to many, just enough to check the switchboard is...

Using MS Access: Using Access 97 with XP OS, using windows 98, access 97
using windows 98, access 97, office 97: I ve been able to run A97 apps under XP without a problem, but I don t know if I have any that use an Inner Join. What I suggest you try, is making a copy of the database and recreating the query under XP. Then seeing if that works on the XP and 98 machines....

Using MS Access: Using Access to create MS Outlook 'Task', chris barrett, creating databases
chris barrett, creating databases, using exchange: Chris, There is definitely a way to do this and I would suggest doing it through VBA code (you may want to check around the web for some plugins if any exist (I don t know of any that do off the top of my head)). As for VBA code, you could get a book that...

Using MS Access: Using Access - getting data from 2 tables, salary history, employee details
salary history, employee details, group header: Create a query that joins the two tables on EmployeeID. Sort the query on EmployeeID. Then create a report that GROUPS on EmployeeID. Move the Employee Detail info into the group header and leave just the salary history in the detail band. HTH Scott ...

Using MS Access: Using Access form to append data to new table, archived records, correct names
archived records, correct names, parameter value: Why do you have two tables? Why not just add a field in the one table to flag a record as Archived? Then filter the form to only show non-Archived records. But the reason for your problem is that you aren t including the correct names to the control you...

Using MS Access: Using Access for registration, database queries, registration tool
database queries, registration tool, access pages: Its defintiely possible. One way to do this would be the use Of Data Access Pages which first came out in Access 2000. However, DAPs will be dropped from Access 2007 in favor of Sharepoint integration. So you might want to look into that. Sharepoint will make...

Using MS Access: Using Access, label wizard, comma separated values
label wizard, comma separated values, import wizard: If the text file is a CSV (comma separated values) then this should be very easy. The wizard will do just about all the work for you, if not all of it. You just need to tell it a few things that are clearly marked (like whether the first row is headings instead...

Using MS Access: Using access to send email, word attachments, church volunteers
word attachments, church volunteers, access queries: Ed, Yes it can be done. There are a number of ways and you may need to do a bit of VBA coding to achieve your outcome. The first is using VBA code to send the email via outlook. It is reasonably complicated, but if you wish to give it a go, just...

Using MS Access: Using Check Boxes in Access, check boxes, bound form
check boxes, bound form, vice pres: Dear Cory, Create a table called Persons with the following fields: PersonName - Text Attend - Yes/No The Yes/No field type is a checkbox already. Open the table and you can see the checkboxes, and thy already bound back to the table. Add these...

Using MS Access: Using Check Boxes in Access, database designers, check boxes
database designers, check boxes, normalization rules: You make a mistake common to new database designers. You are starting with the form design, not the table design. I would suggest that you read up on normalization (google that for references). When designing a database you first design the table structure,...

Using MS Access: Using Check Boxes in Access, check boxes, vice pres
check boxes, vice pres, president vice: If you set up your table with Yes/No field types for the fields you want, they will automatically be checkboxes when you build a form based on that table using Access s wizard. Change the Data Entry property of the form to Yes, and it will automatically open...

Using MS Access: Using Check boxes in Forms, visible control, check boxes
visible control, check boxes, option group: First, there is something wrong with the Followup feature here. Its not showing the whole thread so I can t see what I told you. In the future it might be better if you copied and pasted the previous correspondence into the followup. However, I vaugely recall...

Using MS Access: Using Checkboxes, conditional expressions, archive purposes
conditional expressions, archive purposes, checkbox: First, don t worry about result of calculated fields should not be placed in a table . You are right, historically the $ rates will change, so why not store the rate at the time. To set a forms control to a value do this: on the OnUpdate event of the 3/4...

Using MS Access: Using a Combo box w/ Imported Data, correct time zone, combo boxes
correct time zone, combo boxes, decent success: Time zone should only exist in your State table. Otherwise you have redundant data. if you want to display the timezone on your form for the state in a record, then use a Dlookup in an unbound control. Hope this helps, Scott Microsoft Access MVP 200...

Using MS Access: Using Currencies on forms, correct symbol, currencies
correct symbol, currencies, currency: if you are using mixed currencies, then I would not use the Currency datatype. Use the Single datatype and add a flag to your record to indicate the currency used. You can then use the Format function to display the amount with the correct symbol. HTH ...

Using MS Access: Using a "calculator" on an Access form, vb calculator, umich edu
vb calculator, umich edu, windows calculator: Hi Wont help, since this is a form related question, but will give a little advice :) You can just make a simple calculator on a form, and then let the users use this form, in that way you can ensure, that it will be saved in the right field. To make...

Using MS Access: Using checkbox to determine on what record to update comments, vba code, checkbox
vba code, checkbox, data structure: Mimi hello, In principal you can achieve your requirement by setting up a button that runs some VBA code that updates the underlying table with the comments entered. However: from your description, I am not sure of your data structure and how this relates...

Using MS Access: Using checkboxes with external SQL database, oracle table, boolean field
oracle table, boolean field, native access: I m not too familiar with Oracle, does it have a logical or boolean field (I d be surprised if it didn t)? If so I would add one to your table and populate based on the text values. Then link that field to your checkbox controls. Hope this helps, Scott...

Using MS Access: Using combined text values from multiple fields to create new dataset, baseball girls, activity participants
baseball girls, activity participants, girls softball: Mike, Many thanks for the question. I think the problem you maybe having, is that you have added the calculation to the text box, once you do this it becomes an unbound field and is no longer linked to the underlying field. To get around this you will...

Using MS Access: Using a combo box to select records, ms access database, initial job
ms access database, initial job, query design: Sure, A combobox is based on a query. From the Property dialog select the RowSource property and click the [...] next to it. This opens Query Design mode where you can set criteria to not show completed jobs. Hope this helps, Scott Microsoft Access...

Using MS Access: Using comboboxes, listboxes, relational databases
listboxes, relational databases, exact steps: the following article that I wrote should help with this situation. Displaying Data from related tables on a form This is a very frequently discussed issue. But before I get into the methods, you need to understand one of the principles of relational...

Using MS Access: Using a Date field in a form, lisa sims, dateadd function
lisa sims, dateadd function, current date: Hi Srivas, My suggestion would be to add a button beside the date field that users can click to overwrite the current date with the previous date when needed (by using the DateAdd function to subtract one day from the date). Thanks, Lisa Sims ...

Using MS Access: Using Datepart, birth month, hth
birth month, hth, btw: As a general rule we do NOT store calculated values. There is NO reason to store the Birth month since you can extract it from the birthdate whenever needed. If you want to display the full birthmonth on your form (or a report or query) add an UNBOUND...

Using MS Access: Using Dropdown boxes to find an individual value in a table, dropdown boxes, piping system
dropdown boxes, piping system, dlookup: First you should NOT be using separate tables. You should have one table with a field to indicate the bend or whatever the component is. In a fully normalized strucutre you would probably need 4 tables: tluComponents ComponentID (PK Autonumber) Component...

Using MS Access: Using DTPicker and trying to create a Scheduling form, drop down list, new date
drop down list, new date, increments: I suspect the problem is that your main form is unbound so the subform doesn t display the appts for the date chosen. What I suggest you do, is change the RecordSource for the subform to a query based on tblAppt. In the ApptDate column set the criteria to:...

Using MS Access: Using date parameters in macros, messagebox, source data
messagebox, source data, message box: Hi Donna, Normally we will attach some codes to the report directly than use a macro to feed the report. This is how you do it: Your source data for the report is the query - you already have this. Now open the report in design view. Click View Code...

Using MS Access: Using Excel as an Interface to Access, system tab, access database
system tab, access database, dialog: Michael, You need to set up an ODBC DNS for the Access database, however you can do this as part of the data link process below: From the menu in Excel, select Data, select New Query - the ODBC dialog opens - select a DNS - if none, select the System...

Using MS Access: Using an "excel" like grid..., client names, category column
client names, category column, grid control: I don t know of any grid control that does this. Frankly, I think it needs to be explained to them that data entry and data reporting sometimes conflict. In a relational database a properly normalized record would be structured like: TimeID (PK Autonumber)...

Using MS Access: Using expression in query to build another expression., explicit expression, crosstab query
explicit expression, crosstab query, northwind database: You can t do this in the same query. What is happening is that Access doesn t know what DiscAvg is yet. You can try one of two things. Use an Interim query as the basis of your query and do the calc in the second query or use an explicit expression: DiscPrice:...

Using MS Access: Using Formulas in Access, daniel gregory, costing model
daniel gregory, costing model, choice selection: This depends on how your database is setup and where you need to display the result. One option would be to use the SWITCH function which allows you to specify a value based on a value in another field. Another would be to create a custom function with a Select...

Using MS Access: Using a field name in a public function in a module, strsql, dim rs
strsql, dim rs, access help: This is a little out of my depth, but I think you need to Declare variables for the Form and Control (not field) differently. But I m not sure exactly how. You might try posting this in the Modules foum at utteraccess.com. That s the best site on the WEB for...

Using MS Access: Using a form to change the parameters of a query, condiments sauces, food drinks
condiments sauces, food drinks, keyword field: Hi Danielle, No, its a good question.. :-) Lets assume your form is called frmMain and the combo keyword selector - cboKeyWord You can reference this object in queries and code using the following syntax Forms!frmMain!cboKeyWord So you...

Using MS Access: Using a form field value for a report's parameter value, reading values, s parameter
reading values, s parameter, exact scenario: This is easy. You ll need a FORM with a field on it, a QUERY which uses a parameter that reads the form value, and your REPORT which is based on the query. Let s say you want to see the jobs for a specific customer. Make a query that shows the jobs for...

Using MS Access: Using a form to retrieve data from a table, access tutorials, access tutorial
access tutorials, access tutorial, blank fields: The easiest way to do this would be with a COMBO BOX. Just use the wizard and select find a record based on what I select in my combo box. Then, when the user selects a record from the combo box, Access will automatically find that one and display all the...

Using MS Access: Using forms to search different tables at the same time., lisa sims, sql statment
lisa sims, sql statment, text numbers: Depending on the number of fields you have in your tables, this could be difficult. You basically need to create a select query that uses the value that someone types in the search box as the criteria for your query. Here s an example of what should happen...

Using MS Access: Using formulas that are stored in a table field?, expression builder, field c
expression builder, field c, critera: Can you give me examples of some of these formulas. It might also help if you gave me an example of your table structure. I find it hard to imagine why you might need 250 different formulas. You can use a number of methods (SELECT CASE, IF...THEN...ELSE,...

Using MS Access: Using the "Iff" statement, dsum, dollar amounts
dsum, dollar amounts, microsoft access: I m not sure I follow. First, though, some terminology. You don t have FIELDS on a form, you have controls that may or may not be bound to fields in a table. Now, if you are trying to show the total cost for a quarter, I think using a DSUM is what you need....

Using MS Access: Using the IIF function, input option, object names
input option, object names, spijker: First, I suspect you have a problem with your structure. Its not recommended to use spaces in object names. And it looks like you using the questions as the field name. A questionnaire has a specific structure something like this: tblQuestions QuestionID...

Using MS Access: Using IIF statement in query, microsoft access, mvp
microsoft access, mvp, expression: You can t use an IIF in a WHERE expression like that. You will need to use 2 queries. One where you filter for a Date value the other for N/A. Then set the criteria accordingly. You can then use a Union query to brint them together. Hope this helps, Scott...

Using MS Access: Using linked tables in queries, tables access, primary key
tables access, primary key, two tables: Dear Hayden, Access don t have any problem with editing link tables, if one of the joined fields is a primary key and the other table is not a grouped query, the query are always editable. Normally the problem lie that the join fields of both tables are...

Using MS Access: Using a list to drive query parameters, query vb, query parameters
query vb, query parameters, list of departments: hello Simon - I need some more Info. Tell me if I am correct in this assumption; You have a list of departments and you want access to do something like this; if the first department meets some criteria then do some task Go to the next department if...

Using MS Access: Using Macro to run query, docmd, command buttons
docmd, command buttons, vba code: You have to use the OpenQuery function. Be sure to use the SetWarnings function first. Set it to False (or off) then all of your queries, then SetWarnings again to True (or on). Otherwise you ll be clicking OK 6-12 times. You could also just put it...

Using MS Access: Using MakeTable query for export ?, mdb file, sql code
mdb file, sql code, query in access: Hi Coja You will need to firstly link the table, then copy the table, then populate it with the new data. This is the key code for linking the table: Dim DB As DAO.Database Dim tdf As DAO.TableDef Dim strConnect As String Dim fRetval...

Using MS Access: Using Message Box, global variables, msgbox
global variables, msgbox, hth: There are two ways to do this. One is to store the date in a global variable. Lookup Global variables in Access help to see how you create and use them. The other is to populate a hidden control on a form that remains open (yet minimzed). You can then refer...

Using MS Access: Using Microsoft Access 2003, Counting and minusing from a field based on previous Occurrence date, discipline action, microsoft access 2003
discipline action, microsoft access 2003, occurrences: Dear Delores, Can you please give me more information, this sounds very involved and i really need to understand the whole thing before i can suggest any method. My first idea is, if we can put the steps you mentioned into a second table with a expiry...

Using MS Access: Using Microsoft Access 2003, Counting and minusing from a field based on previous Occurrence date, discipline action, microsoft access 2003
discipline action, microsoft access 2003, occurrences: I think I can, but a couple of points first. Is the 90 day period static or rolling? In other words, today is May 23, 90 days previous was 2/23. Starting tomorrow does the 90 days go back to 2/24 or are these Steps recalculated every quarter or similar static...

Using MS Access: Using MS ACCESS to create an online shop, credit card processing, jeff cooper
credit card processing, jeff cooper, online storefront: It is possible, but I wouldn t go with that platform. First, while an Access front end might work, as a back end to an online store, Access is too underpowered. Second and more importantly, why reinvent the wheel? There are several online storefront apps out...

Using MS Access: Using MS Access 2000 to create a test tutorial, ms access 2000, survey questionnaire
ms access 2000, survey questionnaire, answer database: Sure, I ve seeing many survey/questionnaire databases. Generally the structure is like this: tblQuestions QuestionID (PK Autonumber) QuestionText tblRespodent RespondentID (PK Autonumber) Firstname Lastname other respodent info tblAnswers AnswerID...

Using MS Access: Using MS Access, ms access database, ms access application
ms access database, ms access application, access interface: 1) Create a form using the query as your Recordsource. 2) Yes, you need the Developers Extensions for your version of Access. These are available free for Access 2007, but are pretty expensive for previous versions. Hope this helps, Scott Microsoft...

Using MS Access: Using MS Access Data Access Page, data access page, access pages
data access page, access pages, access data: This is not an area where I am knowledgeable, but I do know that the purpose of Data Access Pages is to allow an Access database to be accessed thru a WEB browser. Therefore having Access would not be needed. But the app and the pages would have be on a WEB...

Using MS Access: Using MS Access to send emails, question deals, sending emails
question deals, sending emails, multiple users: Hi There Check out the following webpage with regard to sending emails from access, or check out the Microsoft pages http://www.dimastr.com/redemption/ With regard to your second question, you can turn record locking off. But what you are asking...

Using MS Access: Using MS Access, source table, ms access
source table, ms access, copy and paste: Add a field to the source table. set its type as OLE object open the table and copy and paste the picture you want to show in your form/report in that field. Open the form or report and add a Bounded Object Frame Setits source vaue to the OLE field of...

Using MS Access: Using MSAccess 2000, user authority question, msaccess 2000, switchboard
msaccess 2000, switchboard, information ie: You don t really need to set any security levels. Frankly I don t use Access security as I find it to cumbersome. There are a few things you need to consider. First ANY multi-user database needs to be split between back end (BE)(tables) and front end (FE)...

Using MS Access: Using the min function across 2 fields in a record, normalization rules, structur
normalization rules, structur, foreign key: Well first, your database is not properly normalized. having fields like Price1, price2, etc constitutes a repeating group which violates normalization rules. What you should have is a structure like: PriceID (Primary key autonumber) ItemID (foreign...

Using MS Access: Using multi-select listbox for query criteria, query criteria, access databases
query criteria, access databases: Hi Laurie You don t filter the records as such in SQL - you are applying a conditional argument that restricts the number of reports being brought back. Normally you would do this sort of thing in a form and filtering results using the Docmd.ApplyFilter...

Using MS Access: Using ODBC with an Access Database, odbc connection, connection strings
odbc connection, connection strings, connection string: This is a bit out of my area, but I may be able to shed some light. I don t know what Timberline is, so how you connect to it may depend on its structure. Generally, when you ODBC to a database, there may be multiple table within the databases that you...

Using MS Access: Using Option Group values to create calculations in reports, option group, report query
option group, report query, billing rate: A rate table with hourly rate as one field and Nurse_Type (1,2,3) fields would help you create the report query you need, however if rates ever change, you might want to have that calculation hours*rate stored in the billable hours table. You may send the...

Using MS Access: Using option box to add dates, dateadd function, numeric value
dateadd function, numeric value, microsoft access: An Option box returns a numeric value. You can control what value is used for each option. So set the option for 3 months to 3, 6 months to 6 etc. Then use the DateAdd function to calculate the date: DateAdd( m ,optPeriod,Date()) This assumes the...

Using MS Access: Using an option group to select data from another table, option group, query criteria
option group, query criteria, billing rate: First, I think you need to restructure your database. You should have a structure like this: tblClients ClientID (PK) other client info (name, address phone, etc.) tblClientRates ClientRateID (PK Autonumber) RateTypeID (FK) Rate tluRateTypes...

Using MS Access: Using Picture, candace tripp, bmp file
candace tripp, bmp file, control source: The recommendation is NOT to use OLE fields or embed the pics in your database. This just causes the databases to become bloated. What you do is stored the path to the image and thenb use that to populate the Picture property of an unbound Image control...

Using MS Access: Using a query to print in a Form, correct criteria, current control
correct criteria, current control, three steps: The easiest way to go about this is to use the builder in the criteria section when you have the query open in design view. Make sure the form you are using is open while you have the query open in design view. Go to the criteria section, right-click it...

Using MS Access: Using RunSQL never turns up results, sxl, isnull
sxl, isnull, datatypes: First, check the number of commas in the Openform method, I think you have too many. Second, make sure the datatypes match. Finally use: Dim sxl As String sxl = SELECT [tblpphase].[pphaseid], [tblpphase].[pid] FROM [tblpphase] If IsNull(Me.OpenArgs...

Using MS Access: Using recordsets, outer loop, separate pages
outer loop, separate pages, inner loop: Yes you are. Though there might be an easier way. Why not print all the students in one report and set up grouping and page breaks so each student has separate pages. But if you want to cycle through each student use a Do While loop thru a recordset. ...

Using MS Access: Using an SQL query for a Report, combo boxes, strsql
combo boxes, strsql, qdf: Dear Travis, Normally it is only necessary to pass the where portion of the sql query on to the report. DoCmd.OpenReport MyRep , , , [WhereCondition] One other way is to create a query with a spesific name like : Dim qdf As QueryDef, strSql...

Using MS Access: Using SQL with Reports, square brackets, access 2002
square brackets, access 2002, queries: You wouldn t need to use VB at all. Is your report based directly off a table, or off a query? Make sure you re running it off a query, then put some criteria in the query that requires the user to enter a range. For example, if you put Between [Enter...

Using MS Access: Using SQL statements with Access, sql statements, access database
sql statements, access database, arrow: Queries -- create query in design view -- escape the show table window, press the icon for sql, in the upper left corner , if it does not show SQL, there is a little arrow by the side of the first icon, and by pressing this you should be able to see sql....

Using MS Access: Using Subforms inside a report, run time error, sql server stored procedure
run time error, sql server stored procedure, recordsource: One thing you have to learn about reports: Once they are opened, thats it. You can t change their values, properties, etc..., nor can you read values from them. You can do that in a form, but not in a report... so that means you can t do that in a form...

Using MS Access: Using Switch function in a query, expression builder, microsoft access
expression builder, microsoft access, hth: Something like this: Public Function CalcFee(strCode As String, dblPct As Double) If strCode = 00060 Then If dblPct 0 AND dblPct .59) Then CalcFee = .05 ElseIf dblPct .594 AND dblPct .694 Then CalcFee = .06 End If Else End If HTH...

Using MS Access: Using a search function in a query, literary description, hotmail
literary description, hotmail, soruce: dear gerardo what you need is to build a form with a subform. the record soruce of the form must be a mother table with a field such as category, or something like that; you must relate that primary table with another that has an IdField, (autonumber) as...

Using MS Access: Using sql syntax in vba code, column widths, microsoft access
column widths, microsoft access, sql syntax: You pretty much have it, but I don t think that s what you really want to do. I suspect what you want is to provide a letter on your form and then filter the combo for names that begin with that letter. In that case your VBA would look like this: MyListBox.RowSource...

Using MS Access: Using Trim() function, control source, mrs mr
control source, mrs mr, unpredictable results: Name is a reserved word in Access. it should not be used for field names. Doing so can cause unpredictable results. So the first thing I would recommend is changing the field name. I tried this on one of my databases and I couldn t duplicate the error,...

Using MS Access: Using text of one combo to create row source for another combo, combo boxes, combo box
combo boxes, combo box, xyz: This is called cascading comboboxes. In the query behind the second combo use criteria like: =Forms![Formname]![combo1name] You will need to add the column to set the criteria for but you can uncheck Show so it doesn t appear. Hope this helps, Scott...

Using MS Access: Using user input to modify SQL queries, database in access, query1
database in access, query1, sql queries: First, let me suggest you take a look at the Filter by Form feature of Access. This feature (which is explained in detail in Access Help) can be used to turn any form into a query. Just enter criteria in any one or more controls and apply the filter. For example:...

Using MS Access: Using VBA to modfiy query data, openrecordset method, adopendynamic
openrecordset method, adopendynamic, query data: I m not the best person for ADO questions, but it looks like you are setting rst to be a NEW recordset. Nowhere do you define what tables are assigned to the recordset. I think you need to use the OpenRecordset method to assign the results of a SQL query to...

Using MS Access: Using VBA modules in another access database, access programmers, google search
access programmers, google search, vba modules: Hi, I must admit I have never tried to do this, I just copy the code. I did a bit of a google search for you and came up with the following site: http://www.access-programmers.co.uk/forums/showthread.php?t=27139 Hopefully this will help you. ...

Using MS Access: Using VBA to pass listbox selection as query parameter, querydef, query sql
querydef, query sql, query in access: Hi David, The solution I would adopt, is to generate a string of values to be selected from, based on the listbox, then use the SELECT... WHERE...IN construct: So if no values were selected, the SQL is a plain; SELECT xxx, .. FROM ..... and if values...

Using MS Access: Using values from form controls in queries, criterium, ppl
criterium, ppl, queries: I m not sure about what your Macro is doing, but the format should be somthing like this: Forms![FindInfo]![Combo0] When building the query, you can also right click in the criteria area for the field you want to set the criteria for and click on Build...

Using MS Access: Using visual basic to move to a subform, combo box, wac
combo box, wac: Ray Hi, Use the combo Change event to trigger the action, then use the .SetFocus action to effect the move Public Sub combo12_Change() If me.Combo12 = PERMISSION Then Forms![WAC INPUT FORM].SubformObjectNameOnMainForm.Form.blackdefects.SetFocus...

Using MS Access: Using webcam in MS ACESS, ms acess, webcam logitech
ms acess, webcam logitech, thelink: Hi There, Storing the photo will be easy, using a OLE object in the table along with the profile. As i dont have access to a webcam to test any system i build i would suggest looking at ActiveX Controls available on your system and possibly looking for...

Using MS Access: Using wildcard queries via a form, wildcard queries, word australia
wildcard queries, word australia, correct syntax: The only thing I can think of is if you have a SQL backend for the data, in that case you use % instead of *. Otherwise, a memo field should act just like a text field when doing a search or query (except for grouping, you can t group a memo field). More...

Using MS Access: Usung OLE to insert a picture to a bound frame, photo editing software, recommended method
photo editing software, recommended method, mdb file: First, its NOT recommended that you use OLE fields to store images. This causes your database to bloat. The recommended method is to store the path to the image file in a field of the table. Then use an UNBOUND Image control and set the Picture property of...

Using MS Access: unable to open database, microsoft jet database, microsoft jet database engine
microsoft jet database, microsoft jet database engine, jet database engine: I m not sure of the specific reason for that error, but this article: http://support.microsoft.com/kb/283849/en-us has a list of things you can do to recover a corrupted file and prevent future corruption. Also, what about restoring the backup? Hope...

Using MS Access: I am unable to open a .ldb..., ldb file
ldb file: Why would you want to open it? The LDB file is used by Access to keep track of who is using an Access app and what records or objects they have open. Your Access MDB will read it. Its created when someone opens the MDB and gets closed when everyone gets out...

Using MS Access: unable to view, swtichboard, hidden modes
swtichboard, hidden modes, switchboard: Bertina, First of all, sorry for the delay. The solution is easy. Hold down the SHIFT key when you open access, this will unlock hidden modes and allow you to see everything. GO to the options and make sure the settings aren t hiding tables or defaulting...

Using MS Access: unbound combo box for view and report records only, button wizard, combo boxes
button wizard, combo boxes, parameter query: On reflection, I suppose you don t actually have to create a command button (and therefore use VBA code). You can select from the combobox and then switch to the Database window and run the report from there. I suppose, if you are developing this database...

Using MS Access: unbound combo box, strsql, column count
strsql, column count, deferent: Rady, Many thanks for the question. Try the following: ***** Dim strSQL As String strSQL = Select [Field1],[Field2] FROM [tblYourTable] Me!Combo15.RowSource = strSQL ***** You should be able to access the back end tables using the...

Using MS Access: unbound field
No its not possible and its not recommended. As a general rule we do NOT store calculated values. The value can be calculated for display at any time. You have the formula, you can use that as the source for a control on a form or report or in a query at any...

Using MS Access: How to undo some, not all bound controls on a form, lisa sims, unbound controls
lisa sims, unbound controls, bound controls: Hi Sharon, Unfortunately, I do not know of a way to clear bound controls due to the fact that they are bound to database fields in the tables. One suggestion is to create an unbound form for the order form instead of a bound form that references the customer...

Using MS Access: undo startup, empty database, shift key
empty database, shift key, access database: Have had this question a couple of times now, and the only way some have solved it, were to create a new empty database and the copy the tables.... from the old database to the new one. But there have to be an easier way, but I have not stumpled upon it....

Using MS Access: undo startup, startup options, shift key
startup options, shift key, access database: Well first of all, I never disable startup options unless I have a backup of the database. So you might consider that for future reference. Are you sure you are holding down the Shift key until the app loads? If you let go before the app is finished loading...

Using MS Access: unexpected error, access file, vergin
access file, vergin, unexpected error: Not from the Access file , no. The setting is on the individuals installation of Access 2003. There is nothing you can do to the database to stop that message from coming up. It all has to do with the settings of the Access installation at the users computer....

Using MS Access: union/crosstab/parameter query, crosstab parameter query, crosstab query
crosstab parameter query, crosstab query, crosstab queries: Patrick, hi, Could you try entering the dates in US format, MM/DD/YY As a point of interest, SQL requires dates to be entered in US format, and whilst the Access front end, does localise for you, it is less than perfectly consistent I look forward...

Using MS Access: union query in Access97 misses data, memory limit, access 97
memory limit, access 97, two tables: Hi Patricia, MS Access does have an overall memory limit - Access 97 is 1GB (Access 2K - 2GB), so it may well be this, that is causing the problem. There are also other limits, like the number of columns etc etc - best check all the specification limits...

Using MS Access: unique equations to generate values in a report, last period, final answer
last period, final answer, data tables: First, if you have a totally new question, please start a new note. I m not sure what you are asking. If you want to select a value from a listbox and use that as the criteria in a query, you can do it by referencing the control using the syntax: =Forms!formname!controlname...

Using MS Access: unique user logins to autopop data, station maps, customer interactions
station maps, customer interactions, users work: Hello Licoln - The first thing I would need to know is if your workstations are networked and do your users each have a distinctive username and password to gain access to your domain. Secondly, is the database on a network share that each users work station...

Using MS Access: unrecognized database format, unrecognized database format, access97 database
unrecognized database format, access97 database, ms access97: You need to recompile your VB6 front end after you relink your data using the new database format. Just remove and recreate the access database links inside your VB6 application then recompile the application. If you don t know how to recompile and modify...

Using MS Access: unsafe expressions, security levels, access security
security levels, access security, file server: Hello Jasmine - MS ACCESS security levels are set per computer - You should go to each machine that will use your database front end and on the top bar go to tools - macros -security and set the level to the lowest allowing the exucution of all macros. This...

Using MS Access: update data, normalization rules, pk
normalization rules, pk: I don t quite understand what you are trying to do, but it appears your table isn t structured properly. If you have fields named like var1, var2, etc. That is a repeating group violates normalization rules. Your table should look like: RecordID (PK Autonumber)...

Using MS Access: update a date field from a concactenation expr., invalid syntax, number rows
invalid syntax, number rows, type conversion: Yeah, I was afraid of some of the fields not updating. Since they are text fields, they might not neccessarily have information that is viable for a month or a day. Nothing you can really do about that one except run the query, and hand/eye correct the rest....

Using MS Access: update date field, network login name, property dialog
network login name, property dialog, previous question: That s a common mistake newbies make. When given lines of VBA code, you need to enter them using Code Builder (the VBE). If you just type the line of code in the Event box on the Property dialog, it thinks its a macro, not VBA code. To enter the Code Builder...

Using MS Access: how to update exchange/outlook 2000 using access 2000 mdb, microsoft exchange server, autoexec macro
microsoft exchange server, autoexec macro, exchange outlook: Jeff, I am not positive I can help, but I am happy to have a look to see if I can detect the problem. Can you send the database to julie@simply-access.com The other option is www.utteraccess.com forum I have found them to be a great help for more...

Using MS Access: update a field in table via command button, customer loans, rental id
customer loans, rental id, supplier id: A comboBox is generally beased on a query. You can click the [...] next to the RowSource property of a combo and go into Query Design mode and build your query with criteria. For example, you could set the criteria for the MediaID to: NOT IN(SELECT MediaID...

Using MS Access: update a form into table in Access2000, normalization rules, access2000
normalization rules, access2000, inaccuracies: As a general rule you do NOT store calculations. Calcuilations can be made on forms, in rpeorts or queries as needed. Storing calculated data violates normalization rules and can cause inaccuracies. Forms can be bound to a table with controls on those...

Using MS Access: How to update quantity in stock control system, stock control system, lisa sims
stock control system, lisa sims, product inventory: Sameer, My suggestion would be to create an update query that executes at the time of a purchase and a sale that updates the product inventory. You will need a way to distinguish between a purchase and a sale. If your transaction table does not have a field...

Using MS Access: update query, structure table, separate tables
structure table, separate tables, school database: I m still not clear on this. I think you should have at least 3 tables here. A Students table with info about each student. A class table that shows info about what classes the student has attended, and your fee schedule. When a record is added in the class...

Using MS Access: update query, open tables, query builder
open tables, query builder, query type: make an auxiliary table with one field, no key, the same data type as the main table s phone # write 9995551212 in the one field table open the query builder and choose append-query as query type open tables an add the one fiel table open the append to...

Using MS Access: update query, canned query, quick reply
canned query, quick reply, strsql: Access is an event driven tool. That means that most code is run as the result of an event. Samples of events are the updating of a value in a cotrol. the pressing of a command button, the opening of a form, etc. You need to determine based on your work flow...

Using MS Access: update query, button onclick, string text
button onclick, string text, primary key: Hello Maryam, You can use either a Save button - OnClick event or a textbox OnExit event as you say. the code will need to buld up an SQL expression thta you can then execute. So lets assume you have two textboxes txtAA and txtBB and you want to save...

Using MS Access: How to update records automatically, book numbers, transaction entry
book numbers, transaction entry, 123456: Hello Usman - I have created a small example demonstrating how to do this- To get the sample database, follow these steps; 1)Go to WWW.Dropboks.com 2)The sign in should automatically appear, if not go to menu - sign in. 3)Use the following credentials;...

Using MS Access: updateqry, memo field, banging my head
memo field, banging my head, qry: if newtable is an existing table and expr1000 is an existing memo field within that table, then this should work. Except for the fact that you are missing some quotes on the last line, and it should be CurrentDB.Execute... CurrentDB.Execute update newtable...

Using MS Access: updateqry for switchboard name, switchboard name, syntax problems
switchboard name, syntax problems, vbyesno: I m not sure what you mean by switchboard name. On the form generated by the switchboard manager are two labels that create a shadowed title for the switchboard. Is that what you mean? If you want to allow the user to customize that, you can. I would do...

Using MS Access: updating a combobox list, notinlist event, db design
notinlist event, db design, combo box: Ahh, but that code does not involve SQL. That s used when you have a Value List, not a query. Hence the message, you ARE adding something after the semi colon. Add Dim strSQL As String To your DIMs then replaces this line: ctl.RowSource = ctl.RowSource...

Using MS Access: updating dates whilst using checkboxes, dateadd function, last test
dateadd function, last test, routine basis: I m a little foggy on this. Why would a date be placed in Date of last test if the test wasn t performed? In other words, that field on the form should only be updated when a new test is performed. So there would be no need of a checkbox. The date of next...

Using MS Access: updating field in form, foreign key, combobox
foreign key, combobox, pallet: First, you don t have fields on a form, you have controls that may or may not be bound to a field in a table. This is a subtle but important distinction. If the pallet is dependent on the width, then you should have a lookup table: tluWidths WidthID...

Using MS Access: updating fields in table via command button, customer loans, rental id
customer loans, rental id, supplier id: adeel, Many thanks for the question. My apologies for the delay in getting back to you. You can add data from a form to another table, but it is not as straight forward as it looks. I need a bit more information. Do you have the forms already set...

Using MS Access: updating a record in a table, vba code, access books
vba code, access books, combobox: Neither. Build a form, using the wizard that is bound to your items table. Add a combobox to the header of the form using the Combobox wizard. Choose the third option to search for a record. Now, you can use that combo to select an item and bring up the record...

Using MS Access: updating a table record in a form, strsql, several records
strsql, several records, command button: You need to use a query to do this: UPDATE contacts SET Activity = Mailer sent WHERE Activity = send mailer ; You can save that as a named query and run it behind your button or use this code: Dim strSQL As String strSQL = UPDATE contacts...

Using MS Access: updating table using visual basic, mail, several ways
mail, several ways, doe: Robert, I can t figure what your problem can be wihtout see the entire application. You know that there are several ways to achieve the same task in programming. If you want to contact me to my mail it is hersain@cesea.org perhasps you can send me a sample...

Using MS Access: updating table via command button, stock table, epos system
stock table, epos system, checkout button: Umm no. See the problem here is there is a flaw in your design. An app that does inventory based on transactions should have a specific structure. As indicated in this article: http://allenbrowne.com/AppInventory.html The structure centers around a transactions...

Using MS Access: updating value based on form update, application centers, inventory application
application centers, inventory application, recevied: It could either be a stand alone form and/or a subform on the Products form. I would tend to use both or at least the subform. This is one place where I make an exception to recommending using an autonumber as a primary key. You need to decide on how many...

Using MS Access: uppercase, input mask, key press
input mask, key press, select properties: Hi, Many thanks for the question. To achieve this, use the Key Press event for each field and add the following code, between the two lines of code created. KeyAscii = Asc(UCase(Chr(KeyAscii))) To create the Key Press event, right click on...

Using MS Access: urgently, portrait orientation, margin changes
portrait orientation, margin changes, portrait view: 1. It sounds like the changes you re making to the report are forcing the report back into the portrait view. Do this still happen if you don t make margin changes? 2. I don t understand what you mean when you say you made a union relationship. What is...

Using MS Access: Should I use Access?, lab test results, yyou
lab test results, yyou, database program: Dear Sy Huynh YYou can use MS Access to build any kind of thatabase. You can get the same levels of functionality, security and presission than any other server based database. The limit of MS Access is that Acces makes one file database which still is using...

Using MS Access: Will I use Access for this project?, church offerings, church departments
church offerings, church departments, intermediate ms: Erick, Good Morning, Access should be fine for everything you have stated, within the following provisos: 1) If Access 97 - Anticipated total size of the database 1GB, or if Access 2000+ 2GB 2) The number of concurrent networked users 10 ...

Using MS Access: How to use Access, microsoft access training, free microsoft access
microsoft access training, free microsoft access, indiana business college: Hi, Many thanks for the question. Try the following site: http://www.educationonlineforcomputers.com/links/Free-Microsoft-Access-Training/Beginner-Tutorials/ You will need to scroll down past the adds to see the tutorials. Hope this is of some...

Using MS Access: how do i use a checkbox to effect criteria, commandbutton, command button
commandbutton, command button, checkbox: There are a few ways to do this. The way I would do this is with two queries. One that utilizes the date criteria and one that doesn t. In the On Load event of your report use code like: If Forms!formname!controlname Then Me.Recordsource = qryWithdate Else...

Using MS Access: How to use Expression Builder to make a fied be displayed, expression builder, calendar idea
expression builder, calendar idea, master source: Access has a pretty good Help facility. If you lookup DLookup there it will explain that it is used to do what you want. The Help shows how to use it and gives examples. After about 10 years of Access development I still use the Help to check the syntax of...

Using MS Access: use of functions in qbe, option boxes, query field
option boxes, query field, val function: Thank you for the detailed explanation. The best way to go about this is to replace the whole query each time you change the criteria. You can do this through code. This code uses the DAO reference, so make sure you have that selected in your Tools, References....

Using MS Access: Can we use IIF with a Count function in MS- Access, accounting 2, becarefull
accounting 2, becarefull, accounting 1: Dear Saritha, Ok the easiest way is to use this query inside a report, in the report footer use tree textboxes with the control source s as follow: =Sum([Mumbai]) =Sum([Pune]) =Sum([Delhi]) Format and move as needed. The other way is to use...

Using MS Access: how do i use ms access,what are advantages?, group applications, development platform
group applications, development platform, ms access: This question is really too broad for this medium. I can tell you that Access is a database development platform designed for end user and small work group applications. Its uses are any place where you want to record and retrieve data. I would suggesy...

Using MS Access: How to use Search option in Form, magic stick, access help
magic stick, access help, search option: You can build a form with the table(s) you want to search data in as form data source go to design view and open th Toolbox. Check the wizzard button (that with the magic stick) and choos the Button object. Follow the wizard and choose Find Records when you...

Using MS Access: why some user can't see log in window?, network folder, access security
network folder, access security, security settings: Hi Nancy, Access security is a wonderful thing. Do you open your database from your computer with a shortcut? If you do, the short cut opens the database along with the security file that was created when you set up the security settings. You will need...

Using MS Access: user tracking, inventory database, access security
inventory database, access security, time and date: Hi, Many thanks for the question. Is there Access security on the database, or is the logon a general logon for your network? If so what Network base do you use i.e. Windows, Novell etc. This will impact on the best way to set up the tracking system....

Using MS Access: using Access databases though the internet, sql server database, access databases
sql server database, access databases, db connection: Hi Yuvel Koren Yes you can connect to databases over IP (internet) If you have an IP address of the web server / database, then you can set up a connect string for use in ADODB with OLE DB. See the URL www.drdev.net - check out Setting up an OLE...