RE: [ms_access] Security for a shared .mdb not working

to change the user's default workgroup for local databases. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access

RE: [ms_access] Memo Field truncated in query result

Dave- If you're trying to list the details and then get a grand total of the failures, use your query in a report, add a Report Footer, and include a text box that uses the Sum function. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft

RE: [ms_access] Make table ID number show related text multiple times in report

John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -Original

RE: [ms_access] is not a valid name error for queries in Access 2007

Can you switch to SQL view on these bad queries? If so, is the SQL correct? Paste an example in a reply here. It might be the case that your queries in 2003 are using some name that is now reserved in 2007 - and you'll need brackets around the name. John Viescas, author Microsoft Office Access

RE: [ms_access] Memo Field truncated in query result

Another common source of truncation is using a Memo field in a GROUP BY in a query. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access] Memo Field truncated in query result

Dave- From looking at your original SQL, it's unlikely that you'll ever get a count of more than 1 unless you have lots of duplicate rows in your data. What is it you're really trying to accomplish? John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access

RE: [ms_access] Query Doesn't Return all records

Need more info. What's the SQL of one of the queries, what result are you getting, and what result to you expect? To find the SQL, open the query in Design view and choose SQL View from the View menu. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access

RE: [ms_access] Re: What Does a Valid Date Time Format Look Like?

contains a valid date/time string. You must convert it. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see

RE: [ms_access] Re: What Does a Valid Date Time Format Look Like?

Mike- If the output is Text, then you'll have to do an explicit convert to date/time data type to be able to stuff it into a date/time field in a table. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries

RE: [ms_access] Re: Can't Use Trim, Mid, etc

Basic editor (CTRL+G should work) and choosing References from the Tools menu. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop

[ms_access] RE: [MS_AccessPros] How to pass an Excel object as a parameter of a function in vba

How do you have the object Dim'd in the first three lines of code? You could simply pass it as an Object: Function Get_Columns_and_Rows( ... , ExcelDoc As Object) As Integer Or you could load a reference to the Excel library and declare it Excel.Workbook. John Viescas, author Building

RE: [ms_access] Re: What Does a Valid Date Time Format Look Like?

to convert it from a linked table in a query. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http

RE: [ms_access] error 3109 When trying to update a table realtionship

Jeremey- What version (and Service Pack leve) of Access are you using? Did you convert this database from an earlier version? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Convert Text String to Actual Date/time variable

Brett- DateValue converts only the date. You need CDate instead. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop

RE: [ms_access] Online Database help

, you might be over your head on this if you've never done any web programming. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside

RE: [ms_access] Can't Use Trim, Mid, etc

Editor (or Press CTRL+G) and then choose References from the Tools menu. Uncheck any libraries marked MISSING. Close the dialog and choose Compile from the Debug menu. If the existing code compiles, you should save the result. Using functions in queries should now work fine. John Viescas, author

RE: [ms_access] ACCESS SYSTEM TABLES

Use USys as the table prefix. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com

RE: [ms_access] Batch Import Problem

as the table ' name. tblName = Expenses DoCmd.TransferText [acImportDelim], Batch, ExpBatch, Name ImportFile = Dir Loop End Sub John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals

RE: [ms_access] Re: mandatory login for user

Doreen- In the Click event of the text box, do: Application.FollowHyperLink Me.txtEmail1 John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access] CurDir help please

Neil- See code I posted that uses CurrentDb.Name but extracts just the Path from it. The Name property includes the full path *and* the database file name. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL

RE: [ms_access] CurDir help please

Neil- See message: http://groups.yahoo.com/group/ms_access/message/20067?threaded=1 http://groups.yahoo.com/group/ms_access/message/20067?threaded=1var=1p=6 var=1p=6 .. for a simpler answer and a useful InStrRev function for A97. John Viescas, author Building Microsoft Access Applications

RE: [ms_access] Re: mandatory login for user

Your Control Source for each text box should look like: =Doreen Darr#mailto:doreen. mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] CurDir help please

Brett- The CurrentProject property doesn't exist in Access 97. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access

RE: [ms_access] CurDir help please

Brett- Shouldn't work. If I go to A97 Immediate Window and type: ?Application.CurrentProject.Path .. I get Method or Data Member not found. How did you make the A97 copy, and how did you compile it? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003

RE: [ms_access] MS Access on a Shared Network Drive

and application code on top of that. 3. Problems: A poorly designed client/server application will have lots of problems - particularly with performance. Your network must be rock-solid - never use dial-up and use VPN only if broadband. Does that help? John Viescas, author Building Microsoft Access

RE: [ms_access] Re: mandatory login for user

