About Experts Sitemap - Group 54 - Page 16 2015-01-20

Using MS Access: Data Base gets big, message language, speed gain
message language, speed gain, dim rs: Fernando, bueno dias A single numeric PK will always be faster than a compound text PK, so if you can restructure your system accordingly there should be a significant speed gain for general table handling. Concerning access to specific messages, I would...

Using MS Access: Data Base Lost, microsoft access, mdb
microsoft access, mdb, mvp: You can do a search on the file name, but if its not found it got deleted somehow. Try restoring the backup. You might try searching for all mdb files created yesterday to see if a copy was created. Hope this helps, Scott Microsoft Access MVP 200...

Using MS Access: Data base for image bank, sql questions, bloat
sql questions, bloat, image bank: It is NOT recommended to store images in an Access database, this cause the database to bloat. Instead, store the path to where the image is stored, then use an unbound picutre control to display the image. You ll find a sample of this at utterangel.com. ...

Using MS Access: Data Control On Access Forms, combo boxes, column count
combo boxes, column count, text boxes: What you want to do is easy if done right. First, to clear up some things. The only thing you need from the Area table is the AreaCode. This is your foreign key. Any other info from the Area table should be for display only. There are a few ways to do this....

Using MS Access: Data Correction, query option, tabels
query option, tabels, access data: Mo, hello, 1. I would sort the offending Date columns (both Ascending then Descending) in the Access DB and get a feel for the number of error dates, and determining the First and Last Dates that are ok . 2. Options are to: a. Use a query and mass...

Using MS Access: Data Correction, tabels, funny things
tabels, funny things, access data: Hello MO - funny things about dates MS ACCESS actually sees 10/25/203 as October 25 of the year 203. The first thing I would do is to determine what the oldest correctly formatted date is in your data set. For example 01/01/1989. Then build an ad-hoc query...

Using MS Access: Data Correction, tabels, text strings
tabels, text strings, access data: Is this a date field or a text field? In Access date fields are stored as a number counting the days from 12/30/1899. How that number is displayed depends on the formatting but doesn t change the stored value. For a three digit year to be displayed, the field...

Using MS Access: Data Correction, query design, key field
query design, key field, correct dates: Mo, Many thanks for the question I am presuming the field containing the dates is of date/time data type. If so, then try the following: Create a query by following the wizard, just adding the table as per your question and the date field and the...

Using MS Access: Data from Different tables in form & If Then, access vba help, dlookup
access vba help, dlookup, ciso: A nested IF is a IF inside a IF. Sometimes such are needed to deal with multiple conditions. Without knowing what your conditions are and what the structure of you app is like, I really can t give any code examples. The Access VBA Help will give you just...

Using MS Access: Data Entry Form Design in Access, access combobox, database application development
access combobox, database application development, data corruption: No, but constrained controls I refer to combo and list boxes, check boxes, options groups etc. Wherever possible I do not let users simply type, I prefer to give them a selection to choose from to ensure data validity. You can easily do a Controlled Edit....

Using MS Access: Data Entry Interface and alot more....., northwinds, foreign key
northwinds, foreign key, normalization: You said; you must mean that I in fact dont have to populate the data in the tblADJUSTMENTS, Yes, exactly. As I said its hard to be specific without knowing your data structure. Looking over your previous posts, you mention things like reinsurer, LOC...

Using MS Access: Data entry with a button, staff id, time staff
staff id, time staff, current date: Sure thing, put some code behind the Event AfterUpdate for the [Complete] checkbox on the form. The code should look like this: If Me.Complete = -1 Then Me.[Start Time] = Now() End If Mind you the stuff after Me. is the name of the object in the...

Using MS Access: Data entry form, employee time card, time fields, employee time card, input mask
employee time card, input mask, time fields: Frankly, no. Any solution involves the user typing 2 characters. Either 0 or number and : to advance to the next section. Dates work the same way. The only alternative I can see would be to use 2 unbound text boxes to enter hours and minutes. Then construct...

