About Experts Sitemap - Group 54 - Page 16 2015-04-21

Using MS Access: Calculating Dates, recordsource, note case
recordsource, note case, days thanks: It depends on what you use as the Interval. If you lookup DateAdd in Access Help it will show you all the interval codes. I may have goofed and ww is for weeks, not a single w. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access...

Using MS Access: Calculating difference between dates in same field, query fields, tips access
query fields, tips access, query field: This is going to get a little complex, but it s certainly doable. You re going to need to make a query. Bring in your ClientID and the Date. Since Date is not a good field name (it s a reserved word in Access) let s call it ApptDate. Now, next you...

Using MS Access: Calculating sales tax, manipulation techniques, david answer
manipulation techniques, david answer, string manipulation: I handled that with string manipulation techniques using the following functions. cstr() Instr([field], . ) Mid() Right() If x 5 then ... y = Y + 1 ... end if I do not have easy access to that code but these functions will get you there. Convert...

Using MS Access: Calculating a single column in Access 2003, access tutorial, tips access
access tutorial, tips access, multiple records: By COLUMN, I assume you mean FIELD. I m also assuming you have a CONTINUOUS FORM showing multiple records. See this tutorial: http://599cd.com/tips/access/form-footer-total-sum/ Cordially, Richard Rost President/CEO 599CD Computer Training...

Using MS Access: Calculating time between delivery stops, vba procedure, editor option
vba procedure, editor option, detail line: If you create a new report, and goto the On_Open event and create a VBA procedure/module, you should get the following in your VBA editor Option Compare Database Private Sub Report_Open(Cancel As Integer) End Sub You will want to place a public...

Using MS Access: Calculating time intervals, time interval, time intervals
time interval, time intervals, integer portion: There are several reasons why we shouldn t store calculated values. One is that if the components of the calc change, then the stored value is no inaccurate. Its also a waste of storage and violates normalization rules. Hope this helps, Scott Microsoft...

Using MS Access: Calculations in report., netprice, access tutorial
netprice, access tutorial, 599cd: You can use an IIF function to determine if the NET price is positive or negative... HandlingCharge = IIF(NetPrice 0, NetPrice*0.15, 0) For more help, see: http://www.599cd.com/tips/access/iif-function/ I hope this helps you. Let me know if...

Using MS Access: Career in Database Creation/Admin, oracle sql server, having a great time
oracle sql server, having a great time, database creation: First, while Access is a great tool, its designed more for small workgroups. Its not something a career in databases would be based on. You would need to learn Oracle, SQL Server from both the programming and administrative sides. There are places that offer...

Using MS Access: Carrying data over from one form to another with a continuous form, command button, command buttons
command button, command buttons, existing records: Okay, take whatever your OpenForm line of code says, and simply add the OpenArgs parameter. Your existing one should look something like DoCmd.OpenForm frm_Name Myself, I always use named parameters, because it is supposed to run faster when you do,...

Using MS Access: Cascading combobox in formSubform, combo boxes, access tutorial
combo boxes, access tutorial, combobox: I get asked this one SO MUCH that I put together a free tutorial on it: http://www.599cd.com/tips/access/two-combo-boxes/ Enjoy! I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO...

Using MS Access: Checking names in tables, actuals, loop through
actuals, loop through, list of names: Yu, Create a select query and on the Column 1 field set the criteria to: (NOT budget ) AND (NOT actuals ) You could also open a recordset and loop through the records to check each record to see if it met your criteria, then if that record met the...

Using MS Access: Combining 2 tables, software dbf, accounting software
software dbf, accounting software, field names: Yves, Many thanks for the question. No it does not matter that the invoice no has a different name for the field name in each of the different tables. Just join the two together. I am presuming you are talking about the field names and not the value...

Using MS Access: Combo box, combo boxes, financial source
combo boxes, financial source, tips access: The BEST way to do this would be to have a hidden text box on your form and in that text box dynamically create your SQL statement. You would need to update it when each combo box is change (in the AfterUpdate event)... so in the case above your SQL statement...

Using MS Access: Combo list immediate update, vba code, button press
vba code, button press, microsoft access: By same field lookups do you mean that you lookup previous entries in that field to select a new one? If so, that would indicate your database is not normalized properly. I would also assume that you have the LimitToList property set to No. If so, why...

Using MS Access: Compare two tables, key field, two tables
key field, two tables, matching records: First, is it necessary that you record if the record was updated? Unless that s the case I would do this with an update then append. Link the two tables on the key field (I assume part) and just update the data table with the new data. Then run an append query....

Using MS Access: Complex query??, key field, design mode
key field, design mode, matching records: You need an Outer join. If I understand your structure you have one record in each table for an hour of the day. But the alarms table only has records for when an alarm occured. So your key field is the hour field. So what you would do is open a query in Design...

Using MS Access: Computation question, currency fields, currency field
currency fields, currency field, decimal field: Ken, Many thanks for the question. You would be on the right track using the IIF statement. Are you able to send me your IIF statement that you created and I will see where we might be able to tweak it. Regards Julie www.simply-access.com ...

Using MS Access: Conditional hide of detail section in report, report in ms access, detail section
report in ms access, detail section, record numbers: Hi, Many thanks for the question. One option is to use conditional formatting: Right click on the Record number field, choose conditional formatting from the drop down list. I am presuming the value is in another field other than the record number....

Using MS Access: Continue of Previous/Dates in Query, dateserial, 4 digits
dateserial, 4 digits, nada: Ok, If its prompting for dateTemp1 and datetemp2, then you aren t using the correct Control names. If Access can t find the referenced control it will assume you want to prompt for the value. So, like I said earlier, you need to check the control names. ...

Using MS Access: Convert mdb to exe, positive reply, reply thanks
positive reply, reply thanks, stripped down version: You can not directly convert an MDB file to EXE. You would have to re-write the application in Visual Basic, C/+/# or some other compileable language. There is however another option. Depending on the version of Access you are running, there is an option...

Using MS Access: Copy multiple records with vba/sql, runsql, sql string
runsql, sql string, source table: Hi Jan, As long as you can define which records you want to SELECT , and your tables contain data in a Normalised format, then SQL should be able to handle the records. In SQL, to move records from one table to another, there are two actions - a)...

Using MS Access: Copying a textbox value, adding it to a table, query builder, hi thanks
query builder, hi thanks, table structure: Hi Chris, Open the Query Builder in Access, in a new query. Right mouse button, select SQL view - a text area appears. Now paste the code into the text box UPDATE university SET [university code] = 0 & Left([university],10) WHERE [university code]...

Using MS Access: Cost Reporting with Related Tables, cross tab, detail line
cross tab, detail line, header row: I think what you re wanting to do is done via a cross-tab query. I m just guessing, because I may be misunderstanding what you need - also you may possibly be needing something beyond the scope of what Access is capable of doing. Basically a cross-tab...

Using MS Access: Count in combo box(display results in text box), mr mrs miss, multiple records
mr mrs miss, multiple records, president ceo: Oh... well if you want to just count the records in a table, you don t need to make that a bound field. Just use the DCOUNT function and an unbound field in your footer, like this: =DCOUNT( * , TableName , Type= Prof ) DCOUNT is very similar to DLOOKUP....

Using MS Access: How to Count Y and N, bond monies, bond project
bond monies, bond project, bond projects: Try this instead: =DCount( * , Project_query , Bond= Y ) You re really counting ALL the records returned, not just that field. Aside from that, your function looks good. I hope this helps you. Let me know if you have any more questions or comments....

Using MS Access: How to Count Y and N values in an Access Report, bond monies, bond project
bond monies, bond project, bond projects: Are your Y/N values actually storing the text y and n , or are they Yes/No fields in your underlying table? If the 2nd option, then you have to switch to using 1 for Yes and 0 for No, like so; =DCount( [Bond] , Project_query , Bond=1 ) (you ll also...

Using MS Access: Countiing expressions that are null in a query, final expression, access tutorial
final expression, access tutorial, president ceo: You could simply use the IsNull() function to determine which cells are null and then adjust your calculation accordingly. You might want to consider using the NZ() function to provide a ZERO value instead of a null for your cells. Then when you average...

Using MS Access: Counting Y and N totals in a report, bond monies, expression builder
bond monies, expression builder, bond project: Did you copy paste the argument here? If so, it s your use of Quotes that is messing it up: =dcount([Bond], Project_query ,Bond= y ) The above may be it. Try using the expression builder for Control Source for that Text box. There are other guaranteed...

Using MS Access: Creating and creating a Sub Form, recordsource, office folder
recordsource, office folder, child relationship: I think your question is clear enough, but I am not sure if your main form and sub-form are bound to each other with a relationship? did you have to pick a master/child relationship when you created the sub-form? I am not sure if knowing this is necessary,...

Using MS Access: Creating Mulitple Files from One Table, parameter query, tips access
parameter query, tips access, access tutorial: All you have to do is use some simple criteria in your query to limit the results to one company. You could do this with a parameter query or a form field. See these tutorials for help: http://www.599cd.com/tips/access/parameter-queries/ http://www.599cd.com/tips/access/forms-formname-field-notation/default.asp...

Using MS Access: Creating a report showing items that need renewing, software assets, dynamic value
software assets, dynamic value, parameter query: I usually don t use a parameter for queries - in fact, don t think I ever have. You can place any number of objects in the query s criteria, such as a reference to a control, or use a dynamic value, such as Date()-30 (anything from a month ago), or a more...

Using MS Access: Creating a schedule, appointment calendar, time slots
appointment calendar, time slots, access tutorial: What you re asking me would literally take me an hour to answer. I do have a tutorial on my web site that I build an appointment calendar / task manager in Access. Here s the link: http://www.599cd.com?GOAC206 Richard Rost President/CEO 599CD Computer...

Using MS Access: Creating a survey in Access 2000, creating a survey, combo box
creating a survey, combo box, select properties: Shirley, Many thanks for the question. What you need to use (most likely) is a list box, then change the multi select property to simple. (right click on the list box, select properties Other tab Multi Select = simple) The only problem with a...

Using MS Access: Creating a survey in Access 2000, creating a survey, access tutorial
creating a survey, access tutorial, 599cd: You ll need to add some additional fields to your tables and forms to hold these answers... or make a related second table that can accept free-form text and store the results there. I hope this helps you. Let me know if you have any more questions or...

Using MS Access: Customer tracking for various purposes, repair ticket, customer table
repair ticket, customer table, information block: First, if you don t understand some term or procedure I refer to. it usually helps to look it up in Access Help. Looking up the Column property would have explained how it worked. If you can t find the term or still don t understand what the Help says, then...

Using MS Access: calculating the sum of several fields, query expression, benevolence
query expression, benevolence, access 2000: Hi Dennis, Many thanks for your question. I am not sure I fully understand what you are asking. But I will have a go at an answer. Try using the NZ function for each of your Sum(s) i.e. Nz(Sum([Building Fund]),0) This will include a zero if...

Using MS Access: change forecolor if statement, sharing your expertise, colors
sharing your expertise, colors: Nope, you re not doing anything wrong. This behaivoir is normal for continuous forms. There is however, an option for you IF you have Access 2007. I believe both Access and Excel 2007 have a Conditional Formatting option where you can make a change in your...

Using MS Access: closing report error, frms, rsts
frms, rsts, rpts: I d have to see the code to see if the DoCmd.Close is misplaced or if there is a better command. You masy want to use the OnNoDate event instead. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access 2007 VBA P.S. If I felt people...

Using MS Access: comparing records between to tables, query wizard, access tutorial
query wizard, access tutorial, president ceo: Use the FIND DUPLICATES QUERY wizard. It s very straight-forward. I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and watch...

Using MS Access: computer asset database, hp 1320n, monitor hp
hp 1320n, monitor hp, hp hp: Well, you d need the table you have, plus one other table to make a list of locations (I d call it LocationT). In that table, you ll need a LocationID and LocationName. You then store the LocationID in the Item table. You could use a ComboBox on your Item...

Using MS Access: concatanate fields, legacy fund, vba code
legacy fund, vba code, lookup tables: INNER JOIN and RIGHT JOIN are not VBA Code, they are how relationships between tables are defined in a SQL query. What you are trying to do creates a conflict with how the tables are joined together. Since I can t see your table structure, I can t really...

Using MS Access: converting unnormalized to 1NF, drid, key field
drid, key field, three tables: You need three tables; Clients, Doctors and Patients. To relate one to another, you include the key field from another. So to relate the patient to a Dr you would include just the DrID in the patient table. Hope this helps, Scott Microsoft Access MVP...

Using MS Access: copy textbox value to 2 fields in table on being primary key, hi thanks, infront
hi thanks, infront, video tutorials: Chris, Here is the concantenation string: [PrimaryKeyName] = 0 & Left([FieldName],10) Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html Find video tutorials at: http://www.myaccessprogram.com/tutorial.html...

Using MS Access: copy textbox value to table of 2 fields one being primary key, hi thanks, thanks in advance
hi thanks, thanks in advance, infront: I can show you how to make the new field equal the old plus whatever string you want, I m not sure - depending on how you are doing your form, but you might also have to write a script to store the new field in your other table, as opposed to data-binding....

Using MS Access: count in a report, access dlookup, activity id
access dlookup, activity id, 599cd: If you re getting a parameter popping up, that s Access way of saying it doesn t recognize one of your fields. Has the field name changed? Sometimes Access changes field names on you - like in an aggregate query, something might become SumOfTotals instead...

Using MS Access: How to count Y and N in a report, bond monies, bond project
bond monies, bond project, bond projects: Beverly, Make a derived column is a query, just insert a new column. Place this type of code at the top: Bond_Money:iif(Bond= Y ,1,0) Now make the report a qroup by report and sum this column to get how many are coverd with bond monies. Bob Heifler...

Using MS Access: countdown day in ms access, tips access, president ceo
tips access, president ceo, 599cd: Yes, it is EXACTLY that... calculating the difference between two days. I m assuming you want to run a query or open a form and see all of your jobs, and how many days left you have for each... well, that s exactly why you would want to calculate the difference...

Using MS Access: create fonction to calculate age years month days, access tutorial, tips access
access tutorial, tips access, president ceo: See: http://www.599cd.com/tips/access/difference-in-dates-times/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and...

Using MS Access: How to create Grade from Score, access tutorial, tips access
access tutorial, tips access, exam scores: Well, if you re only dealing with A,B,C,D,F for your letters, you could just use an IIF function: LetterGrade: IIF(Grade =90, A ,IIF(Grade =80, B ,IIF(Grade =70, C ,IIF(Grade =65, D , F )))) See this tutorial for more help: http://599cd.com/tips/access/iif-function/...

Using MS Access: criteria from form to query, fool proof method, query output
fool proof method, query output, exit function: Chris, Use this in the criteria of the query: Like Pick List]![Combo17] & * I suggest a better fool proof method using a functionto retrieve a public variable that is set on the AfterUpdate event of Pick List]![Combo17] This approach would require...

Using MS Access: criteria in reports, square brackets, report criteria
square brackets, report criteria, vba: You would have to design a form that does this. There are some reporting modules out on the web - I just recently saw one designed specfically for Access, but they cost. I ve designed a User Interface that allows the end-user to design their own report criteria...

Using MS Access: Data check, actuals, list of names
actuals, list of names, microsoft access: You wouldn t. You would do it in a query SELECT * FROM table WHERE Column1= budget OR Column1= actuals That WHERE clause will filter out anything that has anything else in column1. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS...

Using MS Access: Database Access Problem, allexperts, transaction number
allexperts, transaction number, database access: Jamie, obviously it would be IMPOSSIBLE for me to help you with this problem without looking at the database. Yes, it s probably a problem with a query, but again without seeing it, I really couldn t tell you anything. It sounds like it might be a problem...

Using MS Access: Date problem in Microsoft Access, quotation marks, access tutorial
quotation marks, access tutorial, president ceo: How about: CDate( 1/1/ & Year(Date)) I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and watch my 90-minute ACCESS 101...

Using MS Access: Date Stamp on a form, access tutorial, president ceo
access tutorial, president ceo, 599cd: I m assuming your FORM is some kind of an unbound (no table under it) form with a bunch of buttons on it? If that s the case, just BIND it to a small table with a field in it to track your date. When your macro or VBA code runs (to launch your queries) just...

Using MS Access: #Dates#, case delivery, thanks for your patience
case delivery, thanks for your patience, microsoft access: Try If IsNull(Me.DelDate) Then msgbox You have not entered any data to match the roof to. Me.DelDate.SetFocus Else stLinkCriteria = [DeliveryDate]= # & Me![DelDate] & # DoCmd.OpenForm stdocName, , , stLinkCriteria...

Using MS Access: Dates, input masks, decimal portion
input masks, decimal portion, integer portion: Hmm that is odd. I haven t encountered that before. I m going to suggest posting this at utteraccess.com. There are some members of the Access team that monitor that site and may be able to provide more insight. Hope this helps, Scott Microsoft Access...

Using MS Access: Dates in Access, mask type, input mask
mask type, input mask, integer number: Access uses it s calcs by an integer number, counting from 1 = 1/1/1900, not any specified format. What that article is mentioning is how the user interface to Access is translating a format. Go to the table design, under format, type this: dd/mm/yyyy...

Using MS Access: Disable Form, access tutorial, president ceo
access tutorial, president ceo, 599cd: Albert, change your form s properties so that it s both POPUP (stays on top of other windows) and MODAL (users can t leave the window until they close it). I hope this helps you. Let me know if you have any more questions or comments. Cordially, ...

Using MS Access: Displaying all records in a subform, microsoft access, mvp
microsoft access, mvp, microsoft: Let me see if I understand this. You want to use a subform to select a record in the main form, that will then be used to filter other subforms? Why not just use a Listbox (on the main form) to select the record instead of a subform? Hope this helps,...