Doreen- Instead of using MsgBox, open a Dialog form that has this information displayed in labels and text boxes - use the text boxes for the email addresses. Set the Is Hyperlink property of the text boxes to Yes. John Viescas, author Building Microsoft Access Applications Microsoft Office

RE: [ms_access] Re: mandatory login for user

Doreen- Are the controls in the window bound? If so, the Hyperlink data type will work. If not, you can use: Application.FollowHyperlink mailto:; Me.EmailAddress John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access

RE: [ms_access] subform tabbing problem

Doreen- Do you have any code in the form that's in the subform control? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop

RE: [ms_access] #Deleted message shows in form field

Doreen- You must Requery the subform control to get it to not show deleted rows. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside

RE: [ms_access] On No Data Help Please

trap is the only way to do that. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com

RE: [ms_access] On No Data Help Please

Ilona- Flowcharts make sense in a batch sequential system, but not in the global scope of event-driven systems. I still build flowcharts for navigation between forms and for complex bits of code inside functions or event procedures. John Viescas, author Building Microsoft Access Applications

RE: [ms_access] Scanner question

, intP, 1)) Then strResult = strResult Mid(Me.MemberNo, intP, 1) End If Next intP Me.MemberNo = strResult John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris

RE: [ms_access] On No Data Help Please

Ilona- Add an error trap around your DoCmd.OpenReport. Setting Cancel = True creates a trappable 2501 error - your code canceled the action requested by the OpenReport. It could be as simple as: On Error Resume Next DoCmd.OpenReport ... John Viescas, author Building Microsoft Access

RE: [ms_access] Scanner question

Dave- Perhaps use the BeforeUpdate event of the control to eliminate the junk - keeping only the numbers. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

RE: [ms_access] Re: headings in sub forms

recordset that has no records, the form will appear blank. What is the Record Source of your subform? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access] Re: Autonumber Problems

Gary- Sounds like a corruption problem. Create a new empty database and import all the objects from the old one. Compact the database and then see if it still insists on using high numbers. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out

RE: [ms_access] Send ... Rich Text Format Question

Ilona- Export to RTF loses all lines and boxes. Export to Snapshot format if you want to see the report as it appears in Access, but any recipient will need the free Snapshot Reader from Microsoft. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside

RE: [ms_access] Re: renew object in bound frame

Chad- No. You can't dynamically load an unbound OLE Object Frame in a report. The file needs to be stored in a table and displayed in a bound Object Frame. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL

RE: [ms_access] renew object in bound frame

Chad- You can do that in a Form, but I don't think you can do that in a Report. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside

RE: [ms_access]

SELECT [Name], Address, Choose(Count([Time]), Once, Twice, Thrice, Four, Five, Six) As NumTimes FROM MyTable GROUP BY [Name], Address John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals

RE: [ms_access]

To get a similar result in SQL Server, you need to use CASE. See Books Online. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside

RE: [ms_access] Re: headings in sub forms

Bob- Do you have bound controls defined in the Detail section of your form design? What exactly do you mean by disappear on me? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Access 97 and MS SQL 2005 Express

Only if the data types in the tables you want to link to are supported by SQL Server 7. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access] How to return the full path of a file?

Eileen- Well, Office 2003 has a FileDialog object, but that's not available in 2000, so you'll have to call the Windows API to open the standard dialog to find a different file. See the code at: http://www.mvps.org/access/api/api0001.htm John Viescas, author Building Microsoft Access

RE: [ms_access] Sorting a form based on a figure that is the sum of fields in the table

Instead of calculating the total in the form, do it in the query to create a calculated field. You'll then be able to sort on that. In a blank column on the Field line, enter.. Overall Rank: [Field1] + [Field2] + ... John Viescas, author Building Microsoft Access Applications Microsoft Office

RE: [ms_access] creating a calculated field in a table

John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -Original Message

RE: [ms_access] Query Help Please

Ilona- Yes. But why reference an Update query in a Select query? Or am I confused? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access] Command button to sort records on a form

Bill- Just change Case False to: Me.OrderBy = str Me.OrdderByOn = True John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop

RE: [ms_access] Starting queries in other databases with a Macro

Yes. And use the Database Splitter (Tools / Database Utilities / Database Splitter) to dump all the tables out to another file and link them back to your code database that has all your queries, forms, reports, etc. Compact the code database after that to shrink it. John Viescas, author

RE: [ms_access] Default Value/Event Procedure Help

Ilona- Well, I do get the royalties from the original sale - the person on eBay who sold it to you, so no loss. s John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] how to import excel workbook file containing multiple sheets into access

No, you don't absolutely need VBA. You can create a macro with a series of TransferSpreadSheet actions to do the job. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Re: Issue with subform refresh or requery?