Using MS Access: Data entry into a query that uses a calculation, power ram, editable
power ram, editable, data entry: If you have a one to many relationship the query will not be editable. If you need a form where you have multiple editable tables, use a main form and subforms. Hope this helps, Scott Please don t forget to provide feedback for this response (either...

Using MS Access: Data format manipulation, ms access 2000, flat layout
ms access 2000, flat layout, crosstab query: Just an an autonumber field to the table and you can use that as the count. That will get around the 2 field issue. I just tested it and it works fine. As to the large # of diagnoses, that is all the more reason to not have change the table structure. You...

Using MS Access: Data import from Excel, type conversion, coloumn
type conversion, coloumn, alphanumeric character: 1. I really doubt your spreadsheet is too long to import. That s not possible with access. What are the exact parsing errors it reports? 2. Your date column can t have CHARACTERS in it but it can be blank if you want just be sure it s actually BLANK and...

Using MS Access: Data Model, attribute data, data model
attribute data, data model, pk: Virinox hi, In the table you will have a primary key (PK) which may be [StudentID], any foreign keys that are used to relate a STudent record with any other tables (like university department etc,, then the attribute data which is up to you - it would seem...

Using MS Access: Data migration, data migration, mdb file
data migration, mdb file, table structures: Have you changed the table structures ? if not, just go to a table in the existing and copy it to the new, and it will ask for a table name. This is the quickest. Or else you ll need to make a program that transfer them, this will take some time. Denni...

Using MS Access: Data in multiple tables...., three tables, ms access
three tables, ms access: Hommy, I dont fully understand the problem. Have you built a query joining the 3 tables together ? If so generally you will not be able to edit the data through the query. You will need to devise a way to edit each table separately. If you could...

Using MS Access: Data Overflow, positive integer, 8 bits
positive integer, 8 bits, arithmetic: Nagy, hello A byte is a reflection of state , not of value , and therefore as such cannot be subtracted from another byte using scalar arithmetic. Bytes happen to have the convenient appearance of a positive integer (0-255), but behind a byte is an...

Using MS Access: Data Reports, report wizard, trivial question
report wizard, trivial question, cells: If you are using the Report wizard, I m not sure why its not working. The Report wizard asks you first to select a table or query. Then asks for the Field from that table or query that you want to use in your report. Next it asks for any grouping options and...

Using MS Access: Data from Subform to Subform, redundant info, max function
redundant info, max function, overview table: Hello Bert - Lets look at the form issue first then we ll tackle the SQL MAX. Before I begin, I want to make absolute sure that I am understanding your database structure. You have a total of 23 tables, 22 for your modules and one to capture Summary data....

Using MS Access: Data not saving in table from FORMS view, lastname firstname, recordsource
lastname firstname, recordsource, interviewee: Hi Roel Assuming that you have a single people table, you will need to create a new table that records Int er PeopleID with the Int ee PeopleID - This will be the recordsource for the form, then you can pull the details of the Int er and Int ee using...

Using MS Access: Data from subforms is not getting captured in main form, position vacancies, position table
position vacancies, position table, position info: First, it is not a good idea to use SSN as a PK. SSNs can change and there are privacy issues. I would use an autonumber instead. Second, your main form should NOT be bound tbl_EmpPosInfo. You should be working it the opposite way. You can have a form bound...

Using MS Access: Data Transfer Excel to Access, microsoft access 97, microsoft access 2000
microsoft access 97, microsoft access 2000, aqe: You don t identify the sheet as part of the filename, its a separate parameter in the TransferSpreadsheet Action. Use the RANGE argument. The following is from Access Help: Range: The range of cells to import or link. Leave this argument blank to import...

Using MS Access: Data Transfer, ms sql server, mail thanks
ms sql server, mail thanks, abdur razzak: Well, you ll need a program to send the data or a web-application to transmit them. It can not be done solely from access. Depending on the type of information, I would recommend that simple data be send through a web-application, but if there are a...

Using MS Access: Data type mismatch in criteria expression, data type mismatch in criteria expression, type mismatch
data type mismatch in criteria expression, type mismatch, generic search: You have two data types on either side of = that are not compatible. If Easy_no is a number and date1 is a date then you have the source of the error. If you are trying to subtract or add number of days to a date or check the days between two dates use the...

Using MS Access: Getting to Data in an Unbound Control, abc
abc: Dear Lauren, This sound like code running somewhere that do some sort of calculation every time a record get displayed. Code can be in the code section of the spesific form, in Macro s or as a Procedure or Function in Modules. To trace this i would start...

Using MS Access: Data Validation In Access 2007, validation rules, data validation
validation rules, data validation, quotation form: This is more complex than the validation rules can handle. it does require VBA. In the After Update event of the Delivery Date control you would put code like this: If Me.txtDeliveryDate Me.txtMakingDate Then MsgBox Delivery Date must be After Making...

Using MS Access: Data Validation exercise, validation exercise, access vba
validation exercise, access vba, validation rules: Dear Rohini, Textfiles are very difficult to work with using the normal tools of Access. If you want to use queries you need to import those data into access as tables or temp tables. Validation queries, reporting on problems can then be run to give you...

Using MS Access: Database, microsoft access database, excell format
microsoft access database, excell format, microsoft excell: Assuming the files are all in the same format, Import the first file which will create an Access table. Then Import each additional file, specifiying the original as the target of the import. Each file will then be appended to the table. Hope this helps,...

Using MS Access: Database, ayman, access 2000
ayman, access 2000, database access: Hi Ayman, If the database is owned by you, you can always delete or edit the existing relationship, even if the existing relationship is created by default. If you want to delete the existing one and create a new one, then use the Tools/Relationship... menu...

Using MS Access: Database, record source, onclick event
record source, onclick event, msaccess: OK, Assuming you have a form the user is using to enter the data into, and that forms record source is bound to the table, I would put a Save button on that form, call it cmdSave, and put the code below in the OnClick event of the button. This will check...

Using MS Access: Database in Access/VB Question, database in access, david jeffrey
database in access, david jeffrey, text boxes: Hi I would use VBA in Access for this. The following looks up sometable for somefield based on the value of the text box txtSearch and places the return value in the text box txtResults. All the code refers to the current form as me....

Using MS Access: Database for assigning students to classes, ms access 2000, table assignments
ms access 2000, table assignments, spanish version: What you need is a join table, assignments. The assignments table will have 3 fields; AssignmentID (Autonumber, PK) StudentID (FK) GroupID (FK) You would create a subform, linked to the main from on the appropriate ID field (if main form has students,...

Using MS Access: Database Capabilites, vba code, tiff file
vba code, tiff file, archive name: Well first I would change the structure of your database. If a bldg has multiple designations I would have a table like: tblBuildingNames BuildingNamesID (PK autonumber) BuildingID (FK) BuildingNameType (FK) BuildingName The BuildingNameType would...

Using MS Access: Database creation by VBA, openrecordset, archive project
openrecordset, archive project, database creation: Kathi, sorry, but this isn t something I ve done before...I d have to do some tweaking myself. SQL isn t my strong suit. Look through the profiles of some of the other volunteers and you ll see some who claim SQL as one of their strengths. Good luck,...

Using MS Access: Database Design, publication info, minimal changes
publication info, minimal changes, permutations: Hi Cindy, From your brief description, I think you will need a few more tables to build a 3rd Normal form Database, so here goes: 1. tblEmployee - only for employee info = employee#, name, address etc. 2. tblJob - only jobs info = job#, job title,...

Using MS Access: Database Design, client server architecture, java jframe
client server architecture, java jframe, budget comparisons: Normally, I don t do homework questions. But the way you have posed the question, basically asking only to confirm whether on the right track, is the type of question I will answer. 1-If you are asking from an Access standpoint, then yes. Since your frontend...

Using MS Access: Database Design, query filter, recordsource
query filter, recordsource, access databases: Base your forms and reports on a query. Filter that query for the current location. There are a number of ways to do that. If you just need to filter the location for anyone who uses the app, have a local table in the front end That contains the Location ID....

Using MS Access: Database Design Dilemna: Calculated Field, control source, database table
control source, database table, short answer: Hi Rebecca: The short answer is no, you cannot store a calculated field in an Access table. The proper way of doing this is to do your calculation as needed (that is, in the form or report where you are viewing the information). You would do this by...

Using MS Access: Database Design help Needed, entity relationship diagrams, design entity relationship
entity relationship diagrams, design entity relationship, logging events: Michael hello, There can be a primary entity, lets say Project The immediate secondary entities associated with the Project, as per your statement, and a quick review of Big Calm, would be : Client with a Many : Many relationship Staff with a Many...

Using MS Access: Database Design Optimization - Relational or Non-Relational, sprintdb, design optimization
sprintdb, design optimization, dear scott: Frankly, I don t know what Kaione told you, but the ability to use SQL implies at least some level of relational functionality. The whole concept of Joins is a relational database concept. SQL was designed to query relational databases. The thing is the...

Using MS Access: Database Design, auto increment, visual basic programming
auto increment, visual basic programming, location id: Hi Arno If you open the table Contact in design mode, then select the FK field, say, Location_ID. At the base of the dialog, select the tab Look up . In the display control select combo box. Now under the property Row Source, select the table Location...

Using MS Access: Database design, player database, field names
player database, field names, football player: In designing a database, the designer needs to interview the users to determine what data needs to be captured and reported on. Through such interviews (or thought processes, if the user is the developer), the structure of the database becomes apparent. I...

Using MS Access: Database design, three tables, foreign key
three tables, foreign key, combobox: When you say you have created several fields, you are implying one table. I would doubt seriously if a project such as you describe can be done in one table. I would strongly suggest you do some research on the WEB on normalization . You need to properly...

Using MS Access: Database design, key field, relevant fields
key field, relevant fields, thanks john: Yes, that s exactly what you do need. You have a many to many relation since one incident can have multiple attendees and personnel can attend multiple incidents. So you need, what s generally referred to as a join or junction table I.E. tjxPeopleIncidents...

Using MS Access: Database design - migration to SQL Server, data transformation services, design migration
data transformation services, design migration, ms sql server: Hello Nelson Good to hear from you again.. 1. The easiest way forward is to introduce a new entity into your database model, namely, Division: so this will mean a new table with Division ID, Name, etc. Now you will need to add a DIvision Foreign Key...

Using MS Access: Database design for photography site, thumbnail jpg, image theme
thumbnail jpg, image theme, root image: Hi Dermott In my experience it is best to segregate types of attribute with a view to simplifying future maintenance.. I would therefore assume that all derivatives of an image would follow a naming system, so taking your example of Oldman , I would...

Using MS Access: Database design, ghost images, image record
ghost images, image record, image names: Ahh now that is understandable. Frankly, I would handle it differently I would create a new record for each image. Use a structure like I mentioned where your main table is info about each image and you have an attributes table that lists the software or whatever...

Using MS Access: Database Locking, run time error, database mdb
run time error, database mdb, novell 6: Hi Are these 30 users concurrent? MS Access will comfortably handle up to about 8 concurrent users without significant locking problems. As for records not being accessed - are you sure? In most cases, forms are bound to tables or unconditional...

Using MS Access: Database linkage -- one-way?, database linkage, foreign key
database linkage, foreign key, access 2000: What you need to do is create a link to the table in the new database. Then add a new table with the the additional info. Include a foreign key to link it to the original table. this will keep the original table separate fromthe new data. HTH Scott ...

Using MS Access: Database location, iloc, database location
iloc, database location, pleasure: James, what you want to use is : currentdb.Name this is a built in access function that you can call in VBA code. It will return the full path and filename of the file. Two functions I use for DB Name and Path (to remove the name from the path or vice-versa)...

Using MS Access: Database Problem, access tutorials, access tutorial
access tutorials, access tutorial, database problem: I m sorry, but anything that involves me downloading and looking at a database goes beyond the scope of my volunteer work here. You can, however, visit my TechHelp site for additional help and I d be happy to look at your database. Let me know if you...

Using MS Access: Database Question, lisa lisa, access 2000
lisa lisa, access 2000, many thanks: Lisa, It depends on how large it is. (You could winzip it, this would reduce the size) The other option is to open a new database, then go to File get external data import and just import the tables, queries and forms that are causing the problem....

Using MS Access: Database question, question number, two tables
question number, two tables, sorry for the delay: Kevin, Sorry for the delay, I had a problem sending the response before. I need more information to answer your question. Can you tell me if the user is directly in the table when they want this to be done? Generally, most databases have users in a...

Using MS Access: Database Relationships, database relationships, field thanks
database relationships, field thanks, switchboard: Let me address 2 first. Whoa! what you are saying is just the OPPOSITE os what you really want. I almost never use Datasheet mode. You have much greater control over input in Form view. You can do much more validation and data checking in form view. You...

Using MS Access: Database Report Generators, report generators, csv format
report generators, csv format, csv file: Bruce, How about exporting the spreadsheets in CSV format from Lotus then link the csv file (or in the case of Excel, the xls file) into Access and build your reports using Access. Otherwise I know of no other products.... Kind regards Geof...

Using MS Access: Database Report Generators, report generators, lotus 123
report generators, lotus 123, report wizard: There used to be a Report Generator for 1-2-3 spreadsheets. I don t even remember what its called now. But I don t think it exists anymore. Crystal Reports has just about cornered that market. I would consider bringing the sheets into Microsoft Access and...

Using MS Access: Database Report Generators, report generators, lotus 123
report generators, lotus 123, generator programs: Bruce, Many thanks for the question. I obviously use Microsoft Access, specifically the queries section. You can easily import the data into Microsoft Access by selecting on the toolbar File get external data Import Change the type of file...

Using MS Access: Database relationships??, database relationships, microsoft access
database relationships, microsoft access, mvp: Huh, what inventory app? If I recall you asked a question about how to update an Quantity on Hand field. My response was that you don t store this in a field, that you CALCULATE it as needed. I explained about using a Transactions table to record all movement...

Using MS Access: Database for Sales Monitoring, asking the right question, quantity field
asking the right question, quantity field, msaccess: Yes, This is also SOP and illustrated in the Northwinds sample that comes with Access. The RowSource of combo where you select the product should look like this: SELECT ProductID, Productname, Price FROM TBproducts ORDER BY Productname; In the After...

Using MS Access: Database Spliting, excel programming, corruption
excel programming, corruption: Hi Well - I have come across this error in Excel programming but never when splitting a database! What size is your database? Did you compact and repair BEFORE you split? 25 tables is not a lot. Similarly, 1800 records is not an issue (unless you...

Using MS Access: Database for Sportequipment Manufacturer, bicycle frame, assigment
bicycle frame, assigment, question thanks: The first phase of ANY database project is to review what information you need to capture and design your table to be normalized. Once you have that you can then start to design your forms for the entry and retrieval of data. I would use a Multi-select...

Using MS Access: Database Syncronisation, mdb file, database structure
mdb file, database structure, wizzard: Frankly I would manually make the changes on the production back end during off hours. However, if that s not proactical, then simply link to the Productions front end, use append queries to add the data to your new tables and then replace the production...

Using MS Access: Database security, robinsons book, web site software
robinsons book, web site software, best bet: Hi Your best bet is to purchase Garry Robinsons book - which will give you the low down ona potentailly hazardous area of Access. Read what I had to say about it here... http://www.developersforhire.co.uk/resource/resources_dev.htm#MsAccessSecurityChangeEffectsAllDatabases?...

Using MS Access: Database sharing, copy protection scheme, performance standpoint
copy protection scheme, performance standpoint, access2000: I would suspect that this is a hidden security or copy protection scheme. I haven t encountered that behavior myself. However, from a performance standpoint, what you should have is a front end and back end. The back end, containing only the tables, should...

Using MS Access: Database structure - Part II, visible controls, strsql
visible controls, strsql, command button: tblAnswerID needs a third field, the FK to link it to the person taking the survey. Your main form should be bound to tblPersonnel. The subform to tblAnswer linked on PersonnelID. On the subform should be two visible controls. A combobox to indicate the...

Using MS Access: Database Table vs FormView, record selector, database table
record selector, database table, access 97: You are right clicking on the form and selecting Datasheet view and the records show fine, but when you switch back to form view the form is blank? Does it show the correct number of records in the Record Selector bar? can you move to another record and see...

Using MS Access: Database versioning, cat board, audit trail
cat board, audit trail, many thanks: Hi, Many thanks for the question. I am not positively sure what you mean by versioning. I am taking a guess and presuming you mean an audit trail. The following site should give you some clues to this. http://www.utteraccess.com/forums/showflat.php?Cat=&Board=codearchive&Number=98965&Forum=f48&Words=audit&Searchpage=0&Limit=25&Main=98965&Search=true&where=bodysub&Name=&daterange=0&newerval=&newertype=&olderval=&oldertype=#Post98965&bodyprev=...

Using MS Access: Database versioning, reply
reply: Im note sure what your looking for, is it to track any changes in access or to track changes to each record. Anyway, I do not believe access have such a function, but you could just add a field, called updated, where the date for the last update is saved....

Using MS Access: Database, microsoft access database, excell format
microsoft access database, excell format, microsoft excell: Hi Michael Use a single DATABASE file(mdb) and import each into separate tables, eg T1, T2, T3 etc. Now create a master Primary Key (PK) table. You will need to select a column or columns in each of the Excel tables and create this master table. To do...

Using MS Access: Databases, reservation database, key field
reservation database, key field, combobox: You need to understand primary and foreign keys. I would look the terms up in Access Help. Essentially, all you do is have a foreign key field in the Reservations table for the CustomerID. Then select the customer from a combobox on the Reservation form. ...

Using MS Access: Datasheet View, operating system, wizard
operating system, wizard: This is not the function of subforms. If you want to be able to browse the table to select a record, create a Search combo. The combobox wizard will walk you through doing this. The purpose of subforms is to show data from a related table. Hope this...

Using MS Access: Datasheet view, microsoft access, quick response
microsoft access, quick response, mvp: The listbox is only to select a company, the main form is where you edit the selected company record. If you use the listbox wizard there should be a choice for a search. If not, create it as a combobox and right click and select change to to change to a listbox....

Using MS Access: Datasheet view in a form, currency field, record source
currency field, record source, data entry: For your first question, that should certainly be possible, all you have to do is modify the record source of each of the subforms, and they ll bring back whatever questions you want. The answer to your second question is even easier: No, thats not possible...

Using MS Access: Datasheet view in a form, currency field, answer table
currency field, answer table, questionairre: First, a questionairre has a specific structure. You would have a questions table, a respondes table and an answer table. I m not sure how that fits in wuth what you want. it doesn t seem to. Don t use datasheet view, use Continuous form view. You have...

Using MS Access: Datasheet view of main and sub form, contact management, arrow
contact management, arrow, programmer: You can use the Companies table instead of a form based in Companies table. Open Companies table in design view, wright click and point on Properties. In the Properties windowos click at the arrow wright of the SubdatasheetName and add Contacts table. Set...

Using MS Access: Date(), column type, ms access
column type, ms access, many thanks: Wahed, Many thanks for question. What you are looking for is the DatePart function. To do this: In a new column type the following in the first row: YearDate:DatePart( yyyy ,[NameOfDateField]) This will display just the year in the new column....

Using MS Access: Date, calendar function, calendar control
calendar function, calendar control, current date: I m afraid YOU are the one doing the misunderstanding. I fully understand what you want. Let me try to explain further. A control on a form has a ControlSource property and a Default Value property. The controlSource can be either an expression or a field...

Using MS Access: Date Add problems, strsql, dim rs
strsql, dim rs, flatfile: Hi Joe 1. Variables a & b need to be of Date/Time data type for a start 2. You will need another variable say sB of datatype string for the string version of the date, used in the filename, however DateAdd( d ,1,b) requires that b is a DateTime datatype....

Using MS Access: Date Calculation, dateadd, short survey
dateadd, short survey, survey questions: Place this in the criteria of the date field: Between DateAdd( d ,-15,[Enter Date]) And DateAdd( d ,+15,[Enter Date]) I have just tested this for you. Please help me help others by answering 3 short survey questions. Place Karim Survey in the subject line...

Using MS Access: Date Calculation, access tutorials, access tutorial
access tutorials, access tutorial, president ceo: Make a query where your criteria is: =Between MyDateField-15 and MyDateField+15 Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training http://www.599cd.com/Microsoft_Access_Tutorial...

Using MS Access: Date Calculation Issue, wierd results, sanity check
wierd results, sanity check, control source: Your expression should have been this: =DateDiff( d ,Now(),#2004-31-12#) Without the # signs, Access doesn t know you are entering a date which is why the wierd results. However I would do it this way: =DateDiff( d ,Date(),DateSerial(Year(Date())+1,1,1)-1)...

Using MS Access: Date Calculation, datevalue, number 0
datevalue, number 0, case range: In your after update event place this : Me.otherdatefieldname.value = EndDateCalc( M , C ,me.currentdatefield.value) Then place this in a new function in a module in your database. Function EndDateCalc(Range As String, Prev_or_Current As String, Optional...

Using MS Access: Date Condition in criteria(Dmax), agent id, tax table
agent id, tax table, tax period: I gather you are trying to assign the value using VBA? So your code is partially off this way: Use Me.Text42= or Forms!Form_Best_Rate.Text42= But I would use it this way: Me.Text42 = DLookup( [Service_Tax] , Tax_Table , [Agent_ID] = & Me.cboAgent...

Using MS Access: Date Criteria in Access, window pop, parameter value
window pop, parameter value, yyyy: You can t. A date has to be complete. What you CAN do is use a form instead of a parameter prompt. Use Comboboxes to select the month and year. Then add columns to your query: SearchMonth: Month([ExpDate]) SearchYear: Year([ExpDate]) Set the criteria...

Using MS Access: Date calculation, dateadd function, complete connection
dateadd function, complete connection, select connection: Again, you shouldn t be STORING the Bill Date (I assume that s the Owner Bill field). If you want to display the date add a column to your query: BillDate: DateAdd( d ,90,[Proj Complete]) The SQL will look like this: SELECT [Proj Complete], DateAdd(...

Using MS Access: Date calculation in MS access 97, ms access 97, dtmp
ms access 97, dtmp, dend: Hi Eric Paste the function below into a module, then call it where you need the number of weekdays like this: lngDays = WeekDaysOnly(dDate1, dDate2) Public Function WeekDaysOnly(dStart As Date, dEnd As Date) As Long Dim iDays As Long, dTmp...

Using MS Access: Date calculation, target date, vba module
target date, vba module, dateadd: Hello Dan, I have built the following function that will always return the next Friday, unless today is a friday, then it will return today. Public Function ReturnNextFriday(dIn As Date) As Date Dim iDayNum As Integer, iOffset As Integer, iTarget...

Using MS Access: Date calculations, conditional formatting, background colours
conditional formatting, background colours, orange 4: Paul, Hi, You are doing yourself no favours pursuing this approach. I don t hesitate to recommend designing your data table to store unique data types and specification With data correctly stored, you can then use code to test and make comparisons as...

Using MS Access: Date convert, decimal portion, integer portion
decimal portion, integer portion, routine office: Access stores date/time values as a double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion is a fraction of a day (ex: 6AM or 6 hrs = .25) This means that how the date appears is a mmater of formatting....

Using MS Access: Date criteria, correct criteria, pc configuration
correct criteria, pc configuration, qbe: Rahmat hi, Assuming the field is a Date datatype, then #01/01/2000# If you are using the QBE, then the date format will be in accordance with the PC configuration. If you are writing SQL, then dates need to be in US format, ie MM/DD/YYYY....

Using MS Access: Date Diff Function, date1 date2, case users
date1 date2, case users, yyyy: First, you don thave fields on a form. You have controls that may or may not be bound to fields in a table. This is a subtle but important distinction. The expression: =datediff( m ,[Date1],[Date2]) will give you the months between the 2 dates. To...

Using MS Access: Date Diff Function, control source, yyyy
control source, yyyy, date of birth: I would do this in a custom function: Public Function ExactAge(dteDOB As Date) As String Dim intMonths As Integer Dim intYrs As Integer intMonths = DateDiff( m ,dteDOB,Date()) intYrs = Int(intMonths / 12) intMonths = intMonths - (intYrs * 12) ...

Using MS Access: Date Difference using Access, blank query, date function
blank query, date function, access project: All you have to do is build a query for it. Add the table that has your field in it to the design view of a blank query. Add the field in question. In the criteria section for that field, enter a formula like this: (Date()-1826) Dates are represented...

Using MS Access: Date expiry Alert, dcount, dateadd
dcount, dateadd, colour change: I m not real clear on what you are trying to do here. All I ve got is that you want to set an expiration date and have it highlighted when it comes up. What s not clear here is WHEN and WHERE you want this warning. I have a similar app. Its got a duedate...

Using MS Access: Date expiry alert, dateadd function, subscription renewal
dateadd function, subscription renewal, dcount: The answer to this question has multiple parts. The way I would do it is as follows... Create a query based on your client table. Create a column in that query that shows the expiry minus 2 months. You can use the DateAdd function for this, something like...

Using MS Access: Date Fields, normalization rules, strsql
normalization rules, strsql, access programming: You may have the design that you want, but that is NOT correct design. You need to read up on normalization rules. These rules, for the design of relational databases, have stood the test of time for over 30 years. Your design may be easier for entering data...

Using MS Access: Date Format, input mask, mm yy
input mask, mm yy, two different things: Since Access stores the date as the number of days since 12/30/1899, inputting a date and displaying it are two different things. The default date format is determined by your regional settings. It sounds like you have US (mm/dd/yy) regional settings but want...

Using MS Access: Date Format problem in a parameter query, mm yy, unbound controls
mm yy, unbound controls, parameter query: One of the problems here maybe the European date notation. It doesn t always work with parameter queries. There are a few things you can try. First, I almost never use parameter prompt queries. They are very limiting. Instead I use a Form to enter the...

Using MS Access: Date Formats and Masks, input mask, input masks
input mask, input masks, format date: Bryan hi, Onboard help has quite a bit on input mask formatting - have you tried that ? I am not a fan of input masks (except password) as I also get very confused and the constraint just annoys me beyond despair. So I would say strip the input mask...

Using MS Access: Date Function, cu rrent, exact calculation
cu rrent, exact calculation, exact function: I think I already answered this question once but here is the function I wrote a long time ago to calculate beginning and ending dates. Paste it into a new module and read the comments to learn how it s used. ************************************** ...

Using MS Access: Date format, correct expression, import feature
correct expression, import feature, datatype: I made one mistake, but it wouldn t have resulted in what you report. The correct expression is: Phone: Left([phonefield],3) & Mid([phonefield],5,3) & Right([phonefield],4]) You should have been able to figure this out yourself if you understand what...

Using MS Access: Date format in Access, integer portion, double precision
integer portion, double precision, microsoft access: No, the data is saved in the table as a double precision number where the integer portion represents the number of days since 12/30/1899. How the date is displayed is a function of how you format the value. If you want to display the date as dd/mm/yyyy then...

Using MS Access: Date format in MS-Access in different locales, constituent components, script functions
constituent components, script functions, lcid: Jay, Hi Yep - its a pain in the ***** :-) The only safe way I have found is to break the entered date into its constituent components and build a string from them in US format. I have used the regular VBA/VB Script functions to do this along the lines...

Using MS Access: Date format on microsoft access97, microsoft access97, yyyy
microsoft access97, yyyy, dd: Hi Jason, You can use mm/dd/yyyy as the format for your date field. Open the table in design view and select the date field. Under Field Properties in Format type in mm/dd/yyyy without quotes. Save the table. I hope it helps. Thanks. Mark ...

Using MS Access: Date format in reports, input mask, regional settings
input mask, regional settings, uk style: Dates are actually stored as a serial number counting the days from 12/30/1899. So how a date is displayed is a function of the format of the control displaying it. I m not sure if the format you want is available for UK (Date display is a function of the...

Using MS Access: Date format, field thanks, input field
field thanks, input field, ms access: Treasa, hi, Text searching works from left to right, so your text field of mm/yyyy will not deliver the required results If you were to store the data as text in the format yyyy/mm then it would work: however you would need to modify your data entry...

Using MS Access: Date formula, menu field, dateadd
menu field, dateadd, hth: First, You don t have fields on forms, you have controls that may oir may not be bound to a field in a table. This is a subtle but important distinction. Second, as a general rule we do not store calculated values. So what you need is an unbound text box...

Using MS Access: Date function, exact calculation, query criteria
exact calculation, query criteria, date function: Prasad - I am away from Access right now, so I am not able to test a solution that would allow you to calculate the number of days in a specific month. However, an equation that I use in my databases might be all you need-- Computers see dates as numbers....

Using MS Access: Date function, dateadd, date function
dateadd, date function, last two months: Hmm, I ve tried your expression and every permutation I tried comes back with 12/31/02 (note that s 2002) for any date in February 04. In fact I tested it for other februarys and it also had the year wrong. So I suspect a bug in Access when dealing with Februarys....

Using MS Access: Date functions, expense types, monthly expense
expense types, monthly expense, expense report: I m not clear on your structure here. Its certainly unorthodox and may not be normalized. You really shouldn t have to tables to record the expenses. You should have a table of expense types (Paper, Toner, et,) and a table of when these expenses actual occur....

Using MS Access: Date Issue in Access, sensitive cases, transaction data
sensitive cases, transaction data, good date: Ulices, Hi In the background, all Date data is stored as real numbers - 12345.56789 where the left part is in units of days and the right part is in units of milliseconds (for instance) from midnight. Is this significant ? When you specify a date...

Using MS Access: Date match, onchange event, reformating
onchange event, reformating, record source: Hi Paolo DOnt worry about the reformating of Date() as Date - just a keyword. Did you try my secon option for a text formatted date ? ie. Sub Form_Open() Dim dDob as Date dDob = CDate(Mid(Me.[DOB],4,2) & / & Left(Me.[DOB],2) & / & Right(Me.[DOB],4))...

Using MS Access: Date parameters, queries in access, range exp
queries in access, range exp, query criteria: I don t use parameter queries very much. They are very limiting. What I usually do is create an unbound form and put 2 text boxes on it called txtStart and txtEnd. Then for the query criteria, I use: Between Forms!formname!txtStart AND Forms!formname!txtEnd...

Using MS Access: Date-parm Report from MS Access, printcards, dtemp
printcards, dtemp, openrecordset: Dear Tracy, Ok the wizard created these, remove the entire section that look like this: (From Private to End Sub ) Private Sub PrintCardsButton_Click() On Error GoTo Err_PrintCardsButton_Click DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70...

Using MS Access: Date problem, expression builder, query criteria
expression builder, query criteria, access tutorials: You re going to need a third column that shows the difference between your two dates: MyDateDif: [ActualDate]-[ExpectedDate] That column can then have a criteria so that it only shows if it s greater than or equal to 2: =2 Now you can feed that...

Using MS Access: Date Queries, prompt box, option group
prompt box, option group, text boxes: First I do not use parameter prompt queries very much since they are very limiting. I provide parameters to to queries through a form. Create an unbound form and put three controls on it. Two text boxes named txtStart and txtEnd and an Option group named...

Using MS Access: Date Query in MS Access, persons age, clait
persons age, clait, dob: Hi Julie In this sort of situation I build a bespoke function to calculate the persons age for any given DoB, then use the function in the query with the DoB, as if it was the persons age directly... So here s the function you need - paste this into...

Using MS Access: Date query out by two days, microsoft access, current date
microsoft access, current date, december 29: In the immediate window of the VBE type: ? Year(#12/30/2005#) If this returns anything other than 2005, then I haven t a clue what s happening. Run the query and look at the label column and see what it shows. Hope this helps, Scott Microsoft...

Using MS Access: Date Range, microsoft access, 1 feb
microsoft access, 1 feb, mvp: Your database is not constructed properly. Its not normalized. You should never have fields like Jan07, Feb07 etc. What you should have is a record for each month. You can then append a record to your table using a loop for the number of months. Hope...

Using MS Access: Date Record Created, expression builder, data tables
expression builder, data tables, access 2000: I routinely add 4 fields to my data tables; CreatedBy, CreatedWhen, EditedBy, EditedWhen. For CreatedWhen I set the default on the table level to =Now(). For CreatedWhen I add a hidden control to my forms with a default of =fOSUserName(). Note: this functions...

Using MS Access: Date Reference, value expression, must be a way
value expression, must be a way, time and date: First I would not use 2 separate fields. Use one field with Now() as the value to record both date and time. I do this as SOP for many of my apps. I add 4 fields named CreatedBy, CreatedWhen, UpdatedBy and UpdatedWhen to my tables. The CreatedWhen field...

Using MS Access: Date range query, john corrigan, range query
john corrigan, range query, text boxes: I do this all the time. Its based on the feature where you can refer to a value in a control on an open form with the syntax: Forms!formname!controlname What you do is create an unbound form (call it frmDateRange) with 2 unbound text boxes (call them...

Using MS Access: Date ranges, dateadd, shelflife
dateadd, shelflife, shelf life: An interim query is just a query that is used as the source of another query. So you might have a query named qryDiscard that would look like this: SELECT ProductID, ProductName DateAdd( d ,[ShelfLife],[DOM]) As Discard FROM tblProducts; Then use that...

Using MS Access: Date Spread, manipulation functions, dateserial
manipulation functions, dateserial, dateadd: Hi Apologies for the late delay. Does the following help? Here are some simple date manipulation functions that can be used to return a specific date in the future/past. Note that you can combine these functions to, for example, find the lastday...

Using MS Access: Date Stamp on Form, value date, date stamp
value date, date stamp, select properties: Gabe. Right click on the form control that you want the date to show up on. Select PROPERTIES. On the properties dialog go to the EVENTS tab. Double click the empty field next to ON DOUBLE CLICK then click on the ... to the right of the field (which...

Using MS Access: Date Stop and Start Functionality in MS Access, application record, duedate
application record, duedate, initial adjustment: The way I would handle this is with separate table to record process time. Something like this: tblProcessTime ProcessTimeID (PK Autonumber) ApplicationID (Foreign key to application record) ProcessTime (number of days) ProcessTimeType (Initial, Adjustment)...

Using MS Access: Date sorting in Crosstabs, column headings, access tutorials
column headings, access tutorials, cross tab: Yeah, that s a tough one. Without seeing your table I really couldn t give you a solid answer, but you could try just reformatting your headers like this: yy-mm That will give you headers that look like this: 06-11, 06-12, 07-01, 07-02 That would...

Using MS Access: Date/Time automatic update, control names, error method
control names, error method, object names: Probably because you had a space in the object name. As a general rule you should not use spaces in object names just for this reason. Also, you should make use of the Intellisense. When you type Me. it should bring up a list of methods whihc include all...

Using MS Access: Date Time Calculations in MS Access 2000, ms access 2000, explicit function
ms access 2000, explicit function, access store: Dear Pat, Yes with Date and time fields you always need to convert it to a unit number, then average s can be calculated from this and the result can then be converted back to the original point. For Example: Use the previous functions to calculate...

Using MS Access: Date & Time format in MS Access, integer portion, mm format
integer portion, mm format, format date: Just change the format. Date/Time values are stored as a number where the integer portion represents the number of days since 12/30/1899 and the time is represented by a fraction of a day (ex 6AM = .25). So how the date is displayed is a matter of the format...

Using MS Access: Date/Time Load, administrator logs, bound controls
administrator logs, bound controls, time load: I think I follow now, you want to change the controls when you make changes to the SUBFORM, not necessarily the main form? If so, then you need to fully qualify the controls: Forms!formname!controlname = Now() Hope this helps, Scott Microsoft...

Using MS Access: Last Date and Time Modified, relevant table, time field
relevant table, time field, information display: Ok, You need to add a date/time field to your table to use as the datestamp. You then add a hidden control on your form bound to that field. In the Before Update event of the form, you enter the code: Me.txtUpdatedWhen = Now() Hope this helps, Scott...

Using MS Access: Date &Time (- minus) Date &Time!!, date time, many thanks
date time, many thanks, exceptions: parantapkumar, Many thanks for the question. How is this information stored in the table? Is 09/02/2007 01:10:44 To 10/02/2007 00:01:01 all stored in the one field? Are the other columns, in this same table or are they in another table?...

Using MS Access: Date/Time Subtraction, datediff function, int 24
datediff function, int 24, time differences: You need to understand how Access stores time. Its stored as a fraction of a day so that 6AM (or 6 hours) would equal .25. To do time differences, I would use the DateDiff function and calculate the minutes. You can then get hours and minutes from that. ...

Using MS Access: Date/Time stamp, vba code, best bet
vba code, best bet, access 97: No, it doesn t work that way. The scanner is an alternative input device. Somehow (thru some coding), an external process detects the scan, reads the scanned code and inputs that into the Access app. At some point this process has to run some VBA code to populate...

Using MS Access: Date & Time, currentuser, time agent
currentuser, time agent, time field: Steve, Many thanks for the question. The easiest way would be to add this once information is entered into the note. Open the properties for the date field (right click on the text box properties) Click on the data tab - next to default type...

Using MS Access: Date and time stamping records, bound form, access help
bound form, access help, time stamp: Hi Gavin How about something like this... Me!NameofYouField.Value = Now() Place this in a button click event proc to have the user time stamp OR put in the BeforeUpdate event of the form or your field. Please see inline Access help for building...

Using MS Access: Date Validation Rules, date validation, validation rules
date validation, validation rules, date referrals: Im not sure you can do this with just a validation rule. I would be inclined to setup a custom function. Something like this in the After Update event of the control: If Day(Me!controlname) 8 Then If Me!controlName DateAdd( m ,-1,DateSerial(Year(Date()),Month(Date()),1)...

Using MS Access: Date in words
Dear Prasad, Yes you can by creating a function to do this. Example strLngDte = ExtraLongDate(Nz(MyDte)) Function ExtraLongDate(TheDte As Date) As String Dim strDay As String, strMonth As String Dim strYear As String, lDay As Long, lMonth...

Using MS Access: Date, access query, string 4
access query, string 4, as400: =DateSerial(Val(Left(String,4)),Val(Mid(String,5,2)),Val(Right(String,2))) Substitute The field or variable. for string and it will convert to a date. If you want to convert the date in all the records of a table, add a Date field to the table, then...

Using MS Access: DateAdd, dateadd function, different things
dateadd function, different things, plants: Hi Jani How about setting up another column, say, Year2: Year(DateValue([EffFrom])) with the criteria Year(DateAdd( y ,-2,Now())) Have a look at the full spec of the DateAdd function in onboard help.. You may get some ideas.. Kind regards Geof...

Using MS Access: Dateadd function in Access, object names, design mode
object names, design mode, naming convention: Again, you neither WANT nor NEED to store the predicted date. You already have the expression. Some examples: 1) You have a form where you are entering the data. You have one control for the Predicted Start date. The Controlsource for that control would...

Using MS Access: Dateadd function in Access, access tutorial, president ceo
access tutorial, president ceo, video lessons: I d have to look at your database to be sure, but if you re just trying to add 6 weeks to a date, just add 42 to it (7x6). Remember, in Access, 1 day = 1. You don t need to use the DateAdd function. Let me know if you have any more questions or comments....

Using MS Access: Dateadd function in Access, example demonstrations, quotation marks
example demonstrations, quotation marks, video tutorials: You needed quotation marks around the ww weeks. Correct code is: [Predicted Start Date]= DateAdd( ww , 6, [Start date]) Check out www.MyAccessProgram.com for free video tutorials and example demonstrations files at myaccessprogram.com/download.html Bob...

Using MS Access: Dateadd function on unbound fields in a form, incorrect syntax, correct syntax
incorrect syntax, correct syntax, aslo: First, you do not have fields on a form. Fields are within records. You have controls on a form that may or may not be bound to a field in a table. This is a subtle but importanto distinction. The reason for the error is you have incorrect syntax for the...

Using MS Access: Datebase to Datebase, referenced database, database answer
referenced database, database answer, library database: Sorry for the delay in answering. To use an object in a referenced database, you need to create a function in the referenced database to open that object. For example: if you want to open a form named libfrmVendors you would use a function like: Public...

Using MS Access: Datebase to Datebase, library database, preference
library database, preference, automation: Hi You could automation code but this may be overkill. My own preference here is to implement a library database and then set a reference to the db and then it becomes part of your set up. So in fact you can open any object in the other db as if it...

Using MS Access: Datediff fuction, value date, negative numbers
value date, negative numbers, ms access: In the After Update event for the date field add this code: me.fieldwheredayscountgoes.value = me.datefieldname.value-date() You may want to switch date() and me.datefieldname.value around depending on which one is larger so you don t get negative numbers...

Using MS Access: DateRange instructions, combobox, newb
combobox, newb, microsoft access: Yes you can run multiple reports, either with different buttons or using a combobox that lists the reports. You just need to set the same criteria in the queries for those reports. You can group a report on week then month. Hope this helps, Scott ...

Using MS Access: Dates in Access, microsoft access, zima
microsoft access, zima, mvp: First, its not the best idea to delete records. Just filter them out. If you need to not show people who have left, then bind your form or report to a query and set the criteria for the DateDischarged field to: Date() If you really need to delete then...

Using MS Access: Dates in Access, salesperson, salespeople
salesperson, salespeople, access database: Dear James, You need to create a new table that contain the dates when these Salesperson move to a different country or add a date field to a existing table. Write the report based on these dates. A new table containing the Date, Country and Salesperson...

Using MS Access: Dates in access, death date, microsoft access
death date, microsoft access, birth date: You can t. So what you need to do is decide on an arbitrary date of the year to assign to incomplete dates. For example 1/1. You would then use the DateSerial function to convert the date using Nz to supply the default date. Hope this helps, Scott Microsoft...

Using MS Access: Dates and Date Ranges, datae, patient identifier
datae, patient identifier, datevalue: Create a query and in the admission date column set the criteria to datevalue. In the discharge column in the same row put datevalue. That will return all patients who were admitted prior to the datae and discharged after it. Hope this helps, Scott...

Using MS Access: Dates in MS Access, record source, current records
record source, current records, query builder: Hi Carla, In the Query builder, build a query based on the table with all the fields.. Now in the Constraint row, under the column Refresh Date insert the following phrase: Date() - Save the query. This will restrict the records to only current...

Using MS Access: DateTime in database, datetime
datetime: Hi Vitaliy, In the case of ADDED records, set the date field default value to Now() In the case of UPDATED records, you will need to include in the update SQL, the date field, and again set this to Now() EG: UPDATE tblYourTable SET Xxx=1,...

Using MS Access: Day Counter, datediff, colum
datediff, colum, current date: First, you would NOT want a field to store this info. What you would have is 2 fields; OrderDate and CompleteDate. The next step depends on how you want to display the number of days outstanding. For example; if you wanted to display this on a form you...

Using MS Access: Day Query, time intervals, command button
time intervals, command button, microsoft access: Set the criteria for the date column of your query to: =Date() That will produce records only for the current day. If your date and time are in one field, you will need to break out the date, so add a column like so: Today: Int([datetimefield]) ...

Using MS Access: Day of Week formula Question, holiday table, subquery
holiday table, subquery, microsoft access: Not sure I follow. I think you want to do an avg but only for non holidays. If so, what you would do is have a Holiday table, then do a subquery for your criteria: For the date column use: NOT IN(SELECT HolidayDate FROM tblHoliday) Hope this helps,...

Using MS Access: Getting a Day, ms access 97, riyad bank
ms access 97, riyad bank, expression builder: Dear Joan, You can make the textbox enabled/Locked this way you can t change the date, but at least the Saturday will not disappear. But if you want to change the date Access will not accept to change a date with Saturday or Sunday. That s why it change...

Using MS Access: DB Connection, sql server database, sql server forum
sql server database, sql server forum, hth: The answer is yes, you can setup the connection so you won t be prompted for a password. The bad news is I m not sure exactly how to do that. May I suggest you post your question in the SQL Server forum at utteraccess.com. I m sure someone there will know....

Using MS Access: DB Connection, sql server database, ado recordset
sql server database, ado recordset, sql tables: Dear Mo, To change to ADO is much more work because you need to do everything yourself, saving each field after change, reading the info from the ADO recordset and populate all the fields on the form. Basicly everything that access do for you, you need...

Using MS Access: DB Normalization, memo data, field space
memo data, field space, key field: Athalia, Many thanks for the questions and my apologies for the delay in my reply. Q1 As you make changes to the database it will grow quickly. To compact, open the database in Design view, click on Tools Database Utilities Compact and Repair....

Using MS Access: DB Update ?, yesno, report option
yesno, report option, db table: If you move to another form, it should save the current record, but to be on the safe side, do a save. If you add a field to a table you can insert the filed anywhere on the list. You select the row below where you want to insert then use the insert row...

Using MS Access: DB Variable, activex data object, db database
activex data object, db database, mdac: Have had the same problem myself, and i m not sure what causes the problem. But I have an idea, I believe it to be the ActiveX Data Object file (called ADO), and if they have an older version, it dosen t have the variable. try installing the newest version...

Using MS Access: Dbase Query, lotto combinations, lisa sims
lotto combinations, lisa sims, substring function: Hi Eugenio, I would suggest using the count function along with the dbase equivalent of the substring function for dbase along the following lines for each number: Select count(distinct columname) as NumofOnes from tablename where substr(colunmname,1,1)...

Using MS Access: DCount Function, control source, message error
control source, message error, parameters: First try this as a single derived column is a select query DCount( [DisputeNumber] , qryMonthlyNumbers , [ComplaintType] = AG ) and see if you get something returned. Be sure the qryMonthlyNumbers has any needed criteria being fed to it, if any. If...

Using MS Access: DCount Function, average gpa, query report
average gpa, query report, numeric field: Your attempt at the following looks REAL close: DCount( [HSGPA} , NAME OF QUERY REPORT IS BASED ON , [HSGPA] 0 ) I see no reason why this modification won t work: DCount( [HSGPA] , NAME OF QUERY REPORT IS BASED ON , [HSGPA] 0 ) Here are...

Using MS Access: DCount in Query, dcount, query databases
dcount, query databases, aggregate function: Well, it s not Access fault you can t do it in one query. Databases just don t logically work that way. Don t use DCount. Make one column for WHERE, add 1 OR 2 in the criteria, and change the Total row to Where . Make another column (really it...

Using MS Access: =DCount, query name, name query
query name, name query, best guess: Well, this is a little vague, but my best guess would be to take a closer look at your criteria clause: Do you want if the field = true or True ? If your looking for the actual string value (the letters t, r, u, e consecutively), then change the statement...

Using MS Access: Dcount in a query (multiple columns), bedroom size, avg size
bedroom size, avg size, property details: First what does a query have to do with data entry? Second, you would create a Group By query grouping on Bedroom Size. This will give you a count of records by Bedroom size and you can average the rent. As for the rest you can then apply your qualification...

Using MS Access: Dcount syntax problem, select count, thanks in advance
select count, thanks in advance, section 1: Hi Gilbert, Many thanks for the question. Try the following: SELECT Count(*) AS Stu_Count, Sum(FEE) AS Stu_Val FROM TableName WHERE ((DCount( * , TableName , STUDENT_NO = & STUDENT_NO & )=1) AND ((SECTION)=1)); i.e. add a before and after...

Using MS Access: Dear Mr. Henderson,Practically..., relational database design, table relationships
relational database design, table relationships, many cars: Hi Carol Yeah - this is a hot topic in database design generally. Some people lean towards not using many to manys and some people do. This is down to relational database design not being an exact science . I.e. It s more art than science. Personally...

Using MS Access: Dear mister Henderson,Compile..., docmd, quick reply
docmd, quick reply, thanks in advance: Hi This is confusing. Are you sure you have the correct function name and your using that name when your calling the function? This error normally means that you are attempting to call a function like the following... Function TestIt() End Function...

Using MS Access: Dear Rob,I have relatively..., candace tripp, open windows explorer
candace tripp, open windows explorer, artwork catalog: You aren t asking too much and I have answers for both. I have an artwork catalog that does just about the same thing you are asking for working with pictures. The way I do it is to use an UNBOUND Image control. I then set the Picture property of the Image...

Using MS Access: Dear Rob: I have..., sendobject, resource resources
sendobject, resource resources, dear tom: Hi You could use a VBA recordset - find an example here... http://www.developersforhire.co.uk/resource/resources_dev.htm#AdoRecordsetStructureExample ...and as you loop, pick up the name of the contact and use DoCmd.SendObject for sending the mail....

Using MS Access: Decimals, volunteer hours, decimal places
volunteer hours, decimal places, whole numbers: Terisa - I think I can half-way help. In your table, for all the fields that contain numbers of hours worked, make sure that the datatype is set number (which it probably is). Set the fieldsize to Double, Format to standard and Decimal places to 2. This...

Using MS Access: Dedfault Values, trader table, trader name
trader table, trader name, control source: I m not sure why you are going thru this process. You should have a separate table for Trader info. If you want edit Trader info, then use a separate form for that. You should then have a form for your Deals table. One of the controls on that form would...

Using MS Access: Deep ACCESS question, vba code, level developer
vba code, level developer, parameter selection: Change the criteria to: Like Forms!formname!controlname Set the default value of the combo to * or use: If (Me![Status 2nd] = All ) Then Me![Status 2nd] = * End If Now if the field contains any nulls they will be excluded so you...

Using MS Access: Default Values, record parameters, default values
record parameters, default values, occassions: I ve done the same thing on several occassions. I use a one record parameters table that contains the values for that specific installtion. Use the DLookup function to pull the date. For example, the client name would be a field in that table. To display the...

Using MS Access: Default Values, referential integrity, project 101
referential integrity, project 101, demographic info: There are 2 ways to do this, depending on the type of the dependent control. If the control is a combobox, you can use the Column property to to set the dependent control. =[comboboxname].Column(x) where x is the number of the column in the combo s query,...

Using MS Access: Default value for first record set, strsql, dim rs
strsql, dim rs, openrecordset: Hi Leslie, Many thanks for the question. Try to use Error Handling. For example, change your code to the following: RequestedBy _ AfterUpdate Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim NextNum As Integer On Error...

Using MS Access: Defaulting Text Box Value from Pvs. Entry, focus event, current event
focus event, current event, access 2000: Hi, I would create a String variable that would become populated with the value of the field on the lost focus event of the textbox in question. The using the on current event of the form set the value of the string vaiable to the textbox. Dim StrDate...

Using MS Access: Delete and Append, rsq, pseudo code
rsq, pseudo code, query1: Dear Maureen, Visual basic and VBA is the same. But here is a sample of code that can do the trick. Dim Rsq as DAO.Recordset, RsA as DAO.Recordset Set Rsq = Currentdb.Openrecordset( Query1 ,dbopendynaset) Set RsA = Currentdb.Openrecordset( TableA ,dbopendynaset)...

Using MS Access: Delete error, input mask, social security number
input mask, social security number, key field: I ve never encountered this. Something has to occur to generate the error. You didn t answer when its occurring. I would suggest using utteraccess.com to attach a copy of your DB to a post so the experts there can take a look at it. Hope this helps, Scott...

Using MS Access: Delete period from user input, address field, ms access
address field, ms access, len: Dear Samuel, A small function can do the trick Example: Private Sub Address_AfterUpdate() Me.ActiveControl = StripPeriod(Me.ActiveControl) End Sub Function StripPeriod(strInput As String) As String Dim A As Long For A = 1 To Len(strInput)...

Using MS Access: Delete Query
Dear Zeeshah, Try to change the query to this: DELETE [060401].*, [060401].Field1 FROM 060401 WHERE ((([060401].Field1) In ( own , status , position ))); the problem lie in the where clause, Field1 can t contain al three the words on the same record...

Using MS Access: Delete Query, position status
position status: DELETE * FROM 060401 WHERE Field1 = own OR Field1 = Position OR Field1 = Status ; or you can use WHERE Field1 IN( own , position , status ); You can t use AND when comparing multiple values for the same field, since the fied can only be one...

Using MS Access: Delete record from multiple tables, runsql, sql statement
runsql, sql statement, sql statements: Hi Eric Create yourself a procedure (or function if you want to execute from a macro) in a module as follows: Public Sub DeleteQueriesProcedure() or Public Function DeleteQueriesProcedure() Dim sSQL as String Prevent user warnings DoCmd.SetWarnings...

Using MS Access: Deletes records in both tables when it shouldn't, table source, three tables
table source, three tables, key value: If you remove the relationships this will fix it unless you have written code to cause this. I never set relationships and just ensure relationship integrity is controls with my code populating foreign keys when it can not be auto-populated using the built...

Using MS Access: Deletes records in both tables when it shouldn't, global relationships, bogus records
global relationships, bogus records, global relationship: It sounds to me like you just have your relationships set up wrong. If I have a PEOPLE table and a VEHICLES table, with a global relationship (1 to many) from People to Vehicles, with Cascade Updates and Deletes set to ON, and then I DELETE a person, all of...

Using MS Access: Deletes records in both tables when it shouldn't, query criteria, table source
query criteria, table source, three tables: Hello Sarah - I m not exactly sure how you want your end result to present itself. The reason that your records are being unexpectedly deleted is because your query is bound to two records sources and when you delete an entry from the query, it WILL remove...

Using MS Access: Deleting dupliate records, cmd button, excel spreadsheet
cmd button, excel spreadsheet, duplicate records: Herman, hi, How about bringing all the data into a working table (#1) in Access, then condition this data into Access table (#2). Now delete all the data in the Excel spreadsheet, and copy table #2 into the spreadsheet ? Hope this helps Kind regards...

Using MS Access: Deleting duplicate records, queries in access, extra records
queries in access, extra records, column method: Claude, Hello, Three practical ways to delete records AAA - Additional column method 1. Add a new column to a table, called say [DelRec], and set its default value to No . 2. Run a number of update queries (as required) to change [DelRec] = Yes ,...

Using MS Access: Deleting from Multi Select Listbox, strsql, key field
strsql, key field, microsoft access: What this code does is remove an item from a Value List rowsource. It does not delete records from a table. If you want to delete the records from the table. try something like this: Private Sub Command10_Click() Dim strSQL As String For i = List12.ListCount...

Using MS Access: Deleting Part of a Field, last four digits, confirmation field
last four digits, confirmation field, confirmation number: Do you really need to delete the number or just mask it? You might want explore using the expression: =Right(fieldname,4) wherever you need to display the CC #. But if you really want to change what s stored, you can use the After Update event of the...

Using MS Access: Deleting records, column method, deleting records
column method, deleting records, existing records: Hi Claude The default value will only apply for new records inserted after it was set. To set the default for existing records, run the following query, but sub in your table name... UPDATE tblYourTable SET [DelRec] = No ; Now proceed with the...

Using MS Access: Deletion of Current Record in Main & Subform, syntax error missing operator in query expression, run time error
syntax error missing operator in query expression, run time error, runsql: Rather than using 2 variables, I m only using one and populating the variable, executing the SQL and repopulating. so you don t need strSQL_1. Not sure why you are getting that error, though. the code looks right. Check to see what the 2nd SQL stmt looks...

Using MS Access: Dependent Combo Box From Option Group, choice of sources, combo boxes
choice of sources, combo boxes, option group: Dear Pat You can put two combo boxes in a form, one for the option A and the other for the option B. The Control Sources of the two boxes must be different fields of the Form Record Source. The Row Data Type must be Table/Query. In the Row Source row,...

Using MS Access: Deploying Access on a network, standalone machine, windows 2000 server
standalone machine, windows 2000 server, end tables: First, for putting a database on a network you should split the database. Use the Database Splitter under Tools to create a back end (tables) and a front end, the rest of the objects. Next, you can t copy the database from within the database. Just use...

Using MS Access: Design of database for several uses, relational database design, landscape crews
relational database design, landscape crews, payroll database: Hi It seems to me that you need to do a complete analysis of the design. You will need a payroll set of tables and also an estimating set of tables. Ideally, a database should be single valued. By this I mean, you would build a payroll database and...

Using MS Access: Design, that would allow users, separate tables
that would allow users, separate tables, table names: I would not create different tables for the companies, I would hold information for all companies within one set of tables. There is also the upper limit of 255 tables to consider, becuase of this you could potentially only hold 85 companies using seperate...

Using MS Access: Designing a Database, option buttons, answering questions
option buttons, answering questions, many thanks: Horalia, Many thanks for the question, I nearly put it in the Too long and involved section as my time for answering questions is quite limited, and this may take my up to an hour to answer. If though you are willing to wait (it maybe a couple of weeks...

Using MS Access: Designing a database, bridge player, bridge club
bridge player, bridge club, normalization: Definitely not one table. You need at least one table for the players and another for matches. Not being a bridge player myself, I m not sure what else is needed. I suggest you do some research on Normalization which is an all import design technique for databases....

Using MS Access: Designing Forms - OnExit, hth, vba
hth, vba, cursor: If you want the cursor to go to a different control depending on the value of that or another control you use the SetFocus property: Me.controlname.SetFocus. So you might use: If Me.lstlistbox = x Then Me.control1.SetFocus Else Me.Control2.SetFocus...

Using MS Access: Designing Input form for Composite Primary Key table, client satisfaction survey, relational database design
client satisfaction survey, relational database design, case management program: I read the article and I agree with what it says to a point. In my opinion the article doesn t go far enough. What you need are at least two, maybe three tables. Table1 is your questions table with the following structure: QuestionID (PK, Autonumber) QuestionText...

Using MS Access: Designing Interface With VB, customer interface, database developer
customer interface, database developer, basic programming: Dear Charles, The form wizard in MS Access will help you with your first forms, to design these forms in MS Access is pretty much the same concept designing them in full blown VB. So try to design your own forms and ask me questions will you working with...

Using MS Access: Desperate, union queries, continuous records
union queries, continuous records, desperate hope: You can t do this in a query. There are two ways that you might do this. The first way is to write each line directly to the text file using the Write# or Print# commands. The second possibility is to export the detail records to Excel, then add summary rows...

Using MS Access: Detail Section of Forms/Reports being 22 inches, detail section, customer profiles
detail section, customer profiles, detail record: Dear Dave, Yes that is one of the limitatons I found as well. I created a number of headers and trailers to extend this area. Headers and trailers at the same breakpoint as the detail section. Subreports link to the same detail record can also be used to...

Using MS Access: Detail size, detail size, microsoft access
detail size, microsoft access, normalization: First, if you have that many controls to add to a form, then your databases is probably not designed properly and is probably denormalized. You need to read up on normalization and look at redesigning. Hope this helps, Scott Microsoft Access MVP 200...

Using MS Access: Detect File Exist, share artwork, artwork images
share artwork, artwork images, server share: Here s the code I use in a similar app: Dim strFile As String strFile = CurrDBDir() & images & Me!txtArtworkID & .jpg If Dir(strFile) = Then Me!imgPic.Picture = \servershareartworkimages otavail.jpg Else Me!imgPic.Picture...

Using MS Access: Detect vertical scrollbar, rowheight, format tab
rowheight, format tab, thanks in advance: I don t think there is a way to detect if it is displaying the scroolbar, but you can prevent it from displaying it at all (scrollbars property in the Format tab of the properties window), and you can stop a user from adjusting the rowheight. I m assuming...

Using MS Access: Determine DoCmd.SetWarnings state, toggle, btw
toggle, btw: Just change it back, its not a toggle. DoCmd.SetWarnings True Will restore warnings whether its currently False or True. BTW, If you are doing this around a DoCmd.RunSQL method, I would switch to: CurrentDB.Execute strSQL instead. That doesn...

Using MS Access: Determining min/max values, lcd monitors, maximum values
lcd monitors, maximum values, text boxes: Part of this may be structural issues. i would probably have one test results table like this: tblTestResults ResultID (PK Autonumber) MonitorID (FK) SubtestID (FK) Result You can then use on form that would be filtered for the test you want to perform....

Using MS Access: Dev eloping a report; bring into Word, word command, command button
word command, command button, ms word: Hi Kathy, You can customize the toolbar and put the command button for Publish It with MS Word in the toolbar. Just in case you need help with this, right click on the tool bar and select Customize. Then select the Commands tab and highlight on Tools. You...

Using MS Access: Developing a printer billing system, printer billing, pc workstation
printer billing, pc workstation, system interface: Hi Ratidzo This potentially is a substantial system.... :-) Some questions: 1. Is the printing to be done exclusively from an Access application, or is this generally anything that can be printed from a PC/Workstation ? 2. Does the system interface...

Using MS Access: Developing a printer billing system, printer billing, swipe card
printer billing, swipe card, printer locations: Any database would be sufficent for keeping track of payments. The biggest problem you have is data input. You need to determine how usage will get into the system. Are there multiple printer locations? Will there be a person to check the accounts and process...

Using MS Access: DIfferent subforms, patient id, primary key
patient id, primary key, hth: What you want to do is possible, but its not the way I would do it. You should not separate tables for each disease. You should have one table with a field identifying the disease. You would then bind your subform to a query that filters for the disease chosen...

Using MS Access: DIR function, loop through, hth
loop through, hth, array: The DIR function only returns the first file that matches the criteria in the function. What you would have to do is somehow read the files into an array or a table. One way to do this is to create a TEXT file using the DIR command and then import that file...

Using MS Access: Dialog box keeps appearing, detail section, control source
detail section, control source, cumulative total: Every control has a name. You can then set properties of the control in an event. I m not sure of the event you need to use here, it might be the On Format for the Footer band. But using the code Builder you can add code: Me.controlname.ControlSource =...

Using MS Access: Dialogue Box, vstyle, vba
vstyle, vba, damian: Basically I ve sent you some VBA that you can use to acomplish your task - ***Your Code Here*** means that you should insert the VBA you require to complete your task. Sorry If this is confusing, I notice your trying to use a macro - unfortunately I don t...

Using MS Access: Difference Between Excel and Access, project london, basic principle
project london, basic principle, db design: Hi I m afraid that would prove quite difficult as Excel and Access are very different beats and used in a vast array of situations. The basic principle behind a database (and you can view EXcel like this as well) is to write once and read many times....

Using MS Access: Different command functions from XP to 2003, costly exercise, query tables
costly exercise, query tables, command functions: Those problems are not an issue of changes in Commands. Date(), Trim() and Format(fieldname, Currency ) are all valid commands in all versions. These problems usually result from a missing reference. Press Ctrl+G then go to Tools References and check for...

Using MS Access: Different search criteria's, value boxes, search component
value boxes, search component, recordsource: Hi At a basic level you can use filter by form - see Access help for details. If on the other hand you want to build something that is unbound from the data and more like a search form - you will have to build this yourself. A decent search form in...

Using MS Access: Difficulty with Number Fields in Access 97, long integer, datatype
long integer, datatype, datatypes: You need to lookup Datatypes in Access Help. A Number datatype has several variations. When you select Number as the datatype it defaults to Long Integer. Integers don t hold decimals. If you want to store values with decimals you need to change it to a Single...

Using MS Access: Disable mouse scroll wheel, scroll wheels, microsoft newsgroups
scroll wheels, microsoft newsgroups, mouse wheel: Well, one answer to that is to not create forms that require scrolling. Generally I always create my forms so that there are no scroll bars (except for continuous forms). If I need to fit more data I ll use a tab control. But to answer your question, I...

Using MS Access: Disable Security Warning Automatically, macro security level, security precaution
macro security level, security precaution, access tutorials: Again, I m using Access XP (2002) here, and I don t get that error message... but I don t believe there is a way to bypass that security message. Microsoft became very draconian with their macro security after Office 2000 was released. I think it s a security...

Using MS Access: Disabled Fields In Forms (Access 97), naming conventions, control source
naming conventions, control source, current records: You have the right idea but the wrong method. You can t do this in the ControlSource. The Controlsource is used to determine what value is displayed in that control. What you want to do is a processing issue and should be run from an event. In this case the...

Using MS Access: Disabling SHIFT key on opening database, microsoft jet database, microsoft jet database engine
microsoft jet database, microsoft jet database engine, jet database engine: First, is the database split between back end (tables) and front end (everything else)? If this is to be either a multi-user application or one supported by someone other than the user, it NEEDS to be split. And sincve ther is no real need to convert to MDE...

Using MS Access: Disabling scroll, ms access 2000, mousewheel event
ms access 2000, mousewheel event, mouse wheel: Hi Athalia, That scrolling thing sure is annoying. Could be dangerous even. It would help to know what version of MS Access you are using. I am still using MS Access 2000 and there is a fairly simple solution you can implement. Download this zip file...

Using MS Access: Display current year on report in text, report header, answer format
report header, answer format, yyyy: My fault, was rushing that answer. Use: Format(NOw(), mmmm ) The reason why it kep showing January was because it evaluated the Month first which returned 5. Access treats dates as a serial number counting from 12/30/1899. So 5 would be Jan 3, 1900....

Using MS Access: Display data from combo box, city state zipcode, street address city
city state zipcode, street address city, unbound controls: You would use the DateAdd function to add a time interval to a date. Note: you would NOT store this value. As a general rule we do not store calculated values. So there is no need for a field in your table. Youy can just set the ControlSource of your textbox...

Using MS Access: Display first default values, recordsource, question thanks
recordsource, question thanks, text boxes: ahhh, OK, you are still using the wrong syntax. Again, if you are running this code from Form_Find3, then you can just use: [COMPANY NUMBER] = & me.cboItem otherwise use: [COMPANY NUMBER] = & Forms!Form_Find3!cboItem Hope this helps, Scott...

Using MS Access: Display a logo via a command button on access form, command button, click logo
command button, click logo, dialog box: Hello Tina - I think I know what you are trying to do. For this solution, I am making the following assumption about your Db - 1) Your table has an OLE field to accept the image 2) The name of the OLE control on your form is LOGO (replace LOGO with...

Using MS Access: Display numbers to text, microsoft access, thousand dollars
microsoft access, thousand dollars, microsoft: Access does not have a built in function for that, but if you go to www.mvps.org/access, I think they have code for a custom function that will do it. Hope this helps, Scott Please don t forget to provide feedback for this response (either by rating...

Using MS Access: Display Only Top 10 Values in a record, ot hours, period jan
ot hours, period jan, earners: Hi Colin, One way is to use a nested query: So the outer query is the query for the actual report you are trying to produce, and, the inner (nested) query constrains the employeeIDs to those who are in the top ten for o/t. So lets say my employee...

Using MS Access: Display options for tables, normalization rules, seperate tables
normalization rules, seperate tables, tick: From your description you database may not be designed correctly. It would appear you have a repeating group. You should not have separate fields for the options, but one field in which the option is selected. This is a violation of normalization rules. ...

Using MS Access: Display Record Horizentally in Access 97 Report, attendance report, crosstab query
attendance report, crosstab query, access 97: Sanjib Hi, You should build up a final reporting query using a sequence of queries, each of which adds an element to the final recordset Qry#1 can be based on the table and can add a field called NumDays : SELECT Emp_Roll, Emp_Name, [Working Date],...

Using MS Access: Display table items on a form, blank rectangle, 2 my question
blank rectangle, 2 my question, thumb drive: On form #2, when in the properties for the control (text box) if I am at the control source & select the LOV I see nothing That s your problem, you have an unbound form. Therefore the ControlSource isn t found and you get #Name?. If you go into the Form...

Using MS Access: Displaying 2 tables in one form, parent child relationship, lisa sims
parent child relationship, lisa sims, 2 ways: Hi Rowan, I would suggest using the subform method. Instead of setting up a parent/child relationship where the forms are bound to the tables, I would use one unbound form that acts as the container that holds one unbound table information and place a subform...

Using MS Access: Displaying data in the same field same tablein a string text, query expression, thing1
query expression, thing1, printing purposes: Ok first problem is you need to use quotes to identify the table. Second, I did exactly what you did and the problem is your sample doesn t work with this data. You would need to have a table with multiple categories, then to group on that category. Finally,...

Using MS Access: Displaying data in subform, recordsource, requery
recordsource, requery, microsoft access: Create a query with criteria for the date field to be: BETWEEN Forms!formname!enterDateFrom Forms!formname!enterDateTo Use that query as the recordsource of the subform. Add a Requery of the subform, somewhere. Where depends on your worklflow. Hope...

Using MS Access: Inserting data into table based on criteria, command button, case status
command button, case status, place thanks: You should not be doing this. As a general rule we do not store calculated values. In this case Status is a calculation since its based on a comparison of the other 2 fields. If you want to DISPLAY the status, you can do so at any time. You can set a control...

Using MS Access: Displaying Images in Access 2003 Reports, dear peter, valuble
dear peter, valuble, northwind: Dear Peter, The way i m using is to create a Image on the report and then create VB code to change the Image for each record. The code look like this: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Len(Me.Series) 0 Then...

Using MS Access: Displaying jpg in Access 2003 form, speed issue, image object
speed issue, image object, current event: I m not sure what you mean by dialog box. DO you mean there is text where the jpg should be, saying that its loading? Nothing that you have to click past right? What I would suggest, in this case, is adding a simple refresh code to when you go to a...

Using MS Access: Displaying the Missing Records in a Table, missing records, click and drag
missing records, click and drag, two tables: Dear Travis, Yes this is very much possible, create a new query and add the two tables to the query, if the two tables didn t joined automatic, click and drag field ClientID from the one to the other table and accept the defaults. Example: If table...

Using MS Access: Displaying ONLY highest date value in query, key field, quandary
key field, quandary, hth: Two points first. You should also have a Primary key field in the table and you shouldn t use Date for an object name, since its a Reserved word in Access. The following article describes how to create a Top N Group By query. So you can Group by ID sort...

Using MS Access: Displaying query result in sub-form, parameter control, query result
parameter control, query result, parameter value: A couple of ways. You can use a subform bound to the query then use the button to refresh the form. Or you could use a continuous form bound to the query with your parameter control and the button in a header. Again requerying the form once the values are...

Using MS Access: Displaying related records in another table, lisa sims, related records
lisa sims, related records, table structure: Hi Jeff, You can use the Dcount function but you just have to specify the matching criteria (matching field used to link the parent form with the subform) for the form and subform relationship. The control (a textbox that is noneditable)will need to display...

Using MS Access: Displaying variables in a report, three tables, time range
three tables, time range, checkboxes: Your database is not normalized properly. It it was this would be a snap. You should have at least three tables here; tblPatients with patient personal data, tluProcedures to lookup and select possible procedures and tblPatientProcedures to record what procedures...

Using MS Access: Distinct Count, row header, crosstab query
row header, crosstab query, primary key: There are a couple of ways to do this. The way I would do it is with a crosstab query. Set the Referral type as the Column headers and Name as the Row header and then count of the primary key. Another way would be with a GroupBy query, grouping first on...

Using MS Access: DLookUp function, vlookup in excel, query wizard
vlookup in excel, query wizard, record comparison: First, you never do that. Thje whole rationale behind relationsl databases is that date should exist on ONE place only. You shouldn t be adding fields from one to to another. Second, I don t see why you are using a DLookup. There is a query wizard to fdo...

Using MS Access: DLookUp - MS Access, dlookup, column count
dlookup, column count, combobox: I wouldn t use a DLookup for this. Use a Combobox to select the BidID. Add the columns you want to fill in to that query. Then set the ControlSource of those combos to: =[cboBidID].Column(x) where x is number of the column containing the info you want...

Using MS Access: DLookUp, employee lastname, supervisor name
employee lastname, supervisor name, case description: I m not sure I follow your structure here. Your tblRawMaterial looks fine. But what you should have inthe other two are: tblCaseBOM CaseBOMID (PK Autonumber) CaseTypeID (FK) RawMaterialID (FK) Quantity UnitCost tblCaseType CaseTypeID (PK Autonumber)...

Using MS Access: DLookup and 2 If statements how to put it together?, dlookup, all ears
dlookup, all ears, access 2000: Try this: =IIF([DryMatter] = T AND [PackCategory] = ENMB , JPMB , IIF([DryMatter] = T AND [PackCategory] = SNSL , JPSL , DLookUp( [ZILPacktype] , [PackConversion] , [MATPacktype] = & [PackCategory] & )) If that doesn t work. I d try a Custom...

Using MS Access: DLookup Error in Access Reports, employee details form, numeric value
employee details form, numeric value, access 97: It should be: =DLookUp( [Employee Info]![LastName] , [Employee Info] , [Employee Info]![ID] = & Forms![Employee Details Form]![ID]) The way you had it, access won t recognize the ID as a variable. hence resulting in an error. I m assuming ID is a...

Using MS Access: DLookup and IIF Formula combined, notinlist event, dlookup
notinlist event, dlookup, customer table: First, its not good practice to use spaces in object names. Use underscores (ex: Current_Agent_List) or mixed case (ex: CurrentAgentlist) instead. Second, If the trading name is not re-existing, don t you want to add it? Normally, what you do is use a Combobox...

Using MS Access: DLookup Question, command button, time error
command button, time error, hth: If CustomerProjectmanager is a text filed you need: strDLU = DLookup( [CustomerProjectManagerEmail] , tblCustProjectManager , [CustomerProjectManagerID]= & Me.CustomerProjectManager & ) You do not need the value pproperty. HTH Scott ...

Using MS Access: DLookup for textbox control, control source, control question
control source, control question, column count: OK, If you are using that Rowsource, then you can use a textbox for PartType and use the same controlsource. I suspect the Rowsource for parttype uses the parttypeID as the bound column which would result in a blank combo. Hope this helps, Scott Microsoft...

Using MS Access: DLookup, krystel, field names
krystel, field names, primary key: Krystel, The best way to do this is to use the dMax function as part of your filter. i.e. an example would be: DLookup ( FieldName , TableName , IDFieldName = & DMax ( IDFieldName , TableName ) This is presuming you have an autonumber as your...

Using MS Access: DlookUp Error, data type mismatch in criteria expression, type mismatch
data type mismatch in criteria expression, type mismatch, mr geoff: Hi Krystel Try.. VoyageExists = Nz(DLookup( [ID] , Delivery , [Delivery.VesselName] = _ & CStr(Forms![Inputs]!cbVesselName) _ & And [Delivery.ArrivalDate] = # & _ CDate(Forms![Inputs]!txtArrivalDate) & _ # And [Delivery.ID] & _...

Using MS Access: DlookUp, data type mismatch in criteria expression, type mismatch
data type mismatch in criteria expression, type mismatch, dlookup: The error indicates you are trying to comapre 2 different data types in one of your criteria expressions. You need to make sure the data types on both sides of the comparison are the same. Its probably in the Date. If ArrivalDate is a Date/Time field it needs...

Using MS Access: Dlookup, vba procedure, bbbb
vba procedure, bbbb, dmax: Hi Krystel How about using DMax() to look up the ID of the record concerned nested inside a Dlookup() to get the attribute information you need.. eg: sVar = DLookUp( [YourVar] , YourTable , [RecID] = & DMax( [RecID] , YourTable , [Aaaa] = AAA...

Using MS Access: Dlookup Date Error - returns Null, iso date, new trouble
iso date, new trouble, inventory details: If you are using Date/Time fields then Access stores the dates as a serial number counting from 12/30/1899. So how a date is displayed is a function of formatting. By using the # sign you should be telling Access you are comparing a date. HTH Scott ...

Using MS Access: Dlookup woes, cmd button, best route
cmd button, best route, eid: You re right, it does look like it should work, so there must be something else going on there. Is the forms Source set to the query Requester ? Did you spell it the same in both instances? (Code and Object name.) Are you sure this is the best route...

Using MS Access: Dlookup, dlookup, thanks in advance
dlookup, thanks in advance, occurence: Hi If you re looking for a specific row with a DLookUP you can use the conditional argument of the function. I.e. Suppose I want to retrieve the row with the id=10 DLookUp( somefield , sometable , [id]=10 ) The default action of picking up the...

Using MS Access: DOS Copy cmd from Access Query, dos copy, access query
dos copy, access query, move command: Hi Why don t you use VBA? You can copy the files very simply. FileCopy C: est.txt , c: est1.txt This statement copies test.txt from the C: drive to a new file called C: est1.txt Using macros severely limits your options otherwise....

Using MS Access: DoCMD syntax, network login name, openreport
network login name, openreport, control source: Andrew Hi, 1. The filter is equivalent to the WHERE predicate in an SQL statement, so NO, ordering data via the filter is not possible. To order the data in a report - us the reporting Groups - select the columns required and ascending / descending....

Using MS Access: Document, datatype, table design
datatype, table design: Jane, Not sure if this is what you are looking for, but try the following: Tools Analyze Documenter and choose the table you want. The default is to show the Name, Type and Size. If you wish to show the Description, you will have to go into...

Using MS Access: Documentation, sql server data, code snippets
sql server data, code snippets, report query: I m not sure what you need to document. For a user manual use a screen capture utility (like Snagit) and liberally use screen shots to the various options a user has with information on hoe to fill it forms or select options. For documentation, write an...

Using MS Access: Documentation, sql server data, structure layout
sql server data, structure layout, overview document: Hi Mo, I take a fairly practical approach to documentation. Who is it for and what benefit will they get from it. In the case of Access, I tend to have an overview document which decribes the application, where it gets its data from, how it fits into...

Using MS Access: Documentation, data transformation service, sql server data
data transformation service, sql server data, target database: Hey MO - Progress documentation is as diverse a creature as is possible. In lieu of any existing procedures, the single most important issue to remember is line data accountability . You must end up with just as many records on you SQL table as you started...

Using MS Access: Dos Copy file renaming with Query field., dos copy, copy c
dos copy, copy c, file renaming: Brian Hi, You can use the regular file handling commands for copying, deleting (Kill) and renaming commands for file management. If you need to append two files programmatically, try using family of binary file commands - OPEN, READ, INPUT, WRITE etc....

Using MS Access: Double report, reciept, easy solution
reciept, easy solution, half page: Neither an easy question and definitely not an easy solution. You would have to size the report so its no more than 5/5 high (or whatever a half page is for you). Then check support.microsoft.com. I beleive they have an article for printing x copies of the...

Using MS Access: Downloading Office 2003, pirate copy, thanks for your time
pirate copy, thanks for your time, fraction: Hi No I don t think the software is legitimate. Generally when somethings too good to be true it is! Without the mauals or discs - what are you going to do if you run into trouble? Also, if it s not legit, how are you going to do updates vai MS? They...

Using MS Access: Drawin information from multiple tables using checkboes to a report, test table, test record
test table, test record, test product: Ok, let me see if I understand this. The following describes each result: Test Product Box Parameter Result Time If that s correct, you should not have tables for each test. You need at tables like these: tblProducts ProductID (PK Autonumber)...

Using MS Access: Drop Down Boxes and Queries, drop down boxes, new values
drop down boxes, new values, nate: Hi Nate Use the After-Update event on both combos 1 & 2, and reset the rowsource propoerty of the 3rd combo based on the selected value. Eg for combo #1 Me.Combo3.Rowsource = SELECT .... FROM .... WHERE [xxx] = & Me.Combo1.Value & Me.Combo3.Requery...

Using MS Access: Drop-Down List Configuration, lisa sims, hi tim
lisa sims, hi tim, combo box: Hi Tim, You are using the BoundColumn property correctly because it tells Access which column in the combo box holds the value that is to be associated with the control. This property holds a number that represents a column in the row source, starting with...

Using MS Access: Drop Down Menu, access tutorial, president ceo
access tutorial, president ceo, video lessons: I ve never seen this happen before. By drop-down menu I assume you mean a COMBO BOX? You re saying the data is in the box, and it s selecting a value but not SHOWING the data? That s strange. Does it behave this way if you try using the database on another...

Using MS Access: Drop down search showing two fields, doe john, docmd
doe john, docmd, combobox: I m not following this. If you are using a combobox to search for a specific record, use the Combobox wizard and select Search combo (3rd option). That will generate the code for you. The form will then show the record. If what you are asking is to show...

Using MS Access: DropDownList in MS access, notinlist event, dropdownlist
notinlist event, dropdownlist, access help: Depends on what the combobox is pulling the values from. If its querying the field you are saving the data to, just set the LimitToList property to No. However, if its querying another table you can use the NotInList event. See Access Help for an explanation...

Using MS Access: Dropdown box within Query, onchange event, inputbox
onchange event, inputbox, combobox: Hi Richard! Do you mean run the query after the user selects the info from the dropdown box? So your combobox would be on a form and then the query would be run from the OnChange event in the combobox? Otherwise the only type of input you can have when...

Using MS Access: DSum Calculation, target number, time donors
target number, time donors, fundraising database: You could be getting an error for a number of reasons, but to follow the format of the existing DSum, adding your own, I d try: =nz(DSum( AmountPledged , Pledges ))+ nz(DSum( One Time Amount , Contributors )) But really, I don t know what the error is,...

Using MS Access: DSum syntax, iterations, thnx
iterations, thnx, syntax: If the textbox is on frmParts this should work: =Dsum( [QTYReceived] , tblPurchaseOrderDetails , [PartNumber] = & [txtPartNumber]) txtPartNumber would be the name of the control on the form that is bound to the Partnumber. This is assuming that Partnumber...

Using MS Access: DSum, query design, field 3
query design, field 3, buds: Carla, Many thanks for the question. What you need to do is to create a Totals Query - to do this, build a query as you usually do, adding the PotatoID field and the Bud Field. Then, with the query in design view, click on the Totals button on the toolbar...

Using MS Access: Dublicate data., ms access database, information client
ms access database, information client, duplicate records: First I would NOT use name as a PK. names may not be unique and joins perform better with numerical datatypes. What exactly is being duplicated. You may need to set your queries to only return Uniques values. Hope this helps, Scott Microsoft Access...

Using MS Access: Duplicate last record in subform, detail record, detail records
detail record, detail records, redundant data: You should kick off the Append query while the record you want to dup is selected. Include a WHERE clause that restricts the append to the PK of the selected record. Again, if you are duplicating most of the record, then there may be something wrong with...

Using MS Access: Duplicate records - removing, query wizard, duplicate records
query wizard, duplicate records, dups: Use the query wizard to create a find duplicates query. You then write the dups to a tempfile. After deleting all the duplicated records, you append from the tempfile using Select Distinct so you get one record for each dup. Hope this helps, Scott ...

Using MS Access: Duplicates --- marco not working, ticket number, msgbox
ticket number, msgbox, rosemary: Dear Rosemary, The DLookup line is wrong it must look like this: DLookUp( [Ticket Number] , [Open Closed Tickets] , [Ticket Number]= & Form.[Ticket Number]) Is Not Null If Ticket Number is text then it should look like this: DLookUp( [Ticket...

Using MS Access: Duplicates in a Query, peterson street, decent introduction
peterson street, decent introduction, address thanks: You can set the Group By option in queries by clicking View on the toolbar and then Totals . Once you do this, you ll notice there is a new Totals option for each field in the query. However this will only hide duplicates if all fields match. For...

Using MS Access: Duplicating/Copying Records, customer activity, three tables
customer activity, three tables, b product: If the only thing that changes from month to month is the dates, then you need to redesign your database. Have a parent record for the Activity with info about salesperson etc, whatever remains the same from month to month and then a monthly record with the...

Using MS Access: Duplicating data to different tables, table applications, e mail
table applications, e mail, error message: If you are getting a Debug option there IS an error message. It will state that an error has occured with some sort of explanation and asking if you want to Debug or End. Clearly there is something wrong with that statement, but I don t have a clue as to...

Using MS Access: Duplicating the primary key Error, error value, random generator
error value, random generator, fld: Hi Brown I ran a little test and found the error number to be 3283 Here s the code I used - please check that this is what you are trying to do.... Public Sub TestPrimaryKey() Objective - find the error number if you try to insert a second primary...

Using MS Access: Duplicating Records, jack sprat, temp table
jack sprat, temp table, raw product: That doesn t preclude the structure I ve outlined. You want to minimize data entry. You do not want to have enter the same info repeatedly. What you need to determine is what info is specific to the delivery and what info is variable. You may even need to...

Using MS Access: Duplicating Records, table structure, thanks in advance
table structure, thanks in advance, many thanks: You may want to review your table structure. If you have to duplicate whole records then your database may not be normalized properly. But its fairly easy to do what you want. You just need run a For...Next loop for x times that you need add records. ...

Using MS Access: Duplicating a report, parameter query, customer query
parameter query, customer query, scatch: Yes so far so good. But its not clear what data is stored in the combo. Can you copy and paste the Rowsource of the combo into a note? That will tell me specifically how to link what s selected in the combob to filter the report. Basically, you need to put:...

Using MS Access: Duplications!, johnny doe, doe john
johnny doe, doe john, query wizard: The problem here is whether the names are exact duplicates or not. For example, someone might have entered John Doe, someone else might have entered Johnny Doe. The first thing you should do is use the Query wizard to build a Find Duplicates query. Once...

Using MS Access: Dynamic Access Reports, report wizard, dynamic access
report wizard, dynamic access, processing system: This should be pretty simple, just build a query with the results you want, and use the report wizard to build the report. After the report wizard is done, you ll probably want to make sure the pertinent information is ina section you can force a new page...

Using MS Access: Dynamic Checkboxes, strsql, insert into table
strsql, insert into table, dupe: What you want to do is build and execute a INSERT INTO statement within your Loop something like this: Dim strSQL As String For Each.... strSQL = INSERT INTO table (field) strSQL= strSQL & VALUES( & ItemSelected(i) & ; CurrentDB.Execute...

Using MS Access: Dynamic Combo Box Information, pertinent fields, dynamic combo
pertinent fields, dynamic combo, field marketing: Dear Michael, You can pass the Marketing ID to a another form via the Openargs method, button code like this can do the trick: Private Sub MyButt_Click() Docmd.Openform MyNotesForm ,,,,,acDialog,Me.Marketing_ID End Sub In the MyNotesForm create...

Using MS Access: Dynamic data used to create static historic tables, static table, history tables
static table, history tables, dynamic products: Yes some data is dynamic and not static and this is important in tracking. But there is a degree of importance here. For example, it matters greatly that a price changes, you don t want recall an order record using a current price. On the other hand, it matters...

Using MS Access: Dynamic filters, general programming concepts, dynamic filters
general programming concepts, dynamic filters, dynamic filter: The RowSource of your listbox is a query. In that query set the criteria for the company name column to: =Forms!formname!controlname where formname is the name of your form and cotnrolname the name of the control holding the value you want to filter...

Using MS Access: Dynamic Links to External Images, run time error, external images
run time error, external images, image path: Hmm, I just downloaded the same file and unzipped all the files into the same folder and it opened with no problem. But the code is really simple. Like I said, just use the ON current event and assign the value of the Path control to the Picture property...

Using MS Access: Dynamic SQL returns result to FORM, recordcount, returns error
recordcount, returns error, textboxes: Well, the point is that using unbound forms (and yes not having a recordsource means unbound) is very VBA intensive. With unbound forms the developer has to code all I/O between the tables and forms. You can set the Allow Edits property for a form to No...

Using MS Access: Dynamic text box name, array storage, dynamic text
array storage, dynamic text, textboxes: Hi Nagy, Here is a procedure I have built to access all the textboxes on a form. To make this work, just edit the form name, open the immediate pane and run - you should see a list of textbox objects in the form concerned Once you are inside the qualified...

Using MS Access: daily attendance keeping, record absences, option group
record absences, option group, record attendance: Generally I recommend that one not record Attendance, but record absences. So a record is added only if someone is not present. The absence of a record for that date is evidence of being present. However, to do it the way you want requires an option Group....

Using MS Access: to get the data from the db into form, text boxes, exact solution
text boxes, exact solution, combo box: Hello Radhika - The information you supplied to me is a bit too vague for me to give you an exact solution. What I have done is to create a small Access db (Ver. 2003) for you to look at. to get this example, follow the instructions below: Go to WWW.dropboks.com....

Using MS Access: data designing, object names, employee id
object names, employee id, hth: You should have at least two tables. An Employee table and the Absence table. I would also add a lookup table for the types of Absence. Something like this: tblEmployee EmployeeID (Primary Key Autonumber) FirstName LastName other info about employee...

Using MS Access: data entry form, e mail address, database programmer
e mail address, database programmer, normalization rules: First, name is not a good choice as a primary key. You should add an autonumber field to each table and set that as the primary key. The LicenseeID field in the results table should then be a Long Integer datatype. Second, Once that is done, you can use the...

Using MS Access: data find, query wizard, microsoft access
query wizard, microsoft access, calender: I would suspect you need more tables. But I would use a mainform/subform to display the data with the mainform bound to the employee info and the subform to the training info. To find the employees without training, you willneed to run an unmatched query....

Using MS Access: data format, decimal portion, programme access
decimal portion, programme access, integer portion: Access stores date/time values as a double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion is a fraction of a day (ex: 6AM or 6 hrs = .25) What makes you think 8/9 shows as August? European (UK)...

Using MS Access: data import from excell, column headings, step guidance
column headings, step guidance, blank rows: Hello L Fried, The first step is to prepare the Excel spreadsheet ready for linking into Access. Preparation includes the following: 1) Row 1: Contains Column Headings. I prefer to NOT use spaces, so my column headings are formated like: CustomerID,...

Using MS Access: Putting data from multiple sources in one report., snapshot format, redundant data
snapshot format, redundant data, easy solution: Without seeing your data structure its really hard to tell. I would experiment with queries to see if you can get all the info into one query or as few queries as you can. Don t worry about redundant data in the queries, you can eliminate them using grouping...

Using MS Access: data normalization, relational database systems, abc inc
relational database systems, abc inc, quesions: Hi Flea The primary objective of normalisation is to breakdown information into a series of tables that have: 1. A primary key (PK) that uniquely identifies a row of data 2. A series of attributes that depend totally on the PK, and 3. No interdependencies...

Using MS Access: Take a data from table in a form, control source, column number
control source, column number, scheme name: Make the control source of your textbox like this: =[ComboBoxName].[Column](1) The columns in ComboBoxes are numbered, starting with 0. If your ComboBox has 2 columns, one for the ID (whether it is visible or not), and another for Name, the column number...

Using MS Access: Getting data from tables using sql, sql string, rooms booking
sql string, rooms booking, room numbers: Adrian Hello, I assume the Bookings table (I will call tblB) has the following columns - RoomID (Room Number), FromDate, ToDate (these are both Date datatypes) I will also assume the Room table is called tblR, with a primary key of RoomID. So lets...

Using MS Access: Getting data from tables using sql, atabase, sql string
atabase, sql string, rooms booking: This is an extremely difficult task. Frankly I m not sure if I can help. A booking atabase like this is one of the most difficult to develop. The problem arises in that rooms can be booked for different lengths of time. The only way I might be able to...

Using MS Access: data type conversion, control source, type conversion
control source, type conversion, exceptionality: No, forget what I said before. Just start with the subform. It should be linked to the combobox which is storing the Except_ID. If you still can t get this to work, you can send be a copy of the database to dafiles@optonline.net and let me take a look....

Using MS Access: data type in tables, transaction section, transaction form
transaction section, transaction form, decimal numbers: If your using a text field then you must place quotes arounf the expected value. ie If X = 10 then ##do somthing## You should be using number, either Long or Integer dependant upon the range of numbers expected. You should also set the decimal...

Using MS Access: About data types in Access, conversion function, datatype
conversion function, datatype, foreign key: Change the datatype of the foreign key. That s what you should be doing anyway. If you can t change the datatype of the FK (because its an external table or some other reason) then create a query on that table. In the query, use a conversion function to change...

Using MS Access: database, yard trucks, personal folders
yard trucks, personal folders, myevent: Sue, Many thanks for the question. I have recently done this for one of my clients. The code below will import a task and a calendar event into Outlook. I am not sure of your knowledge of VBA, so if you need further instructions then let me know....

Using MS Access: database design, table database, access file
table database, access file, capslock: While I sometimes do allow people to send me files, I prefer to try and work through the problems in correspondence first. Give me a description of your database and your specific questions and lets see what we can work out first. I can tell you that when...

Using MS Access: database design, dbase tables, networked drive
dbase tables, networked drive, database in access: In EVERY situation where you have either a multi-user database or a database supported by someone other than the user, you NEED to have a split structure. This is called a Front End/Back End. The Back end contains just the tables, the Front End contains...

Using MS Access: database design 2000, selection choices, validation rules
selection choices, validation rules, combobox: Hi Col, Normally we use a combobox to select the choices on the data entry form. You can create the combobox using the Toolbox, and while creating the combobox, it will let you enter the 4 values you want as selection choices. Check the Help pages for...

Using MS Access: database design - adding a new field to a query?, unique records, existing records
unique records, existing records, workshop session: Justine hello, You are nearly there, just maybe a couple of mods... tblWorkshop has a 1-M relationship with tblSession these two tables define the structure of the generic workshop that could be run anywhere in the world any time (given the...

Using MS Access: A database design approach, customer areas, customer name
customer areas, customer name, customer company: Hi John You will need a UNION query compbining two sub queries - the first will be those cust/areas from Table1, not in Tables 2 & 3, and the second all customer / areas in Table 3. Here is the SQL - you will need to weed this so that it ties in with...

Using MS Access: database design, code segments, memo field
code segments, memo field, source program: ANY database can do the job as long as it supports a memo field. You will need to output your segments into a text file that can then be opened or imported into your programming environment. I doubt if you can get something that can be compiled right out...

Using MS Access: database form design problem, firstname lastname, expired membership
firstname lastname, expired membership, mouse wheel: As I started to read your question, the thing that came to mind was you should be using a visit table rather then a last visit date. And then I got to the point where that s what you wanted to do. You should have at least 3 tables here (probably more)....

Using MS Access: database normalization, ms access database, street address city
ms access database, street address city, state zip code: Your schedules table should look more like this: SchedID (Autonumber, PK) EmpID (FK) Day Shift StartTime StopTime So a record might look like: EmpID: 12345 Day: Monday Shift: 1 StartTime: 9:00AM StopTime: 1:00PM Hope this helps, Scott ...

Using MS Access: database problem, database problem, selection table
database problem, selection table, code description: Maria, Many thanks for the question. To do what you ask, you will need to do this in a few steps: First of all you will need to create a union query, to put all the colours into one field and the associated cost into another field, instead of the...

Using MS Access: database question, stock table, stock details
stock table, stock details, inventory stock: This is not the correct way to handle inventory. Stock on hand should be CALCULATED. As a general rule we dont store calculated values. You also have some other normalization issues with your tables. I assume, from your structure that you also have an...

Using MS Access: database question, entity relationship model, parent child relationship
entity relationship model, parent child relationship, stock keeping unit: Martin, Hi, This is a classic Sales / Inventory ER (Entity-Relationship) model. Your database schema is nearly there, lets make a few tweaks.. Customer looks good Orders needs to be split into OrderHeader OrderID PK CustomerNo FK ...

Using MS Access: I have a database of seven..., seven tables, many thanks
seven tables, many thanks, queries: Rich, Many thanks for the question. Try this: Build 7 append queries. The first append query appends the TableID to Table2 using the fields that form the one to many relationship, the second append query appends Table2ID to Table3 and so on. ...

Using MS Access: database using access, size database, bonding company
size database, bonding company, computer guy: Hello will With access you can build any middle size database that help you to manage your bussiness well. You need to build the database first in your mind and then begin to build it in the pc. You can customize Access, also, but I recommend you to use the...

Using MS Access: database window in ms access, water quality data, quarterly water
water quality data, quarterly water, database window: Check for scroll bars. I ve sometimes had the database window moved to where its off the screen and need to scroll to find it. Also try using Window Unhide to see if its been hidden. One last thing to try is maximizing a window, which will maximize all windows,...

Using MS Access: database, database in access, sum function
database in access, sum function, howa: Its not clear what you want. If you have any calcs you want to display for each row on a report, I would do them in the query the report is based on. Then you can just add the added column to your report. If you want to sum values for a group of records,...

Using MS Access: databases, ms access databases, diferent places
ms access databases, diferent places, composite keys: No, I do not recommend or use composite keys. My junction tables (as noted) have their own PKs and the join fields are FKs. Create the structure as I outlined. You join the PKfrom one toable to the FKin the junction table. That s why I namemy FKs the same...

Using MS Access: databse splitting, database window, pc question
database window, pc question, menus: Question 1. What s the question? I see a statement of the preferred way to deal with a multi-user database. The back end goes on a shared drive, the front end on the local PC. Question 2. Nothing is going to stop a knowlegeable user from poking around....

Using MS Access: date, input mask, access tutorials
input mask, access tutorials, 599cd: Have you tried getting rid of the input mask? How about trying a simple input mask like 00/00/00 ? Let me know if you have any more questions or comments. Cordially, Richard Rost President/CEO 599CD Computer Training http://www.599cd.com/Microsoft_Access_Tutorial...

Using MS Access: date calculation, 28days, rental period
28days, rental period, table names: Your going to need to write some code within your interface. I would put a Calculate Rent button that creates a rental record for each rental period. I would need to know more about the structure of your table to help you write the code for the (field names,...

Using MS Access: date calculation, loan repayments, loan loans
loan repayments, loan loans, due dates: Samantha, Many thanks for the question. My apologies for not replying sooner. The simple answer is Yes this is possible; but much too complex to go through it all in one answer. I will step you through it. Get back to me when you have finished each...

Using MS Access: date calculations, simple arithmetic, mod 8
simple arithmetic, mod 8, days thanks: In that case its simple arithmetic. The key is using the Mod operator to get the remainder For example: Years = INT(numdays/365) will get you the years Then Months = INT((numdays MOD Years)/30) Days = Numdays - ((Years * 365) + (Months* 30))...

Using MS Access: date criteria in queries?, c shift, 599cd
c shift, 599cd, efficency: If I understand your problem, you need to be able to generate a report based on fractions of a day... not just whole days. Access treats days as whole units, so one hour is 1/24 of a day. If you need to generate a query showing all of the data from midnight...

Using MS Access: date criteria from unrelated tables, union queries, queries in access
union queries, queries in access, three tables: You cannot join a table except on an exact match of the key fields. You could use a Union query however to report on the three table. However, in a union query each query in the union needs the same structure. Lookup Union Queries in Access help for more details....

Using MS Access: date entry as numbers, change to date format, input mask, date entry
input mask, date entry, zeros: if you use an input mask they can just type the numbers. Though they will have to type the leading zeros. The only way to do this without typing leading zeros is to use separate controls for each part of the date. Hope this helps, Scott Microsoft Access...

Using MS Access: date expressions, time value, datatype
time value, datatype, date time: Ok, so you have a record that contains a date field, holding a date/time value. The way I would handle this is to add 2 columns to your query: RecYr: Year([datefield]) RecMth: Month([datefield]) Then set the criteria respectively as: =Year(DateAdd(...

Using MS Access: date function.., query column, query field
query column, query field, blank area: No problem Lana - Go to www.dropboks.com and use donalddarracq@yahoo.com as the username and 123456 as the password. Right click on Lana.mdb and down load it. It is in Access 2003 version so if that is not your version you may have to convert it. Once down...

Using MS Access: date input mask, input mask, number ie
input mask, number ie, format option: Hi Alan, Easiest way to do that is to use the format option rather than the input mask d-mmm-yyyy will give you what you are looking for. Or you can use the dd-mmm-yyyy for a two digit day number ie 04-Sep-1948 with dd-mmm-yyyy or 4-Sep-1948...

Using MS Access: date lock, tab button, select properties
tab button, select properties, drop down list: Enrico, Many thanks for the question. Try the following: Right click on the date field, select Properties from the drop down list. Click on the Other Tab and change automatic Tab stop to NO . Hope this is of some help. Regards Julie ...

Using MS Access: Last date of Medicine, time dave, last date
time dave, last date, primary key: You will have to create an interim query that returns the primary key of the record with the MAX date grouped by resident. Then use that query as a join table in between the resident and meds table. Hope this helps, Scott Please don t forget to provide...

Using MS Access: date operations, conventional time, control source
conventional time, control source, day of the year: txtDate is the control on your form Bound to the Date field in your table. The DateSerial function requires arguments of Year, Month and Day. But you don t have that. So what you need to do is get the first day of the year represented by the date you have...

Using MS Access: date operations, dd hh, ss format
dd hh, ss format, datediff function: You need to understand how Access stores dates first. A date is actually stored as a double precision number where the integer portion is the number of days from 12/30/1899 and the decimal portion represents a fraction of a day (i.e. 6:00AM = .25). If...

Using MS Access: date parameter, date1, syntax
date1, syntax: Sorry for the tardy response but I was away for the weekend. Depends on how you are doing it, on a form, in code, a query or what? The syntax for the criteria would be BETWEEN Date1 AND Date2. But where to put that depends on where you need the calc....

Using MS Access: date picker in query, time duration, date picker
time duration, date picker, time periods: Access 2007 has a nice date picker that appears on forms with date fields. See MyAccessProgram.com for a free video tutorial on this feature. If your Access program is prior to 2007, there are many people who have created object to import into your program...

Using MS Access: date problem, e mail, add data
e mail, add data, dbl: try this: open the form in design view, go to Properties/Events/On Dbl Click and click the ellypsis (...) choose Code Builder and insert this code: DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , if this doesn t work send me a copy of your work...

Using MS Access: date problem, field criteria, square brackets
field criteria, square brackets, switchboard: So, this is what I ment: 1st query: SELECT tblHelp.Name, Max(tblHelp.Date) AS MaxOfDate FROM tblHelp GROUP BY tblHelp.Name; 2nd query: SELECT tblHelp.Name, tblHelp.Date, tblHelp.Number FROM tblHelp INNER JOIN qry1 ON tblHelp.Name = qry1.Name...

Using MS Access: date query, query design, misson
query design, misson, two tables: Jim, When you created the Totals of query, the date field that you have asked for the Maximum date of only, will have a new field name that has been created by the query, which is the name of the field with MaxOf in front of it. You need to put this...

Using MS Access: date search, strsql, recordsource
strsql, recordsource, sql statement: You ll have to, in the same code, build a SQL statement (using a WHERE clause that looks a lot like the sFilter string in your code, and set the recordsource of the list box to that SQL statement, then refresh the form. It ll look something like this: ...

Using MS Access: date time split, time fields, date time
time fields, date time, long time: you can use a third column in a form or report using a calculated field build a form with the Origin and Completed time fields add a bounded text box with this source: = [Completed]- [Origin] wright click on the text box and open its properties to set...

Using MS Access: Last date, time dave, key field
time dave, key field, design mode: Hello Dave, Sorry for the delay.. Try the following query: SELECT T1.NameOfResident, T2.Medicine, Max(T2.[Date]) As LastDate FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.PKID = T2.FKID GROUP BY T1.NameOfResident, T2.Medicine You will need...

Using MS Access: I have many dates to track, control source, apposed
control source, apposed, option 1: You certainly can identify which records have a record for DateFaxed and which do not. Instead of recognizing if it s blank, you just test for if it exists. Do this with queries. (You ll use the criteria to find the record, and if it returns 0 results,...

Using MS Access: Up dating ages automatically, dates of birth, colum
dates of birth, colum, current value: Yes, and this is why we DO NOT store calculated values. There is no need for an age field in your table. You can find expressions and functions here: http://www.mvps.org/access/datetime/date0001.htm to calculate age. You can use whatever expression of...

Using MS Access: Up dating ages automatically, dates of birth, colum
dates of birth, colum, date of birth: Hi David, Yes, you can calculate how old the person is now automatically. For that, you can have a calculated column in a query that updates the table (you will need to add the calculated field in the table) Expression for the calculated field will...

Using MS Access: dBASE case statement conversion, access vb, currency value
access vb, currency value, case statement: The Access CASE statement is not dissimilar to dBase. The syntax is: SELECT CASE value Case = x Case Else End SELECT However, you can find a module to do what you want here: http://www.mvps.org/access/modules/mdl0001.htm HTH Scott ...

Using MS Access: db design basics, monster table, design flaw
monster table, design flaw, schools of thought: Hello Val - WOW you have asked the question of all questions! There are many schools of thought as to whether linear table design is more or less effective then linked, smaller tables. So I will tell you this, My current project du jour contains one linear...

Using MS Access: dbase query, lotto combinations, dbase database
lotto combinations, dbase database, number position: You can do a group by query that groups on each number and counts the number of times they were used. You would probably need to do 6 queries for each number position then do a union query to get the totals for each number. Hope this helps, Scott Microsoft...

Using MS Access: dbl click / query, mp3 data, xp sp2
mp3 data, xp sp2, hyper link: 1) First, a Subform is actually a form placed on a mainform, not a popup. Just so we are talking the same language. So why not make this as a subform instead of a popup? Place it on the main form and link it on the appropriate key field, then you don t have...

Using MS Access: decimal places, reult, numeric fields
reult, numeric fields, decimal places: Well first, you shouldn t be storing calculated values in a table. You can use the expression to display the reult wherever needed. How the result of a calculation is displayed depends on the formatting of the control displaying it. Percent is a format,...

Using MS Access: decimal places in access 2000, decimal scale, decimal portion
decimal scale, decimal portion, decimal points: Hi, Many thanks for the question. Try the following format in the table (note if you have added this field to a form, you may need to delete and re-add it to the form, for the changes to appear) Field Size Decimal Scale 2 Decimal Points 2 Hope...

Using MS Access: decimal places, decimal places
decimal places: Are you inputting more decimal places but restricting the display? Access round it for display if you do. The underlying number will still be the number you input. Hope this helps, Scott Please don t forget to provide feedback for this response (either...

Using MS Access: declaring variable in access having ref to excel pivot item, excel application, xls file
excel application, xls file, excel sheet: Rajiv, hi, Herewith, code to open Excel and get a value Public sVar As String Declared in the declarations area. .... inside your VBA .... Dim xlObj As Object Open Excel application Set xlObj = CreateObject( Excel.Application...

Using MS Access: define # of forms, nfl games, combo box
nfl games, combo box, event id: Hi Joe First of all is all the data related? Is there someway of currently identifying what data sits with what? Assuming the above, I would be inclined to use a combo box to apply a filter that restricts the records in the form. If you have related...

Using MS Access: again why defining the primery key on table feild, everyday work, command button
everyday work, command button, microsoft access: Ok, First, are these table being made just to export to Excel? If so, then you don t need a Make Table, just use a query as the source of your export. Even if you do a make Table, if its just for export then no PK is needed. Generally, tables made with a Make...

Using MS Access: defining the primery key with vba, command button, microsoft access
command button, microsoft access, vba: Can you explain what you mean by define? Do you want to assign primary key status to a field in a table or do you want to generate a key for a record? If the first, why are you trying to do this? If the second, why not just use an autonumber? Hope this...

Using MS Access: delete, five tables, image references
five tables, image references, problem thanks: Ok Rad - next suggestion - Make the code look like this DoCmd.SetWarnings off If MSGBOX( some wording ,vbYesNo, Some Title ) = vbyes then DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70...

Using MS Access: delete items from listbox, rodrigo, step 1
rodrigo, step 1, multiselect property: Hi Listbox - Deleting Items. To delete selected items from a multiselect listbox, loop backwards through the items, then remove those where the Selected property tests True. For example, suppose you have a list box with item1, item2, item3, item4, item5....

Using MS Access: How to delete a record from a query table., structured query language, query table
structured query language, query table, action query: Hi Patricia There are 2 primary types of query a) Action query (Append / Update / Delete) b) View data query What you have done is a b) - view data query To delete a record in one of the tables, you need to determine the constraints of deleting,...

Using MS Access: delete row, vb function, ship type
vb function, ship type, rudie: Dear Zeeshah, You can t do this by query, you need to write a VB function scrollong thru the data deleting the rows occording to your criteria. The first thing you need to do is to give each record a unique primary number. So that you can delete the correct...

Using MS Access: delete row, source stream, commercial solution
source stream, commercial solution, fastmail: Shah Hello, You have an impossible task for Access or any other RDMS as the data has not been stored in 3rd Normal form (This is a prerequirement of RDSMSs) I would suggest that, if you want to persue a database solution, the first task is to split out...

Using MS Access: delete, separate tables, old hat
separate tables, old hat, key value: Keyfield is the Primary key value of the record. If you have 4 separate tables that have related records to be deleted (Not sure why you need 4 tables), you would have to run 4 separate queries, one for each table. You may be able to only do it once if you...

Using MS Access: dependent fields, dogs cats, breeds of dogs
dogs cats, breeds of dogs, field input: I m not sure I follow what you want here. I understand you are using a concept called cascading comboboxes to restrict the items shown in a combobox based on a selection in a previous combo. This is done by referencing the previous combo s results in the query...

Using MS Access: how to design db to keep track of changes, driven web application, web application design
driven web application, web application design, ms access database: Most tracking apps can use a main table like this: RecordID (PK Autonumber) DatatypeID (FK) Data So, for example if you were tracking inventory, the datatypeID would be a ProductID and Data the amount. Obviosuly you might need to add a data anf whter...

Using MS Access: design issue, infinity sign, relational properties
infinity sign, relational properties, relation ship: Hi First of all, if you re setting primary key relationships make sure they are the same data type (I.e. Number, text, etc) It s no bad thing using an Id field for your table as this is the ID for the table ropws not the data. As long as you have...

Using MS Access: design issue, infinity sign, index property
infinity sign, index property, relation ship: Krystal, If you do not use the ID field you will need another field that you can set the Index property to Indexed Yes with No Duplicates for the one to many relationship to work correctly. Sometimes you cannot do this with just one field, so you will...

Using MS Access: command buttons, command buttons, multiple files
command buttons, multiple files, hundred times: Hi I m still not with you. If you have multiple files, you ll need multiple hyperlinks. (Unless of course you are just linkng to the folder - but then - I don t see the benefits of that cause you still have to identify the files inside the folder)....

Using MS Access: designing forms, junior football club, color paint
junior football club, color paint, persons address: Hi, The easiest way to do this is to make the border of each field white. You can do this by clicking on each of the fields whilst in design view. If you hold down the shift key as you click on them, they will all be selected. Then use the line/border...

Using MS Access: How to detect the height of a field?, border property, line thickness
border property, line thickness, report thanks: I m not sure if you can detect the height of a control where the Can Grow is set to use. The only thing I can think of is to use the On Format or On Print event of the report band. In that event use something like: Dim dblCtrlHt as Double dblCtrlHt =...

Using MS Access: dialog box prompt for parameter, project numbers, onclick event
project numbers, onclick event, command button: You are missing something. When you use the multi-select property, the control doesn t store the selections in the Value property. It stores each selection in the ItemsSelected collection. You then have to loop thru that collection to retrieve the selected...

Using MS Access: dialog box prompt for parameter value, ms acess, query parameters
ms acess, query parameters, designview: I rarely use parameter queries. They are very limiting and you get prompted for the parameter each time the query is run. Instead I generally use an unbound form to enter query parameters. Basically creating your own dialog box. With an unbound form you...

Using MS Access: diff. between MS EXCEL AND MS ACCESS, database management program, calculation capabilities
database management program, calculation capabilities, spreadsheet program: Excel is a spreadsheet program. Its designed to do a lot of calculations on data. Access is a database management program. Its designed to manage the entry and retrieval of large amounts of data. While Excel has some database capabilities and Access some...

Using MS Access: why diff options, switchboard, hth
switchboard, hth, different places: First, by expand do you mean, in the Code Builder, you are type Me plus a period and getting a list of options? If so, The options will change depending on the controls used. There is no FormTitle property. So you must have a control on Form1 by that name,...

Using MS Access: How to differentiate a button, operational procedure, depressed state
operational procedure, depressed state, toggle buttons: You can use toggle buttons which will show a depressed state. You can also change the caption to differentiate. You can place a colored box behind the button and then unhide or change the back color so the button appears highlighted. Hope this helps, Scott...

Using MS Access: differentiating records in an access subform, tutor name, diary dates
tutor name, diary dates, coloured background: Roy Hi This is not one of Access s strong areas - basically, assuming your subform shows several records in form view using the Dividing Line , you can use a record counter and the Detail object to get every 4th record, however once you instigate a change,...

Using MS Access: disabling warning message prompts, true hope, command button
true hope, command button, many thanks: Hi Ron, Many thanks for the question. To do this type the following before the SQL DoCmd.SetWarnings False then do not forget to turn the warning back on again after the SQL has been run DoCmd.SetWarnings True Hope this is of some help ...

Using MS Access: disappearing data, zip code field, incoming sales
zip code field, incoming sales, rep name: Your code is fine, but the question is what is the Controlsource of the BUManager control. I assume you are using bound forms and controls. In which case the control should have the corresponding field in your table as the ControlSource. Hope this helps,...

Using MS Access: display credit hold message, run time error, dlookup
run time error, dlookup, query expression: If Bill-To-Name is text then use: If Not IsNull(DLookup( [bill-to-name] , tblCreditHold , [bill-to-name] = & Me.[BILL-TO-NAME] & )) Then You have to concatenate in single quotes to identify the value as a text string. However, you shouldn t use...

Using MS Access: display data on form from two tables, text boxes, group names
text boxes, group names, address key: If there a person can only belong to one group, then you can do this with a query. However, if they can belong to multiple groups then you should use a subform. You can format a subform so it doesn t have borders or record selectors so it can appear as just...

Using MS Access: displaying a message box after form load, declarations section, focus event
declarations section, focus event, current event: Try the On Current event, but you will need to be a little creative. Declare a public variable in the declarations section of the form module. In the On Load event set that variable to True. Then in the On Current event, test if that variable is true, if it...

Using MS Access: dividen/divisors when both a 0 values, divisors, mohsin
divisors, mohsin, andrea: Mohsin - You should only have to test if B=0. Your test already covers A B (0 is not anything) and A=B (testing B=0 would cover this). So I suggest: if(A =B,0,if(B=0,do this,A/B) Fill in the do this with what you want to happen if B=0. Let me...

Using MS Access: dividing a string in a cell, cell content, grids
cell content, grids, consistency: Hello Jennifer - The best way to do this is to create a query based on table two. Then type the following code into the field (top part) of one of the empty grids; Expr1: Left([field],7) Where field is the name of the field that contains the string....

Using MS Access: divifing one row by another, alex dear, dim rs
alex dear, dim rs, vb program: Dear Alex, I don t know what you mean by the criterias, do you want to select only certain criterias or do you want the dividing breaking on certain criterias? Both we need to write a small VB program. First create a field in tblinput that will except...

Using MS Access: division by zero If statement, integer variables, divide by zero
integer variables, divide by zero, integer c: When your program executes: abc = c / d The d value is 0 and you can not divide by zero in the Access program. It s just not allowed and will error out. To prevent error you have to prevent that line of code from executing. To do that do this: if...

Using MS Access: division by zero If statement, integer c, correct response
integer c, correct response, happy holidays: Hello Lancer - I m not sure what you are doing here but I did a test, changing up your code a little bit and got the correct response. Here is what I did: Private Sub Command0_Click() Dim b, c, d, e, abc As Integer b = InputBox( B? ) c = InputBox( c?...

Using MS Access: dLookup in report, dlookup, report header
dlookup, report header, group header: If there are more than 2 values, you could use the SWITCH function instead of IIF. Or you could use the DLookup. The quotes issue has to do with Data type. If you are comparing a text variable to a text value, the test vlaue has to be in quotes (Ex: X...

Using MS Access: dlookup, sql 2000, information user
sql 2000, information user, current show: It looks like what you have is a Many to Many relation, not one to many. A Member can go to more than one show and a show will be attended by more than one member. Your DealerShows table appears to be a Join table that is necessary to manage a many to many....

Using MS Access: dMax question, e mail account, microsoft dao
e mail account, microsoft dao, city state zipcode: Autonumbers are not necessarily sequential. If a record is deleted, a gap is left, and that number is never used again (that s not entirely true, because if the database is compacted, numbering will begin from the last used number +1). It doesn t sound...

Using MS Access: dopdown box, column count, number values
column count, number values, column widths: What you should is create a lookup table like so: tluEducation EducationID (Primary Key Autonumber) EdLevel Points In your data table, you add a foreign key field for EducationID. On your form, you create a Combobox with the following properties:...

Using MS Access: drop down / list box, access forms, stmt
access forms, stmt, drop down list: Using a multi-select listbox requires code to process the selections. Unlike a regular control, a multi-select listbox doesn t store its selections in the Value property. So you need to loop thru the selected items collection to create your criteria. This...

Using MS Access: drop down menus in view, drop down menus
drop down menus: I m not sure what you are referring to. What are you calling a view . Could you mean a query or a form? By drop down menus are you referring to comboboxes that drop down with a list of options? If you are referring to comboboxes on a form, then each combo...

Using MS Access: dsum error, target number, time donors
target number, time donors, donation type: There is a problem with your database structure. What it appears you did was add a field (One Time Amount) to your table. That was not the proper way. What you sould do is add a field for donation type. The values of this field would be Over Time or One Time....

Using MS Access: dsum error, target number, time donors
target number, time donors, fundraising database: Hello Carol - I reproduced your data - to get the sum of both tables I used this Expr2: (DSum( onetimeamount , Contributors ))+(DSum( AmountPledged , Pledges )) And to get the number of pledges from both table I used this Expr1: DCount( PledgeID ,...

Using MS Access: how to duplicate information, normalization rules, redundant data
normalization rules, redundant data, foreign key: I wouldn t do it that way. You would be violating normalization rules. There are two ways to handle this. Either add a field to your company table that flags the company as an advertiser or add tblAdvertisers: AdvertiserID (Primary key Autonumber) CompanyID...

Using MS Access: duplicate records, query1, strsql
query1, strsql, docmd: OK, This isn t easy. But here we go. 1) Create a query that makes the 7 fields you have into one, with the id_number field. That query will look something like this: SELECT Table4.AutoID, [Text1] & [Text2] & [Text3] & [Text4] & [Text5] & [Text6] &...

Using MS Access: duplicate values, ok check, long integer
ok check, long integer, teacher class: No they should NOT have the PK indication. A PK designation means the value has to be unique. And a FK in a 1:many can t be unique. They should be the same datatype as the corresponding PK. so if the PK is an autonumber then the FK should be Long Integer....

Using MS Access: duplicating a record, customer table, customer record
customer table, customer record, pkg: I see. You can use the example found here: http://www.mvps.org/access/forms/frm0012.htm In this way you can repeat only the values that would be duplicated. or you could use the code found here: http://www.mvps.org/access/forms/frm0004.htm To...

Using MS Access: duplication in reports, adam mucci, report wizard
adam mucci, report wizard, jane doe: Man I hate to just give up on you like that! The issue is - if Adam Mucci has two entries as the resource under one project title then no matter what you do the name Adam Mucci will appear twice. You can tell the textbox to hide duplicates, but in case where...

Using MS Access: dynamic reports for crosstab query problem, query parameters, followup question
query parameters, followup question, dynamic reports: Hi Rich Have you seen this article? http://office.microsoft.com/en-us/assistance/HP051874071033.aspx This gives a step by step procedure for creating a report based on a cross tab. Are there any differences in what this says and what you re doing?...

Using MS Access: dynamic SQL statement too large, string variables, listboxes
string variables, listboxes, max characters: Dear Brad, Maybe this is a silly question, if the user select everything, why do you build a SQL statement for each one of the items, if you don t put anything in the SQL statement everything will be selected? For example Select * from TableA where id=10...

Using MS Access: dynamic SQL statement too large, sql length, listboxes
sql length, listboxes, generic approach: Hi Brad, I am not aware of any SQL length limit, though it would not surprise me if there was. That aside, here are some ideas to consider.... Regarding the recordset used for the listboxes, my generic approach (including combos). If the following is...

Using MS Access: dynamic SQL statement too large, listboxes, check boxes
listboxes, check boxes, max characters: Catering to the user is always the most difficult challenge! What I would do is check to see how many check boxes they have checked. If they have more than half checked, then I would use EXCLUSION instead of INCLUSION as the criteria so instead of saying...

Using MS Access: E-mail Access Report, free snapshot, snapshot viewer
free snapshot, snapshot viewer, matter what type: There are two basic options, either use the Snapshot type or PDF. Snapshot is a built in Access export type. it requires Access or the free Snapshot viewer available from Microsoft. PDF is a more universal format, but you need to create the PDF first. This...

Using MS Access: E-mailing an access report, key column, access report
key column, access report, microsoft access: The report should be based on a query. In the query, the criteria for the primary key column should be: =Forms!formname!controlname Where formname is the name of your form and controlname the name of the control holding the PK value. The report will...

Using MS Access: E-mailing data from Access, default mail client, e mail address
default mail client, e mail address, mailing data: Hi Imre If you use a form you could do this quite easily. Using the following code... Send the mail DoCmd.SendObject acSendNoObject, , , Me.txtEmailTo, Me.txtEmailCC, _ Me.txtEmailBCC, Me.txtSubject, Me.txtBody, False...

Using MS Access: E-mailing from Microsoft Access, autoexec macro, access xp
autoexec macro, access xp, e mail: First, you can check the SendObject command for sending e-mail. Second, you can use Windows Task Scheduler to open an app at a specified time. You would then have an autoexec macro or code running in hte On Open event of a default form to run the code to send...

Using MS Access: E/R Diagram- Microsoft Access Problem/Help, microsoft access database, state zip code
microsoft access database, state zip code, institution street: In answer to your follow ups: 1. That s the tluAnimals lookup I referred to. 2. That s taken care of by tjxAdoptions. You use that table to determine exactly what pets each family has. 3. I would include that in medical info 4. Whether you call...

Using MS Access: Easy calculation but have show on table, unbound controls, control source
unbound controls, control source, original score: Like I said its a subtle but important distinction. Correct, you place the calculation as the controlsource. Not sure what you mean by fill out the textbox (whihc is a form of control) manually. Hope this helps, Scott Microsoft Access MVP 2007 ...

Using MS Access: Edit-able Report, datasheet view, feture
datasheet view, feture, report preview: Use a query to limit to a subset. You should never allow users access to tables. Open a form bound to the query. Or use a WHERE clause when opening the form. HTH Scott P.S. Yes, I would suspect that in any group you would be inthe older folks ;) ...

Using MS Access: Edit Date, visible property, network logon
visible property, network logon, data tables: I do this as a matter of routine. I add 4 fields to my main data tables: CreatedBy, CreatedWhen, EditedBy, EditedWhen On my forms, I add controls for all four and set the Visible property of them to False. The CreatedWhen control is set to default to...

Using MS Access: Edit Options, record navigation, invoice number
record navigation, invoice number, navigation buttons: Use the Filter by Form feature. using this feature, you can enter any criteria in one mor more controls and have the form filltered to only show matching records. So you could just enter the invoice number and bring up that record. Hope this helps, Scott...

Using MS Access: Edit record of a aggregate field, input interface, table desk
input interface, table desk, dear scott: You still use an Update query but you need to modify it a bit. The SQL I gave you will update on the part level but only for one product. If I follow you, you need to update several product with varying discounts. So you need to create a temp table with...

Using MS Access: Editable field based on criteria, individual records, combo box
individual records, combo box, cheers: If the box is UNBOUND and on a continuous form then any action taken on a that control will affect all records in the Continuous form. If its bound then the answer I gave you probably (I would have to see the answer) should work only on the current record....

Using MS Access: Editable fields based on criteria, editable fields, combo box
editable fields, combo box, diana: Dear Diana, The easy way to do this is to put these code in the form event called On Current each time a new or different record is current, that part that enable or disable will run and the textbox receive it s attention. Hope this help, please ask...

Using MS Access: Editable list box or maintain linked tables, combo box, category name
combo box, category name, clone: Hi Yes you re right here Jerry in as much as the form is strange to work with. It was only as recently as Access 2000 that introduced conditoinal formatting for a continous form - something Access developers had complained about for years. You re expierencing...

Using MS Access: Editing displayed data on a form after search, distinct name, bound form
distinct name, bound form, text boxes: Can I ask why you are doing it this way and not using a bound form? If you are new to Access and especially to VBA, using bound forms saves you lots of headaches. Create a form bound to your table. Use the combobox wizard to create a Search combo. The...

Using MS Access: EER Diagram, going in the right direction, mini analysis
going in the right direction, mini analysis, empty tables: Hello Jenski, I have had a quick look, and offer the following advice based on the info provided... In building an ERD, it helps sometimes to look at sub components in isolation, then bring them together after individual analysis. In this respect,...

Using MS Access: Elapse Timer, interview length, timer function
interview length, timer function, datediff function: I would use a button to close your form where the interview is taking place. This way, the OnClose event could save the time in your table, and you wouldn t need a Timer function. Just mark the time the form was opened and closed. Yes, you could use the...

Using MS Access: ElseIf using And, string data type, many different things
string data type, many different things, texbox: It could be a number of things. Is YrsCT a string data type or numeric? You are testing it as a string value. If you want to see how each line of code and it s values are being evaluated, you need some basic debugging. You can find an excellent video on...

Using MS Access: Email addresses into outlook, outlook mail, elses question
outlook mail, elses question, mail message: Liam, The short answer is yes you can do this. Note: This code will open an Outlook mail message for each email address, you will then need to go through each one and click on send - are you happy to do this? The other alternative is to use SMTP -...

Using MS Access: Email a form, three dots, many different ways
three dots, many different ways, memo line: Dear Janet, If you want to put a field in the subject line you need to run the same command thru the VB script side. Here you have much more control over the command. Instead of selecting a macro on the event, you select [Event Procedure]. Click on...

Using MS Access: Email using Access, docmd sendobject, mail body
docmd sendobject, mail body, programming side: Hi Cesar, Sorry - I am not quite sure what you want to put in the body of the email... however if you need to include dynamic data in the SendObject s stDocName then you will need to use Word to build the document then on completion and saving, run the...

Using MS Access: Emailing from Access, jmail component, bcc field
jmail component, bcc field, sendobject: As far as I know, it isn t possible to send to over 200+ in outlook at one time, and therefore it is not possible to do so in the bcc field. You could take a look at Dimac s Jmail component for email sending, it works rather good, and I have used it with...

Using MS Access: Emailing attachments from Access through Notes, docmd sendobject, email attachments
docmd sendobject, email attachments, ddress: Dear Chris, To read from a textbox you change your code to this example: strToWhom = Me.TextBox_To strToCCWhom = Me.TextBox_CC both these textboxes must exist on the form otherwise a error will appear. Also this code must exist in the form s code...

Using MS Access: Emailing embedded objects from MS Access, rich text format, pdf document
rich text format, pdf document, ms access: Hi Sam, You can send the whole table or form or query or any object in your database through email. Under the File menu there is Send To/Mail Recipient (As an attachment)menu item. You can use that to attach your object in the email. You may have to select...

Using MS Access: Emailing Individual Pages., structured query language, database in access
structured query language, database in access, sql query: SQL (Structured Query Language) is the language used to build queries. When you build a query using query designer it actually generates SQL code. You can see this code in SQL view. The coding would be put into an Access module using VBA. Creating such...

Using MS Access: Emailing reports from access, emailing reports, odd chance
emailing reports, odd chance, darracq: Hello Cathleen - by some odd chance do you work with someone named Heather? I think she is the one who asked this question earlier. What you are looking for, at least in a pure Access environment is far to wordy for this forum. I have created a sample...

Using MS Access: Employee Anniversay dates, anniversary dates, anniversay
anniversary dates, anniversay, current date: You want a custom function that will assign a range. Something like this: Public Function AnniversaryRange(dteDOH As Date) As String Dim intSvce As Integer intSvce = DateDiff( yyyy , dteDOH, Now())+ Int( Format(now(), mmdd ) Format( dteDOH, mmdd...

Using MS Access: Employee Feedback Database, attendance manager, feedback database
attendance manager, feedback database, feedback sheets: First, I gave the name tluFactors to indicates its a lookup table. The sole purpose of tluFactors is to list the different factors to be evaluated. Each record in tblEvalFactors represents the result of the evaluation for each factor in the employee evaluation....

Using MS Access: Employee Feedback (Evaluation) database, jet database engine, ms jet
jet database engine, ms jet, evaluation database: If you try adding a record to the table, you have to first select the Evaluation. If you are adding a record in a subform of the Evaluations table, then the subform should be linked in EvaluationID. If it is, then you shouldn t have any trouble adding a record....

Using MS Access: Employee/Schedule DBS, gyst, cross tab
gyst, cross tab, temp table: This is actually pretty complex. Off the top of my head, I think you would need to run some VBA code to cycle through the records and append a record to a temp table for each hour of each day. The temp table could then feed a cross tab. Hope this helps,...

Using MS Access: Employee/Schedule DBS, gyst, elegant method
gyst, elegant method, action query: This may not be the most elegant method, but it should work for what you want. Create a new query, and put the Day field in it. Then, for each hour that you want included, put an entry like this... 7am: IIf([StartTime] =#7:00:00 AM# And [EndTime] =#7:00:00...

Using MS Access: Employee/Schedule DBS, gyst, employee id
gyst, employee id, beginner level: If you are storing in a table, Employee_ID, DayOfWeek, HourOfDay, you could easily get your answer of Who is available on a given hour and day. If you do not have this table of data yet, that is your first step. If you do, it s a simple query for one day...

Using MS Access: Enable Command Button Click Event, exit routine, command button
exit routine, command button, command buttons: Dear John, In the Exit routine if that field was not populated or maybe wrong i would ask the user if he would like to cancel the total record, something like this: If Msgbox( Input field is wrong, Cancel whole Record ? ,vbYesNo) = vbYes then Code...

Using MS Access: Enable Command Button event, cancel button, vbyesno
cancel button, vbyesno, button event: How about something like this in the Lost Focus event of the text box: IF IsNull(Me.txtText) Then If MsgBox( You left fieldname blank! Do you want to proceed to next control?, vbYesNo) = vbNo Then Me.txtText.SetFocus End If End if HTH Scott ...

Using MS Access: "Enter Parameter Value" error in MS-ACCESS, ms access 2000, ms windows xp
ms access 2000, ms windows xp, rhmb: srivatsan hi, Difficult to diagnose from the info provided.. 1. Are the program versions identical on PC and laptop ? 2. What happens if you copy the Laptop .mdb file onto the PC ? 3. On the PC version, open the listboxes, and in turn the rowsource...

Using MS Access: Entering one criteria for several queries, command buttons, separate tables
command buttons, separate tables, current users: Yes, but you have some design issues. Is this a shared database so that all the tables are in one MDB file? If so there is NO reason why you need separate tables. You should have ONE table with a flag that indicates the office. You then use separate queries...

Using MS Access: Entering Data into Forms and Subforms, automatic fill-in of Link ID, bound form, license id
bound form, license id, link id: Yes, if you create a subform on a bound form, the Link control on the subform is automatically filled in with the value of the current record on the main form. One of the advantages of using subforms. Hope this helps, Scott Please don t forget to...

Using MS Access: Entering data in Forms and Subforms, combobox, table structure
combobox, table structure, input data: I think you need to explain your table structure in greater detail. It sounds like you have a Products table, then Distributors table, Retailers table, etc. Unless a product can have multiple Distributors, Retailers etc, then I wouldn t use subforms here....

Using MS Access: Entering a hidden password, timer event, hidden password
timer event, hidden password, report preview: Before we try sending me the app, try detailing the process and shoing me the code you use to verify the password. I m not sure why you are using a Timer event. You should be just opening the password form, entering the info, then testing the password and...

Using MS Access: Entering Item numbers, autonumber field, seperate tables
autonumber field, seperate tables, dear scott: It makes sense, but its not the way I would do it. I really do not see the value in numbering detail items. I can see a value in a total of items, but not numbering. However, it can be done. The way to do it involves using the DMax function. You make the...

Using MS Access: Entering info into table, john corrigan, firstname lastname
john corrigan, firstname lastname, personnel id: I strongly recommend you do NOT do this. The purpose of a primary key is uniquely identify a record. A PK has to be both unique and unchanging. People names do not qualify under either criteria. Access provides an autonumber datatype which is designed specifically...

Using MS Access: Entering a start date and finish date to find data, dateadd function, warehouse location
dateadd function, warehouse location, hth: First I would not store the next service date. If the period of time to next service date is the same, I would just calculate it using the DateAdd function. If its variable for different items, then store the period and use that in your calc. For example:...

Using MS Access: Entering values into a field based on listbox values, google search, google
google search, google, vba: Well, I can tell you you re going to need VBA code to do this. I don t have any handy, but if you do a Google search on multi select listbox you ll probably find some somewhere. Yes, it s certainly possible, but you have to programmatically read each choice...

Using MS Access: Entity Relationship Model, entity relationship model, expression builder
entity relationship model, expression builder, component group: Hello Mark You can build calculated fields in the select query, using the query builder. Then you can use these calculated fields in a form or report. You can also insert a bounded text box in a form, and put a formula based on other boxes in any opened...

Using MS Access: Epiration Date - Change Color, cdate, current event
cdate, current event, elseif: Marcus, Hi, Use the Form Current event, which occurs each time you change record, and build the code in there along the following lines... Sub MyForm_Current() If CDate(Me.txtExpiryDate) DateAdd( d ,-7,Date()) Then Me.txtExpiryDate.ForeColor=255...

Using MS Access: Equations in a Report, microsoft access, circumstance
microsoft access, circumstance, mvp: It sounds like your data is not setup properly if you have fields that are dates. I can t imagine any circumstance where I would have a fieldname that is a date. You should have a table where the date value is a value in a record. From there it will be easier...

Using MS Access: ER Diagram, entity relationship diagram, logical models
entity relationship diagram, logical models, example answer: Daniel Hi, The primary Entities are going to be: 1 Room: - RoomID, Room Num, type, number of beds, etc. 2 Calendar: - Date, Status (Open or Closed) 3 Client: - ClientID, Client name, address, tel, email etc 4 RoomStatus: RoomStatusID, RoomStatus...

Using MS Access: ER diagram - Mricrosoft Access Relationship Help, microsoft access database, state zip code
microsoft access database, state zip code, institution street: LeeAnn, Hello, You are embarking on a significant development project - consider it equivalent to designing and building your own automobile.... I would seriously recommend that you take an introductory course and learn the building blocks from foundations...

Using MS Access: ERROR IN USING SQL SYNTAX IN MS ACCESS, runsql, access tutorials
runsql, access tutorials, input table: Do you have your strings set up properly? For example, TBL = TableName & 5 is a whole lot different from TBL = TableName & 5 Check that out and see if that s the problem. Let me know if you have any more questions or comments. Cordially,...

Using MS Access: Error:2287 using Docmd.SendObject under XP, mail session, mail application
mail session, mail application, ms knowledge base: Hi Rod My first thought here is there is a significant change to the way files are stored under Win 2000+ over Win 98. As you may know, Win2K stores everything in relative paths..Document and Settings, etc. This is a by product of the security model...

Using MS Access: Error 3218 Could not update; currently locked., access 2003 runtime, novell network
access 2003 runtime, novell network, simultaneous users: According to the MSKB Error 3218 is common on a Novell network when trying to delete large blocks of records. Not sure if that is your situation. The message indicates there is a problem with record locking, which may be due to users trying to open the...

Using MS Access: Error 3709 in Access, access corruption, odd data
access corruption, odd data, autonumber field: Hi, This is an extract from my Newsletter: My best guess is that you have a corrupt record. Can you open your table and do a sort, ascending and descending? The corrupt record usually appears first when sorted, you can then see which one it is as it...

Using MS Access: (Error 3734), open mode, menu tools
open mode, menu tools, group permissions: Hi Tiago, Ensure all are logged out, then open the db. From the Menu select Tools / Security / User & Work Group Permissions. Select Database in the RH object combo Uncheck Open Exclusive & Apply Select Groups For each group, Uncheck...

Using MS Access: Error - Adding data in subform Version: 2000 (9.0), relationship change, messagge
relationship change, messagge, key field: the relationship is set main file [field]- child file [PrimaryKey-field] thus, every time you try to add a record you are traing to update an autonumeric field which is no duplicate also. the relationship must be: main file [PrimaryKey-field]- child file...

Using MS Access: Error in appending a table with MS Access 2003, destination table, synchronization problem
destination table, synchronization problem, records c: Hi Apologies - I thought it was 39 macros. It s still very difficult for me to debug this as 39 updates are quite extensive. Are you sure you have no sorts on the columns in the table? Also, is the data you re appending in a sorted condition? ...

Using MS Access: Error "Cannot find a record in the table...", microsoft jet database, microsoft jet database engine
microsoft jet database, microsoft jet database engine, jet database engine: Hi This sounds like your relationships have ref integrity and are preventing the update of the table. I.e. With referential integrity a one to many means you you must provide the one side before the many Does that make sense? I would welcome the...

Using MS Access: Error Linking a Subform, automation object, winnipeg canada
automation object, winnipeg canada, object doesn: Bob, I m afraid even your very detailed explanation of your problem doesn t provide enough information for me to see what you re trying to do and where the problem may lie. For example, what fields are you displaying in the form? In the sub-form? Also,...

Using MS Access: Error Message, version microsoft, macro security
version microsoft, macro security, documents and settings: Shell is a function not a procedure, so CALL is not appropriate. Try it this way: Dim stAppName As String Dim RetVal As Variant stAppName = C:Documents and SettingskevansMy DocumentsBILLING SOLUTIONS.ppt RetVal = Shell(stAppName,...

Using MS Access: Error in MS Access, design updates, repair database
design updates, repair database, database utilities: Well first you NEED to split the database. Even if you continue to store both pieces in the same network share. Splitting the database will not change anything with the way the users use the database. Their desktop icons should continue to work as before since...

Using MS Access: Error Msg, ocx dll, design mode
ocx dll, design mode, multiple computers: Hello Dima, It looks like you have a Referencing problem - the ActiveX on one PC is different to that on the others. To check this - Open a module in design mode, from the menu, select Tools / References: Now check for any broken or missing refences...

Using MS Access: Error message, option group, hth
option group, hth, error message: The solution to this is simple. You should not have 2 tables for subscriber s and non subscribers. Use ONE table with a field that identifies a person as a subscriber or non subscriber. What is happening is that your relations prohibit adding a payment...

Using MS Access: Error when open a form, run time error, command2
run time error, command2, requery: The problem is most likely in the query behind the form. Also you don t really need a subform for this. What I would do is use a continuous form and put an unbound combobox in the header to select the category. In the query set the criteria for the category...

Using MS Access: #Error record value ... in Access XP database, data corruption, application startup
data corruption, application startup, power failure: Dear Rohit, These types of errors mean Access recovered from data corruption. Corruptions can take place with bad and unstable networks, bad and unstable PC hardware/Software and unreliable power. Secondly the application must also be written in such a...

Using MS Access: #Error record value ... in Access XP database, network failure, application startup
network failure, application startup, power failure: Dear Rohit, I don t know much about vb.net but an #error like this is when the app shutdown or break in the middle of a transaction. Network failure s are sometimes the culprit. You mention open and close the connection, what about open and close the recordsets...

Using MS Access: Error in sub-sub form, prefixes suffixes, client database
prefixes suffixes, client database, company id: Hi Marc, I would have a separate table for Company information that is independent of year, then create a new first level sub form on that table to edit the company details for the company linked to the general data table Using autonumbers is fine -...

Using MS Access: Error trapping/ or error translation, sql server 2005, recordcount
sql server 2005, recordcount, primary key: Thats funny, why would you think this would be a challenge for me? Run a Select query to your table before the insert runs using the data that is about to be inserted. Then, enclose the recordcount in an IF statement. If the record count 0, then pop...

Using MS Access: Error trapping/ or error translation, sql server 2005, dim rs
sql server 2005, dim rs, findfirst: Dear Mo, Function CheckDups() If len(Me.Field1) 0 and len(Me.Field2) 0 then else exit function endif Dim Rs as Recordset Set Rs = currentDb.Openrecordset( The Table ,DbOpenDyanset) if rs.eof then rs.close exit function endif ...

Using MS Access: Errors while using database on Server, microsoft access database, end tables
microsoft access database, end tables, multiple users: It doesn t sound like its a security issue. The first question is whether your database is split. Any multi-user database should be split between back end (tables) and front end (everything else). Access includes a splitter wizard to do this. For optimum...

Using MS Access: Make the key prompt, ascii value, keyascii
ascii value, keyascii, keystroke: Put code behind the form s On KeyPress property to test each keystroke and act accordingly. If you want to capture each keystroke anywhere on the form turn the Keypreview property to Yes. The ASCII value of the Esc is 27 so your code might look something...

Using MS Access: Estimating database, spredsheet, discount model
spredsheet, discount model, customer info: This medium is best suited to asking and getting answers to specific questions. If you want to explain the difficulties you are having, I would be glad to help if I can. Also, when you import data from a flat file (like an Excel table), Access has an analyzer...

Using MS Access: EXPRESSION BUILDER, expression builder, employee id
expression builder, employee id, critera: Almost anything is possible. What I would do is create a user defined function for this. Something like this: Public Function CoFee(dblAppAmt as Double, intProgram As Integer) as Double If dblAppAmt Between x and y AND intProgram =36 Then Fee = formula...

Using MS Access: Excel to Access, michigan state university, column names
michigan state university, column names, spread sheets: Hi For your file import... 1. Change the column names so you can get the data into Access 2. Ensure the tables you re importing into do not have a primary key - you can set one after you ve got your data. 3. Any errors in the import will be written...

Using MS Access: Excel into access, query wizard, database window
query wizard, database window, hth: That really depends on the quality of the data. Importing the sheets is easy. Just select New Import from the database window and follow the prompts. You can then use the Query wizard to create a Find Duplicates query that will match them. But if there is...

Using MS Access: Excel Form Template for Data Tracking, excel 2002, excel 97
excel 2002, excel 97, vba: Yes, I think they would need Excel 2002 to use a form developed in Excel 2002. While spreadsheets are interchangeable between 97 and later versions, not all features are supported. I think that changes have been made in VBA in later versions that would cause...

Using MS Access: Excel import in access, type conversion, excel sheet
type conversion, excel sheet, error type: An access table cannot have more than 255 fields, there is no way around that. (I might suggest that if you have an Excel sheet that large, the data should be normalized first before trying to import it into a database.) - - is not an acceptable Date...

Using MS Access: Excel vs Access, mid field, c4
mid field, c4, a4: Sue, I will have to play around with this to figure it out since I haven t done anything quite like this before. Question: Is the number at the beginning that you want to separate out always the same length, or can it vary in length so that you have to look...

Using MS Access: Excel, seperate sheet, filter capacity
seperate sheet, filter capacity, cross tab: Hi Gloria, Actually using Excel for data management is quite desirable when number of records are not many (say under 1000). Excel s auto filter capacity and pivot table for cross tab provide a lot of capability to manage data. Back to question: It is...

Using MS Access: Exception handling of Cancel button click on InputBox Function, seed inventory, explicit function
seed inventory, explicit function, mydata: The input box will close when you press cancel or any other action. So what you need to do is test for what s returned. If Cancel is pressed the function returns a zero-length string. So you would use something like this: If Len(InputBox( Scan SIID , Seed...

Using MS Access: Exe file of MS access database, ms access database, runtime version
ms access database, runtime version, tool bars: You can t do it. There is no compiler for Access code. What you can do is get the Developer extensions for the version of Access and distribute that with the runtime version that comes with the Developer Extensions. But even then You still have the access...