Replies

Try – DMax(“CLng([Check])”, “tbl_Check”)

Thanks Tom and Hans

Tom I tried use the Replace function you suggested,
Replace(strMySearchCriteria, chr(34), chr(34) & chr(34),1)

I received the following error –

Undefined function ‘Replace’ in expression.

Is this function available in Access 97?

Thanks Mark.

SQL2 isn’t a saved query but you reply gave me an idea.

I replaced the SELECT statement from SQL1 with the SQL2 SELECT statement and the correct results were returned.

INSERT INTO Table2 (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field)
SELECT Table1.FIELD1, Table1.FIELD2, Sum(Table1.FIELD3) AS SumOfFIELD3, Sum(Table1.FIELD4) AS
SumOfFIELD4, Sum(Table1.FIELD5) AS SumOfFIELD5, Table1.FIELD6, Table1.FIELD7, Table1.FIELD8,
Table1.FIELD9
FROM Table1

Thanks again.

Hi Mike,

Does the date field contain the time also? You may have to add a +1 to your ending date.

Try changing the Unique Records in the query properties from No to Yes.

Okay, it’s Friday. I’m going home.

I think this is what you are after.

Create a table with the days of the week listed.

My Table3. One Field. [Weekdy]

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Table 2 has the fields [workdy] and [Employee]

[workdy] [Employee]
Monday employee1
Sunday employee2
Sunday employee1
Tuesday employee1

Here is the SQL:

SELECT Count(Table2.workdy) AS CountOfworkdy, Table2.workdy
FROM Table3 INNER JOIN Table2 ON Table3.weekdy = Table2.workdy
GROUP BY Table2.workdy;

Results:

[CountofWorkdy] [Workdy]

1 Monday
2 Sunday
1 Tuesday

HTH

Hi

Do you share the DB with anyone else?

Try here:

http://home.wanadoo.nl/njh/download_tools97.htm%5B/url%5D

Edited by Charlotte to activate link

This may not be applicable but, depending how far and how deep the litigation may go

Visual/Access Basic Is Both a Compiler and an Interpreter (Q109382)

http://support.microsoft.com/default.aspx?…b;EN-US;q109382

This may not be the best way to achieve this but it will give you the chance to step through one of several processes to see how you are gathering data and produce your report. If anyone has a better idea please feel free to jump in here.

Start backwards. Design a blank report with all the fields you will need. This will give you a picture to work from.

Create two tables. Select a specialist and Append all his/her data for that week to the 1st table.

Build the 2nd table from the fields on your report example.

If you are familiar with VBA you can open recordsets based on queries from the 1st table and place that data onto the 2nd table.

If you are unfamiliar with VBA the same thing can be accomplished with the use of queries to pull, and total data from the 1st table. Using forms based on the queries, cut and paste data onto the 2nd table.

Base your report on the 2nd table.

Once you have everything in place you can create a macro to automate the process.

HTH

You are not mixing apples and oranges. To keep it simple you could use the report wizard based on your query.

To get the total jobs without a completion date you could user your (Between #6/10/02# and #6/14/02#) in the criteria under Date Job Assigned. You will also place in the criteria under the Date Completed field (Is Null) (Is Null is without the parentheses.) This will pull all the records without a completion date. Save the query. Run the Report wizard based on the query.

The great thing about the Report wizard is that it can do sorting, math functions and summarize data.

You could base some of your other reports off the same query but with different criteria.

HTH

Every database created in Access has an Admin group and an Admin user. If you don’t remove permissions from the Admin user anyone with Access can open your database. Don’t forget to make yourself a member of the Admin group.

There is all kinds of other fun stuff that needs to be done to secure your database. Stuff like after creating the .mdw creating the database under the new .mdw. Joining the Admins group. Passwords. Removing permissions from the Admin user ONLY after you have joined the Admins group.

Go to ftp://ftp.microsoft.com/softlib/mslfiles/secfaq.exe and download the Security Faq. It details Access security.

Also, don’t make the mistake of replacing the standard system.mdw on your machine found in /windows/system or in WinNt/system with the secured one or ALL your Access apps on that machine will require you to logon. Put it elsewhere and preferably call it somethingelse.mdw

Practice on a copy of your original database first. I locked myself out of a couple of databases before I got a handle on Access security.