on the main form. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access

RE: [ms_access] Re: Adding a new record

Theodore- It doesn't make sense to me that these two tables are 1-1. What is the structure of the two tables? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

RE: [ms_access] Simple Search problem

and choose SQL View from the View menu.) John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com

RE: [ms_access] First and last TWO items in a group

WHERE T2.Group = MyTable.Group ORDER BY Sequence) UNION ALL SELECT Group, Sequence, Text FROM MyTable WHERE Sequence IN (SELECT Top 2 Sequence FROM MyTable As T3 WHERE T3.Group = MyTable.Group ORDER BY Sequence DESC) John Viescas, author Building Microsoft Access Applications Microsoft Office

RE: [ms_access] Re: Adding a new record

intTrans = True ' various updates / inserts / deletes CommitTrans intTrans = False ' more code ExitPlace: Exit Sub Ooops: If intTrans Then Rollback MsgBox Unexpected error: Err , Error Resume ExitPlace John Viescas, author Building Microsoft Access Applications Microsoft Office

RE: [ms_access] Simple Search problem

Are you using a parameter query? Is there code in the second form to apply the filter? More details, please. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

RE: [ms_access] Where am I going wrong?

to the sum of sales in case some products had no sale. That will ensure you get all inventory part numbers and any matching total sales. Let me know if you need help building the SQL. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft

RE: [ms_access] Re: Adding a new record

, and delete of the offer all happen successfully or none of it does. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007

RE: [ms_access] Re: A Job for Access?

Duh. Left off part of the SQL: UPDATE Sample SET sample.Day_Choice_1 = (SELECT Day_Choice_1 FROM Sample As S2 WHERE S2.ID = DMax(ID, sample, IDsample.ID And Day_Choice_1 Is Not Null)) John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside

RE: [ms_access] Re: A Job for Access?

Mike- It's SQL. Start a new Query, switch to SQL view, and paste in my code. In my example, I used your sample table. You'll need to change the table name if yours is different. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running

RE: [ms_access] Qry help needed for finding COUNT . Please?

# field choose Count on the Totals line, and under the qty sold field, choose Sum. Just your basic Totals or aggregate query. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Re: Adding a new record

a halt on the db.Execute, copy what's in strSQL into the SQL View of a new query, and see if you can run the query from the user interface. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Max Size

and then write your own Append (INSERT) query. Set the Use Transaction property of the query to No to avoid the transaction overhead. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access]MsgBox Error Help Please

. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -Original Message

RE: [ms_access] Batch Import Question

Gary- Short of writing some nasty code to open the text files directly, no. What's wrong with issuing several TransferText commands back-to-back? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries

RE: [ms_access] Code Help

should see either [Event Procedure] or a macro name in that property. Click in the property and then click the Build button (...) next to the property setting to open the code or macro. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running

RE: [ms_access] Time calculations for flight operations

Dave- How about: SELECT Hour(OpsTime) As OpsHour, Count(OpsTime) FROM MyTable GROUP BY Hour(OpsTime) I thought the Partition function might also work to give you a range, but Help says it works only with whole numbers, and a time value is a fraction of a day. John Viescas, author Building

RE: [ms_access] Re: Code Help

OK. What is in the On Click property of the command button that you want to investigate? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France

RE: [ms_access]Help with Report Please

Ilona- You shouldn't see a blank page unless you've done something to create one. One example that comes to mind is an empty Report Footer section that's set to print on a new page. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running

RE: [ms_access] Set Autonumber back to #1

MyTable, dbFailOnError ' Reset the counter db.Execute ALTER TABLE MyTable _ ALTER COLUMN ID Counter(1,1), dbFailOnError .. where MyTable is the name of your table and ID is the name of the AutoNumber field. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003

RE: [ms_access] Query Problem

. If it complains about a library being invalid, choose References from the Tools menu and fix anything that's broken. Did you recently add code or move this database to a different machine? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running

RE: [ms_access] Query Problem

Mike- If any form or report has code, then you have modules. Uncheck that referece and try a compile. If all is well, then your problem should be solved. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL

RE: [ms_access] Query Problem

Mike- A compact does not compile anything. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http

RE: [ms_access] Adding Records to Bridge Table

Bob- Northwind.mdb is a sample file that gets installed with Access. If you have Access 2002 or 2003, you can choose Sample Files from the Help menu to find it. Otherwise, use Windows Explorer to search for it on your hard drive. John Viescas, author Building Microsoft Access Applications

RE: [ms_access] Adding Records to Bridge Table

to create a form with a subform, and you should be all set. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007

RE: [ms_access] Dlookup function help