Using MS Access: DLookup on Combo box, dlookup, control names
dlookup, control names, correct syntax: First, the default value only comes into play when you add a record. If you are adding a record, then comp_id will be blank. I also do not recommend using the field name for a control name. You should be using a naming convention and adding a prefix to your...

Using MS Access: DLookup on Combo box, dlookup, combo box
dlookup, combo box, dbo: Hi Anita, Many thanks for the question Try the following: =DLookUp( reportname , dbo_comp , comp_id = & [comp_id]) As you have not any spaces in the names of your fields, you can choose to have or not have the [ ]. Hope this is of some help....

Using MS Access: Dlookup, access tutorial, tips access
access tutorial, tips access, text boxes: Sounds like you just need an AfterUpdate event for each of your text boxes. See this tutorial: http://599cd.com/tips/access/afterupdate-event-vba Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and watch...

Using MS Access: Duplicate Entry, tab place, select properties
tab place, select properties, many thanks: Hi, Many thanks for the question. You are on the right track. The Micrsoft Site explains how to do this best, so I will not re-invent the wheel. http://support.microsoft.com/kb/209487 Explanatory points: The Condition should look like this...

Using MS Access: Duplicating information, example john doe, information question
example john doe, information question, separate tables: I understand what you are saying. For example, John Doe is renewing his ticket subscription for 2008. So rather than re-enter the subscription info, you just want to copy it. That s what my suggestion will do. Hope this helps, Scott Microsoft Access...

Using MS Access: Same data, multiple fields - how can I return all matching records?, asking the right question, running into trouble
asking the right question, running into trouble, table database: You re going to just need to set up your criteria such that you re using the OR rows correctly. Your query needs to return results where Field1= Joe OR Field2= Joe OR Field3= Joe etc. I cover this in my Access 102 tutorial which you can find on my...

Using MS Access: How data is saved, docmd, access tutorial
docmd, access tutorial, president ceo: Sounds like the data just isn t getting saved to the table before your report is opening. Data is saved to the table whenever you close a form or move to a different record. Just try throwing a Me.Refresh in the button that opens your report BEFORE the...

Using MS Access: dsum, tips access, access tutorial
tips access, access tutorial, sum function: The error is because you re not using DSUM right. The syntax is: DSUM(Field, Domain, Criteria) You can t put a calculation in the Field part of the function. Plus, if these fields are the fields from the report, you probably don t need to use DSUM......

Using MS Access: Editing a report is slow, president ceo, access training
president ceo, access training, field names: Without actually seeing the database, it s very difficult to diagnose this problem. Some ideas: Compact and repair the database? Is the database over a network drive? If so, does the same problem happen when you copy it locally? Can you simplify your...

Using MS Access: Enable/disable control depending on value of another control, code snippet, option group
code snippet, option group, group control: In the After Update event the previous group use the Code builder to enter code like: If Me.optgroup1 = 1 Then Me.optgroup2.Enable = False Else Me.optgroup2.Enable = True End If If you want this done when you view previously entered records, put...

Using MS Access: Enable/disable option group controls, option group, group control
option group, group control, group controls: Just set an AfterUpdate event for the first option group and say: If OptionGroup1 = TheValueIWant Then OptionGroup2.enabled = TRUE Else OptionGroup2.enabled = FALSE End If Here s a tutorial that will help you with the AfterUpdate event:...

Using MS Access: Encrypt database file together with Workgroup file, line c program, command line c
line c program, command line c, encrypted database: Heres a sample of the command line: C:Program FilesMicrosoft OfficeOfficeMSAccess.Exe C:MyAppFolderMyApp.MDB /wrkgrp C:MyAppFolderSystem.MDW You might want to read up on uses a wrokgroup file here: http://support.microsoft.com/kb/305542/en-us...

Using MS Access: Encrypting database with desired workgroup file, command line switch, encrypted database
command line switch, encrypted database, access tutorial: If you REMOVE all permissions from the ADMIN user and ADMINS group, then any time someone tried to access your database WITHOUT your special MDW file then they wouldn t be able to see anything. The first step to good security is to set yourself up with...

Using MS Access: Expressions, query fields, tips access
query fields, tips access, access tutorial: By default, NO values are 0 and YES values are -1, so you need a CALCULATED QUERY FIELD to multiply your responses by -1 to get positive values. See: http://599cd.com/tips/access/calculated-query-fields/ Then you can use an AGGREGATE QUERY to add them...

Using MS Access: Get External Data from Excel, microsoft access, vba
microsoft access, vba, ms office: Generally, Access will interpret the import based on the data in the first couple of rows. So one trick is to create a dummy record in the first row. Another method is to link to the Excel file, then use an Append query to add the data to an existing table,...

Using MS Access: exporting report to clipboard, clipboard object, docmd
clipboard object, docmd, 599cd: Very interesting question. I ve never tried to do this with Access, so I did a little research. First, I figured I could just use the Clipboard object that comes with Visual Basic 6.0. Nope. It doesn t exist in Access. In VB, it s as easy as this: Clipboard.SetText...

Using MS Access: FILENAME, tips access, docmd
tips access, docmd, access tutorial: Of course it s going to give you an error. Access has no idea what Table1.Field1 means. You could have both of these fields open in a FORM and refer to them as Forms!Form1!Field1 and Forms!Form2!Field2 for example. See: http://599cd.com/tips/access/forms-formname-field-notation/default.asp...

Using MS Access: Field default value, Null or "", report query, empty strings
report query, empty strings, access tutorial: You could manipulate the field properties ALLOW ZERO LENGTH and REQUIRED as needed. The former prevents empty strings. The latter will prevent both empty strings and null values. Or you could program your way out of it. :) I hope this helps you. Let...

Using MS Access: Filtering Subforms, stock control system, stock query
stock control system, stock query, option group: Create three queries to show the records for each of the three conditions. Then, i would use an Option Group on the main form with four choices; All, Out of Stock, Near Reorder, Below Reorder. In the After Update event of the option group use the colde...

Using MS Access: Filters, combo box, customer data
combo box, customer data, data entry: Peter Hi, You need a look-up to define the relationship between ProductID and ClientID - this data may reside in your product table, or as a separate table. Next you need to build this data into the query that supports the product subform, and ensure...

Using MS Access: Filters, control names, combo box
control names, combo box, peter answer: I m assuming the CustomerID is on the main form, so you would use the main form name. Make sure there is a match between what s stored inthe CustomerID and the customerID field in the Product table. Hope this helps, Scott Microsoft Access MVP 2007 ...

Using MS Access: FORM STRUCTURE, many thanks, wizard
many thanks, wizard: Khalil, Many thanks for the question. If you build a form by following the wizard, you will get to a section where you can choose continuous form. I think this is what you are looking for. Hope this is of some help. Regards Julie www.simply-access.co...

Using MS Access: Form View, access tutorial, president ceo
access tutorial, president ceo, 599cd: It sounds to me like you just need to resize your form. Go into DESIGN VIEW and then drag the edges of the form to the size you want. Then move your fields around so you can see them where you want them. See my free Access 101 tutorial (below) for more...

Using MS Access: Forms, freight account, proper accounts
freight account, proper accounts, allexperts: Hi Brian, I assume you have two tables anyway (one for the order header and one for the Order Line) Build your sub-form on the Order Line table, and then on the column to store the posting code, set up a combo box. The sub form then sits nested inside...

Using MS Access: Formula for Inventory, question thanks, inventory report
question thanks, inventory report, microsoft access: You d probably need to do use multiple queries then combine them for your report. To obtain the beginning number you sum the transactions prior to the beginning of the month. Another to sum the transactions during the month and the third to show the balance...

Using MS Access: Formula for Inventory, types of queries, query designer
types of queries, query designer, default option: The correct way to do this depends on many different factors, but I can share with you *a* way to do it. In designing queries, there are several types of queries you can create, the default option is Select, probably the next most common one is append, where...

Using MS Access: Formula for Inventory, access tutorial, access update
access tutorial, access update, tips access: That all depends on how the data is stored in your table. Without seeing your database it s impossible for me to tell, but if you have two fields called EndingInventory and BeginningInventory for each part, you could just use an UPDATE QUERY to change them...

Using MS Access: Formula in Table, query fields, control source
query fields, control source, source option: You can t put calculations in a TABLE. You have to do it in a QUERY, REPORT, or FORM. See this tutorial: http://599cd.com/tips/access/calculated-query-fields/ It s for Access XP/2003, but the concept is the same for 2007. I hope this helps you....

Using MS Access: Access 2007 - Comma delimited field, user1, delimited data
Using MS Access: Access 2007 - Comma delimited field, user1, delimited data, address data

Using MS Access: Access 2007.How to eliminate Parameter Value Dialog box prompting at each report running, group header, structure change
Using MS Access: Access 2007.How to eliminate Parameter Value Dialog box prompting at each report running, group header, structure change, database structure

Using MS Access: Access Count duplicates help, multiple group
Using MS Access: Access Count duplicates help, multiple group

Using MS Access: Access Form Maximized in Design View
Using MS Access: Access Form Maximized in Design View

Using MS Access: Access VBA code, value null
Using MS Access: Access VBA code, value null

Using MS Access: Auto resize line in report, format menu, boxes
Using MS Access: Auto resize line in report, format menu, boxes

Using MS Access: Combo list immediate update, combo list
Using MS Access: Combo list immediate update, combo list

Using MS Access: Convert mdb to exe, microtools, access forms
Using MS Access: Convert mdb to exe, microtools, access forms, converters

Using MS Access: calculating the sum of several fields
Using MS Access: calculating the sum of several fields

Using MS Access: closing report error, exit function, rpt
Using MS Access: closing report error, exit function, rpt

Using MS Access: dsum, ms access help, square brackets
Using MS Access: dsum, ms access help, square brackets, help topic

Using MS Access: Editing a report is slow
Using MS Access: Editing a report is slow

Using MS Access: Expressions, expression
Using MS Access: Expressions, expression

Using MS Access: multi value field, multiple records, relational tables
Using MS Access: multi value field, multiple records, relational tables

Using MS Access: Query Criteria from code
Using MS Access: Query Criteria from code

Using MS Access: Select Last 6, access htm, golf leagues
Using MS Access: Select Last 6, access htm, golf leagues, ms access

Using MS Access: fConcatenateRecords, strsql, fcon
strsql, fcon, noise noise: I think you need to modify the function a bit. You need to modify the recordset so its filtered for the current case and pass a CaseID to it. something like this: Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator...

Using MS Access: GIS, access tutorial, odbc connection
access tutorial, odbc connection, president ceo: I ve never used ArcGIS, but if it has a standard ODBC connection, you should just be able to link to its tables. I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer...

Using MS Access: Goto last record, record navigation, existing records
record navigation, existing records, key field: Yes, these are subforms, but not in the most common usuage. In the most common usage, a subform is linked to the main form on a key field so it displays only records related to the record in the main form. What you are doing is just creating an interface using...

Using MS Access: Graph in Ms Access report, microsoft jet database, microsoft jet database engine
microsoft jet database, microsoft jet database engine, jet database engine: Don t use a parameter prompt query. Instead, create a form with text controls for start and end dates. Reference the values of the controls using the syntax: Forms!formname!controlname Hope this helps, Scott Microsoft Access MVP 2007 Author: MS...

