Replies

Just add the two fields:
Expr1: [Date Entered]+[Days to Complete]

If you have a form (or switchboard form) set to open automatically on startup, I wouldn’t add more code, but I would change the “Allow Design Changes” property for that form, from “All Views”, to “Design View Only”. This way, the properties window will only display, when the form is in design view. There should be no reason for the users to see the properties window. In fact, I would do this for all forms before letting others use the database.

Go back to your original “On click” code (not the DCount code), and try this before you open the report:

DoCmd.SetWarnings False

Then add this after opening the report:

DoCmd.SetWarnings True

This should stop the systen generated message. (Always set warnings back to true, to catch any other errors that may occur).

Change your query to a “Totals” query (click the button that looks sort of like an “E”, it’s actually the greek letter known as “sigma”). Now all of your fields should show “Group By” in the total line. Change each field except the case number field, to “First”. Sort the sequence number field “Ascending”. Now put “Is Not Null” as criteria for the consultant(name?) field. This will now only show the first record (based on sequence number) for each patient that has a consultant.

If the two tables are linked by the system number, there is no need to display the number twice or the date twice. Open your query in “Sql view” and use this sql (rename the fields and tables to match yours):
SELECT Table1.SysNum, Table1.dateA
FROM Table1 INNER JOIN Table2 ON Table1.SysNum = Table2.SysNum
WHERE (((Table1.dateA)=[Table2].[dateB]));

Your validation rules cause your query to select records that pass the validation. Create a second query, similar to the first, except that now you want to re-word the validation to select records that would be rejected by the first query. In other words, if the first query only selected records where a particular field was less than 1000(this is just an example), then let the second query select records equal to or greater than 1000. Make this an append query to a new table(either design this new table yourself, or you can run a make table query one time(based on the second query) to create the new table. Now simply set up your database to always run both queries, instead of just your original query.

If you want a parameter query, then instead of my previous reply, create a new column in your query with the field name as an expression: Mid([YourTextFieldName],13,1). Then in the criteria of this new column put this: [Enter Value].

I replied too quickly. You want to use this on the criteria line of your query, for the text field you mentioned: Mid([YourFieldName],13,1) =”value”. Replace “value” with the value you’re looking for.

MID([FieldName],start_num,num_chars). For your query use: Mid([YourFieldName],13,1)

One more thought. You only say that some of the “Revtime” values are incorrect, so I assume the timestamp values are correct. If the timestamp values in a record are correct, and the formula that generates the “Revtime” is correct, then I think it may be that when the formula actually runs, it is using incorrect data sometimes(possibly due to stopping, starting?). What I’m saying is this: If you have correct data used as input to a valid formula, the results should always be correct. If this “Revtime” is generated on the fly (during data entry); something must be feeding incorrect values to the formula, even though correct timestamp values are being saved. Could the process that generates the “Revtime” values be separated from the rest of this process and run at a later time? In other words, could you create the “Revtime” values as a “batch” process using only completed records as input, instead of these values being created during data entry?

Instead of trying to fiqure out a more complex query, why not query for all records where the “Revtime” is too large, then compare this to the records from the query you already wrote (the one that finds records where no other record was started). If you compare the two results, you will see that any records in the new query that are not in your previous query, would be the records that you are looking for.

Sometimes it’s difficult to fiqure out a query by looking at it in design view. If you look at it in sql view, you will see what this query is really doing. You can see alias column names (if any), types of joins, sort order, etc…

Because of an established relationship, you can not run the make table and replace the existing table. You can run a delete query on the Access table(to delete all records), and then run an append query to grab the fresh data from the ODBC table. In other words, empty out the “Items” table, and then fill it up form the ODBC table. Caution: make sure the delete query runs against the “Item” table in your Access database, and not against the ODBC connected table.

Have you tried the Access database wizard? You can quickly create and then test several inventory sample databases that you can alter to fit your needs. I have also seen free(and not free) inventory databases available for download. Anybody else have some recommendations?

Charlotte’s reply is exactly what you’ll have to do. You can export your DataEase table as a delimited file that can then be imported into an Access database as a table. You can not directly import/export between Access and DataEase.