trying to do? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access

RE: [ms_access] Dlookup function help

it to match what's in the table. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access

RE: [ms_access] Dlookup function help

a match. If that's the case, you'll need to round the result before trying to compare it. Number of decimal places doesn't matter - that affects only the display of the number, not the actual stored value. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside

RE: [ms_access] SQL Query

Does Table-B have a Division field defined? What are the Primary Keys of the two tables? If Table-B has a Division field, you can perhaps run an update query: UPDATE [Table-B] SET Division = (SELECT Division FROM [Table-A] WHERE [Table-A].District = [Table-B].District) John Viescas, author

RE: [ms_access] Report possible, or pipe dream?

Mike- The short answer is an emphatic Yes. But you'll probably need a bit of VB code to set the option buttons you would need to use to emulate the look of the form. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access

RE: [ms_access] Re: A Job for Access?

Mike- Sorry, I don't speak SAS. You can zip and upload a sample database to the files section of this Yahoo list. http://groups.yahoo.com/group/ms_access/files/ John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL

RE: [ms_access] Top 10 query

SELECT Top 10 ID, Total FROM MyTable ORDER BY Total Desc; If you want the top 10 values per ID, then do: SELECT ID, Total FROM MyTable WHERE Total IN (SELECT Top 10 Total FROM MyTable As T2 WHERE T2.ID = MyTable.ID) John Viescas, author Building Microsoft Access Applications Microsoft Office

[ms_access] RE: [MS_AccessPros] finding blank entries

= Responses.QuestionID AND qryAllPersonsQuestions.PersonID = Responses.PersonID WHERE Responses.QuestionID IS NULL John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

RE: [ms_access] (unknown)

fields in the combo box when the user selects a new CustomerID. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007

RE: [ms_access] Trying to set up form to record views of record

= MemoViewed ; CurrentUser _ Time WHERE MyTable.Pkey = Me.PKey, dbFailOnError John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside

RE: [ms_access] ComboBox Selection for a form

Dave- Try: MyWorkOrder.FindFirst [WorkOrderID] LIKE '* Me.qWorkOrderNumber *' Or: MyWorkOrder.FindFirst [WorkOrderID] = Me.qWorkOrderNumber John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries

RE: [ms_access] Re: Form will not accept input??

Record Source. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ -Original Message- From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf

RE: [ms_access] Form will not accept input??

It sounds like you have created a non-updatable query to use as the Record Source of your form. To completely solve the problem, you will need to post the SQL from your query - open the query in Design view, choose SQL View from the View menu, and post what you see here. John Viescas, author

RE: [ms_access] Re: Count Function

and CompletedClasses in the subreport, put Students in the outer report, and link on ParticipantID. You can then count students independently in the outer report. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries

RE: [ms_access] Re: Form will not accept input??

If you have no queries, but your form is not updatable, then there's probably a query (an SQL statement) defined as the Record Source of your form. Open the form in Design view and open the Properties window. Let us know what's in the Record Source property. John Viescas, author Building

RE: [ms_access] help with rounding to nearest dollar

Eileen- All rounding in Access uses banker's rounding that rounds an exact 0.5 to the nearest even value. So, 892.5 rounded to an integer = 892. On the other hand, 893.5 rounds to 894. Why do you want to round to an integer? What is the business problem you're trying to solve? John Viescas

RE: [ms_access] Converting a number to hh:mm:ss format

Mike- Format(Int([Seconds]/3600), 00) : Format(Int(([Seconds] Mod 3600) /60), 00) : Format([Seconds] Mod 60, 00) John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Converting a number to hh:mm:ss format

You can use it on the Field row in a query, prefixed with a name you want for the field and a colon. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

RE: [ms_access] Displaying first record from a set of records in a report

BY CASEMASTER.LOCATION, CASEMASTER.CASENO; John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ -Original Message- From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED

RE: [ms_access] Categories table - how to cater for subcategories?

is a user might end up creating a new category by mistake by slightly misspelling it. Have fun... John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ -Original

RE: [ms_access] Report fields limited to 256 characters???

Mike- Use the UCASE function to convert it and dump the Format spec. It's the Format that is truncating the field. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Report fields limited to 256 characters???

Well, you can use it in the query or simply use it in the Control Source of the control on the report: =UCase([Note]) John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http

RE: [ms_access] Re: Continuous form problem

Theodore- No. Storing a calculated value in a table is a very bad idea. You would have to write a ton of complex code to make sure the value stays in sync. For this one case, save the SQL as a query. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003

RE: [ms_access] Autotext

Bill- You might get a better response in one of the Excel groups. This group is for the Access database. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com

  1   2   3   4   >