Using MS Access: generating invoices in access, invoice detail, employee details
invoice detail, employee details, customer invoice: Link your customer, invoice header, and invoice detail tables in a query that feeds a report grouped on first invoice header data, then customer (or the reverse order, depending on the look you want, then leave invoice detail in the detail section of the report....

Using MS Access: On Hand to Beginning Inventory, dsum, inventory report
dsum, inventory report, thanks in advance: Hi Ahl, Many thanks for the question Try this, For your create a report with the fields you have I presume you have based the report on a totals query, you will have your normal fields grouped and summed, but you will need to add a Min for the Date...

Using MS Access: On Hand and Beginning Inventory Calculations, expert answer, proper solution
expert answer, proper solution, inventory report: I know it s been awhile since my last posting to your follow-up, but I ve been trying to think of a way to do what you re wanting, and without storing the values themselves in a table somewhere, I don t think it s possible to do. Sorry bout that - not...

Using MS Access: On Hand to Beginning Inventory, access update, tips access
access update, tips access, president ceo: You want to change all of the values? Sounds like the job of an UPDATE query: http://599cd.com/tips/access/update-query/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO...

Using MS Access: Help with Tables, query wizard, access tutorial
query wizard, access tutorial, president ceo: Use the FIND DUPLICATES QUERY WIZARD. This will show you which records are duplicates. The ones that are left are unique. I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD...

Using MS Access: hello, insert into table, microsoft access
insert into table, microsoft access, mvp: So if you type 11 and 20 you want to add 10 records with the numbers 11 through 20 in a specified field? Try this: FOR i = Me.txtLower To Me.txtHigher strSQL = INSERT INTO table (SeqNum) VALUES( & i & ); CurrentDB. Execute strSQL Next i Hope...

Using MS Access: hi, control source, access tutorial
control source, access tutorial, text boxes: Sounds like you have a PARAMETER somewhere in the REPORT that s prompting you for data. Remove it and move the parameters to a query under the report. A parameter could even be a mistyped / misspelled control source somewhere in your report text boxes....

Using MS Access: hyperlink to a form, access tutorial, syntax error
access tutorial, syntax error, president ceo: It doesn t look like you re using OpenForm correctly. First, there shouldn t be any parentheses. It s not a function. Here s what I do... make a command button using the wizard, and then copy the code it gives you. In it s simplest form, all you need is:...

Using MS Access: Identify blank text fields, blank entries, blank text
blank entries, blank text, text column: In a text column, I m assuming you mean in a QUERY. Try this for your criteria: Is Not Null I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training...

Using MS Access: IIf Statement in Access For a Query, expression builder, minutes format
expression builder, minutes format, access query: I know this isn t directly related to your question about over , but have you checked into the DateDiff function? I was looking over your posted code and was going to recommend going over that with a DateDiff, or even passing the results of DateDiff into...

Using MS Access: Importing Data, applicant details, database in access
applicant details, database in access, test database: That s why! Since there are no matching records in the children s table then they would not be in the query. As a general rule you shouldn t bind forms to multi-table queries. The mainform/subform setup manages that. The main form should be bound to the parents...

Using MS Access: Include todays date as part of file name, format date, filename format
format date, filename format, access tutorial: I haven t tried it, but this MIGHT not work as part of a macro. No problem though, just convert the macro to VBA code and it should work fine in there... MyFileName = C:FileName & Format(Date(), yymmdd ) & .XLS DoCmd.TransferSpreadsheet MyFilename...

Using MS Access: Incremental count, detail section, lucian
detail section, lucian, many thanks: Hi Lucian, Many thanks for the question. This can be done in Access - but it does take coding to achieve. If you wish to go down this route, then let me know. It is easier to achieve in a report. Create a report in design view and choose to group...

Using MS Access: Incremental count, select count, lucian
select count, lucian, dob: Hi Lucian, I built a table speced as follows tblNames Name - Text DoB - DateTime using your data, this query works fine SELECT A.Name, A.Dob, (SELECT Count(Name) As C1 FROM tblNames WHERE Name = A.Name) - (SELECT Count(Name) As C2 FROM tblNames...

Using MS Access: Incrementing Item Numbers on a Subform, access tutorial, tips access
access tutorial, tips access, president ceo: There s almost ALWAYS a way to do just about everything in Access. See: http://599cd.com/tips/access/incrementing-your-own-counter/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO...

Using MS Access: Inserting blank in number field in table, numeric data type, sql insert statement
numeric data type, sql insert statement, dick dick: Dick, The numeric data type of size integer or long integer will accept a null value via an append or update query in Access. Executing SQL will have the same effect. I see no problem in inserting nulls in numeric fields using Access version 2000 and up....

Using MS Access: Inserting Records, string query, syntax error
string query, syntax error, microsoft access: I have to ask this. If you ask someone a question and they give you an answer, why don t you use the answer they gave you? That is not what I told you to do. I haven t a clue where you got DateTime.Today from. All you need is: select * from info where...

Using MS Access: The Internet and Access, drive web, real security
drive web, real security, access pages: You can export reports to HTML format that can be added to a WEB site. But if you want a searchable database, you will need to use Data Access Pages, ASP or some other database drive WEB technology. You can still use the same Access database but it will need...

Using MS Access: Invalid Service Code, current bill, code question
current bill, code question, code column: Looking for a null value in the outer joined table, the table with the join arrow pointing to it, will always let you know which value in the main table are not found in the outer joined table. Please take the time to review the results when you make each...

Using MS Access: Inventory control using MS Access, almond company, 599cd
almond company, 599cd, allexperts: Yes, of course it will, but of course there is a significant learning curve and development time if you have never used Access before. Access is (in my opinion) one of the most POWERFUL software tools on the market... but if you don t know how to use it, you...

Using MS Access: Inventory db - keeping it normal, spreadsheet style, inventory history
spreadsheet style, inventory history, normalization rules: You re welcome. You don t really use an index directly, its used by Access whenever you use a table. The multi-field unique indexes just say you can t have a duplicate of that combination of fields. Hope this helps, Scott Microsoft Access MVP 2007...

Using MS Access: Invoice Number, dlookup, sequential number
dlookup, sequential number, dmax: There are a couple of ways you can do this. If you have a separate table that records info about the invoice you can generate a new sequential number using a DMax to get the highest value and increment that. Another method would be to store the last used number...

Using MS Access: iif function, access tutorial, tips access
access tutorial, tips access, president ceo: You ll just need to NEST multiple IIF statments... X=IIF(Condition1,TrueValue1,IIF(Condition2,TrueValue2,FalseValue)) This is the same as saying: IF Condition1 = TRUE then X=TrueValue1 ELSEIF Condition2 = TRUE then X=TrueValue2 ELSE...

Using MS Access: importing ascii fixed length files, action queries, detail records
action queries, detail records, pete jones: It is possible? Absolutely. Is it something I can go into detail on here, sorry, no... it would take me an hour to explain how to do this. Basically, you re going to need to read in each line using basic File I/O and look for anything that marks the start...

Using MS Access: incremental adding in Access, access tutorial, easy solutions
access tutorial, easy solutions, president ceo: You want this to happen as the data is entered, or just whenever you pull up a query you want it to calculate this number in general? You d probably have to do it with a DCOUNT function, and count the previous records where the name is the same, but the...

Using MS Access: inventory sales, stock count, trans type
stock count, trans type, fld: it s tricky and always discouraged from using calculated fields - I probably should have mentioned that before when I originally posted. But, fortunately, it s possible to do. You would have to have your current level of inventory stored in your parts...

Using MS Access: Limiting the scope of a Lookup, combo boxes, access tutorial
combo boxes, access tutorial, tips access: See this tutorial: http://599cd.com/tips/access/two-combo-boxes/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and...

Using MS Access: Linking 2 Access databases, access databases, access tutorial
access databases, access tutorial, president ceo: You need to link one of the database s tables to the other. Go to File Get External Data Link Tables. Select the tables you want Now those tables will show up in that database so you can work with both of them at the same time. They might be renamed...

Using MS Access: Linking nested levels of data, hardware table, subcomponent
hardware table, subcomponent, server rack: Donna, You need a , a join table which stores many to many relationships. This table stores the unique ids only between any two related components. It would contain just two values, the unique keys of both pieces of hard ware. So, 3 and 4 would have...

Using MS Access: Linking pictures to access 2007, move event, default property
move event, default property, mouse move: Peter hi, Try this: Private Sub Form_Current() If Len(Me![Photos]) 0 Then Me![Picture].Picture = c:pictures & Me![Photos] Else Me![Picture].Picture = End If End Sub Notes: 1. The object Me![Photos] can be specified as Me![Photos].Value,...

Using MS Access: Linking a Report to Specific Query Criteria, canned query, query criteria
canned query, query criteria, work flow: You can t have it both ways. Either you supply criteria to the query or a filter for the report or you use a canned query. Without knowing your work flow, or the criteria its hard to advise further. It might be possible to pull the criteria based on other...

Using MS Access: Linking a Textbox to a Label, visible property, access tutorial
visible property, access tutorial, tips access: This isn t that hard. Just create an AfterUpdate event to change the label s visible property based on the value. If MyField 100 then MyLabel.visible = FALSE else MyLabel.visible = TRUE end if See: http://599cd.com/tips/access/afterupdate-event-vba...

Using MS Access: Not in List, cancel button, visible property
cancel button, visible property, switchboard: You can use the OpenArgs parameter in the On_Open event of the form. You don t need to pass the form an OpenArg every time you open the Products form (like from the switchboard), but you do have to be consistant. For example, turn these Ok/Cancel buttons...

Using MS Access: List Box on existing Data Base-table, form, step manner, combo box
step manner, combo box, thanks in advance: I m confused. I know from your post that you are wanting a Combo-box, and you have a list-box. What I m not sure of is what you mean when you say when I added new field it works fine , and it still works the old way . I gather from the last part...

Using MS Access: Locking a TABLE in Access DB, knowledgeable user, database window
knowledgeable user, database window, microsoft access: Ok, the easiest thing is to just not display the Database Window (see Options Startup) and use a StartUp form. Of course a knowledgeable user can get around that pretty easily. Access is not a very secure platform, but users should know not to poke around....

Using MS Access: Long iif statement, type aa, nonstock
type aa, nonstock, type rm: If you copied and pasted directly from your VBA into AllExperts, then I can see that your problem is you need to change all of your linnes that say getComments = ... to getComment because that is how you declared it in your Function Public Function...

Using MS Access: Long iif statement, type aa, nonstock
type aa, nonstock, whse: You need to figure out the nesting. You have to approach this logically and figure out sequences. You start off with a determination if X is true, what do I want. If its false then what do I want. You have to do this with each set of conditions building the...

Using MS Access: Look Up doesn't display entries, microsoft knowledgebase, knowledgebase article
microsoft knowledgebase, knowledgebase article, access tutorial: This is a known bug. Someone sent me a link to the Microsoft Knowledgebase article that dealt with this problem - if you search thru my old questions you might find it - or look on Google. I don t remember the fix. I hope this helps you. Let me know if...

Using MS Access: Look up value in another table, option groups, column count
option groups, column count, relevant properties: First, its not recommended that you use lookup fields on the table level. Lookups should be done on forms as combos, lists and option groups. If you use the Combobox wizard to create the combo on the form, it will, by default, set the Primary key of your...

Using MS Access: Lookup and Importing, access tutorial, tips access
access tutorial, tips access, president ceo: The best way is to use the DLOOKUP function. See this tutorial for help: http://www.599cd.com/tips/access/dlookup-function/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO...

Using MS Access: link between barcod scanner and an access file, printing barcodes, barcode scanner
printing barcodes, barcode scanner, barcode scanners: This is a question I actually get asked pretty frequently. Using barcodes with Access is not hard. What you need to do is find yourself a barcode scanner that simply inputs the scanned data as if someone were typing the data in at the keyboard. I haven...

Using MS Access: linking a form to a Word document, relative beginner, query in access
relative beginner, query in access, application record: Jennifer, Word s mail merge has linking ability to a saved query in Access. Just set the criteria of the query to the current desired record then word will use only that database data in it s mail merge document. I would first try making an Access report...

Using MS Access: Macros and SQL, relational database solutions, simplistic terms
relational database solutions, simplistic terms, attendence: Andrew, Hi In relational database solutions, we design and build the tables first, then use forms and queries to enable the tables to be populated In simplistic terms you will need the following tables to cover the are you have described. tblMember...

Using MS Access: Matching records on forms, matching records, suggestion
matching records, suggestion: My first suggestion would be to move the search onto your main form, but if that s not an option, then you should be able to use the AbsolutePosition function to move the selected record of your main form, based on the search form. You would have to make...

Using MS Access: Message: This record has been changed by another user since you started editing it, ms access, clipboard
ms access, clipboard, ui: I don t know what your question is. This is an error that occurs when you try editing a record that someone else has changed. Sometimes when developing, that other person can be a lock that you previously opened and did not exit/close out of, so the...

Using MS Access: Microsoft Access, query condition, object names
query condition, object names, foreign key: What you need is to sort by Name and date (in Descending order). then use the TOP qualifier to get the TOP 1 for each Name. This article: http://support.microsoft.com/kb/210039/en-us explains how. Hope this helps, Scott Microsoft Access MVP 2007...

Using MS Access: Monthy usage from inventories, tips access, access tutorial
tips access, access tutorial, access update: Well, as far as subtracting units used from units on-hand, you could set up three fields: OldQuantity QuantityUsed NewQuantity And then use an UPDATE QUERY to assign the NewQuantity to the OldQuantity - QuantityUsed. Then zero out the QuantityUsed...

Using MS Access: MS ACCESS PROJECT, room booking system, user level security
room booking system, user level security, input mask: You re asking a lot of questions here, and I get a lot of them, so I try to limit each answer to a few minutes. Sorry... but I can point you in a couple of directions. 1. Sending the user to a specific form is just a matter of making another command button...

Using MS Access: MS Access 2000/ Images, ms access 2000, hyperlink type
ms access 2000, hyperlink type, gb storage: Bob, Yes there is. Allen Brownes Code is the best: http://www.mvps.org/access/api/api0001.htm Although if you are an Access Novice, this maybe all too overwhelming. The easiest option if to create a field that is of a hyperlink type. Add...

Using MS Access: MS Access 2003 - Combining Rows, error id, access tutorial
error id, access tutorial, president ceo: Sorry, Mack, but I can t think of a NON-PROGRAMMING way to handle this. You d have to loop through all of your records and build a new string with all of the days in it. Not an easy task. I hope this helps you. Let me know if you have any more questions...

Using MS Access: MS Access, ms access programming, query criteria
ms access programming, query criteria, database operator: Well, number 1 is easy... that s just a matter of setting up the right query criteria to say you only want to see records where RecordCount is 1. I cover basic query criteria in my Access 101 tutorial, that s free on my web site (see link below). 2 an 3...

Using MS Access: MS Access Automatic Fill-in Date on Form, ms access database, date control
ms access database, date control, object names: What was the error message? And that s not atypical for the parentheses to be removed. Is the name of your control Date? Date is a reserved word in access and should not be used for object names. Also, is the underlying field a date/time datatype? ...

Using MS Access: MS Access Clock Control, e mail address, using activex controls
e mail address, using activex controls, clock control: I m not a fan of using ActiveX controls for time or date pickers. You have to mke sure they are registered on each desktop. My colleagues at UtterAccess.com have put together a demo app of date and time controls, that are not ActiveX based. If you send me...

Using MS Access: MS Access - Combine records, google search, access tutorial
google search, access tutorial, tips access: You mean, take all of the countries in your table and make a single form field out of them that says: USA, Canada, Germany, UK Or something like that? This isn t going to be easy. First you d need an Aggregate Query to group all of the countries together...

Using MS Access: MS Access form problem, field sheets, combo boxes
field sheets, combo boxes, state question: Sounds like a variation of the update my list of cities when I pick a state question. See if this tutorial helps you: http://599cd.com/tips/access/two-combo-boxes Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit...

Using MS Access: MS Access Problem, ldb files, 599cd
ldb files, 599cd, john stein: It sounds like there s a problem with the file access. Some ideas: have you compacted and repaired your database lately? Try closing all copies of the database and deleting any LDB files that might be hanging around. Copy the MDB file locally and see if the...

Using MS Access: MS Access - Queries, ms access queries, 599cd
ms access queries, 599cd, query group: No, if you follow the tutorial and build a proper AGGREGATE QUERY, it will GROUP BY the one field (only show one item per type) and perform a calculation on the second field (sum, avg, min, max, etc. whatever you type). Try it again. I hope this helps...

Using MS Access: MS Access via the Web, access pages, access data
access pages, access data, microsoft access: Yes and no. An Access database can be used as the back end to a WEB page, though its not the best choice. You can use Data Access Pages to front end thru the WEB. Again, not the best choice, but workable. But it means designing DAPs from scratch. Another method...

Using MS Access: MS Access via the Web, web question, security integration
web question, security integration, access pages: It would depend on how complex the database is - how many tables, their interconnectivity (relationships) and how many forms/reports you need. The best way to get a good idea would be to see the database first hand. Let me address one thing up front,...

Using MS Access: MS Acess, ms acess, video tutorials
ms acess, video tutorials: Hiba, Place = #1/03/2008# in the criteria of the date field. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/Download.html Find video tutorials at: http://www.myaccessprogram.com/Tutorial.html Contact...

Using MS Access: Multiple Answers in one field?, access tutorial, president ceo
access tutorial, president ceo, 599cd: It s possible, yes. You could use a MULTI-SELECT LISTBOX where the user can select multiple items... but it s NOT easy to code. You could also have the user select items from a listbox and add them to a subform. You d need a second table to hold the answers....

Using MS Access: Multiple values Access 2007, global module, comma separated list
global module, comma separated list, openrecordset: That code goes into a Global Module. You then use the function fConcatenateRecords() where you want to display the list. The function takes as arguments, the field name, the name of a table, query or recordset that contains that field and the character you...

Using MS Access: ms access, acccess, office microsoft
acccess, office microsoft, video tutorials: Varma, For Access 2000-2003 here are the features: http://support.microsoft.com/kb/823089 For the 2007 features: http://office.microsoft.com/en-us/access/FX100487571033.aspx Best regards, Bob Heifler MyAccessProgram.com Find example files at:...

Using MS Access: ms access, employee payroll, payroll taxes
employee payroll, payroll taxes, s corp: Nash, They are limited only to non-internet use of less than 10-15 concurrent users. I personally ran an S Corp with full employee payroll, taxes, and accounting within MS Access. This saved a considerable amount of time and expense. Lately I am using it...

Using MS Access: ms access - Compare tables, unmatched records, query wizard
unmatched records, query wizard, destination table: What you need to do is build a query that joins the two tables on the common fields. Once you get a query that shows the correct matching records you can then turn it into an Update query to populate the other fields. For the unmatched records, use the...

Using MS Access: ms access - Compare tables, vba procedure, thanks in advance
vba procedure, thanks in advance, two tables: Your English is adequate enough for me to understand that you have a complicated situation going on here. Too much for me to really understand completely without going into the details further. But that may not be necessary. If I am understanding the situation...

Using MS Access: multi value field, relational tables, access tutorial
relational tables, access tutorial, tips access: It sounds like you just need a good basic tutorial on working with relational tables and forms. Start with this tutorial: http://www.599cd.com/tips/access/relational-tables/ Once you ve mastered that, then you just need to learn how to use Form/Subform...

Using MS Access: Non-Matching value in SAM Testing, unmatched query wizard, freebee
unmatched query wizard, freebee, video tutorials: Robert, You can exclude Smith by placing this in the criteria row of the field that contains Smith : Not Smith Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/Download.html Find video tutorials at: ...

Using MS Access: networkdays, holiday hope, microsoft access
holiday hope, microsoft access, vba: No problem since I already have a function I use for that: Public Function AddWorkdays(dteStart As Date, intNumDays As Integer) As Date Dim dteCurrDate As Date Dim i As Integer Dim intWeekday As Integer dteCurrDate = dteStart AddWorkdays = dteStart...

Using MS Access: new field, customer numbers, video tutorials
customer numbers, video tutorials, gaps: Margaret, If there is more than one user adding a new record within the same minute, it gets more complicated than what I suggest here. Use a DMax() function on the number field to retreive the largest number in the table, then add one to it. Bob Heifler...

Using MS Access: Outlook to Access, line input commands, e mail
line input commands, e mail, address example: You are going to have read them in as text files. Check out the Open File and Line Input commands in VBA help. You will find sample code that should help. Basically you need to loop thru line in the file. Identify the label for that line, then write the value...

Using MS Access: Outlook emails to a Access database, mail object, outlook mail
mail object, outlook mail, file eml: This can be done, but I m afraid it s going to involve a good amount of programming. You would basically have to save it as a text file (.EML file) or programmatically access the Outlook mail object to read it from your inbox. Then, you d have to parse each...

Using MS Access: Outook to Access, email inbox, address example
email inbox, address example, using exchange: It s not going to be easy. Access 2007 has a collect data via email option, but I m trying to remember if that requires you to have already setup the email template ahead of time and send that out to anyone trying to send you data. I d have to wait until...

Using MS Access: optionboxes problem, option group, access tutorial
option group, access tutorial, 599cd: The whole point of an option group is that your users are limited to selecting ONE item from the group. If you need the ability to select multiple checkboxes, then don t put them in a group. Put a box around them if you want for appearance, but leave them...

Using MS Access: other transaction aside from orders, northwind database, video tutorials
northwind database, video tutorials, ms access: Joan, Yes you will need a separate table for each area that requires many entities that will relate to one entity, unless you were to stack all 3 transactions in one table and also add a field for the type of transaction and of course the foreign keys to...

Using MS Access: other transaction aside from orders, northwind database, access tutorial
northwind database, access tutorial, president ceo: Well, ORDERS can be used for tracking units sold. RETURNS could just be orders with a negative quantity. I m not exactly sure what you mean by deliveries. How are they different from orders? Are you sure you just don t need a delivery METHOD? I hope this...

Using MS Access: Parameter Collector Forms, case management database, drop down menus
case management database, drop down menus, access help: You are almost there. The thing you are missing is that you CAN T get the query to open the form so you don t do it that way. You open the form first. Add a button to the form to run the query or a report based on it or open a form bound to the query. Hope...

Using MS Access: Parameter queries, vba module, access vba
vba module, access vba, parameter query: Sorry I didn t get back to you sooner, I kinda of tackle quick questions right away, and while the answer to your delimna is easy, it s not quick. How comfortable would you be with a VBA module? What you are wanting to do would be possible, but only if you...

Using MS Access: Parameters, parameter value, combo box
parameter value, combo box, microsoft access: I almost never use parameter prompt queries for that reason. Instead, I use an unbound form. This works because you can reference a value in a control on an open form using the syntax: Forms!formname!controlname So put your combo on the form and set...

Using MS Access: Parts Lookup using two criteria, combo boxes, recordsource
combo boxes, recordsource, combo box: Brian, Use the afterupdate event on the 1st combo box to reset the recordsource of the 2nd. My example files will show you a working program of this. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html...

Using MS Access: Passing Report Data, recordsource, text boxes
recordsource, text boxes, textboxes: Create a query that lists the data you want in the report. Make that query the Recordsource of your report. Next, create an unbound form with 2 unbound textboxes named txtStart and txtEnd. Set the criteria in the query for the date field to: BETWEEN Forms!formname!txtStart...

Using MS Access: PDFing from Access, left hand column, occasional report
left hand column, occasional report, 599cd: Sorry, but this does sound like an Acrobat issue. Perhaps check for an updated PDF driver? Unfortunately, I don t do a lot with Acrobat myself, so I m not really an expert with it. Sure, I manually print the occasional Report to a PDF file, but that s about...

Using MS Access: Perform Operation Like Excel VLookup, excel vlookup, vendor names
excel vlookup, vendor names, relational databases: You will find this article I wrote useful: 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 databases. That principle...

Using MS Access: Pivot charting Help, microsoft access, macros
microsoft access, macros, vba: Well first of all I wouldn t use macros. Macros are very limiting. Second, I don t know of any specific sites that deal directly with pivot charts. I might suggest posting this at utteraccess.com. There are a lot of very knowledgeable Access gurus there. Some...

Using MS Access: Primary keys in table, field index, primary keys
field index, primary keys, corrigan: Yes, it is recommended that you use autonumbers as your PKs. That s what they are meant for. To prevent duplications you can set a multi-field index on a combination of fields like names and DOB. You can also check for similar names when you enter a new...

Using MS Access: Printing reports problem, macro button, printing reports
macro button, printing reports, microsoft access: Try this: DoCmd.OpenReport noterpt , acViewPreview,, [billingid] = & frm![billingid] The WHERE caluse you were using won t work because its not going to find a value within the quotes. You want to concatenante in the value on the form. Hope this...

Using MS Access: Problem with defining foreign keys in hierarchy, relational database design, martha jones
relational database design, martha jones, example id: I m sorry, Dave, but I m confused. Why do you have three different tables when in reality all of your records are of the same type: people. First, take a look at this tutorial on proper relational database design: http://www.599cd.com/tips/access/relational-tables...

Using MS Access: passing query parameter, expression builder, query parameter
expression builder, query parameter, button code: This won t work. You can use a conditional criteria that way. I almost never use paramter prompt queries because they are so limiting and this is one example. Instead, create an unbound form. Add two controls, one hidden and one visible. Add a button to...

Using MS Access: pre-filled fields in form?, docmd, runsql
docmd, runsql, assessment components: Yes, there is more that goes in there... pseudocode is just semi-code so you get the point of what I m trying to do. You can look up the exact syntax for GoToRecord in the Access help or online. I m afraid off the top of my head, I *think* it s: Docmd.Gotorecord...

Using MS Access: preventing a ask field from repeating prompt for answer in a report, unbound controls, prompt box
unbound controls, prompt box, microsoft access: That s what I mean. Instead of using [startdate] and [enddate] you would have controls on a form (maybe named txtStart and txtEnd) and then your criteria would be: BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd This way the query looks to...

Using MS Access: preventing changes to a record once it has been saved, knowledgeable user, microsoft access
knowledgeable user, microsoft access, deletions: Access is not the most secure platform. A knowledgeable user can get around most security. One way to do this is to use 2 forms. One form with the data entry property set to yes so it only can be used to add new records. The other with the Allow Edits, Deletions...

Using MS Access: Query, microsoft access, occurance
microsoft access, occurance, support microsoft: I think what you need is contained inthis article: http://support.microsoft.com/kb/210039/en-us You want the TOP 1 per group where sorted by person, then date in descending order. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office...

Using MS Access: Query Criteria from code, option group, code question
option group, code question, query criteria: You are making this more complex than it needs to be. You also aren t following the process here. Step 1) user enters record, entering a month name and year for the date. Somewhere this is converted and stored as a a date/time datatype for the 1st of that...

Using MS Access: Query for creating advanced joins, account transactions, select department
account transactions, select department, select statement: The problem is stemming from the fields in your SELECT statement SELECT Department, Charge_Code You have to tell it which table you are wanting to pull Charge_Code from, change this from just plain Charge_Code to either Chargemaster.Charge_Code...

Using MS Access: Query Date, report grouping, report wizard
report grouping, report wizard, query group: Pride, Use the Month() function: Month[DateField] Use a report grouping based on this derived query field. The report grouping icon is on your report tool bar or ribbon. or use report wizard and have it group on date field and instruct it to group by...

Using MS Access: Query Date, report wizard, group options
report wizard, group options, access query: If you use the Report Wizard to create your report and you select a date value as a grouping field, you can set the grouping options to be by day. week, month etc. If you aren t using the Report wizard You can go into the Sort and Group options to set the...

Using MS Access: Query Date, access query, access tutorial
access query, access tutorial, tips access: Well, you could create a custom month field: MyMonth: Month(MyDate) And then perform an Aggregate Query to group those all together: http://www.599cd.com/tips/access/aggregate-query/ -OR- You could put the data in a Report and then just create...

Using MS Access: Query by Form in a unique database with lots of test, memo fields, form resources
memo fields, form resources, database stores: I would suggest just using the Filter by Form feature built into Access. Also you need to relook at your structure a table with 70 field and a lot of checkboxes is most likely not normalized properly. Normalization is a KEY design technique. Having a denormalized...

Using MS Access: Query to find Age at time of Death, calendar oddities, rough approximation
calendar oddities, rough approximation, 599cd: You re fine. The number is the number of DAYS they were alive. Just divide it by 365 for a rough approximation of their age. See: http://599cd.com/tips/access/difference-in-dates-times/ If you need a more exact figure, however, which takes calendar...

Using MS Access: Query Help, separate tables, simple matter
separate tables, simple matter, microsoft access: First you have a problem in structure. You should never have separate tables for a year. Instead you should have a single table with a field to indicate the year. With that structure it would be a simple matter to join the Aftap table to the Grid table on...

Using MS Access: Query Help, access tutorial, president ceo
access tutorial, president ceo, 599cd: So, your AFTAP table has only ONE record and that s either 2007 or 2008.... or is AFTAP a related table and we re looking at some record that has related fields in both your Grid table and AFTAP table? I need more information on how your tables and relationships...

Using MS Access: Query Help, vba procedure, best bet
vba procedure, best bet, source table: sorry it s been a few days - seems like my allexperts inbox is filled up when I first log-in at work in the morning, and I m swamped at work, so I get to the quick ones first, then move along as I have time. really your best bet wouuld be to merge your...

Using MS Access: Query Help, video tutorial, two tables
video tutorial, two tables, video tutorials: Suzanne, You would use append queries with the criteria you specified. I have free video tutorial on my site. Some deal with queries. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/Download.html Find video...

Using MS Access: Query with several date parameters, report in access, report header
report in access, report header, record source: The reason why I gave you the second link is because you should put the two date fields on a FORM and then make a button to open your query. You won t be prompted FOUR times for your dates... the query will just read them off your form. See the tutorial...

Using MS Access: Query using Expression Builder Access 2007, expression builder, query wizard
expression builder, query wizard, query design: This medium is really not meant for that type of tutorial. You may want to get a tutorial book on Access that can teach you how use it. Also use the Help screens. For Access 2007 if you open the Create ribbon, you willsee the Query Wizard and Query Design....

Using MS Access: query construction, query construction, 599cd
query construction, 599cd, google: The only way I can think of to handle this problem would be to write a RecordSet to loop through the values, looking for 500, and incrementing a counter each time the value changed. Unfortunately, I don t have a tutorial available yet for RecordSets. Check...

Using MS Access: query to mail merge, jazz record, music genre
jazz record, music genre, access tutorial: Personally, I would have the query send a letter to all users who have purchased ANYTHING from that genre, not just their most recent purchase. What if I like jazz, and bought a jazz record two months ago, but my last purchase was a rock album? Plus, this...

Using MS Access: query parameter, query parameter, access tutorial
query parameter, access tutorial, 599cd: The parameter is simply evaluated when the query runs and can t find a value that s asked for (from the datasource). I don t know where the value is actually STORED, if it is at all. You can also specify parameter values in the query s properties. I hope...

Using MS Access: query regarding Connecting to MS-Access Report, access tutorial, access report
access tutorial, access report, president ceo: Well, you can t connect to a report in a different database. You d have to open that other database and have a LINKED TABLE in there that points back to your original tables. Then you can open the report. I hope this helps you. Let me know if you...

Using MS Access: query regarding missing operator -access vba, syntax error missing operator in query expression, hh mm ss
syntax error missing operator in query expression, hh mm ss, run time error: In Access, dates and times should be surrounded by # signs. I haven t tested it, but try this: strSQLAuthors = insert into citrix values( & Trim(uname) & , & Trim(Appln) & ,# & (Format(Dtime, hh:mm:ss )) & #,# & Format(Rtime, hh:mm:s ) & #, ...

Using MS Access: query to show date greater than first then show less than, current date, video tutorials
current date, video tutorials, less than sign: Jeff, That would be a separate new query with the less than sign Date() instead of the sign. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html Find video tutorials at: http://www.myaccessprogram.com/tutorial.html...

Using MS Access: RE: Report for multiple fields linked to same list of values, access tutorial, allexperts
access tutorial, allexperts, president ceo: I d need to see how your database is set up to give you a good answer. You can always create a series of queries and automate it with a macro or VBA code. I hope this helps you. Let me know if you have any more questions or comments. Cordially, ...

Using MS Access: Re: MS Access Word 2007, database terminology, access tutorial
database terminology, access tutorial, word answer: Neither is more beneficial. They each have their own distinct uses. See my free Access 101 tutorial (link below) if you need help with basic database terminology and design. I hope this helps you. Let me know if you have any more questions or comments....

Using MS Access: Receipt -command button code, openreport, docmd
openreport, docmd, button code: Access doesn t like spaces. DONT use them in your field names. Change your line to this: DoCmd.OpenReport Receipt , , , Receipt No= & [Receipt No] And repeat after me, I will NOT use SPACES in my field names, table names, or other object names!...

Using MS Access: Most Recent Query, goto view, query designer
goto view, query designer, sales rep: There are a couple of ways to do this. The first is, in the query designer, goto View - Properties. You ll see a standard property window just like in a form design. Look for Top Values and you can change how many rows are return. Unfortunately, this...

Using MS Access: Record source, left hand corner, record source
left hand corner, record source, upper left hand corner: Hi Tracy, Many thanks for the question. To do this, with the form in design view right click in the upper left hand corner of the subform (where the two rulers meet), slect the properties option, then click on the data tab, then change the record source...

Using MS Access: Regarding Access 2007, position table, information teachers
position table, information teachers, class changes: Is it okay if we normalize these tables a bit more? I m assuming that you might have a bunch of cases where a student is going to keep coming back to your school multiple years (and so do your teachers, more than likely)? If so, you will want a Student...

Using MS Access: Report Grouping Totals, invalid syntax, report grouping
invalid syntax, report grouping, detail lines: sorry I didn t get back with you sooner than now - been swamped at work. can you post the exact syntax of what you are using that produces the error. Unless the syntax is exactly what I posted, but with only changes to the field name... If you used...

Using MS Access: Report for multiple fields linked to same list of values, wildcard characters, combo boxes
wildcard characters, combo boxes, access tutorial: Just make a query using the LIKE keyword and wildcard characters that includes all of the word you re looking for. Base your report off this query. I cover this type of query in my Access 102 tutorial. I hope this helps you. Let me know if you have any...

Using MS Access: Run Access Query in Powerpoint Presentation, snapshot format, docmd
snapshot format, docmd, database location: I m not a powerpoint guy and what you are doing appears top be using Office Automation from within Powerpoint. You are outputting a report to a snapshot file, and then you need to refresh the snapshot file in Powerpoint. But I m not sure that s necessary....

Using MS Access: Running balance in MsAccess 2003, currency euro, parameter query
currency euro, parameter query, access tutorial: To calculate the balance of the previous year, use the DSUM function, such as: =DSUM( FieldName , TableName , DateField =#1/1/2007# AND DateField =#1/1/2008# ) DSUM is very similar to DLOOKUP. here s a tutorial that might help you: http://599cd.com/tips/access/dlookup-function...

Using MS Access: This record has been changed by another user since you started editing it., video tutorials, ms access
video tutorials, ms access, elaine: Elaine, You get this message when someone else has started an edit or has locked temporarily the same record you are changing or are in edit mode on. If you just want to view (no edits) the data in the form, base the form on a query that has the property...

Using MS Access: record locking, president ceo, 599cd
president ceo, 599cd, allexperts: How about this... as soon as a user starts to edit a record, set a value (call it CurrentlyEditing ) and immediately refresh the record so it s saved to the table. Now the user can go about his business, and then when he leaves the record, set CurrentlyEditing...

Using MS Access: record numbering, temp club, 599cd
temp club, 599cd, allexperts: How about this... make a separate query that creates a numbered list of your regions sorted alphabetically... then DLOOKUP the number from this query to determine the rank of your region. http://www.599cd.com/tips/access/dlookup-function/ I hope...

Using MS Access: No records from Query, isnull, matching records
isnull, matching records, microsoft access: I m assuming that after the user enters the criteria you have a button to open the results form. In the code for that button should be somethng like: IF DCount( * , queryname ) 0 Then DoCmd.Openform formname Else msgBox no matching records ,vbOKOnly...

Using MS Access: report criteria, square brackets, report criteria
square brackets, report criteria, microsoft access: Yes. I almost never use parameter prompt queries (which is what you refer to). The basis here is the ability to refer to a value in a control on an open form using the syntax: Forms!formname!controlname So you create a form with controls to enter the...

Using MS Access: reports, square brackets, access tutorial
square brackets, access tutorial, tips access: Sure... you can use a text box or even a combo box on a form to supply your criteria. See this tutorial: http://www.599cd.com/tips/access/forms-formname-field-notation/default.asp I hope this helps you. Let me know if you have any more questions...

Using MS Access: retrieve field properties?, java sql, president ceo
java sql, president ceo, 599cd: Java? I have no idea. I don t program in Java. I don t even think Access allows you to use Java. To use Visual Basic, just access the property value like this: AmIVisible = FieldName.visible AmIEnabled = FieldName.enabled And so on. ...

Using MS Access: retrieve field properties?, java sql
java sql: I may be possible to retrieve data from the field properties , however, I m not sure exactly what you are wanting to pull, so the answer is it depends . Post some more detail in a follow-up on what you would like to retrieve and I will see what can be...

Using MS Access: retrieve field properties?, java sql, vba
java sql, vba, property values: Hi, Many thanks for the question. You can do this, but will need to write a bit of VBA to achieve this. I am not sure what property values you wish to use and what you need to use them for. If you click on Alt and F11, this will open the VBA window....

Using MS Access: retrieve field properties?, java sql, microsoft access
java sql, microsoft access, vba: Sure, you would need to do this using VBA. But the following syntax would do the job: Forms!formname!controlname.propertyname will grab the value for the specified property from the specifed control on the specified form. Hope this helps, Scott...

Using MS Access: Saving and editing data in table, book column, field c
book column, field c, combobox: Dennis, You should not have to press a save button if each field control on your form is bound to a field in the table that your form s sql or query is based on. Be sure that only one table is being used to feed the form. Bob Heifler MyAccessProgram.com...

Using MS Access: Saving selected combo entry to separate table, selection updates, access tutorial
selection updates, access tutorial, 599cd: You re not taking a record from a table and inserting it into another table. You just want to take values from the currently open form and insert those into your log table. So you just need: INSERT INTO MyLogTable (UserName, MyTimeDate) SELECT MyEmployeeName,...

Using MS Access: Search criteria string question...., number ie, field 3
number ie, field 3, combobox: Like * & [Enter Part Number] & * However I would not do it that way. I almost never use parameter prompt queries because they are very limiting. I generally use an unbound control on a form and set the criteria using the syntax: Forms!formname!controlname...

Using MS Access: Search Form, currentdb, combobox
currentdb, combobox, combo box: There are several ways to accomplish this. Here is how you can pull the list of field names from a table, and add them to a combox box. (You may want to ensure the combobox is cleared before inserting new ones, and I didn t find a clear method, so you...

Using MS Access: Search Form, button wizard, solution code
button wizard, solution code, docmd: EASIEST SOLUTION: If you use the COMMAND BUTTON WIZARD, one of the options in there is to open a form and find specific data to display. Just set the value you want in your search form and then have the user click this button. BEST SOLUTION: Code your...

Using MS Access: Search form in access, combo boxes, combox
combo boxes, combox, query string: I m not sure I follow. Are you trying to display a list in a combo box based on the selection of a prior combobox? If so, there is a lot of information on-line already about cascading combo boxes http://www.google.com/search?source=ig&hl=en&rlz=&q=cascading+combo+box...

Using MS Access: Select Last 6, golf league, better solution
golf league, better solution, ascending order: Larry, Many thanks for the question. OK. Try the following. I cannot think of anyway to do this in a query, without writing multiple queries, so you will need to write some code. Not sure if you have done this before or not, so I will step you through...

Using MS Access: Select question, access tutorial, president ceo
access tutorial, president ceo, 599cd: Turn on TOTALS in your query and set the date field to GROUP BY. I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and watch...

Using MS Access: Selecting multiple external files via command button, shell command, access tutorial
shell command, access tutorial, command button: Here s a simple solution off the top of my head... You can use the SHELL command in VB to launch a program and open a specific document, like: Shell Notepad.EXE MyDoc.TXT , vbNormalFocus If you can determine what type of file it is (Word DOC, PDF,...

Using MS Access: Sending Email through Access, e mail address, microsoft access
e mail address, microsoft access, complaint form: If you are pulling it from a form, then just put: Me.controlname where controlname is the name of the control that has the e-mail address in the TO area of the SendObject method. When you enter Docmd.SendObject, the Intellisense helper shows you what...

Using MS Access: Separate the data from the Code and Tables, custom menus, code tables
custom menus, code tables, access97: No it is not like an EXE. it cannor be run stand alone. You still need Access to run it. But it will prevent the user from getting into any VBA code. If you use custom menus and prevent access to built in menus you can control, to a large extent, what the...

Using MS Access: Setting Up Reminders Based on Date Field, last names, drop down list
last names, drop down list, right direction: Paul, Many thanks for the question. Not sure if I have enough information, but I will give it a go. Create a report based on a query with the required fields, i.e. LastName, FirstName and YesNoField (and any others), in the criteria row for the YesNoFiled...

Using MS Access: Show Different Image Per Record using the image path stored in table, image control, image path
image control, image path, current event: On current is a Form event, not a control event. it would look like this: Me.imgPic.Picture = Me.txtPath where imgpic is the image control and txtpath the control holding the path. If there is a possibility of the path being null or the file not...

Using MS Access: Simple Microsoft ACCESS Question, unmatched query wizard, unmatched records
unmatched query wizard, unmatched records, 599cd: First, I never use Data Access Pages. I hate them. They never work right. Second, the FIND UNMATCHED QUERY WIZARD has always produced the results I ve asked for, so without seeing your database I don t know if I can help you. I can only assume you must...

Using MS Access: Special form to search for records, combo boxes, control search
combo boxes, control search, question thanks: Ok, that s different from your first post. But you can t get away without having multiple controls. You can t have one control search through different fields. So you are going to need separate controls anyway. You can put search combos in the form header...

Using MS Access: Special search form, combo boxes, text boxes
combo boxes, text boxes, tips access: Yankel, there is a quantity discount available whenever you purchase more than one course - the web site will automatically calculate a discount for you. If you just want to start with the VBA, then start at lesson 301. You can download the sample database...

Using MS Access: Specific client lookup on form, parameter query, 599cd
parameter query, 599cd, tips access: Base the form on a query that uses the order number as a parameter. You can make a simple parameter query as shown in this tutorial: http://www.599cd.com/tips/access/parameter-queries/ This tutorial might also help you: http://www.599cd.com/tips/access/forms-formname-field-notation/...

Using MS Access: SQL query, c111, fuel surcharge
c111, fuel surcharge, john smith: sorry for not getting back with you sooner. I would feel remiss if I didn t point out that you should normalize your data further by only have the Cust#, Svc Code and Svd Date in one table, which moving the description to your Auth Sevc Code table - and...

Using MS Access: SQL Syntax -, access tutorial, president ceo
access tutorial, president ceo, 599cd: How about: SQL = SELECT * FROM Expedite WHERE PO_Number= & PO_Number & AND Part_Number= & Part_Number You don t need the brackets unless you have SPACES in your field names... SQL = SELECT * FROM Expedite WHERE [PO Number]= & [PO Number] &...

Using MS Access: Sql count statement, question thanks, combo box
question thanks, combo box, evaluator: You would write just like any WHERe clause without the WHERE. If DCount(( * , tblEvaluation , [EvaluatorMgr] = & txtRandomMgr & AND [Management_Type] = & cboMgtType) Then The ampersand is the concatenation character that concatenates the value...

Using MS Access: Stock Tables, stock tables, stock value
stock tables, stock value, questioner: Correct, The Transaction Type is a lookup table. =[qryCurrentStock]![Current Stock] The above code won t work because you can t address a value in a query or table directly. You have to use a Domain aggregate function like the DLookup. You could setup...

Using MS Access: Storing calculated values?, creating an invoice, price variation
creating an invoice, price variation, correct evaluation: John, You make a good point. Follow your correct evaluation on this and reject data that does not apply to your needs. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html Find video tutorials at:...

Using MS Access: SubForm does not update automatically, combo box, combobox
combo box, combobox, initial selection: I think I may know what is going on, but this suspicion is based on personal experience from when I first started using Access. If you have a main form that is bound to sub-form(s), and you drop a combo-box control onto the form, whenever you change the...

Using MS Access: Subforms, customer table, customer field
customer table, customer field, details table: Hi Peter, Many thanks for the question. I am not sure if understand fully. Just to check, in your ProductTable do you have a field linking to the customer field? Or are the start and finish numbers of your products are in the customer table. Or...

Using MS Access: Subforms are not populating properly, record source, source object
record source, source object, combo box: Sounds like you are doing it right, so I can t tell what might be wrong. I suggest you post a copy of your database in a note at either askmehelpdesk.com or utteraccess.com. You can zip the db and attach it to a note. Hope this helps, Scott Microsoft...

Using MS Access: Sum only positive or negative values, dsum, negative values
dsum, negative values, many thanks: Miguel, Many thanks for the question. I hope I have understood the question properly. To do what you ask try the following: For the negative (change to =0 for the positive IIf(Column1 0,Column1,0) + IIf (Column2 0, Column2,0), IIf(Column3...

Using MS Access: Getting Sums from groups, detail line, query designer
detail line, query designer, table keeps track: I can provide personal help, if you have a budget for outside consultancy. Just something to consider. if interested, which version of Access are you using, and is this a db that you have created from scratch or taken over from someone else, and does it...

Using MS Access: Syntax, syntax question, message boxes
syntax question, message boxes, case company: You need to use an octothorpe (#) to delineate dates, the apostrophe is used to delineate strings and numbers are left alone. For example: Numfield = & numvariable stringfield = & stringvariable & datefield = # & datevariable & # Hope...

Using MS Access: separate Fname and LName in a field, instr function, len function
instr function, len function, lname: Hi Treasa, Let sTmp = Smith, John LName = Left(sTmp, InStr(sTmp, , ) - 1) FName = Right(sTmp, Len(sTmp) - (InStr(sTmp, , ) + 1)) Notes: The InStr() function returns the position of the , . We then use this to calculate the cut-off, to the left...

Using MS Access: shared database, multiple users, microsoft access
multiple users, microsoft access, network share: Is your database split? EVERY multi-user app should be split with the backend containing just the tables and located on a network share and the front end containing everything else on the local drive. This would eliminate your problem. Hope this helps,...

Using MS Access: sql count, evaluator, random numbers
evaluator, random numbers, sql statement: Simone, hi Your question is only about 60% clear... I can see there is something wrong with your SQL If you have SELECT tblEvaluation.EvaluatorMgr, Count(tblEvaluation.EvaluatorMgr) AS MyCount, & _ tblEvaluation.ECycle , tblEvaluation.Month,...

Using MS Access: sql count, groupby, evaluator
groupby, evaluator, random numbers: Hi Simone, Many thanks for the question. I think you need a GroupBy in your SQL, Try the following: strSQL = SELECT tblEvaluation.EvaluatorMgr, Count(tblEvaluation.EvaluatorMgr) AS MyCount, & _ tblEvaluation.ECycle , tblEvaluation.Month,...

Using MS Access: sql count, tips access, president ceo
tips access, president ceo, 599cd: Instead of using a recordset, why don t you just use the DCOUNT function to count the number of records that has a particular evaluator ID in it (of course you ll need to store that when the record is evaluated). DCOUNT is very similar to DLOOKUP. Here...

Using MS Access: start and end times, focus event, default property
focus event, default property, time doesn: The value in the default property of a control is used only when the record is first created. So the thing you are missing is how to trigger the assignment of Time() to the EndTime control. Not knowing exactly how your work flow goes, I m not sure what the...

Using MS Access: subform requery not being refreshed, openrecordset, header section
openrecordset, header section, addnew: I don t think I could fix this without looking at your form, but why are you using a form/subform here if your main form is unbound? Why not just use a CONTINUOUS form with a HEADER section? I hope this helps you. Let me know if you have any more questions...

Using MS Access: subforms, northwind traders, c students
northwind traders, c students, three tables: It sounds to me (and i may have this wrong) but students is linked to subjects only and not to teachers. If this is the case then you can t create a sub-form on Instructors that gets your students. In order to for a sub-form to work properly there has...

Using MS Access: subforms, point don, relationship
point don, relationship, child record: have you tried doing a refresh/requery of the subform? If I m understanding correctly, you are saying that the values in fields on the main form change just as if the form is navigated to a different record. Are any of these fields that change calculated...

Using MS Access: Doing a sum, input numbers, time a change
input numbers, time a change, sql statement: I actually have a text box being automatically filled with some values based on 2 other fields, and what I have done is created a procedure within the form (because my database only has 1 form, no reports or anything else that utilizes these fields), basically...

Using MS Access: syntax error(missing operator)in query expression, syntax error missing operator in query expression, openrecordset
syntax error missing operator in query expression, openrecordset, string field: If you just copied and pasted that line from your code, it looks like you need to add some SPACES in there (before Where for example). Also, that doesn t look like a valid command line to me. You can only use WHERE in an SQL statment SELECT Fields...

Using MS Access: Table combo bowes and filtering, combo boxs, combo boxes
combo boxs, combo boxes, finished reports: Obviously without being able to see your database, I can t really tell what is wrong, but combo boxes should behave exactly like text boxes provided you have the NAME right, and the value you want to refer to is in COLUMN ZERO of the box. I hope this...

Using MS Access: Tables, access tutorial, president ceo
access tutorial, president ceo, 599cd: Without seeing your database, I have no way of knowing what you did. I can only suggest a COMPACT AND REPAIR of your database. If that fails, restore from your backups. I hope this helps you. Let me know if you have any more questions or comments. ...

Using MS Access: TIME DEFFERENCE, datediff, quary
datediff, quary, minuets: take a look in your help files for DateDiff - I know it says Date in there, but you can take the difference between two sets of time as well, and go down past the second (I believe - if need be), but since you asked about minutes, here s the recommended syntax...

Using MS Access: time calc between delivery stops, access tutorial, tips access
access tutorial, tips access, delivery drivers: See my tutorial on Access time/date values: http://www.599cd.com/tips/access/difference-in-dates-times/ I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training...

Using MS Access: Unable to export report in excel, mail recipient, excel 97
mail recipient, excel 97, word format: Reports are like documents, or even pictures, in Access, they are not a data format (although they may look that way). Excel can t read a report. Try the Send Object Action with the query that drives the report. Or, use Word format to send the report...

Using MS Access: Unable to export report to excel, compact repair, mail recipient
compact repair, mail recipient, blank database: did the compact/repair error or did it work fine and you still can t export your report. All you say is it doesn t seem to work which could mean either one. If the r/c worked but you still can t export the report, did you try creating a new blank database...

Using MS Access: Union Query, select name, two tables
select name, two tables, many thanks: Hi, Many thanks for the question. Your Union Query should look like this: SELECT [ID],[DEPT],[NAME] FROM [SUPPLIER_ALPHA] UNION SELECT [ID],[DEPT],[NAME] FROM [CONTRACTORS_BETA]; Hope this is of some help. Regards Julie www.simply-access.co...

Using MS Access: Update data in a open form, requery, computer hope
requery, computer hope, second computer: First you have to commit the record on the data entry computer. Until you do, its not reflected in the table. You would then have to requery or refresh the form on the second computer to retrieve that record. Hope this helps, Scott Microsoft Access...

Using MS Access: Update Query help, stock table, inventory application
stock table, inventory application, transaction type: You aren t reading what I said. You should NOT have a field in a table for stock on hand. This value should be CALCULATED when needed and not stored. So there is NO need to deduct the the ordered quantity. The way I generally do this is I have a Transactions...

Using MS Access: Update Reciprocity, state zip code, address city state
state zip code, address city state, small exception: I don t know what steps you re taking, but if I go to File Get External Data Link Tables... change the date type to EXCEL (XLS) and link to a spreadsheet, I have *NO* problems editing the DATA in that sheet. You cannot change table structure because you...

Using MS Access: Update Totals Via SQL, detail records, different things
detail records, different things, vb code: Sorry i haven t written in quite some time, I have tried handling some of the quicker posts that others have given to me, but I think I should refer you elsewhere for your answer - if you haven t already gotten it somewhere else. There is a similar question...

Using MS Access: Updating records with data from the same table, access tutorial, vba code
access tutorial, vba code, tips access: OK, I m assuming you mean that you have 3 date FIELDS in the same RECORD, not just the same TABLE... that your table is set up like this: MemberID MemberName OldestDate MiddleDate RecentDate Correct? If so... I would just make a form and show...

Using MS Access: Updating a specific record in a Table, dde link, mask2
dde link, mask2, search parameters: First, let me begin by saying that I don t like to hunt through peoples source code for problems. In fact, my pre-question instructions say, I won t read through 100 lines of your source code looking for errors, so please don t post it all here. With...

Using MS Access: Upgrade to Access 2007 - not calculating, microsoft access, vba
microsoft access, vba, ms office: You need to look at the source of the calculation. it may be done in the Invoice report or, more likely, in a query the report is based on. You may have to re-enter the calc. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access...

Using MS Access: User Level Security, user level security, custom menus
user level security, custom menus, mdb format: First Access is not the most secure platform even with ULS. Second, you can still use ULS, but wioth the MDB format not the ACCDB format. Frankly, I have never implemented ULS. I find it to cumbersome for the little security it gives. I generally program in...

Using MS Access: Using 2 fields in a lookup list, relational databases, microsoft access
relational databases, microsoft access, two tables: Yes, Normalize your database properly and this doesn t become an issue. Normalization is a key factor in designing relational databases. What you need is two tables a household table and a members table. You would also have an events table and a signups tables....

Using MS Access: Using 2 fields in a lookup list, seperate fields, count members
seperate fields, count members, attendence: I m not sure exactly what it is your trying to pull 2 differnt fields into, so how you do what you re wanting will depend on your specific scenario, which is just a tad bit vague enough to not be able to fully assit you. My ultimate recommendation would...

Using MS Access: Using data in an array as Control Source, array value, control source
array value, control source, access tutorial: I m SURE there is a BETTER way to code this, but this is what comes off the top of my head... you could loop through all of the controls on your form, and then if you find the one that matches your array value, change the contents... Dim Ctr As...

Using MS Access: Using switchboards, switchbord, switchboard
switchbord, switchboard, 599cd: Honestly, I never use switchboards. I hate them. They re so limited. I make my own menus and use custom command buttons to open the forms I need. However... A switchboard is really just a form like any other forms. Go into your Database Startup properties...

Using MS Access: Using wildcards in a formula, wildcard search, query criteria
wildcard search, query criteria, ac line: Now you re getting a bit trickier. Here s what I would do... create a text box (call it MySQLStatement). In this text box, place the SQL statement that controls your form or report. Now, based on the value of the COMBO BOX, you can set the value of your...

Using MS Access: unique number using combination fields, kbl, instance name
kbl, instance name, video tutorials: Martin, In the form s code module write a subroutine : Sub TrackingNo() [TrackingNoField]= [ProjectFieldName][LocationDropdownFieldName] & [TypeFieldName] End Sub I would check for null on each field before allowing the write using Isnull() function....

Using MS Access: update controls with combobox choice, database manipulation, combo box
database manipulation, combo box, choice question: ahh now I understand. For the first combo box I would use the comobobox wizard to create a search combo (the third option under the wizard). This will allow the user to select a record and populate the controls on the form with the values from that record....

Using MS Access: update query & editing cell information, access tutorial, access update
access tutorial, access update, tips access: Your UPDATE TO line would be: RIGHT(MyField,LEN(MyField)-1) This says to make the field equal to the right-most X characters, where X is equal to the total length of the field minus 1. Here s a tutorial on Update Queries in case you need more assistance...

Using MS Access: using the datediff function, datediff function, microsoft access
datediff function, microsoft access, vba: Why would you need to? As a general rule we do not store calculated values. Such values can be calculated for display when needed. As long as you are storing the 2 dates there is absolutely no need to store the difference. Hope this helps, Scott Microsoft...

Using MS Access: VBA Code, access tutorial, president ceo
access tutorial, president ceo, 599cd: Replace your IF line with this: If Appeal = TRUE Then I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site and watch my 90-minute...

Using MS Access: VBA code syntax, excel sheet, field names
excel sheet, field names, appending: Hi Yu, Many thanks for the question. Try the following: Public Sub sFieldNames() Dim dbCurr As DAO.Database Dim tdfCurr As DAO.TableDef Dim fldCurr As DAO.Field Set dbCurr = CurrentDb() Set tdfCurr = dbCurr.TableDefs( tblPassports ) For Each...

Using MS Access: Vb Programming in Access 2007, access vba programming, sam davis
access vba programming, sam davis, 599cd: Sam, honestly I haven t really worked with AC2007 much yet. I m still using XP/2003 here. From what I ve read, the VBA programming in 2007 is not much different from XP/03. The main changes in 07 have been interface design changes. So if you re currently working...

Using MS Access: View & Use database using single form (Mainform), database properties, access tutorial
database properties, access tutorial, database window: Go into the database properties and turn off the DATABASE WINDOW and then specify a STARTUP FORM, like your main menu. Then all you ll see is your startup form. I hope this helps you. Let me know if you have any more questions or comments. Cordially,...

Using MS Access: Visible, notinlist event, switchboard
notinlist event, switchboard, cancel button: Sure, In the NotInList event you have an OpenForm method. Set the OpenArgs argument to Visible or similar. In the On Open Event of the form use: If Me.OpenArgs = Visible Then Me.cmdButton.Visible = True End If Hope this helps, Scott Microsoft...

Using MS Access: Yes/No Pop up message, minor tweaks, title buttons
minor tweaks, title buttons, avialable: You can certainly do this, but I don t usually like to generate a message box unless it is absolutely necessary - in other words, there may be other ways to do this that are less instrusive to the User experience. having said that, what you will want to...

Using MS Access: 1:1 relationship, access tutorial, relational tables
access tutorial, relational tables, tips access: Uhm... without a LOT more information, I really can t tell what you re doing wrong. No, Access does not ignore 1:1 relationships, but it all depends on how you re setting it up. Here s a tutorial that might help you: http://www.599cd.com/tips/access/relational-tables...

Using MS Access: AC2002- report from a combo box, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, dlookup: You could either create a text box with a SUM function in it to add up all of the values for the boxes. Here s how to do it in a form footer (report footer is similar): http://599cd.com/tips/access/form-footer-total-sum?key=AllExperts ...OR use the...

Using MS Access: ACCESS and photos, storage folder, store images
storage folder, store images, network location: The way I would treat photos is making sure they are all placed into one directory (be it a local folder or a network location) and use a picture control to display the image. The only limitations in this is that you might not be able to dynamically resize...

Using MS Access: Acces 97 Queries Question, field names, design mode
field names, design mode, microsoft access: Hide what line? I don t really understand what you mean. You don t have to use a query to set the order of fields as displayed in datasheet mode. You can set the order in Table Design mode. Hope this helps, Scott Microsoft Access MVP 2007 Author:...

Using MS Access: Acces 97 Queries Question, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, query criteria: Sure. Just set up another field called HideMe (or whatever you want it to be called) and then set the criteria for it in your query to: FALSE. Now open your table and click on the HideMe field for any records you don t want to see. Now when you run the query,...

Using MS Access: Access 2000 field list, source table, sample applications
source table, sample applications, data source: Dale, Look at your new form s property called RecordSource ant select the table or query that the form should be based on. You will then get to see a field list for that data source. You would of course need to have that data source (table) available as...

Using MS Access: Access 2003 Active X Calendar 7, calendar object, access tutorial
calendar object, access tutorial, 599cd: You just need to know how to refer to the date value of the calendar object from your query. See this tutorial: http://www.599cd.com/tips/access/forms-formname-field-notation For example, let s say you just had a text box named MyDate. In your query,...

Using MS Access: Access 2003- Difference between UK dates, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: You re going to have to convert those TEXT dates to DATE dates. Try using the CDate function... MyDateDif: CDate([ActualDate])-CDate([ExpectedDate]) See if that works. Depending on the regional settings in Windows, Access should see that UK-style date...

Using MS Access: Access 2003 Security, access tutorial, switchboard
access tutorial, switchboard, backend database: You have to set up Wrigley on the same MDW workgroup file that you re using. Every connected user needs to access the database using that MDW file. Set it up using the Workgroup Administrator. Cordially, Richard Rost President/CEO 599CD Computer...

Using MS Access: Access 2007 Question, combo box, combobox
combo box, combobox, parameters: My recommendation would be to use the Not_In_List event procedure of the combo_box control. if you want to tell users they can type something in to have it added, use a Label control, and make it stand-out with a different background and font color. ...

Using MS Access: Access Database question, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, query fields: Chris, that video explains pretty much everything you need to know. If your data is ALWAYS in the same format, you can do it like this: MyYear: Mid(MyField,2,4) MyLetter: Mid(MyField,7,1) Just slap these in as calculated query fields, and you re good...

Using MS Access: Access date field, minimal programming, date control
minimal programming, date control, visible property: This gets more complicated. You would need a field in the table for LateReason. I would also create a lookup table for the valid reasons. I would then create a combo to select the reason, but set its Visible property to No. In the After Update event of...

Using MS Access: Access Form - Subform with New Records, record source, source catalog
record source, source catalog, microsoft access: Yes, that does make a difference. I searched that error message at support.microsoft.com and it came back with a half dozen possibilities: http://support.microsoft.com/search/default.aspx?query=doesn%27t+satisfy+the+criteria+in+the+underlying+record+source&catalog=LCID%3D1033&spid=&mode=r&lsc=0...

Using MS Access: Access Formula, expression builder, property dialog
expression builder, property dialog, access example: Did you try following the instructions I gave you? You have to have the ControlSource property active to get the [...] next to it. And no, you don t put these calcs in the table. Again you enter them in either the Controlsource property of a control on a form...

Using MS Access: Access Formula-calcuating weeks, access programmers, query designer
access programmers, query designer, datediff: I m going to assume you re wanting to do this in a query and not tables, because Access doesn t allow you to place a calculation as a default value - I take that back, I know it s not a good practice to store a calculation as a default value, because you can...

Using MS Access: Access Macro - How to?, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, macro in access: I wouldn t use a macro... I d use a form field and some vba code. Watch this tutorial: http://599cd.com/tips/access/forms-formname-field-notation That will show you how to have a query get its criteria from a form field. Then use VBA to run all...

Using MS Access: Access Query's, access query, excel spreadsheet
access query, excel spreadsheet, msgbox: You can t do it that way. That s why I rarely use parameter prompt queries. What you are better off doing is creating a form bound to your table, then use the combobox wizard to create a search combo. The user can then type in the number and it the Autoexpand...

Using MS Access: Access, Replication and Design Master, master question, dummy data
master question, dummy data, sequential numbering: Splitting is not just for large apps. EVERY app that is either multi-user and/or supported by someone other than the user NEEDS to be split. This article may help you with more on this: http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53&Number=1264601&page=0&view=collapsed&sb=5&o=&fpart=1...

Using MS Access: Access Report Multi-entry Field, header footer, group header
header footer, group header, headers footers: In addition to Page and Report headers/footers, you can create your own group header/footer, which in theory can summarize various values, like the total value of sales for a particular product category, giving each individual product as a detail. The thing...

Using MS Access: Access reports, visible control, paste method
visible control, paste method, visible property: Gotcha.... Reports work a little differently in Access than Forms, and I don t think you have the option of doing what you want. (Forms run on code that is dependent on the data it pulls and current records, Reports run on code dependent on entire datasets.)...

Using MS Access: Access to Sql Server, data management system, rdbms
data management system, rdbms, multiple users: You don t have to worry about that. Part of the data-management system that SQL Server provides is Row level record-locking. The moment someone accesses a record and starts to edit it, it s locked for everyone else to edit. This is handled automatically...

Using MS Access: Access to Sql Server, access tutorial, president ceo
access tutorial, president ceo, 599cd: If you want complete control over the process, the BEST solution for this is to add a field to your table indicating whether or not the record is currently being edited. As soon as the user starts to edit the record, set the value of this field to YES, and...

Using MS Access: Access to Sql Server, editing records, table structure
editing records, table structure, multiple users: Access and SQL both should have record locking for editing records. Have you tested this out to see if your current system is allowing multiple edits at the same time? There are ways to ensure this is not happening, and it all depends on how you want to...

Using MS Access: Access, subform dependent combo box, combo boxes, database dialog
combo boxes, database dialog, combobox: Hi Margaret, 1. Ensure that the sub-form holder object is named as sfrmPOdetails , as it is this that is used in the naming hierarchy and NOT the name of the form as held in the database dialog. 2. Now I will assume that this code is running from MainForm...

Using MS Access: Access subform that updates parent form, teacher data, tips access
teacher data, tips access, access tutorial: It sounds like you re on the right track as far as how you have your database set up. As far as your query problem goes, if three TEACHER records have the same AppID on them, you should see them all when you run your query - if your query is set up right....

Using MS Access: Access top 10 by category, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, rep info: You might have to do this with multiple queries... in fact, you might have run this as a series of Top X queries (one for each leader) and then fill those results into a maketable/append query to put all of your results into one table... and then display that...

Using MS Access: Access "Unlock Field", control 1, add button
control 1, add button: In the On_click event of the Add button, add the following piece of code for any control that you have locked and want to unlock Me.Control_1_Name.enabled = true and you may also have to add Me.Control_1_Name.locked = true Also, if you are...

Using MS Access: Access2000/2002: Assigning Unique ID based on 3 separate pieces of data, vba code, sequential number
vba code, sequential number, tips access: Yeah, you re probably going to need some VBA to do this. It s not hard. What you just need to do is figure out how to create your own COUNTER and then add on to it the other things (business unit and year) which should be easy. Here s a tutorial I put together...

Using MS Access: Add a Record to a Table, column table, command button
column table, command button, table column: I don t understand why you re not binding the form to the table? If you bind the form to the table, then you can bind all the controls on the form to fields within the table, and you can control whether you want the form to be read-only, edit-only or add-only...

Using MS Access: Add record to multiple databases simultaneously, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Well, I m curious as to why you don t just have all three databases LINK to the same Employee table. That would solve your problem and eliminate duplicate entries. But assuming that you NEED to have the same data in three different databases, I would link...

Using MS Access: AfterUpdate, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: If you re using AfterUpdate events already, then you re on the right track. Now just put four Yes/No values in your table, and four checkboxes on your form that are bound to them, call them FirstYN, SecondTN, ThirdYN, FinalYN. Now put AfterUpdate events on...

Using MS Access: Not Allowed to Edit - No reason why., updateable query, reading data
updateable query, reading data, vba: Hi Chris, The best way is to break out individual tables into sub-forms, nested on the main form with a linked Master/Child fields. So set up the main form on the main table, then build a separate form for each joined table (on its own). In design...

Using MS Access: Allowing users to choose report sort at runtime., microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, report sort: Josh, You could make a form that has your criteria, sort method, or even a custom SQL statement on it, and then have a query that reads that information... on which your report is based. Here s a tutorial that explains it: http://599cd.com/tips/access/forms-formname-field-notation...

Using MS Access: Auto-Populate Fields from one table to another, storage table, storage records
storage table, storage records, storage files: If I am understanding correctly, you are wanting to duplicate your case information into your storage table, based on the case number. This isn t necessary and will create database bloating (your file size will keep increasing more than necessary). The...

Using MS Access: Autocalculate value from another feild in the same table, group displays, query designer
group displays, query designer, desinger: You won t want to do this in a table - I can say with 99% certainty that you can t. You can, however do this in a query. I m not sure how familiar you are with the query designer - there is 2 ways to show you how it s done. The first way is in the desinger...

Using MS Access: Autocalculate value from another feild in the same table, group displays, dummy records
group displays, dummy records, sequential number: First, if you delte all records and compact and repair, the autonumbering will start from 1. However, This shouldn t matter. An autonumber should not be counted on as a record number or sequential number. You can set up a multi-field unique index to prevent...

Using MS Access: Autonumber, concatenate and hidden text box, membership table, long integer
membership table, long integer, primary key: First, if its an autonumber datatype, then it can be any value that fits within the range of a long integer datatype, not just 5 characters. If you are displaying it as 5 characters you are using a Format to do so. This should be done in the form, NOT in...

Using MS Access: Autonumber with a "text" field type, sql server database, access tutorial
sql server database, access tutorial, tips access: Not really. Well, you could go with a RANDOM number/text string and then just DLOOKUP to check and see if it exists or not each time you add a new record. Or, you could keep a separate counter field that is numeric and add your custom text on to it. Either...

Using MS Access: access 2000 printing report from form, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: It could be any of the following... You need brackets in your report name (assuming it s spelled correctly) because of the space: strDocName = [rptcement report] I don t think that s it because Access would have said it couldn t find the REPORT,...

Using MS Access: acess table, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, excell spreadsheets: Sure. You re fine. I m assuming you brought two FIELDS together into one TABLE. So you have Field1 and Field2 and you want to see if they re different? Just make a QUERY and set the CRITERIA for Field2 equal to: Not [Field1] -OR- [Field1] ...

Using MS Access: How to add Dynamic Control to ms access form, ms acces, dynamic control
ms acces, dynamic control, database structure: Can you explain, in greater detail, what you are trying to accomplish here. A database is a not a spreadsheet with rows and columns. You work with fields and records. So your question indicates you may have issues with your database structure. Hope this...

Using MS Access: add multiple records as per date range criteria, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, openquery: This will require using some VB code and either an INSERT query or a recordset. I m working on a recordset tutorial right now (check my web site in a week or so) at http://www.599cd.com/Tips/Access In a nutshell, for what you want to do, you ll have to...

Using MS Access: alert box, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Sure. You ll have to program an event somewhere - I d recommend in the OnOpen event of a form - which could say something like: MyDeadline = #4/25/08# If Date = MyDeadLine Then MsgBox DEADLINE IS HERE! End If If you need help with Event programming,...

Using MS Access: autocomplete text box based on combobox, text boxes, column widths
text boxes, column widths, microsoft access: Thanks for letting me know, Carol. And may I commend you on searching for a solution, finding one and sucessfully adapting it to your needs. If more people would do that, I would have less questions to answer. One caveat, though. I m not sure which answer...

Using MS Access: automatic changing date, database option, second date
database option, second date, textboxes: Hi Taco, For each updateable object, you need an after update procedure along the lines of: Private Sub AnyUpdateableObject_AfterUpdate() Me.LastUpdate = Format(Now, dd-mm-yyyy ) End Sub So if you have 4 textboxes on a form, you will need 4...

Using MS Access: automatic changing dates, database option, time field
database option, time field, second date: I m not sure why its not working, but that s not the code I would use. I assume wijziging is a Date/Time field. If it is you should not be using the Format function because that turns a time value into a string. Just use the fiollowing Private Sub Form_BeforeUpdate(Cancel...

Using MS Access: autoupdating inventory, inventory transactions, query key
inventory transactions, query key, stock inventory: Something like this would be best handled by an UPDATE QUERY or a RECORDSET. I have a tutorial on creating update queries: http://599cd.com/tips/access/update-query?key=AllExperts You could run that at the end of the day to update all of your stock inventory...

Using MS Access: Basic Delete Query Problems, key job, client fax
key job, client fax, client address: Your SQL looks OK to me at a glance. Without seeing your database, however, it s hard to troubleshoot something like this. I wouldn t even both with the SQL view of this. Just build it in the graphical query builder and then run it from there. Essentially...

Using MS Access: Basic Delete Query Trouble, key job, client fax
key job, client fax, client address: Peter, Many thanks for the question. Because you have many tables in the delete query, it does not know whether to delete from the client table, the job table or the areaplanning table. You may need to create three or more delete queries, one for...

Using MS Access: Basic Delete Query, key job, client fax
key job, client fax, client address: Peter, hi, You ll need to run the following queries: the last one needs to be run last (if you want to remove the JobRef from the JOB table. DELETE * FROM CLIENT WHERE [Job_Ref_No] IN ( SELECT Job_Ref_No FROM JOB WHERE Dead_Filed=-1) DELETE...

Using MS Access: 'Batching' Records, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, action queries: Absolutely... you could use an APPEND query to copy all of the records in TableA to TableB, and then use a DELETE query to remove them all from TableA. I cover Action queries (Append, Delete, Update, MakeTable, etc.) in my ACCESS 222 tutorial: http://www.599cd.com?GOAC222...

Using MS Access: Bound/Unbound Forms, s books, sr 1
s books, sr 1, microsoft access: I list the title in my signature. You can get it from Amazon: http://www.amazon.com/Microsoft-Office-Access-Business-Solutions/dp/0789737310/ref=sr_1_1/102-2698372-4255300?ie=UTF8&s=books&qid=1191427757&sr=1-1 Hope this helps, Scott Microsoft Access...

Using MS Access: Button to run report, microsoft access, vba
microsoft access, vba, ms office: If you have a follow up question please use the followup options. I answer dozens of questions daily and can t remember my previous options. I m sure I never told you to use: =[Reports]![rptPM]![PM] Hope this helps, Scott Microsoft Access MVP 2007...

Using MS Access: Calcuated fields in forms, microsoft access, data source
microsoft access, data source, vba: Can you explain this further. Forms don t have fields. They have controls that may or may not be bound to a field in a table. Controls have a Controlsource property that defines what the control will display. So what are you trying to do? Hope this...

Using MS Access: Calculated Field in Access, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: In order to do this, you would need to build a form or report and put the total in the footer. See: http://599cd.com/tips/access/form-footer-total-sum Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Using MS Access: Calculated field, database attribute, table element
database attribute, table element, onclick event: Vinson, If you are calculating something that you want stored in the [Age] field you would simply trigger some code on the form to write to the form s control the result of the calculation. Let s say the form s control name is Calculated Age . You would...

Using MS Access: Calculating Average Values in a Pivot Table, pivot table, numeric field
pivot table, numeric field, microsoft access: When building the Pivot table using the wizard, it prompts you for the field to use in the body of the table. At that point you have several options, to sum, average, count and more. Are you not getting a choice to average then? Hope this helps, Scott...

Using MS Access: Calculating and displaying a custom total field, programmer analyst, exact piece
programmer analyst, exact piece, aggregate functions: Cathy, this should be easy enough to calculate using the DCOUNT() function. I don t know if you re familiar with the Domain Aggregate functions like DLOOKUP, DCOUNT, DMAX, DMIN, DSUM, etc. but they basically allow you to look up values from a table or query...

Using MS Access: Calculation, global module, microsoft access
global module, microsoft access, microsoft office: No this would go in a global module as a stand alone function. You would then use the function just like any other when you want to display those values. Hope this helps, Scott Microsoft Access MVP 2007 Author: Microsoft Office Access 2007 VB...

Using MS Access: Calculations in a Table, numeric data types, kiss method
numeric data types, kiss method, math formula: you can do calculations in Access forms: say you have two text box in a form, say text_box_A and text_box_B with numeric data types you can place a new text box with = [text_box_A]+[text_box_B] as its data source (take in account that text_box_A, is...

Using MS Access: Calling word from access, word mail, office automation
word mail, office automation, access query: Yes it is possible using Office Automation. I suggest going to support.microsoft.com and search on Office Automation. You will find several articles that deal with controlling one Office app from within another. The topic is really too much to go into here....

Using MS Access: Changing the colour of a check box, access tutorial, president ceo
access tutorial, president ceo, 599cd: Joe, I ll start out by saying that I don t use AC2007 yet. I m still with 2003. I don t like 2007 much. :) I don t know if they ve change this in 2007 yet, but in 2003 you can t change the foreground or background color of a checkbox. All you can change...

Using MS Access: Check boxes and If statements, rules of normalization, stock table
rules of normalization, stock table, check boxes: You may need to redesign your database. Under rules of normalization you should never have a value dependent on another value. So the Availability field is superfluous. If the QuantityInStock is zero then that tells you its not available. If you want to...

Using MS Access: CheckBox ComboBox Extending the ComboBox Class and Its Items, check boxes, relevant records
check boxes, relevant records, combobox: Hi Harry, Try using a subform approach. Build a form as Continuous Forms with the check boxes etc linked to the table. Now open the main form in design mode, and drag the above form onto the main form as a sub-form. You will need to complete the...

Using MS Access: Checkboxes, colomn, coloumn
colomn, coloumn, access vba: I m not sure I fully understand your question, but this knowledge may help: A Yes/No datatype in Access can hold two values, or be Null: Yes = -1 (not 1) No = 0 Assuming the recordset you have created is pulling a value from a Yes/No field, and...

Using MS Access: Checkboxes with Insert statement, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Well, let me see your code. :) Just refer to the values of the checkboxes as TRUE and FALSE or -1 and 0. Other than that, I don t know without seeing what you ve got. Let me know if you have any other questions or comments. Also, be sure to visit my...

Using MS Access: Combo Boxes, combo boxes, access tutorial
combo boxes, access tutorial, combo box: You want to take items from a TABLE and only list certain items in your combo box? Well, you d need some way to select those items. I d recommend a CHECKBOX (yes/no field) in the table where you can select the items you want, then have those items limited...

Using MS Access: Combo box, query builder, combo box
query builder, combo box, elipses: The best way I ve found to get the syntax right is use the query builder. When the form is open, open up the query builder, and in a blank Criteria section, click the build button. (The elipses at the right of the field.) Navigate to the object...

Using MS Access: Combo boxes, combo boxes, control names
combo boxes, control names, combo box: What you have to do is assign as the Rowsource of the combo a SQL statement that will return the records you want. As long as you properly address control names it should work. If you want to display the value when only one record is returned, you have...

Using MS Access: ComboBox Run-time error '3077' with quotation marked records, run time error, setting up a database
run time error, setting up a database, single quotation marks: I ve never encountered this error before, and without setting up a database to test it, I really have no ideas. If it s a problem, why not set up a QUERY to just remove the quotes from your names BEFORE pulling them into the combo box: NewName: Replace(OldName,...

Using MS Access: Comparing Dates, comparing dates, example address
comparing dates, example address, stamp value: So you ve got two or more Member records with basically the same information (or maybe an address or phone number change) but a new DateStamp value. If you want to look up the latest member information (say, in another form or report) you could use the...

Using MS Access: Conditional Statement, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: I would go with an event that runs just when the user tries to exit the record/form. Probably the BeforeInsert event would be best. Just check the value of both fields: If Combo= Other AND IsNull(Explain) Then MsgBox You must explain Cancel=TRUE...

Using MS Access: Constantly updating data, pool repairs, tips access
pool repairs, tips access, customer table: Garrick, it sounds like you re on the right track. You ve got an ID set up that links the two together. Here s a tutorial that will show you all about setting up a relational database: http://www.599cd.com/tips/access/relational-tables/ Now it s just...

Using MS Access: Copying data from one form to another, trade description, 599cd
trade description, 599cd, tips access: If the code is running on your selection form, then ANY references to fields in your code are going to point to the selection form. If you need to set a value on a DIFFERENT form, you need to refer to it as: Forms!FormName!FieldName For example, you...

Using MS Access: Copying from Excel, oapp, best guess
oapp, best guess, paste errors: Check the Error type in your paste errors table... that should give you some clue to what s happening. Is there some text added with the positive numbers? Like a plus sign + ? Maybe there is a space you are inadvertantly copying over? (like 35 instead...

Using MS Access: Count number of updates on a record, initial idea, loan applications
initial idea, loan applications, video tutorials: Imran, This depends on how you are undating the re-received field. That is the point in time when the number would have to be increased. If on a form it you just be [count field] = [count field] +1. Bob Heifler MyAccessProgram.com Find example files...

Using MS Access: How to Count Y/N in a Footer, footer section, boolean field
footer section, boolean field, false hope: The first argument in the Dcount is the fieldname. Since Dcount is used to count records, entering a fieldname is superfluous. So you use the * to stand for all fields. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access 2007...

Using MS Access: Counting Y/N in a Report, footer section, detail section
footer section, detail section, video tutorials: Beverly, In the detail section of the report, create an unbound textbox called Y_Count If the field that contained Y was called MyTextField you would place this in the Y_Count field: =iif([MyTextField]= Y ,1,0) In the footer of the report, place...

Using MS Access: Creating Formulas in a Query, seed variety, field id
seed variety, field id, seed type: Use an IIF function in your query. See this tutorial: http://599cd.com/tips/access/iif-function I hope this helps you. Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training ...

Using MS Access: Creating multiple records, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, access dlookup: There are two ways you could do this. One would be to use RECORDSETS to programatically loop through TABLEA and add a field to TABLEB. This is the best solution, but it involves a lot more programming (and is more detail than I can go into here). The second...

Using MS Access: Creating a survey in MS Access, employee exit interview, creating a survey
employee exit interview, creating a survey, column count: If the questions are going to be the same for every interview(Employee) then you can simply make one table that has a field for each question, and for those that are not open form questions - meaning the person is answering in their own words, then you can...

Using MS Access: Creating a Unique Value Check across Multiple Fields, key icon, null value
key icon, null value, microsoft access: Alan, Go to the table s design view, select the 3 fields, then use the yellow key icon button at the top to select that multifield key. Any field you choose may not ever contain a null value, that is be empty. Also, you may not already have duplicate data...

Using MS Access: Update Statement, microsoft access, sql statement
microsoft access, sql statement, first response: You aren t listening to me. There is NO reason to have this as a field in your table. Why do you think you do? If you want to display the value, you can use the formula [Bins]*[Each] whenever you need to display this value as I indicated in my first response....

Using MS Access: calendar within a combo box, date picker, combo box
date picker, combo box, comn: You don t do it in a combobox. Access includes a date picker ActiveX control. You can use that, though I don t recommend it. if you check the Code Archive at utteraccess.comn (you ll have to join but its free), there is a sample file with a few different date...

Using MS Access: carry forward to next record, microsoft access, vba
microsoft access, vba, microsoft office: Access is a database, not a spreadsheet. Your problem is the strucutre of your table is not correct. Opening balance and closing balance are CALCULATED values and should not be stored. You calaculate them by adding the incoming transactions and subtracting...

Using MS Access: cbo box to subform + add records, forest for the trees, access tutorial
forest for the trees, access tutorial, combo box: I m glad you got it to work. You re right - half the time the problem I have with helping people is trying to get a clear picture in my mind of WHAT you re trying to accomplish. I m still a little foggy on the details, but since you re up and running, it s...

Using MS Access: change filename, filesystemobject, string functions
filesystemobject, string functions, getfile: Alan, hi Use the FileSystemObject: Look this up in onboard help, but here is a snippet base on a dump from the Name Property. This should do the trick Kind regards Geoff :-) Sub RenameFile(filespec) Dim fs, f, s Set fs = CreateObject(...

Using MS Access: changing date in a form, database option, question thanks
database option, question thanks, whic: when you say that pressing the F9 didn t work does that mean it didn t highlight the line of code red, or that when you tried to modify the record, it did not stop on that line? If the first - the code was highlighted red, and you went to modify the record....

Using MS Access: changing delimiter, field thanks, opentextfile
field thanks, opentextfile, filesystemobject: Hi Alan, I have lifted this off the Net - so if you can get it to work, brill: if not well, I tried.... Use the FileSystemObject Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject( Scripting.FileSystemObject ) Set objFile...

Using MS Access: checkbox()
I am just guessing here, but I am thinking you are seeing an issue because you don t have the ELSE clause filled in. When you are doing something like this and you have an IF statement performing some action, then you do not to specify which action to take...

Using MS Access: checkbox for each row in a tabular form, d store, access update
d store, access update, doctor information: It sounds like you have an UNBOUND checkbox in your form - which is why ALL of them are checking on/off when clicked. You ll need to make it a BOUND checkbox that s tied to a field in your table, so you can individually select records. Then, when the user...

Using MS Access: checkboxes, one for each row in a list, d store, doctor information
d store, doctor information, patient doctor: What you have found out is that a continuous form is really one form dispplayed multiple times. So if you have an unbound checkbox, checking one of them checks all of them. This gives you two options around this. The first is what you are apparently trying....

Using MS Access: combo box, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, string concatenation: Use STRING CONCATENATION in a query to build your single field, like this: FullName: FirstName & & LastName See this tutorial: http://599cd.com/tips/access/string-concatenation Then base your combo box on that query instead of the table. ...

Using MS Access: combo box in access query criteria, combo boxes, query criteria
combo boxes, query criteria, spelling mistake: Yes, What you need is to run the query from a form. Use an unbound form. This works because you can reference a value on an open form using the syntax: Forms!formname!controlname Just set that as the criteria in your query. Hope this helps, Scott...

Using MS Access: combo box to disable combo boxes, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, combo boxes: How about: Private Sub CheckBox1_AfterUpdate() If Me.CheckBox1 = True Then ComboBox1.enabled = False ComboBox2.enabled = False Else ComboBox1.enabled = True ComboBox2.enabled = True End If Let me know if you have any other...

Using MS Access: compare two access db tables, table structure, microsoft access
table structure, microsoft access, vba: You can do an unmatched query to add the new records. You can use a duplicates query to determine the duplicates then use that to determine the changed records. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access 2007 VB...

Using MS Access: compare two huge tables and store in new table, delta table, csv file
delta table, csv file, performance issue: 100k+ rows should not be a performance issue. If you think that would be a performance hit, if you have more than 10 fields to compare you can t use a query. You will need to loop through each record and check for changes and then write out the record...

Using MS Access: compare tow access db tables, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, query wizard: You can use the FIND DUPLICATES QUERY WIZARD to find any data that is the same between your two tables. The rest of the records are either new or updated. I cover this in detail in my Access 223 course: http://599cd.com/site/courselist/access/access223...

Using MS Access: comparing data between two table in MS Access, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Create a query with both tables in it, linked by whatever ID field you have. Then, set your criteria for Field2 equal to: Field1 That should show any records that are different. Let me know if you have any other questions or comments. Also,...

Using MS Access: compound conditional expressions, vba module, conditional expressions
vba module, conditional expressions, nlcc: you don t specify how you are doing this (in query, or VBA, etc.) but I am thinking what you are wanting to do might be best done in writing a VBA module and passing a return value of type based on the parameters sent in. Btw, I try to stay away from...

Using MS Access: concatanate fields/Complex If Statement, software contract, contract software
software contract, contract software, software maintenance: Iif(AND(([hardwarepurchase]+[hardwaremaintenance] 0),([softwarepurchase]+[softwaremaintenance] 0)), Hardware Contract & Software Contract , Iif([hardwarepurchase]+[hardwaremaintenance] 0, Hardware Contract , Iif([softwarepurchase]+[softwaremaintenance] 0,...

Using MS Access: concatenated key in Access, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, issue checks: Just go into the GLOBAL RELATIONSHIPS setup and create the join and referential integrity settings in there. If you don t know how to do that, see my Access 220 course: http://www.599cd.com?GOAC220 Let me know if you have any other questions or comments....

Using MS Access: connect fields, problem category, combo box
problem category, combo box, microsoft access: Sounds like you wan what is called cascading or synchronized combos. This is a standard technique and the process is detailed here: http://support.microsoft.com/kb/209576/en-us Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office Access...

Using MS Access: Can I make just ONE control on a form editable?, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Just set all of the controls on the form that you don t want them messing with so their ENABLED property is FALSE. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute ACCESS TUTORIAL....

Using MS Access: convert decimal latitude and longitude into degrees/minutes/seconds, latitude and longitude, math error
latitude and longitude, math error, tips access: Well, if I understand converting from decimal to degress, you take the fractional component (in the case of 47.6913 it would be 0.6913) and multiply it by 60 to get minutes, which would be 41.478. Now take that fraction and multiply by 60... 0.478 x 60 =...

Using MS Access: How to create a form with an insert record function button., blank rows, blank row
blank rows, blank row, compact data: Rami, Hi To inser a new record for data entry, either use the * button in the form navigational control (lower left of form), or place a command button on the form. To do this:- Open the form in design mode Display the Toolbox - ensure the toolbox...

Using MS Access: How to create a form with an insert record function button, microsoft access 2003, interface
microsoft access 2003, interface, microsoft: I m not sure exactly what you are wanting to do. All records are automaticaly added at the end of a table, however you can sort the data in any order that you desire through a query. Are you wanting to duplicate certain entries in certain fields for a...

Using MS Access: DAP opens a secondary window in internet, internet question, target
internet question, target, familiarity: If you have anywhere in your DAP that contains the following A HREF= somewebadress Link Name /A then you can place it like so A HREF= somewebadress TARGET= _New Link Name /A If you don t see anywhere where there s an A HREF... then it...

Using MS Access: Data Access Page editable query, data access page, lastname
data access page, lastname, dap: Janell, hi, There is a simple rule... If you want to edit/add/delete data you need to access the table directly, or a view of selected columns from only that table My preference is to command a procedure to make the change, however this is not a method...

Using MS Access: Data Entry for Joined Tables, glossary table, glossary database
glossary table, glossary database, combo boxes: You ve got a basic MANY-TO-MANY relationship going on here with a JUNCTION TABLE (or cross-reference table). I cover this concept in my Access 309 course: http://www.599cd.com?GOAC309 I have NO IDEA how to set this up in OpenOffice Base because I ve never...

Using MS Access: Data Entry web page, data access page, drop down boxes
data access page, drop down boxes, page thanks: Add the DISTINCT keyword to the SQL query in the recordsource: SELECT DISTINCT fieldlist, FROM table I m assuming this needs to be on the WEB. If not, there are other alternatives. Hope this helps, Scott Microsoft Access MVP 2007 Author: Microsoft...

Using MS Access: Database Creation, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, custom databases: Anthony, yes I do build custom databases on request, however I don t actively advertise my services because there are SO MANY other developers out there - and a good number of them are college students who work dirt cheap... so I focus mainly on training,...

Using MS Access: Date calculations, stock dates, 599cd
stock dates, 599cd, allexperts: Rachel, see this tutorial: http://599cd.com/tips/access/difference-in-dates-times Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute ACCESS TUTORIAL. Go to http://www.599cd.com/Access/AllExperts...

Using MS Access: Date picker, data picker, date picker
data picker, date picker, code archives: I don t like using the ActiveX control data picker. I prefer using form based ones. If you go to utteraccess.com and look in the Code Archives, you will find a smaple Access database with examples of form based date and time pickers. Hope this helps, Scott...

Using MS Access: Date or Time Autofill, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Sounds like you just need to learn how to program an AfterUpdate event with a DLOOKUP function. See these tutorials: http://599cd.com/tips/access/afterupdate-event-vba http://599cd.com/tips/access/dlookup-function Enjoy. Let me know if you have...

Using MS Access: DCount I Think...?, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, mrp systems: Sure... sounds like DCOUNT is the perfect function to use. Of course, without seeing your database, it would be hard for me to tell you EXACTLY what to do. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Using MS Access: Disabled Fields In Forms, microsoft access examples, ms access databases
microsoft access examples, ms access databases, microsoft access database: You can use the On Current event to run your enable/disable code. This means that every time you change records, it will re-evaluate and change the Enabled property. it will still do this for all controls, but it will change whenever you change the record....

Using MS Access: Display count results in textbox according to selected item in combo box, strsql, combo box
strsql, combo box, microsoft access: This is the way I would do it. I would set up a table like this: tblCounts CountID (PK Autonumber) ChosenValue ValueCount In the After Update event of the combo I would run code like this: dim strSQL As String If DCount( * , tblCounts , [ChosenValue]...

Using MS Access: Display count results in textbox looking at selected item in combo box, combo box, data source
combo box, data source: IŽll try writing a code like thhis: if (control in ComboBox ) = Professor then Counter = Counter +1 end if put the code in the on update property of the combo box, opening the form in design view and opening the properties of the combo box and create...

Using MS Access: Display count results in textbox according to selected item in combo box, access tutorial, soultion
access tutorial, soultion, president ceo: I think I remember this question (I get a lot of them), but I don t remember what solution I gave you. You should be able to just use an AfterUpdate to increment your counter - which you can make a bound field to a table. Cordially, Richard Rost ...

Using MS Access: Display count results in textbox according to selected item in combo box, query design, combo box
query design, combo box, combobox: Hi, Many thanks for the question. Not sure if I am missing the point of your question, but are not the results from the combobox stored in a table? If yes, then you can easily count them. Build a query choosing design view, choose the table that...

Using MS Access: Displaying months and counting records, dateadd, aggregate function
dateadd, aggregate function, date function: You can use the Month() and Year() or the DatePart() function to extract specific elements from a date value. I m not clear what you mean by going back 18 months. If you wanted a query to return the current month s records you would add two columns to...

Using MS Access: Dynamically generate text in a text box, paint ball markers, lastname firstname
paint ball markers, lastname firstname, hidden values: Your first problem can be solved with a little DLOOKUP function: http://599cd.com/tips/access/dlookup-function Your second problem is actually a little more complex. Have you have tried linking both sets of data together with a form/subform relationship?...

Using MS Access: data automatically appear in field on form basaed on criteria, microsoft access, vba
microsoft access, vba, ms office: 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. To do what you want is easy. In the After Update event of the Status control use3 the code builder...

Using MS Access: data entry through vba in subform, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, record menu: [1] You can create a loop and use the DoCmd.GoToRecord command to move to a new record and insert your data in the subform. [2] I m afraid I don t understand this part. Let me know if you have any other questions or comments. Also, be sure to visit...

Using MS Access: date probelem with sql satement in ms access, access tutorial, president ceo
access tutorial, president ceo, 599cd: I would first set up another query and make sure all of your dates are formatted the same: NewDate: Format(OldDate, mm/dd/yyyy ) Then insert that value. Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web site...

Using MS Access: dates in msacces, ddate, yyyy
ddate, yyyy, vba: Hi, Many thanks for the question. I often have this problem as I live in Australia. VBA handles all dates in US format. It knows to put them the right way for the ones that are obviously wrong but as you said dates like 08/04/2008, VBA does not know...

Using MS Access: Eliminating blank FIELDS using querys, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, blank cells: OK, I understand now. Your best bet is to make a form with ALL of the fields on it, and then just DISABLE or HIDE the fields if the value is blank. For example, in the OnCurrent event, you can put this code for each field and it s associated label: If...

Using MS Access: Email Report to Multiple Recipients, free microsoft access, command button
free microsoft access, command button, tips access: Basically what you want to do is set up a FORM that isn t bound to any table, but has a text box and a command button on it. In the text box you enter in a date, then hit the command button to generate your report. Now, this would be SUPER easy to do if you...

Using MS Access: Employee Database, datediff, access tutorial
datediff, access tutorial, employee database: The function in Access is IIF not IF. Secondly, why are you taking the difference in days and then dividing by 365? Why not just take the difference in YEARS? Cordially, Richard Rost President/CEO 599CD Computer Training P.S. Visit my web...

Using MS Access: Exporting to Txt and losing leading zeros, ms access database, export wizard
ms access database, export wizard, wrapper function: Hi Tina, Try the following: = Right( 000000000 & CStr(CLng([MyNum])),9) & Left(CStr(CDble([MyNum]) Mod 1) & 000000 ,6) Otherwise, look at your data types for the import data. Your work around is fine whilst you have 2 dec places, what happens...

Using MS Access: expression to only show first 2 numbers, control source, video tutorials
control source, video tutorials, expression: Jean, Use a text box control on your form: If the table field name was [MyField] then the code would be: =left(2,[MyField]) Just make the Control Source property of the unbound text box control that you place on the form equal to the above code. Bob...

Using MS Access: Field box automatically adding other fields together..., score field, max score
score field, max score, control source: Aaron Hi, Lets assume your 4 text boxes with values are called:- txtValue1 txtValue2 txtValue3 txtValue4 And lets say you want the total of these four text boxes in another textbox called:- txtTotalValue In the Properties dialog for the textbox,...

Using MS Access: File Transfer Message, file transfer, msgbox function
file transfer, msgbox function: Hi David, Just wrap the FT into a macro or VBA procedure, and follow the FT command with a MsgBox() function call. EG Public Sub TransferMy File() DoCmd.TransferText....... MsgBox( File transfer complete ) End Sub Kind regards ...

Using MS Access: Filter Form Button on Switchboard, combo box, switchboard
combo box, switchboard, command button: Hi Angel, Yes. Use the Wizard to generate the code for the button to open up your required form: open this up in the code viewer: it will look like this, but with your form details Private Sub Command1_Click() On Error GoTo Err_Command1_Click ...

Using MS Access: Filter form by combo box values, option group, combo box
option group, combo box, switchboard: Here is my recommendation. First, when you say Combo box, are you referring to a drop-down box, or are you using an Option group . An option group allows you to bind a field to a list of values (in this case it would be true/false). You can use multiple...

Using MS Access: Filter subform based on 2 criteria, storage location, sql statement
storage location, sql statement, database setup: I don t see any reason not to use a Popup form. but if you want to use subforms, then in the After Update event of each combo, set the Recordsource of the subform to a filtered query. Hope this helps, Scott Microsoft Access MVP 2007 Author: MS Office...

Using MS Access: Filtering records based on birthday, dateofbirth, dateadd
dateofbirth, dateadd, datepart: Add a column to your query: CurrYrDob: DateSerial(Year(Date()),Month([DateOfBirth]),Day([DateOfBirth])) Set the criteria for that column to: BETWEEN DateAdd( d ,15,Date()) AND DateAdd( d ,60,Date()) Hope this helps, Scott Microsoft Access MVP...

Using MS Access: Filtering records in a Report, filter property, filter criteria
filter property, filter criteria, header section: No, unfortunately you can t use a textbox/combobox or any other control in a report as a filter. The reports are meant to be run first, then the header/detail/footer is displayed. The only possibly ways to filter your report is use these controls on a form...

Using MS Access: Form, e mail, text boxes
e mail, text boxes, dateadd: I would have to be able to test the app to see why it may not be working. However, I have an app that does almost exactly what you are looking for. If you send me an e-mail address, I ll send you a copy. Hope this helps, Scott Microsoft Access MVP 2007...

Using MS Access: Form Based Parameter Query, parameter query, many thanks
parameter query, many thanks, brackets: Hi Derek, Many thanks for the question. Have you tried putting in your own brackets i.e. same as for the criteria in the query. Although have you tried running the query without the parameter? Just the criteria should be enough. Parameters are...

Using MS Access: Forms, unbound controls, time slots
unbound controls, time slots, requery: I suspect you are using the On Open Event to populate the unbound controls (I also suspected you were using unbound controls). So your problem is that the code you are using to populate the controls isn t running when you add or edit a record in the setup...

Using MS Access: Forms and recordsets, time slots, access tutorial
time slots, access tutorial, president ceo: You probably just need to requery the form. Put the following line in the OnClose event for frmAppointment: frmTimeSlots.REQUERY This should refresh the records for you. Damn I m good. :) Cordially, Richard Rost President/CEO 599CD...

Using MS Access: CheckBox ComboBox Extending the ComboBox Class and Its Items, design view
Using MS Access: CheckBox ComboBox Extending the ComboBox Class and Its Items, design view

Using MS Access: ComboBox Run-time error '3077' with quotation marked records, double quotation marks, quotation mark
Using MS Access: ComboBox Run-time error '3077' with quotation marked records, double quotation marks, quotation mark, single quote

Using MS Access: carry forward to next record
Using MS Access: carry forward to next record

Using MS Access: Date picker, date picker, control source
Using MS Access: Date picker, date picker, control source, datepicker

Using MS Access: Exporting to Txt and losing leading zeros, export wizard, zeros
Using MS Access: Exporting to Txt and losing leading zeros, export wizard, zeros

Using MS Access: Nested iif (ms access), gross weight, sake
Using MS Access: Nested iif (ms access), gross weight, sake, logic

Using MS Access: Printing one Record, record operations, command button
Using MS Access: Printing one Record, record operations, command button, wizard

Using MS Access: getting feilds to auto fill based on first field selection, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, relevent fields: Lee, sounds like you need a combo box with an AfterUpdate event. See this tutorial: http://599cd.com/tips/access/afterupdate-event-vba Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute...

Using MS Access: foreign key, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: Sergey, sounds like you just need a simple AfterUpdate event for your combo box. See: http://599cd.com/tips/access/afterupdate-event-vba Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute...

Using MS Access: Grouping a sorted query, stock code, faster computer
stock code, faster computer, tips access: You re correct in the method that you re using. Two queries like you mentioed is perfect... and probably the fastest method. You re either going to (a) need a faster computer, or (b) a faster network connection if over a LAN. You could use DLOOKUP to try...

Using MS Access: Groups on a report, intage, global module
intage, global module, member number: Yes, I ve done this several times. But you need to do it with a custom function. Open a global module and enter a function something like this: Public Function AgeGroups(dteDOB As Date) As String Dim intAge As Integer intAge=DateDiff( yyyy , [dteDOB],...

Using MS Access: generating a random number with variable range, upperbound, primary key
upperbound, primary key, random number: Hi AG, Many thanks for the question. Try the following: RandomNumber: Int(DCount( LBHID , LBH )*Rnd()) Hope this is of some help. I have presumed you are doing this in a query. If it is on a form or report then ignore the RandomNumber: bit...

Using MS Access: gotorecord, access tutorial, existing records
access tutorial, existing records, president ceo: That command doesn t clear anything... what it does is MOVE to a new record. If that s your parent/main form, then it looks like the data was cleared - but you just moved to a blank new record. If you issue that command while in the SUBFORM you re just...

Using MS Access: Highlighting rows (with different colors), microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, ms access database: You could not do this with a standard XLS export from Access. You would have to use Automation VBA programming to control Excel from Access. YES, it can be done... but it would take far too long for me to explain here. Sorry. I m sure if you Google it, you...

Using MS Access: highlightling records / forms / listboxes, datasheet view, access forms
datasheet view, access forms, microsoft access: This may help: http://www.mvps.org/access/forms/frm0024.htm Hope this helps, Scott Microsoft Access MVP 2007Author: MS Office Access 2007 VBA The problem here is that continuous forms are really a single form displayed multiple times. So anything...

Using MS Access: hyperlink, mdb, microsoft access
mdb, microsoft access, support microsoft: This article: http://support.microsoft.com/kb/829072/en-us Will tell you how to suppress that message. What type of network do you have? Are you sure all PCs are mapped the same way? Also, it appears you are running the MDB from the network. Is the...

Using MS Access: Imported Data, string loop, string x
string loop, string x, list of names: Why would a list of names have leading zeros? But yes you could do this using a custom function to add a leading zero to each name until you reach the length you need. You would use a Do While loop. Something like: DO While Len(string) x string...

Using MS Access: Importing Caller ID, taxi dispatch, dispatch program
taxi dispatch, dispatch program, voice modem: This is a function of your phone system. You have to talk with the people who provide your phone service. If you use a regular dialup service you have to use a voice modem and answer the calls through a computer to be able to do this. If you use a VoIP service,...

Using MS Access: Increment numbers, increment numbers, microsoft access
increment numbers, microsoft access, ms office: Well that depends on you. Do you need for the user to see this number right away, or can it wait until all the data is entered? As I said, you need to save this right away. So your workflow will dictate. Unless the user needs to see this number right away,...

Using MS Access: Invoice number issue, invoice data, issue question
invoice data, issue question, finance application: Simply format the Excel column for Date format. When you inport that value into a date field, it will work with your Access formatting. I don t understand why you would want to do the conversion in Excel. That will cause problems when you convert to Access....

Using MS Access: inserting a character in an existing data field entry, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, mail field: Well, you could just use a CALCULATED QUERY FIELD to do this: MyName: FirstName & . & LastName & @domain.com See this tutorial: http://599cd.com/tips/access/calculated-query-fields Or if you want to make it permanent you could use an UPDATE...

Using MS Access: Limit number of records in form (Windows XP 2002), microsoft access, vba
microsoft access, vba, ms office: In the Before Insert event run code like: If Dcount( * , tablename ) 100 Then Me.AllowAdditions = False MsgBox the limit for records in this table has been reached ,vbOKOnly Me.Undo End if Hope this helps, Scott Microsoft Access MVP 2007 ...

Using MS Access: Limiting access to fields, dlookup, current event
dlookup, current event, programming experience: First you need a way of identifying the users. You would then have a table that listed each user and assigned them an Access Level. In the On Current event of your form, you would check the ID of the user against the table and retrieve their Access level...

Using MS Access: Link a table to a text box, input control, carriage return
input control, carriage return, several ways: You will have to be able to signify to your application that a new record is needed. This can be done in several ways, but it is going t depend on what you are capable of doing. For example, the simplest way is to use some sort of terminator signal on the...

Using MS Access: Linked .csv File Locking Problem, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, advanced vba: Wow... interesting question. I m not sure. I would think that Access is only performing RECORD-level locking of the files - so if I m modifying Joe Smith, you can t modify Joe Smith. That s why the fact that it s a read-only directory doesn t matter... Access...

Using MS Access: Login Form, separate tables, vendor id
separate tables, vendor id, microsoft access: With the current setup, you need to store the login ID somewhere and use it as a filter for the queries the forms are bound to. This way the user only sees records with their vendorID. In the future, you can use a cookie to store the vendor ID and use...

Using MS Access: Login and Password field data Validation, data validation, password text
data validation, password text, tips access: Sure. First, watch this tutorial on how to use the DLOOKUP function to look up a value from a table (in this case, once you know the ID of the employee, you can look up their password): http://599cd.com/tips/access/dlookup-function Now, have them enter...

Using MS Access: link msword files with access prog, hyperlink field, video tutorials
hyperlink field, video tutorials: Make a hyperlink field and use a follow method to have the user follow the address of it. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/Download.html Find video tutorials at: http://www.myaccessprogram.com/Tutorial.html...

Using MS Access: login form with access rights check, isnull, combo box
isnull, combo box, start 1: Hi VTG, I ll start 1/2 way down... Check value of password in tblEmployees to see if this matches value chosen in combo box If Not Me.txtPassword = DLookup( Password , tblEmployee , [EmployeeID]= & Me.txtUserName) Then Exit Sub End If ...

Using MS Access: M.S. Access Website, microsoft access tips, customer management database
microsoft access tips, customer management database, free microsoft access: Dan, you could try doing a Google search for access templates and see what you find. I have my own sample template that I let people download. You can find it here: http://599cd.com/landing/?SRC=AllExperts&CAM=Access&ADG=Template It s a basic customer...

Using MS Access: Maint program design, design question, many tables
design question, many tables, microsoft access: There is NO way for anyopne to answer that. Each application is different. Designing the structure of a database is not very different from platform to platform. What I do is make a list of all the data I want to capture in the application. I then organize...

Using MS Access: Manipulating dates, case situation, 599cd
case situation, 599cd, allexperts: You re going to have to approach each of these on a case-by-case situation, and it s probably going to involve some programming with loops. Microsoft Outlook handles dates like this. For example, to find the 2nd Monday of January 2008, you d have to set...

Using MS Access: Microsoft Access - Parameter Query, parameter query, valid dates
parameter query, valid dates, time values: Is the field a date/time datatype? If so does it only contain a date or does it also contain a time? If the field contains time values, that would explain it. To fix that, add a column to your query: DateOnly: DateSerial(Year(datefield), Month(datefield),...

Using MS Access: Move Text Data To Next Empty Row In Column, record operations, move text
record operations, move text, table column: Hi Birol, On your form you need a button to save the record: the easiest way is to open the form in design mode, ensure the toolbox is open and the wizard is active. Now select a button and place it on your form. The wizard will launch, showing various...

Using MS Access: Move Text Data To Next Empty Row In Column, barcode scanner, move text
barcode scanner, move text, type script: There could be multiple factors to this, because I have done some investigating into barcode scanning and I know they can be a bit tricky. One possibility is that the scanner is only sending the last bit of data that it collects. I doubt that this is it,...

Using MS Access: MS Access 2003 - Form Design, drop down menus, first selection
drop down menus, first selection, microsoft access: This is a standard technique called Cascading or synchronized combos. First your tables have to be properly normalized. Then this article: http://support.microsoft.com/kb/209576/en-us details how to do it. Hope this helps, Scott Microsoft Access...

Using MS Access: MS Access 2007, invalid property assignment, wrong number of arguments or invalid property assignment
invalid property assignment, wrong number of arguments or invalid property assignment, vba module: Nick, Try msgbox test No parenthesis. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html Find video tutorials at: http://www.myaccessprogram.com/tutorial.html Contact: http://www.myaccessprogram.com/contact.htm...

Using MS Access: MS-Access, hyperlink address, navigator window
hyperlink address, navigator window, mdb files: try with an hyperlink open the form from where you want to call the other database in design view select an object like a button, a picture, a textbox, a label and open its properties in the Hyperlink Address row, write the path to the second database...

Using MS Access: MS-Access, hi bob, video tutorials
hi bob, video tutorials, ms access: Sachin, Create a user table and an login form. The table could store user preferences metadata and passwords as well. Bob Heifler MyAccessProgram.com Find example files at: http://www.myaccessprogram.com/download.html Find video tutorials at:...

Using MS Access: MS Access, public variables, query parameters
public variables, query parameters, combo boxes: Idris, Using the after update event on the first combobox you will have to reset the record source of the 2nd combobox. The second combobox should have as it s record source a query that uses specific criteria that you want to filter the list on. This...

Using MS Access: MS Access, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, 599cd: I don t know of any way without serious programming, no. Why would you need to? Access will just rebuild any indexes you need based on your current tables. Let me know if you have any other questions or comments. Also, be sure to visit my web site and...

Using MS Access: MS Access - Automactically Updating, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, table queries: Well, you could use a MAKE-TABLE QUERY to simply make an automated copy of the entire table, say, once a day (or whenever you click the BACKUP button). Make-Table queries are very similar to UPDATE queries. Here s a tutorial that should help you: http://599cd.com/tips/access/update-query...

Using MS Access: MS Access and concurrency, compact repair, vb6
compact repair, vb6, access database: You might want to consider making the Front End unbound. This would open the connection, perform an action (load, edit, etc.) then close it upon completion. As for the hanging problem, there probably isn t anything that can be narrowed down to, if it is...

Using MS Access: MS Access DLookup Can it work with different tables and all of them in a single query command?, dictyionary, query command
dictyionary, query command, hts: The reason for the error is because MotherED is not just blank, its Null. A Null is different from a blank So its never going through the Case statement. you can get aroundf that in a couple of ways. What I would use is this: Description: MyLabel(Nz([MotherED],...

Using MS Access: MS Access Expressions (Advanced), microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, query expression: Sure. Just use Forms!Formname!Fieldname notation. See if this tutorial answers your question: http://599cd.com/tips/access/forms-formname-field-notation Let me know if you have any other questions or comments. Also, be sure to visit my web site and...

Using MS Access: MS Access Forms - Auto Fill, microsoft access tips, free microsoft access
microsoft access tips, free microsoft access, ms access forms: Sounds like you need the DLOOKUP function: http://599cd.com/tips/access/dlookup-function Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute ACCESS TUTORIAL. Go to http://www.599cd.com/Access/AllExperts...

Using MS Access: MS Access - Limit to no of fields, google search, access file
google search, access file, rdbms: 10,000 columns?!?!?!?! Access has a limitation of 256 fields in a table.... no exceptions. I m guessing, if you organize your data into a normalized form, you wouldn t need a table with more than a few fields, but would need several tables. You need...

Using MS Access: MS Access link 2 mdb files, mdb files, supplier details
mdb files, supplier details, mdb file: Hi Sher, Many thanks for the question. The easiest way to do this, it to add a hyperlink to your form (should be on the toolbar, it has a picture of a world and a chain), when you place this on the form, a wizard window will open. Choose existing...

Using MS Access: MS Access Query, access query, john doe
access query, john doe, thanks john: the straight up code for it will be Dim rsRecord as Recordset set rsRecord = currentdb.openrecordset( Select myField from myTable where myName = & someVariable or form!Control & ; ) rsRecord.movefirst Your results will be rsRecord( myField...