# Solved: Creating a monthly query/report in Access 2007



## krs1716 (Feb 10, 2012)

I have painstakenly begun the creation of a database that tracks people in various ways. However, the only benefit the database will have is if I can create reports that group them into relevant information. Most of the information that I need to report is based off of monthly, quarterly, and yearly groupings. However, I want the users to easily be able to access this information.

I would like to create queries (which will then feed the reports I plan to create) that break the information down into monthly, quarterly, and yearly reports based upon various dates included in the database. I have investigated the sample Northwind database, and it has this great Report dialog box to make it easy for users to gather the information they want. The only way I could figure out to gather information by date is to have Between dates Parameters (a confusing endeavor for some of my users). Does anybody have any suggestions for me to make my queries/reports easier to use for the novice user?

Just a note: I'm not great with VBA, but I do know how to type in it? (just can't write it very well myself)


----------



## OBP (Mar 8, 2005)

You should not need much VBA.
Do you want users to be able to just choose "Monthly report" or Annual report etc?
The Monthly report would probably need to be tied to a year.


----------



## krs1716 (Feb 10, 2012)

Yes, I want them to be able to choose the month and year and view the information. I would also like the corresponding report to state the month and year.

P.S. Glad to hear about the VBA.


----------



## OBP (Mar 8, 2005)

There are a lot of ways of showing the data, do you know what a Crosstab query looks like?
Do you have any idea what sort of output format that would like or currently use?


----------



## krs1716 (Feb 10, 2012)

I've read about Crosstab queries, but I have never used one. I'm not sure what you mean by output format. Is that the "output all fields" in the query properties?


----------



## OBP (Mar 8, 2005)

No, how you want the Report to look, lists of records, a summary, both. Something else?


----------



## krs1716 (Feb 10, 2012)

I have the report set up so that it shows County, Program, Name (last then first), date, employer, job title, weekly hours, hourly wage, and benefits (a check box). Eventually, I would like there to be a count to tell how many contacts are included in the report for the month. This particular report is technically a details report. The number of people included in the query is then reported as just a number at the end of the quarter. If that makes any sense?


----------



## OBP (Mar 8, 2005)

OK, do you have a query for the report?


----------



## krs1716 (Feb 10, 2012)

I have created a query, and I am currently using a Criteria of : Between [Type beginning date] And [Type Ending Date]. This produces the desired results, but I wanted an easier-to-use method for the everyday user.

Of course, I look at the Northwind sample database, and I get all excited by the possibilities, but have no idea how to create that type of database.


----------



## OBP (Mar 8, 2005)

The best method of inputting the dates is on a form.
You use 
Between Forms![formname]![Beginning Date] and Forms![formname]![Ending Date]
Where formname is the actual name of your form and the field names are [Beginning Date] and [Ending Date]

It takes time to build up to databases like the northwind one.


----------



## krs1716 (Feb 10, 2012)

So, there is no way to choose the month (i.e. January, February, etc.) and the year without breaking the dates down to month, day, and year in the tables?


----------



## OBP (Mar 8, 2005)

Yes of course there is.
There are 3 methods in queries, here is an example for a Month..
Format([datefield], "mm")
DatePart("mm" [datefield]])
Month([datefield])


----------



## krs1716 (Feb 10, 2012)

Where do I put that information at? In the criteria under the date field?


----------



## OBP (Mar 8, 2005)

No you create a new heading like
Month: Format([datefield], "mm")
You then put your criteria in the new column's criteria row.
In the Access help look at "Examples of expressions" in queries - dates.


----------



## krs1716 (Feb 10, 2012)

Okay. That worked well. Thanks so much. I wrote the expression as Month/Year: Format([Job Start Date],"mm/yyyy"), and used [Enter Month and Year: mm/yyyy] as the Criteria. This allows me to type the month and year as a parameter before the query runs.

If I wanted to go fancier and use a form to pull the report that comes from the query, how would I go about creating that form?

P.S. If it's possible I would prefer the form, for the user's sake, to use the words "January, February, March, etc." and then type a year (perhaps unbound?) in a text box, allowing them to easily pull reports. Is there a way to associate the month's numbers with the month's name?


----------



## OBP (Mar 8, 2005)

Yes you can associate the months and numbers, but you can also use whole months.
Try
Format([Job Start Date],"mmmm/yyyy")
The criteria would then use the format that I gave you in post #10, but you would need to combine the month and year on the form.
You can use Combos for the Month and Year Selection


----------



## krs1716 (Feb 10, 2012)

Once again, I'm investigating methods by looking at Northwind (a dangerous habit, I know), but I like how there is a form that allows all reports to be brought up based on criteria chosen in different fields. Is there a guide to creating that somewhere, or maybe a forum?


----------



## OBP (Mar 8, 2005)

Do you want to create reports or show the data in a form?
Remembering that you can save as a form as a report.


----------



## krs1716 (Feb 10, 2012)

I'm not really sure what I want to do with that. I liked the idea that users would be able to choose a report and then choose the time frame they wanted the report to specify, and then the report would automatically be generated. So...I guess I want to create reports?

P.S. I have a new question. (if I should start a new thread, let me know). I have a query, and a subsequent report, that pulls what's called case notes for individual people (only one person at a time).

In the query, under field, I typed: Contact Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])).

Then, for Criteria, I typed: [Enter Contact Name]

This works well...however, if two people have the same name, I have no way to differentiate between the two. So, I wanted to have a second Criteria that required the user to type the Participant ID (a field I created in my tables- not a primary key) only if two names are duplicates. Thoughts?


----------



## OBP (Mar 8, 2005)

OK, new question first.
I would use a Combo box on the form to select the person, the combo can show "extra Columns" of data so that the user knows which of any duplicates to select. The first, hidden column would of course be the key field, which should be used in the query as the Criteria.
There are a few ways to create "Search forms", how complex, ie how many fields do you expect to search on?


----------



## krs1716 (Feb 10, 2012)

Well...I may have moved too quickly with discussing the form before I had all of my queries and reports completed. However, I know that I would like some reports to be viewed monthly, quarterly, fiscal year, and Year-to-Date. However, others will be by specific date, while others will be between certain dates. Still, others will be by people, like the report I mentioned earlier.

The issue I'm having in totality, in regards to queries vs. reports, is how to format things. My office is currently using Excel to document how many people fit into a certain criteria per month. However, all the information will be documented in the new database. So, I want to replace that method with queries and reports. I just can't figure out how to get the information that I need. Furthermore, I can't figure out how to include only the number of people, not the details of the information. I'm thinking I need to create several small queries that feeds into larger queries that create subreports that create one report. Does that make any sense?


----------



## OBP (Mar 8, 2005)

Can you attach an Excel report version, please don't show any personal data?
You may not need as many Queries and Reports as you think.


----------



## krs1716 (Feb 10, 2012)

Okay, I've uploaded the Excel file that contains the information currently reported. You'll notice there are several sheets. Each sheet represents a different department. There are fields that are the same on all sheets or two sheets, and there are fields that are pertinent to only one department. For the database, the fields that are present on several sheets do not need to be broken down into department.

I erased all personal data, so nothing is filled in. Just so you know, the way it works now is that, as people meet the criteria in the headings, staff change numbers (1 changes to 2, 2 changes to 3, etc.) in the fields to reflect these changes, then document the criteria in comments. In other words, the numbers are very important. We must be able to tell how many people meet criteria within a certain month. However, we must also be able to trace who those people are. (I'm wondering if a detail query and a Summary query would be necessary.) Furthermore, the way this is setup can just be how it looks on the report, since that is all I want the average user to be able to see.


----------



## OBP (Mar 8, 2005)

Do names or numbers go in the Cells B & C etc?
Can the output be reversed, ie the dates at the top going across?


----------



## krs1716 (Feb 10, 2012)

In the cells, staff type numbers. So, if on the 1st of the month, one person qualifies, then staff type 1 and add a comment to describe who it was and what criteria he/she met. Then, on the 12th of the month, another person qualifies, staff changes the 1 to a 2 and type another comment, and so on.

As far as I know, the dates could go on top.


----------



## OBP (Mar 8, 2005)

What cells do the comments go in?


----------



## krs1716 (Feb 10, 2012)

Comments don't go in the cell. We use the "Add a comment" function so that the cell's right corner is colored red, and if you hold the cursor over the cell, the comment appears.


----------



## OBP (Mar 8, 2005)

Right, so they don't appear in the Printed Report?
Do you print the Report or do the users just view the Worksheets?


----------



## krs1716 (Feb 10, 2012)

No, and I'm not so sure the comments need to be in the query. It's difficult to explain, and I'm sorry I'm not doing a very good job of it.

The comments are used so that if we are audited, and they ask where we got the numbers from (so we can't randomly add numbers), we can trace it back to specific people. That's why I mentioned possibly making a detail report and a summary report.

We don't print the reports...that I know of. Actually, some of the numbers are used to create other reports that I haven't even considered tackling yet.


----------



## OBP (Mar 8, 2005)

The reason for the questions is that the output that I described can be achieved with a Crosstab Query.
Have you looked at a Crosstab query yet?
You use the Query Menu>New and then follow the instructions, but you would have to declare the Criteria in your current query as Parameters.


----------



## OBP (Mar 8, 2005)

My previous reply was to your previous reply.
Surely with Access the traceability is back to the table.


----------



## krs1716 (Feb 10, 2012)

The issue I'm having with the crosstab query (which I did try to use...fruitlessly) is that each of the columns that you see are a product of two or more fields in the tables. So, I'm not sure how query all that information together. Will I need to make small queries that feed larger ones?

P.S. I just found out that I do keep the information separated by department (people).


----------



## krs1716 (Feb 10, 2012)

Is there a way for me to show you my database, and then I can tell you which fields in the database feed into the cells that you see in the Excel worksheets?


----------



## OBP (Mar 8, 2005)

That is why you would need to switch the Columns for rows, so that the dates are at the top.
You can up to 3 fields in the Rows section.


----------



## krs1716 (Feb 10, 2012)

But you can only make a crosstab query from one object.


----------



## OBP (Mar 8, 2005)

Can you supply me with a copy of the table(s) with some dummy data?


----------



## krs1716 (Feb 10, 2012)

I created a new database that contains only the tables that contain the information we are discussing. The information contained in the database is fictional. Feel free to alter it in any way to obtain the results you need.


----------



## OBP (Mar 8, 2005)

Sorry, can you convert it to Access 2003 please?
Or I can post a blank 2003 database and you can import the tables in to that.


----------



## krs1716 (Feb 10, 2012)

Okay. Sorry about that.


----------



## krs1716 (Feb 10, 2012)

Is it possible to create a query that includes all the fields that would contain my criteria, and then write an expression that totals (counts) the number of people that include specific data?

The problem I'm having is that my queries are returning information that is not relevant. So, I want an expression that would mean: Total number of people registered in this month AND this checkbox is selected AND this person is the caseworker AND the person is involved in this program.

Does that make sense?


----------



## OBP (Mar 8, 2005)

Yes it should be possible.


----------



## OBP (Mar 8, 2005)

Is the General Information Table ID field the CustomerID?
The database that you posted does not have any relationships setup, so I am not sure how to relate the data for your query.


----------



## krs1716 (Feb 10, 2012)

Oh. The relationships must have been lost in transition. Yes, the General Information ID is the same as Customer ID.


----------



## OBP (Mar 8, 2005)

I do not see any many to many linking table in the database. 
can you explain how more than 1 person gets related to Services or Case Notes etc
This question is in relation to "Total number of people registered in this month AND this checkbox is selected AND this person is the caseworker *AND the person is involved in this program*"


----------



## krs1716 (Feb 10, 2012)

Um...I couldn't remember why I made a many-to-many linking table, so I deleted it. Guess that wasn't a good idea.


----------



## OBP (Mar 8, 2005)

OK I will create the table and see how it goes.


----------



## krs1716 (Feb 10, 2012)

Thank you.


----------



## OBP (Mar 8, 2005)

Now which table(s) do I need apart from General Information?
The Services table has the "Registration Date" in it, is that the Customer registration date, or the service registration date?


----------



## krs1716 (Feb 10, 2012)

It depends upon what you're trying to gather. You definitely won't need the case notes table. Most likely, you'll be working with the following tables:
General Information
Services
Training
Exit Information


----------



## OBP (Mar 8, 2005)

Can you explain how the data should work, ie does the customer have more than one service, can the service have more than one customer and the same goes for the other 2 tables, can they have more than one customer?


----------



## krs1716 (Feb 10, 2012)

Yes, customers can have more than one service. In fact, they all will. Services will all contain multiple customers. What happens is that a customer will register (website registration), then they enter into our services based upon the level of their needs. From that determination, they can enter into detailed services. In detailed services they can be involved in several at one time, or in one at the start, and then change the service. They can also be involved in different types of training, which guarantees that they are in multiple services. Finally, when they have completed the process, they exit. Upon exit, they receive a placement date, exit date, and most of the fields in the Exit Information table will be filled.

In order to keep track of specific instances, the Case Notes Table is necessary. I hope this information helps.


----------



## OBP (Mar 8, 2005)

OK, that is good info, it means there will be 2 many to many tables one for services and one for training.
I will start with the query for Customers/Services first.


----------



## krs1716 (Feb 10, 2012)

Ok. Thank you.


----------



## OBP (Mar 8, 2005)

Is the Customer "Prep Entrance Date" the registration date?
As I think the registration date should be in the Customer table.
I think you also need to look at Orientation date, Active status (if it applies to Customer), Customer Survey data (maybe should be in the Customer/service table) plus all the Start dates if they apply to the Customer.


----------



## OBP (Mar 8, 2005)

See my previous post about my reservations about where the data should be in the tables.
To demonstrate a Crosstab query take a look at the 2 queries in the attached database. As I am not sure where to find the data that is shown in your Excel Worksheet I have just used Program & Level Of service as the left hand row headings and the Date (as Month) for the Column Headings with the Customerid counted for the Grid.
It uses the General Information Query as it's data source.


----------



## krs1716 (Feb 10, 2012)

I understand what you're saying about the information in the customer table, but if I followed that formatting, then everything would be in the customer table. Everything is pertaining to the customer in this database. Maybe my misunderstanding isn't with queries and forms, as I thought, but with tables. As for the Prep date, it is not the registration date, though sometimes they are the same date. The registration date is the registration date. I'm so confused now. Should I re-format my tables?


----------



## OBP (Mar 8, 2005)

Well I can do it for you but I don't understand the "Process" the way that you do.
I would have thought that anything to do with the Customer should either be in the Customer table or the CustomerServices table.
It depends on whether it is a 1 off value like the Registration date which could go in the Customer table or it is something that applies to the services being provided. If it relates to the Customer and service then it should go in the CustomerService table.
For instance if it is a date where the customer finishes a particular part of the service, but you could get the same finishing date for that customer and another part of the service then it would go in the CustomerService table.

Getting the data in the correct tables and the Relationships correct is the most important part of designing the database.

If you could tell me what the Process is I can help you make the decisions.

Did you look at the Crosstab query?


----------



## krs1716 (Feb 10, 2012)

Okay. Can I explain the process to you in a place that isn't public? Also, it may be that some information should go in the CustomerService table, and I just wasn't sure if it was necessary.

The crosstab is great, but I'm wondering if it's the best option for me because you can only have three rows, and all of my criteria are four or more.


----------



## OBP (Mar 8, 2005)

On the crosstab you can only have 3 columns on the left hand side, but as many rows as you like.
I will private mail you my email address and you can send me a description of the process.


----------



## OBP (Mar 8, 2005)

In the Services table what is the function of the 
WorkKeys Completed
WorkKeys Date
Placement Date
Support Services Start Date
Support Services End Date
ie what do they represent, is it the Customer's experience in the Service or does it relate to the Service table, as it looks like it is the Customer's data, which means it should be in the CustomerServices.


----------



## krs1716 (Feb 10, 2012)

Those are all relating to start and completion of a specific customer's service.


----------



## OBP (Mar 8, 2005)

OK, I am getting the picture.


----------



## OBP (Mar 8, 2005)

In the training table are the start and end dates fixed or for when the customer starts and ends?


----------



## krs1716 (Feb 10, 2012)

For when each customer starts and ends.


----------



## OBP (Mar 8, 2005)

You have a field on the Contact Details form, first tab, called "Special Circumstances", it does not have the same field in the general info table. Do you still need it?
In the training table you have a field called "Training Provider", do you have "providers" that would provide training for more than one Customer?
ie Common providers.


----------



## krs1716 (Feb 10, 2012)

Um...I erased Special Circumstances for the transition to 2003. It was a field list that allowed multiple values. So, I do need it. As for the Training Providers, there probably would be common providers, but I don't know which ones.


----------



## OBP (Mar 8, 2005)

OK, you can put back the Special Circumstances in your version. You could add a Table of Providers for selection in a combo.
I have re-arranged the data so that there is a CustomerService linking table and a Training Linking table.
This will allow you to use the new TrainingCategories and the Services table data in one field for each, will allow the training category or service to be used in one field in the crosstab query, which will group them, but list all those that have data.
I have arranged the fields in the tables how I think they should be, but you would have a better idea about that in terms of what fields could be in the Services and which in CustomerServices etc.
I have changed the Training Subform to something like how I think it should be, some fields would only be visible for certain categories like when OJT is selected in the combo.
Have a look at the crosstab and subform and let me know what you think. 
If this is the way to go then I can create the Services subform or you can.


----------



## krs1716 (Feb 10, 2012)

I've reviewed the changes. I guess I'm a little confused as to the why of everything. Why the previous setup wasn't ok? Why this one is better?


----------



## OBP (Mar 8, 2005)

Because you had separate fields for the various Services and Training and as you say Crosstabs can only handle 3 fields, whereas this way there is only 1 field with as many Services and Training categories as you like.
It is the normal method of showing Many to Many relationships between 2 tables.

Of course if you don't want to use a Continuous Forms mode subform you can show the Training data on a single record subform, I only created that way so you could see the different Categories selected in the combo.
You can use your method if you want, we would just need to come up with another method for collating the data.


----------



## krs1716 (Feb 10, 2012)

I like the idea of it. My concern is that users will have problems when it comes to inputting the data. Because certain fields pertain only to certain programs, I'm worried that they may fill in all fields, even when they are not related to the customer. Whereas, in the old system, the titles told them which fields to fill in.


----------



## krs1716 (Feb 10, 2012)

Would a DCount method, with a Criteria, work to count the records?


----------



## krs1716 (Feb 10, 2012)

Did you change any settings for the "Contact List" or "Contact Details"? The problem that I was having with adding new customers is no longer a problem in your version of the database, and I am at a loss as to why.


----------



## OBP (Mar 8, 2005)

RE "Would a DCount method, with a Criteria, work to count the records?", do you mean using your version or mine.

I can't actually remember what I did, as it is automatic for correct that kind of error as I go along, plus of course the fact that I was working on 3 other posters questions at the same time.


----------



## krs1716 (Feb 10, 2012)

The DCount question was in regards to my original database. I found a couple of other things that pose a problem. I keep getting updates on what needs to be included/excluded from the database, making it somewhat difficult to create. Anyhow, one field that I found was no longer necessary was the "Level of Service", because the information is given in different areas. So, I'm not sure what that does to your version. I am sincerely sorry.

As for the correction of my database, that's ok that you don't remember. I understand that you're expertise is in demand. Do you think, if I copy your forms from your version of the database and paste them into my version of the database, they would still work?

By the way, I tried to write my own expression for DCount (my first solo expression ever)...it didn't go well.


----------



## OBP (Mar 8, 2005)

Dcount can be a bit tricky to get the correct syntax, Queries have their own "Grouping" functions which do Group by, Count, Sum, Max Min and quite a few others as well so you probably won't need Dcount.

The only problem with importing my forms is that you need my Tables & Queries as well, otherwise you won't have the correct data structure. You can always move, edit and delete fields once you have imported them though, but you must do so to the tables, queries and forms otherwise you will generate errors. 
Let me know if you still need me to work on what I had created as it is no where near finished, it was just an example of how I thought the data would be best structured.

It is an old saying with database programmers that a database is never really finished, users and bosses change their minds and when they see some of the things it can do you get the inevitable questions like "can you add so & so report", can you get it to display it "this way" etc.

I will have to go now to pick up the Grandchildren. I should be back in a couple of hours.


----------



## krs1716 (Feb 10, 2012)

Thank you for the words of encouragement. I like what you've done, and I think we should move forward using some of your additions/alterations. As for the Training Subform...I made some changes. However, I'm wondering, for the sake of readability and ease-of-use, if there is a way for certain fields to only be available when certain categories are chosen. For example, if OJT is chosen, only the fields pertaining to OJT would appear on the form.

I've attached my altered version of the database that incorporated your changes. You'll see how I changed the form (mostly just formatting in an attempt to appeal to staff...it didn't work).

I think what I'll do to ensure that the problem with my forms is fixed is when all is said and done, I'll copy and paste what I'm keeping from my database into your version.

Thanks again!


----------



## OBP (Mar 8, 2005)

Yes you can only show the fields when required, I will take a look at what you have done.


----------



## OBP (Mar 8, 2005)

Do you have a space restriction, as your subform is rather narrow, which makes it taller than it needs to be?


----------



## krs1716 (Feb 10, 2012)

Just a note...I don't have it formatted this way in the version that I attached, but I would like it so that Training Provider is only available for OJT or ITA. Also, I found out that WorkKeys is not a Training Program.


----------



## OBP (Mar 8, 2005)

OK, by the way, the ID fields where you have "do not edit" can be set to Visible = "No" so the users will not see them.
Did you see my previous question?


----------



## OBP (Mar 8, 2005)

Are there any fields to be shown for Shalenet and Wolip?


----------



## OBP (Mar 8, 2005)

Actually there is a problem with making fields visible on a form in Continuous forms mode, I forgot it only shows whatever is in the Current" record.


----------



## krs1716 (Feb 10, 2012)

Whoa! Lots happening here.

No real space restriction- just made it smaller so that things looked neater. I would prefer the Contact Details form not to be too much bigger.

As for the ID fields, I wasn't sure if that would ruin the master/child link.

The only fields that need to be visible for SHALENET and WOLIP are the start/end dates.

I guess for the continuous form, there would be no way for you to know what would happen if I were to change the field to allow multiple values?


----------



## krs1716 (Feb 10, 2012)

Nevermind, can't do that because it's not a field in a table. It's a field in a form.


----------



## OBP (Mar 8, 2005)

No, which field needs to be multiple values?


----------



## OBP (Mar 8, 2005)

Can't fields in forms be multiple values in Access 2007?


----------



## krs1716 (Feb 10, 2012)

I was hoping I could make Training Category a multiple value field, and so as different selections were made, different detailed information would appear, but that won't work.


----------



## krs1716 (Feb 10, 2012)

I think it has to first be a field in a table. The only place I've seen that option is in the detailed view of a table.


----------



## OBP (Mar 8, 2005)

You don't need to worry about that, the vba that I have created will do that. But it would probably be best if the subform was set back to Single Form mode and I provide "Previous Course" and "Next Course" buttons.
Have a look at this version which is using the vba and see if it would be too confusing, to display the fields for the second record you need to click on it.


----------



## krs1716 (Feb 10, 2012)

What am I clicking on?


----------



## krs1716 (Feb 10, 2012)

Found it. I wouldn't have a problem with it, but I can guarantee that others would. What did you mean when you said about "Previous Course" and "Next Course"?


----------



## OBP (Mar 8, 2005)

I have VBA code that would display buttons along the bottom of a Single Form which would basically say
"displaying course x of x number of courses they have attended". Then there would be command butttons to move to the "next course", but only if there is one, or previous course if there is more than one and you move off the frst course.
They are like the form's navigation buttons, but they are more obvious.


----------



## krs1716 (Feb 10, 2012)

Unfortunately, upon discussion with staff, I don't think that will work. Apparently, we need to be able to compare the information (like the end date of one vs. the start date of another). Thus, the original setup. Thoughts?


----------



## OBP (Mar 8, 2005)

Why do you need to be able to compare it?
It can be built in to the database that the later course can't start before the old course is ended, if that is a concern?
Or you could have a summary, that only shows the start & finish dates.


----------



## krs1716 (Feb 10, 2012)

It's not that. It has something to do with how long it took from the ending of one to the starting of another. I'm not entirely clear on the reasoning, but, apparently, it's necessary.

I do know that they can't be used more than once, if that helps.


----------



## OBP (Mar 8, 2005)

How do they do it at the moment?
That data can be displayed on the header or footer of the Form, perhaps if you can pin them down on exactly what they want to see we can come up with a solution.
I would have thought that relying on someone just "looking" at the data would not be as good as having it computed for them, or is the original data in Excel?


----------



## krs1716 (Feb 10, 2012)

I think it has more to do with visual readability. It's possible that the problem is more in how busy it looks, which is just formatting. Is there a way to show records differently than one on top of another? Like perhaps in a box like format. One on the upper left, upper right, lower left, lower right?


----------



## OBP (Mar 8, 2005)

I don't know if Access 2007 can use a box type format, certainly Access 2003 can't. Although it can on a report.
I have to go now, it is 7:15pm here in the UK, I will talk to you tomorrow.


----------



## krs1716 (Feb 10, 2012)

Okay. Thanks so much for the help.


----------



## OBP (Mar 8, 2005)

How do you wish to proceed?

I was thinking about the "Box" format and it is actually possible, but would require quite a bit of work. It would require 4 identical subforms, each one displaying one record. 
The problem with that method, like your original form is that if they ever add another training or Service category you have to redesign the forms.


----------



## krs1716 (Feb 10, 2012)

What about if we made three subforms for the page? One would include your category drop down and the start/end dates. The other two would include the information pertinent to OJT and ITA, respectively. Then, can we use a VBA code under On Data Change for the category subform that uses an Ilf statement to specify when the other two subforms appear?


----------



## OBP (Mar 8, 2005)

That should work, I will take a look.
Do you want to use a similar system for Services?


----------



## krs1716 (Feb 10, 2012)

I think a similar setup will work for services. Most, if not all, of the items on the left side of the Services Subform should always be present. Then, the dropdown list of categories will be beneficial for where I used to have the "Program" set up. My only concern would be the drop down box for Support Services. That is definitely only associated with Support Services, and so it shouldn't be available for all choices.


----------



## OBP (Mar 8, 2005)

Ok, it will only appear when "Support Services" is selected.


----------



## OBP (Mar 8, 2005)

The separate Subforms for OJT & ITA can't be used with a Continuous forms mode form, subforms are not allowed.
So I am afraid we can't do that unless we use the single form, if you could it would also suffer from the same problem of showing only the current record's subform.


----------



## krs1716 (Feb 10, 2012)

Okay. Can we make the Category subform continuous, the other two single, and not include the single forms on the continuous form...have them separate on the same page, though.


----------



## OBP (Mar 8, 2005)

OK.


----------



## krs1716 (Feb 10, 2012)

Just a thought as I was testing the forms...is there a way that once one of the training options has been used, it is no longer an option in the drop down list?


----------



## OBP (Mar 8, 2005)

I will need to think about that.


----------



## krs1716 (Feb 10, 2012)

Ok. Thanks.


----------



## OBP (Mar 8, 2005)

OK, take a look at phase 1, this is the Training tab with the 3 subforms in place, see if it is what you want.
If it is I will create the Service Version and then look at filtering the Combo box.


----------



## krs1716 (Feb 10, 2012)

I looked over it. I think that staff will still have some complaints. What if we allowed the OJT and ITA subforms to be showing always?

I adjusted a few things on the attached file. Let me know if that will still work for the queries (take special note of Training Provider).


----------



## OBP (Mar 8, 2005)

OK, if you are happy with that, so am I.
Do you want the same for the Services?
I will have to look at the queries again as I am not sure what fields you want in your report as it seems to have changed a bit from your Excel sheet.


----------



## krs1716 (Feb 10, 2012)

Same for services...the programs will be in a continuous form. Not entirely sure what to do about Support Services Combo Box, though. It really should be with Support Services information. Thoughts?

As for the information on the left side of the subform, it can just remain as is.

By the way, I finally figured out how you fixed my forms: I had been using subforms for each page, with no information actually contained on the main form. You took my General Information Subform and placed it directly on the form. For whatever reason, that fixed the problem.


----------



## OBP (Mar 8, 2005)

Yes I remember now, if you don't do that and just have the ID on the Mainform a subform with the same data tries to create a new record.
So it is always best to have the data straight on the Tab.


----------



## krs1716 (Feb 10, 2012)

Right. Lesson learned!


----------



## OBP (Mar 8, 2005)

Do you only send one Customer Survey for all the services?


----------



## krs1716 (Feb 10, 2012)

To the best of my knowledge, yes.


----------



## OBP (Mar 8, 2005)

There are now 2 subform_training forms which one is your preference, subform_training or subform_training1?

Can we create a table for Support Service Options?


----------



## krs1716 (Feb 10, 2012)

Um...I don't know which one is which, but I want to use the one that has the continuous form on the top, and then the OJT and ITA forms at the bottom.

Upon consideration, a table would work well for Support Services. Can it then become a continuous form?


----------



## OBP (Mar 8, 2005)

That is subform_training.
I have created tables for all the Combos that have value lists as they are not good for flexibility. If someone wants to change a name or add a new one and there is not an Access Programmer around they are stuck.
So the tables will have queries for the Combos and subforms. You can then have tabs for the subforms to keep the tables up to date.
I am looking at the Services subform as well, do you want to stay with your design with some VBA or use a Continuous mode subform.


----------



## krs1716 (Feb 10, 2012)

Thank you for those adjustments. I was somewhat concerned myself about how to handle additions and deletions after I leave. That will help tremendously.

As for the Services subform, I'm not sure what you mean by staying with my design or using a Continuous mode subform. I don't want the whole form to be continuous. However, I would like there to be a program subform that is continuous. Is that what you mean?

However, now you have me thinking...I'll have to consult with staff about that.


----------



## OBP (Mar 8, 2005)

I wasn't sure exactly what you wanted, but now I do.
Do you have a list of programs, or is it already in the database?


----------



## krs1716 (Feb 10, 2012)

The programs would have been listed in the original database. If you open the Services Subform, on the right side there is a Label "Program." Anything under that label is one of the programs, with the exception of the ones we now have listed under training.


----------



## OBP (Mar 8, 2005)

Is Literacy a Service Program or Training?

ps ignore this question, I found it in the new "Services" table, should it be renamed programs?


----------



## OBP (Mar 8, 2005)

Here is my latest effort, I am sure you can re-arrange the Service subforms to suit what you want.


----------



## krs1716 (Feb 10, 2012)

Okay. I think that will work well. I do have some concerns, though.

1. A few fields seem to be missing, and I can't find them anywhere in the database. (Registration Date, Employed at Registration, Prep [checkbox], Prep Entrance Date, and WorkKeys Completion Date)
2. Can Support Services be a continuous form?
3. Do the ID numbers have to be visible for the forms to work properly?
4. How will this setup determine my queries for my reports? (I'm assuming that multiple queries will make up a report that looks similar to the Excel document we're currently using)
5. Why are there so many tabs on the main form? Do I need them to be there?


----------



## OBP (Mar 8, 2005)

1. Registration Date is on the general tab, the others I appear to have missed.
2. I thought Support Services is a continuous form.
3. No they should be hidden, they are only there to show you how the data is related.
4. If you can give me the current Field names that you want in the report I will take a look.
5. The tabs are only there because i like to keep everything in one place.


----------



## krs1716 (Feb 10, 2012)

Okay. I've been working in the database that you posted. So, don't worry about the missing fields. I added them. Support Services Option didn't show up as a continuous form in my copy, but I messed around with it, and I think I found something that will work.

The field names are complicated. I was referencing my post about creating a crosstab query that would then be reports (the one that sparked the mass re-formatting of the database).

By the way, thank you so very much for all your hard work. I appreciate it.


----------



## OBP (Mar 8, 2005)

Ha, Support Services Option as a continuous form, not the Support Services.
I only need the fields you are sure of.
Perhaps we can get there between us.


----------



## krs1716 (Feb 10, 2012)

Um...my Support Services Option continuous form doesn't work. I don't know what happened, but it is so far gone, I don't know how to figure it out. 

Do you remember the Excel spreadsheet from post#23? Those Excel sheets are what I need to replicate as a report in Access. I can type up a document that explains which fields fill each of those numbers in the spreadsheet in order to create a query/report. Would that work?


----------



## OBP (Mar 8, 2005)

That would help.


----------



## krs1716 (Feb 10, 2012)

Okay. It will take me a little while to get it all typed up. Then, I'll e-mail it to you. Is that ok?


----------



## OBP (Mar 8, 2005)

That will be OK.


----------



## OBP (Mar 8, 2005)

Can you post a copy of your current database so that I am working with your version please?


----------



## OBP (Mar 8, 2005)

Ok, I am looking at your Excel sheet, where or how do I identify in the database "New Customers" and "Intensive".
I see from the word doc that ECS Start date, Funding Stream and Career Planner are involved.

ps I have found "Intensive" under service levels.


----------



## krs1716 (Feb 10, 2012)

Sure...here it is.

Note: Support Services continuous form is still not working properly, so you may have trouble with that information.


----------



## OBP (Mar 8, 2005)

I will see if I can fix it.
I am making some progress identifying some of the data re my post #136


----------



## krs1716 (Feb 10, 2012)

Intensive is ECS. I don't think I'm using Service levels at this point, so that won't help if information isn't being put into that field. The document is telling you what needs to be selected in order for that field to add a record. As in a customer has to have ECS chosen under program in order to be qualified.

Does that make sense?


----------



## krs1716 (Feb 10, 2012)

In addition to the post above, customers must meet *all* of the criteria I have listed in parentheses in order to constitute qualification for the STAT category.


----------



## OBP (Mar 8, 2005)

Yes, I I am getting there, I will put the data in as identify the need for it..
Your New Subform works great, what did you think was wrong with it?
Should it only show when Support Services is selected?


----------



## krs1716 (Feb 10, 2012)

Well...that would be nice, but the problem is that when I try to do more than one support service and then exit, an error message pops up.


----------



## OBP (Mar 8, 2005)

I am not sure what you mean by more than one, i have just entered Mileage & Daycare, is that what you mean?


----------



## OBP (Mar 8, 2005)

I have looked at the current design of the services tab and I can foresee a problem.
You have a subform for the "Program", but the data goes in to the Customer Services Table with the other data at the top of the tab in the Customer Services subform. The top subfrom only has one set of data, but the "program" subform currently has 2 and can have more than that.
The results of this is that the table has no data in the fields in the top subform for all the records other than for the first record of the program subform, there is no related data for the later records.
Similarly the Service Options would only be related to the Customer, if you want it to relate to each record in the program the design needs changing.


----------



## krs1716 (Feb 10, 2012)

Okay. How would I edit that format in order to fix the problem?


----------



## OBP (Mar 8, 2005)

Does the top section relate to the Customer or to the Service?
I assume that the Service Options relate to the Customer and the Service.


----------



## krs1716 (Feb 10, 2012)

I'm not sure how to answer that. I guess some are related to the customer, and some are related to both the customer and the service.


----------



## OBP (Mar 8, 2005)

Can you list the ones that are related to just the service?


----------



## krs1716 (Feb 10, 2012)

I don't see any that pertain only to services.


----------



## OBP (Mar 8, 2005)

Are there any that pertain only to the Customer?
The problem is the Continuous form subform where you are entering many records, but the top subform is single and doesn't have the records to go with it.
If the lower service records have the same the top form data then the lower form should be in a separate table.


----------



## OBP (Mar 8, 2005)

I think I originally had the whole form as a continuous form.


----------



## krs1716 (Feb 10, 2012)

Okay. Is it a problem to contain the data in a separate table?


----------



## OBP (Mar 8, 2005)

I have spent hours getting this to work as every time I made changes it corrupted the original forms. So I eventually gave up deleted the forms and service tab and rebuilt them from scratch.
You now have synchronised forms, which works reasonably well.
However they still do not make sense to me, most of the items on the main service form seem to apply to the Customer and not to the Services, the ones that seem to apply to the services are 
Funding Stream and purpose of funding
Level of Service?
Active Status? but only if it applies to the service program and not the whether the customer is active in the overall service.

I wonder if there should be a General Service tab to hold the other data.

Anyway have a look at the new service tab and see what you think.


----------



## krs1716 (Feb 10, 2012)

I can understand your confusion. I've been working on this for months, and I am still surprised by requests from staff. As far as the Services vs. Customers issue, everything relates to the customer in some way. While you were working on this version, I changed a few things on my version, and I'll be merging them soon. On my version, level of service and purpose of funding stream are deleted, and Active Status has been moved to General Information (it really does only apply to Customer, not CustomerService).

The whole point of this part of the database is to document what applies to the customer. I originally started with only one table, but as you can see from the amount of data being collected, I soon found that one table wasn't sufficient. Thank you so much for your hard work.

Questions: 
Is there any formatting that I should not do on the services tab (for fear of compromising the data relationships)? i.e. making IDs non-visible
Also, the funding stream drop down menu only shows numbers, not the titles of the funding streams...can that be remedied?


----------



## OBP (Mar 8, 2005)

To answer your questions, yes the IDs can be hidden.
The funding stream can be properly shown by setting the column count to2 and the column widths to 0cm;2cm


----------



## krs1716 (Feb 10, 2012)

I think that this will work now. Thank you so much for your help. I really appreciate it.


----------



## OBP (Mar 8, 2005)

Ok, now we need to get back to your original question and identify the fields that we need for the Query(s).


----------



## krs1716 (Feb 10, 2012)

Do you think it would expect too much of the database if we made individual reports (every orange colored field in the STAT Explanation sheet would be a report)? Then, count the number of records, and use the counts to create a STAT report for each department?

Then, each of the numbers (1, 2, 3, etc.) would be a hyperlink to the smaller reports that would give the details?

Does that make any sense?


----------



## OBP (Mar 8, 2005)

I don't know that we need to. 
Once I get the correct fields from the tables to create a query we will have a much better idea of how to manipulate the results.


----------



## krs1716 (Feb 10, 2012)

Alright. Then, the fields that we will be using are all outlined in that Word document that I e-mailed to you. The ones in orange represent the individual columns from the spreadsheet. The parentheses list the fields that make up the query (I think).


----------



## krs1716 (Feb 10, 2012)

By the way, here's my updated version of the database.


----------



## OBP (Mar 8, 2005)

I understand that, but where do I find the equivalent fields in the tables, do they have the same names?


----------



## OBP (Mar 8, 2005)

That version won't run at all for me and I can't modify it at all.
Can you untick the last ticked VBA Library Reference and also remove the Contact Detail form's On Load event as well?


----------



## krs1716 (Feb 10, 2012)

Yes, the fields I mentioned in the parentheses should have the same names when you look for them in the tables. The only exception is that I think Career Planners are called "Career Planning" or "Career Plans", but the options in the box all read Career Planner. 

As far as which tables to look in...most of the fields for the first two departments should be in Customer Services and Exit Information. The Training Department should use Customer Services, Exit Information, and Training.

Edit: Whoops. I just found an exception to the above statements. ECS Start Date will be determined by combining Service= ECS (from the Services Table) and Start Date (from the CustomerServices Table).


----------



## krs1716 (Feb 10, 2012)

I'm not sure what you mean by VBA Library Reference.


----------



## OBP (Mar 8, 2005)

Press alt + f11 and on the main menu Tools>References.


----------



## krs1716 (Feb 10, 2012)

Try this one.


----------



## krs1716 (Feb 10, 2012)

Just wondering how it's going. Any problems or questions? Did that last database work?


----------



## OBP (Mar 8, 2005)

Sorry, I haven't been working on it over the weekend.
I will take a look.


----------



## OBP (Mar 8, 2005)

It is still the same, but never mind I will try and get the query working with the fields that you need.


----------



## OBP (Mar 8, 2005)

Your 5.1 version does not appear to be the same as mine, you do not have the Registration date and Employed at Registration fields in the General Info table that my version has?


----------



## krs1716 (Feb 10, 2012)

No, they belong in Customer Services. I guess I don't understand which checks need to be unchecked in order for you to see it properly.


----------



## OBP (Mar 8, 2005)

Don't worry I have fixed it now and the forms work quite well, although without the Record Selectors you can't see which Services on the left subform apply to the main service record.
Do the Registration Date & Employed at Registration fields apply to the services table? As they appear to belong in the general info of when the Contact came in to the program rather than into an individual service.


----------



## krs1716 (Feb 10, 2012)

Glad to hear things are working properly, now. 

The Registration Date and Employed at Registration belong in the Customer Services because, like all of our programs, they are individualized. (Technically, I guess Registration Date belongs in Customer Services, and Employed at Registration belongs in General Information...but I'm not fond of breaking them up because they go hand-in-hand).


----------



## OBP (Mar 8, 2005)

I found it & deleted the question as you answered it. 

New question, can the DW funding stream apply to an Adult and a Youth?

I notice that in both cases you have Career Planner 4, is that the real data or will something else go in there?


----------



## krs1716 (Feb 10, 2012)

I don't understand where Youth came from.

Career Planner 4 is a place holder for someone's name right now, for privacy reasons. Will that be a problem.


----------



## krs1716 (Feb 10, 2012)

It might be beneficial for you to see the entire database, instead of the small portion you're working with now. However, I don't know how to upload it, since it is so large.


----------



## OBP (Mar 8, 2005)

If you compact & Repair it and then zip it you should be able to email it to me.

I didn't realize that there was more of it.


----------



## OBP (Mar 8, 2005)

The Youth comes from the General Info, Youth tick box.


----------



## krs1716 (Feb 10, 2012)

I had the same thought, but it is still too large.

The Youth checkbox denotes a department taking credit for the customer. It is relevant to some other reports. It has nothing to do with STATS.

As for DW and Adult funding streams, they are completely different. They denote which governmental program is funding a particular customer's services and/or training.


----------



## OBP (Mar 8, 2005)

OK, now it is clear to me.

How large is the zipped file?


----------



## krs1716 (Feb 10, 2012)

The database is 1,257 KB-zipped.


----------



## OBP (Mar 8, 2005)

That should be OK for emailing to the address you emailed the Word doc to.


----------



## krs1716 (Feb 10, 2012)

Okay...I sent it. Hope that helps give you a better understanding of how the database is supposed to work.


----------



## OBP (Mar 8, 2005)

Ok, I have it. I can see what you are trying to do, there is not too much missing from my version.
I will continue with it tomorrow.


----------



## krs1716 (Feb 10, 2012)

OBP,

Just wondering how things are going. This is my last day this week, so I won't be responding until Monday. Any questions or concerns?


----------



## OBP (Mar 8, 2005)

I am currently working with the Complete database as the other ones are giving me grief again on the Forms.
I have currently got the data in the tables for the Trade Department "Placements" and I am going to look at the best way to organise the data for the Crosstab output.
At the moment I don't have any questions. I am juggling with 4 databases plus any questions on here, so it is a few more hours in the day that I need. It is not very efficient to keep switching between them as I tend to lose track of where I am, my brain is not as quick as it used to be. Hopefully I will have something by Monday.


----------



## krs1716 (Feb 10, 2012)

Okay. Glad to hear about the progress. I didn't mean to rush you. I just wanted to be sure that any questions you had would be answered quickly.


----------



## OBP (Mar 8, 2005)

I do have a question, what is the difference between Careerlink Change and CareerLink Referall?
As your word doc does not have an explanation for the Careerlink Change.

Another question, are any of the heading mutually exclusive, ie, if someone has a Placement can they also have a Discontinue and/or workkeys etc?


----------



## krs1716 (Feb 10, 2012)

Career Change is not used anymore, that's why it wasn't explained in the Word doc- sorry about that.

Customers can be in more than one heading within the same month.


----------



## krs1716 (Feb 10, 2012)

Just a quick question: Will STATS be one report or several reports, when it's all done?


----------



## OBP (Mar 8, 2005)

That depends on what you mean by the Stats, you have one sheet per section, so we should be able to manage one report per section.
I was hoping it would be one query and one report, but I am now of the opinion that it will be multiple queries combined in to one query and then one report, but I may be wrong and end up with more than one per section.


----------



## krs1716 (Feb 10, 2012)

Okay. I was just wondering. I'm starting to see the light at the end of the tunnel on the forms and other objects I've been making, and I'm starting on the dialog boxes for reports, so I wanted to know how I should be setting them up.


----------



## OBP (Mar 8, 2005)

You create what you want the reports to do on your Form and we will see if we can fit the reports to it.
At the moment I am working on another database I should be back on yours soon.


----------



## krs1716 (Feb 10, 2012)

Okay. Thanks


----------



## krs1716 (Feb 10, 2012)

In the process of creating reports, I have come to realize that there is something wrong with the services tab in Customer Details. Can you check on your version to see if anything is wrong. The problem seems to occur when I try to add multiple entries in either service options (Program) or Support Services. It also seems to happen sporatically, so I would recommend trying several combinations at different instances. I have tried to find the problem, but, to my dismay, it persists.


----------



## OBP (Mar 8, 2005)

Which version of the Services Tab are you using the one in OBP Database 5 or the one in the Career link database, as they are different.


----------



## krs1716 (Feb 10, 2012)

I believe I have been using the one in CareerLink Database.


----------



## krs1716 (Feb 10, 2012)

I went through and updated the tables and relationships based upon OBP Database 5. The error is no longer appearing. However, I now get the following message when I try to alter Support Services: "The expression you entered has a field, control, or property name that Microsoft Office can't find. 2424". Also, my reports are not pulling information correctly now.


----------



## krs1716 (Feb 10, 2012)

I think I found the problem that the above error message was detecting: I forgot to update the hidden ID fields so that they reflected the new relationships.


----------



## OBP (Mar 8, 2005)

I think I have found the solution to your Report output, it is not very elegant but it seems to work ok.
Shall I send you a copy of the CareerLink Database or post it on here?

The solution involves using a query for each section of the output, up to now for the Trade Dept. I have done the Placement, the Negative Exit and the Neutral Exit Strategies and then adding them all to a query with the Year & Month from a new "Month" table as the link. When this is output to a Report you can change the Labels to suit your current Excel Format.


----------



## krs1716 (Feb 10, 2012)

You can just send me your copy of the CareerLink Database in an e-mail. It's easier for me to see the changes when I'm comparing similar databases.

Thank you!


----------



## OBP (Mar 8, 2005)

I have sent it.


----------



## krs1716 (Feb 10, 2012)

I'm not entirely sure what's happening in the queries, but, from what I can see, everything involving the month and year is pulled from the registration date. I don't know if that will work. For instance, the TD All Query is showing a Neutral Exit in January. From what I've found, there is no neutral exit in January, there is a neutral exit in February (because exits are based off of Exit Date not Registration Date).

I understand that there is a lot going on behind the scenes, so I could be reading it wrong. Other than that...I like the idea (especially since it should be fairly easy to add more years- for future users).


----------



## OBP (Mar 8, 2005)

That is OK it was my lack of understanding, I used the registration date for the Exit as well as the Placement Month, so you can either add another "Month" column for the Exits and link to that or move the "Month" Column to the Grouping Query itself.


----------



## krs1716 (Feb 10, 2012)

I'm not entirely sure what you mean by moving the "Month" Column to the Grouping Query. Do you mean for TD Placements I would have a month column based on Placement Date, then for TD Volun.Disc. Neutral I would have a different month column based on Exit Date, etc.?


----------



## OBP (Mar 8, 2005)

Yep, got it in one.
But the other method is just as good.


----------



## krs1716 (Feb 10, 2012)

So just re-label each Month in the Trade Department Output to "Placement Month" "Exit Month" "Registration Month" based on the different dates?


----------



## OBP (Mar 8, 2005)

To be on the safe side I wouldn't leave a space between the words, but that would be a good way to do it.


----------



## krs1716 (Feb 10, 2012)

How would TD All be formatted then? Would it still be able to just have "Month" or would it have to have all those different titles of Months I mentioned above?


----------



## OBP (Mar 8, 2005)

TD all can just have Month in the Month Table which is what is currently displayed and grouped, but the links to the other queries will have to be changed to reflect your new names.


----------



## krs1716 (Feb 10, 2012)

I think I understand. I will have to change a few things, perhaps make a dummy report, and then get back to you on functionality.

From what I can see now (including what we've just discussed), I think that this will work. My only concern: Will creating this many queries/report coupled with the thousands of customers we serve overload Access?


----------



## OBP (Mar 8, 2005)

My works database was 80Mb and worked fine, the limit is 2Gb. Yours is currently just 10Mb.
If the worst comes to the worst you can split the front and back ends


----------



## krs1716 (Feb 10, 2012)

Okay. Thanks.


----------



## krs1716 (Feb 10, 2012)

Having some trouble with TD All Query. I started making the changes we discussed, but then I couldn't use the TD All Query (an error message appeared). So, I tried to replicate it, but it isn't bringing up all of the information. Would I need to include the Months Table in the Grouping Queries and create the joins there?

The error message says: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)"


----------



## OBP (Mar 8, 2005)

The joins are important, if you look at my original you will see that the lines form the months table have a small arrow pointing to the contributing queries, that is the outer join, they must all be like that.
To set it like that you Right Click on the Line and Join Properties and select "All Records from Month table and only those matching records from the other tables".


----------



## krs1716 (Feb 10, 2012)

Okay. I fixed that. I had noticed the join properties before, but misread and thought that the last option was the one to choose.

Now, I'm having issues with the WIA Funding Stream. It only shows up for Placements because that is the query that it is based off of. Should I include the CustomerServices Table and include it from there (however, that will only show up as a number, right?)?


----------



## OBP (Mar 8, 2005)

I will need to look at it.


----------



## krs1716 (Feb 10, 2012)

Okay. I planned to send you my updated version with your updated version integrated into it. However, upon attempting to do so, I have noticed some major differences- mostly in relationships. Entire tables are no longer in your version. Therefore, if this is part of your design, I have no idea where to begin. Would you like me to e-mail you what I have right now?


It is possible that I added things from previous versions, too.


----------



## OBP (Mar 8, 2005)

I would suggest that you use your own latest version and import the Month table and the queries
Trade Department Output
TD Placements
TD VD Negative
TD VD Neutral
TD All
from the version that you made the "Month" changes to the queries.
That should then incorporate the new Reports queries in to your best copy of the database.


----------



## krs1716 (Feb 10, 2012)

Okay. I've moved all of that information into my database. Things are not coming up correctly. In fact, nobody is coming up in the queries, and the queries can only be altered in SQL mode. I will send you what I have in an e-mail.


----------



## OBP (Mar 8, 2005)

You do not have have any Career Planner = 4 in the CustomerServices table, so no Trade Dept records. 
Better check the other fields for values as well.


----------



## krs1716 (Feb 10, 2012)

Whoops! I changed a couple customers to Career Planner 4...no change in query results. I also checked the criteria for Trade Department Outputs and at least one customer meets all criteria (since I changed the Career Planner), and still no results show up. Am I missing something else?


----------



## OBP (Mar 8, 2005)

Are the correct dates in place?
What Customer did you change that should be showing?
Check the Trade Department Output Query first to see if that has any records.


----------



## krs1716 (Feb 10, 2012)

All dates are for 2012. I changed Mark Jones' Career Planner. The Trade Department Output has no records in it.


----------



## OBP (Mar 8, 2005)

OK, you need to remove the Funding stream look up combo and make it a text field and then enter the Funding stream, enter the placement date.
I then get his record coming up in the Trade Dept query and the TD Placements query, but not the TD All query as the joins were messed up, I had to re-establish them.


----------



## krs1716 (Feb 10, 2012)

Remove the Funding Stream lookup combo in the Contact Details form?


----------



## OBP (Mar 8, 2005)

No, in the CustomerService Table in the Funding Stream field.


----------



## krs1716 (Feb 10, 2012)

Now my queries won't open, but an error message comes up: "Type mismatch in expression." What do I need to do to the joins to fix them? (Just so you know...Joins are a bit of an issue with me. I rarely choose the right one.)


----------



## OBP (Mar 8, 2005)

Month to month and year to year and choose the middle option.


----------



## krs1716 (Feb 10, 2012)

I think the WIA Funding Stream in Trade Department Outputs is the problem. I still have the Funding ID (from WIA Funding Stream Table- an Autonumber Field) connected to WIA Funding Stream (Customer Services- a text field).


----------



## OBP (Mar 8, 2005)

The WIA Funding Stream (Customer Services- a text field) should be set to Number type Long.


----------



## krs1716 (Feb 10, 2012)

Okay. I finally get it...sorry it took so long. The queries seem to be working, so I think that this method should work.

Should there be a relationship between the Months and Years tables for the Year field in the Months table? Also, since the Grouping queries specify that the year must be 2012, how would I make it so that users can replicate these results every year?


----------



## OBP (Mar 8, 2005)

That would be selectable in a form, I just did it that way for testing.
Yes the Year should be related the same way as the Month.


----------



## krs1716 (Feb 10, 2012)

Sorry...I meant in the relationships window, should the Year Table be related to the Month Table so that years are available from the Year Table in the Month Table?

Not to pester you, but how would I set up that form?


----------



## OBP (Mar 8, 2005)

The years table is for selecting the years on the form using a combo.
The form would be set up based on a combo getting the Years from the Years table and then using the form selection as the filter instead of the "2012".
You use 
forms![formname]![fieldname]
instead, where formname is the name of the form and fieldname is the name of the combo.


----------



## krs1716 (Feb 10, 2012)

Is that how the Northwind Database does the Sales Reports Dialog? Choosing the Report, then the year, quarter, and/or Month? So, I could have all of my reports by year, quarter, and/or Month?


----------



## OBP (Mar 8, 2005)

More than likely, I haven't looked at Northwind for about 15 years.
But you can certainly do it that way.


----------



## krs1716 (Feb 10, 2012)

Would it be efficient?


----------



## OBP (Mar 8, 2005)

Yes as far as the user is concerned, but takes a bit of work by you though.


----------



## krs1716 (Feb 10, 2012)

Yippee Because I like to complicate things, would I also be able to make it so that in the future, when I'm gone, there would be a dialog box to add years?


----------



## OBP (Mar 8, 2005)

Of course and Months/Years in the Months table as well.


----------



## krs1716 (Feb 10, 2012)

For the Months Table, does each year need have its own set of months?


----------



## OBP (Mar 8, 2005)

Yes, the easiest way is to copy a years worth and then paste it in to a new record, then change the first new one to 2013 and then move down to the next record and use the Cntrl + ' to copy that year down to that record and repeat down to the end .
Or of course you could create queries or VBA to create the new records for you.


----------



## krs1716 (Feb 10, 2012)

Queries and VBA- my two favorite things! Should I ask how that would work?


----------



## OBP (Mar 8, 2005)

You have a table of years and a table of months, you create a query using both tables, but not joined in any way, so you get every month for every year. You change it to an Append Query using the current Months table that has months and years and append the months to the months and years to the years.
You would obviously start the years after the ones in the table, or clear the table all together and start from scratch.


----------



## krs1716 (Feb 10, 2012)

So, I need to make another table that just has months-no years?


----------



## OBP (Mar 8, 2005)

Yes, when you first create the Select query with the 2 tables run it and you should see what I mean about all the months with all the years.


----------



## krs1716 (Feb 10, 2012)

So, basically the Append Query typed the data in for me?


----------



## OBP (Mar 8, 2005)

Yes it will, it can add thousands of records in a few seconds.


----------



## krs1716 (Feb 10, 2012)

Okay...now, I used the append query to populate the Months Table (the one you made), but when I add another year to the Year Table, how do I use the append query to update the Months Table?


----------



## OBP (Mar 8, 2005)

You would need to add a criteria to the year column
> the last year in the Months table
ie
>2015
etc


----------



## krs1716 (Feb 10, 2012)

How would that work in the future, for users not familiar with Access development? Would I be better off to create an update query, and use VBA on my form to automatically add it?


----------



## OBP (Mar 8, 2005)

Why not put in the years & months up to say 2025 now, that should be long enough.
You could then have your form for selecting whatever Year is wanted by the users.


----------



## krs1716 (Feb 10, 2012)

Months and Years fixed! Yay! New issue: I found out that we don't need to separate STATs by Career Planner (sorry for the news...I know you worked hard on that). However, we do need to know which Career Planner is responsible for the STAT. So...I've been thinking. Can we make Career Planner a continuous form with a hidden date field that reads: Date()? Then in the STATs report, allow each number to be a hyperlink to a detail report that will tell who is included in the number count and who their prospective Career Planner was for that STAT number?

Does that make any sense?


----------



## OBP (Mar 8, 2005)

That might well work, I would need to think about it a bit more. I am on Grandchildren sitting duty this aftrenoon.


----------



## krs1716 (Feb 10, 2012)

I completely understand the need for thinking time. I was given this tidbit of information at 8:45 a.m. this morning and am still reeling from the impact. 

Relax...Enjoy your grandchildren!!! My problems will still be here tomorrow, I'm sure!


----------



## OBP (Mar 8, 2005)

OK, can you give me some idea of a revised Stat requirement, I am not sure about using a hyperlink as I never use them.


----------



## krs1716 (Feb 10, 2012)

For the requirements for the new STATs, we can just remove the Career Planning field from the queries, then only create one column for each repetitive title on the Word doc I sent.

I wouldn't actually use a hyperlink. I meant show it as a hyperlink on screen with a VBA code to open the form on click.

I have a new question that isn't about the STATs (if I should, I can post a new thread). I just need to know how to count the number of records in a continuous form. It's for my Event Details form. I would like to be able to tell how many attendees have been scheduled.


----------



## krs1716 (Feb 10, 2012)

About counting the number of records in a continuous form...I just decided to show the subform in Event Details as a datasheet with a totals row (it works well!).


----------



## OBP (Mar 8, 2005)

So have built a report yet?


----------



## krs1716 (Feb 10, 2012)

I have a lot of reports that I built- some that work, some that don't, none of which are STATs.

Actually, I have plans to launch the database on Monday morning, sans Customer Reports. My reasoning is because everybody is putting new customers into the old system, when the new system is pretty much ready for data input, I think. So...I haven't worked much on the STATs. On this subject, if you have a chance, could you look over the basics of the database (like the relationships and the tables) to see if you notice any major issues? If you can't, that's fine.


----------



## krs1716 (Feb 10, 2012)

Still having issues with Contact Details form.

Question: In the relationships window, the Career Plans and Industry Cluster Tables both have one-to-many relationships without enforcing referential integrity, but the Services and WIA Funding Stream Tables have one-to-many relationships with referential integrity enforced. Which way is right?


----------



## OBP (Mar 8, 2005)

There isn't really a "Right".
If you enforce referential integrity the field MUST be filled in with a value from the related table, so if you have fields that may not ALWAYS have a value or where there could be a delay in getting the data to be entered enforcing referential integrity creates a problem.
The answer is to create a Dummy record in the related table like "Not Applicable", or "Not Yet Available" etc and set that as the Default value for the field on the form.


----------



## krs1716 (Feb 10, 2012)

What if I used "Select"...would that work?

Secondly, as I mentioned above, I am planning to launch a preliminary version of this database on Monday. I originally just thought to put the database on our shared network. However, upon research, I am now considering splitting the database. Based on what you've seen, do you think that splitting is a viable option at this point in development?


----------



## OBP (Mar 8, 2005)

I think it may be a bit early, how much more development do you think you will be doing?
Keep in mind that the Front end will have to be distributed to all users each time you make a change.

I am not sure what you mean by "What if I used "Select"...would that work?", do you mean using a combo?


----------



## krs1716 (Feb 10, 2012)

I thought it was early for splitting the database. Am I going to have issues making new reports and such, though?

I meant that "Select" would be an option- that could be the default value. However, I'm not sure that would work, because then there would be records on those instances for all people and my reports wouldn't work right. I'm totally overwhelmed.


----------



## OBP (Mar 8, 2005)

You can overcome default values in a query using the new column method something like
change: iif([fieldname] = "select", "-",[fieldname])
What that does is check if the entry is select, if it is replace it with a - or you could use " " to make it appear blank, if it isn't select it uses whatever is in the field.


----------



## krs1716 (Feb 10, 2012)

Typically, in order to make it so that the group category doesn't show people without those values, I type Nz([FieldName]) in the criteria so it doesn't show. Will that still work?


----------



## OBP (Mar 8, 2005)

I don't know I have never tried that.


----------



## krs1716 (Feb 10, 2012)

Okay...so I'm back to working on STATs. I'm wondering if I should follow the previous format of having one (rather large) query that feeds to smaller queries, that then feed into the query that actually shows the STATs. Is that still the best method? If so, would the join types for the tables in the first query be the middle option (you know how those join types befuddle me)?


----------



## OBP (Mar 8, 2005)

Yes I would use the same layout as the Trade Department query.


----------



## krs1716 (Feb 10, 2012)

I'm still working on laying out which fields from which tables need to be included for the STATs.


----------



## krs1716 (Feb 10, 2012)

Work on STATs has come to a halt. I found that my continuous forms are not recognizing which contact is associated with each entry (i.e. Contact Details form: Services Tab: Program drop down menu). I have no idea how to fix this.

Thoughts?


----------



## OBP (Mar 8, 2005)

Is this the subform that was a problem and you replaced with mine?
If so check the Master/Child links are still there.


----------



## krs1716 (Feb 10, 2012)

The subforms I was having an issue with were CustomerServices C, Subform-Service Options, and Subform-Training Categories. I changed the Master/Child Links on CustomerServices C and Subform-Training Categories so that everything is based off of Customer ID, but Subform-Service Options doesn't have Customer ID, because it's based off of the original table. I'm not sure whether I should add that field or not.


----------



## OBP (Mar 8, 2005)

No, that is controlled by VBA form the CustomerServices subform.


----------



## krs1716 (Feb 10, 2012)

So, should I be concerned about it or not. Nobody has used Support Services (Subform-Service Options) yet because it is a program that ran out of funding.


----------



## OBP (Mar 8, 2005)

Have you tested your version to see if it is updating?


----------



## krs1716 (Feb 10, 2012)

I will have to do that. I'm working at a different desk, in a different position today.


----------



## krs1716 (Feb 10, 2012)

I added a few things, and I'm not seeing any major issues, but I'm still concerned. I looked at the relationships, and I noticed that the Support Services table isn't related to the CustomerServices Table because it's related to the CustomerSSOptions Table. The CustomerSSOptions Table does not have Customer ID as a field, so I'm not sure if the data is relating to an actual person. Can you look at the setup and let me know if I need to change/add fields or relationships? I can't tell whether I need to add Customer ID so that Support Services works correctly.


----------



## krs1716 (Feb 10, 2012)

While trying to create STATs, I've noticed that I get no people coming up after including all the fields that I think that I need. I suspect that this has something to do with the Join Type. Upon further investigation, I noticed that the original version "Trade Department Output" only included records for which there was Exit Information included for a customer. Once again, I suspect this has something to do with Join Type. How would I fix this issue?


----------



## OBP (Mar 8, 2005)

Is what you have in the database that you sent me for the Delete Record?


----------



## krs1716 (Feb 10, 2012)

No, but I can send you a new one with fake scenarios so that you can see what I mean.


----------



## OBP (Mar 8, 2005)

OK.


----------



## OBP (Mar 8, 2005)

What query are you working on?


----------



## krs1716 (Feb 10, 2012)

Well, I created STATs Main based off of Trade Department Output. So, those are the two I have been working with.


----------



## OBP (Mar 8, 2005)

I have returned your database, I have corrected the Joins in the Stats Main query note that they all must go outward from the General Information Table on both sides, each table join must continue the outward direction in their joins. Otherwise an error is generated.
Once you have all the data you can then filter by whatever means you want in other queries.


----------



## krs1716 (Feb 10, 2012)

Okay. I now see the difference. Just to confirm...the relationships created in a query will not influence the relationships in the relationship window, right?


----------



## OBP (Mar 8, 2005)

Correct.


----------



## krs1716 (Feb 10, 2012)

Okay. I spoke with staff today, and learned that STATs is not a priority (so sorry to deliver that news). However, other reports are a priority. I have created a few that I think will suffice for now. However, they are quarterly reports that have a parameter. 

Preferably, I would like a form that allows me to choose a report from a list, then choose either Monthly, Quarterly, or Yearly (with each of those categories having a list to choose from). I'm under tremendous pressure to have the whole database up and running (error message free) by the end of the month, and I have no clue how to begin making this form. 

On a side note, I am also being told that I need to make everything so that staff (not Access programmers) can easily add and alter elements of the database. So, I would prefer that the list of reports be automatically generated each time a new report is created. Thoughts? Is this possible? Should I run screaming for the hills?

P.S. If you need a new version of the database (one that has newer reports), let me know, and I can send one to you.


----------



## OBP (Mar 8, 2005)

Non programmers should have tables/queries/forms to enter/edit all background data as required as well as the main section, so all Combo boxes should be based on tables and not lists.
To make life easier all date fields should have both Format and Input Masks set.
You could set up a Report list of all available reports, but I am not sure it is worth the programming time.

Your report form should have no record source (Unbound) and an Option group to select either Monthly, Quarterly, or Yearly type of report. If a start and finish date is required you should have fields for that.
You then replace the query parameters with the form fields using
forms![formname]![fieldname]
or 
between forms![formname]![fieldname1] and forms![formname]![fieldname2]


----------



## krs1716 (Feb 10, 2012)

Sorry to keep bugging you (though I do it anyway), but I realized that my quarterly reports need to be based off of our fiscal year (July 1-June 30). I thought that if I created a public variable establishing the fiscal year and then the fiscal quarters (i.e. July 1- Sept 30; Oct 1- Dec 31; Jan 1- Mar 31; April 1- June 30) I would be able to use that variable in expressions throughout the database. However, after thoroughly researching the topic, I'm not entirely sure how to proceed.

A few examples show establishing a Constant (I think that's what they called it) that establishes the month that begins the fiscal year (July) and the month that ends the fiscal year (June), but then they establish everthing by months, when I need quarters. So, I don't know if I can establish quarters under the same premise? Do you know of an easy way to accomplish this? A good resource?

I'm trepidatious with VBA, as you know, so I prefer to be absolutely certain of what I'm doing before proceeding.


----------



## krs1716 (Feb 10, 2012)

I think I may have answered my last question (maybe). This is what I typed into a module:

Option Compare Database
Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.
Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function
Function GetFiscalQuarter(ByVal x As Variant) As Integer
Dim FiscalMonth As Integer
If IsDate(x) = True Then

FiscalMonth = Month(x)
Select Case FiscalMonth
Case 7, 8, 9
GetFiscalQuarter = 1
Case 10, 11, 12
GetFiscalQuarter = 2
Case 1, 2, 3
GetFiscalQuarter = 3
Case 4, 5, 6
GetFiscalQuarter = 4
End Select
End If
End Function


Does that seem like a correct answer?


----------



## OBP (Mar 8, 2005)

I use a fiscal year table of dates for the quarters and then use dlookup in the query.
I have posted a copy of that database on the forum somewhere, I will see if I can find it. If not I will try and get it off my old broken computer.


----------



## OBP (Mar 8, 2005)

Funnily enough I found one where I use a Function to return the Fiscal year start date.
It is here
http://forums.techguy.org/business-applications/1023347-solved-access-2007-vba-fiscal.html


----------



## krs1716 (Feb 10, 2012)

I looked at the thread and the attached database. However, I don't think that just identifying the fiscal year will work for my purposes. I think that I have to identify not only the fiscal year, but also the fiscal quarters. My code from above works in the immediate window (it recognizes that if I type "?GetFiscalQuarter(#3/1/1999#)" the date resides in the third quarter of that year. However, I can't figure out how to use the function in a query.

The reason I think I need the fiscal quarters defined is because I want to be able to have a fiscal year and fiscal quarter option in my report dialog box. Am I making this too complicated (as always)?


----------



## OBP (Mar 8, 2005)

No that is not complicating it too much, it is a requirement.
I haven't found the table version on the forum or on my computer yet, but I will keep looking.
If necessary I can recreate it or get you function working in the query over the weekend.


----------



## krs1716 (Feb 10, 2012)

Okay. Thank you!

Honestly, I do try to find answers to my questions before I bother you, but inevitably, I only find half an answer (sometimes no answer).


----------



## OBP (Mar 8, 2005)

You have the "answer" what you don't have is the Implementation.


----------



## krs1716 (Feb 10, 2012)

Ahhh. I see. So, I can find an answer, but I can't find a solution on my own.


----------



## OBP (Mar 8, 2005)

Here is the database I was looking for, have a look at the query called Pre-Calc, it compares dates in the query's table with those form a different table.
If you had your normal years, fiscal years & quarters set up with the actual dates of the quarters you can use the same kind of system to convert the dates to those matching in the fiscal year table.
If you can provide the Fiscal Year and the Fiscal Quarter dates I will see if it will work.


----------



## krs1716 (Feb 10, 2012)

I have new questions in addition to the issue I posted earlier today. When we were working on STATs, you helped me change my combo boxes' source to a table using relationships.

1st Issue: I'm having difficulty with each record associating itself with the proper person and/or record. For instance, if I type a Training Provider into the OJT section of the Training subform, the same Training Provider appears on the ITA side. Also, Training Categories do not register a Customer ID once the record is saved.

2nd Issue: Will allowing Value List Edits (so that users in the future can easily add new categories) add the new options to the appropriate table? Also, will that allowance mess with data integrity too much? Is this method effective/efficient?


----------



## krs1716 (Feb 10, 2012)

The fiscal years would just be July 1 of one year to June 30 of the next year (i. e. July 1, 2011- June 30, 2012). Then the quarters are as follows:
1st Quarter: July 1- Sept 30
2nd Quarter: Oct 1- Dec 31
3rd Quarter: Jan 1- Mar 31
4th Quarter: Apr 1- June 30


----------



## OBP (Mar 8, 2005)

Which financial year would July 1, 2011- June 30, 2012 be, 2012?


----------



## OBP (Mar 8, 2005)

Assuming that July 1, 2011- June 30, 2012 is fiscal year 2012 here is how the query comparing to a table of fiscal data works.


----------



## krs1716 (Feb 10, 2012)

I don't quite understand what's happening in the query. There is a lot of information, and a lot of extra dates. I apologize, but I'm kind of confused.

On a side note, I figured out why my function wasn't working: I forgot to establish it as a Public Function. So, now my quarters work, but there is something wrong with the code about the establishing the fiscal year function (it has something to do with null values).

So...I don't know what to do.

If you think that the query method will work long term (am I going to have to create all the quarters for as many years as I think staff will need to use them?), I am willing to try that. Otherwise, I guess I'll have to keep researching for a Public Function that works.


----------



## OBP (Mar 8, 2005)

You can work with the query, the extra dates are only there to show you where the data comes from in the table which is used in the fiscalQtr calculations.
The only columns you actually need are fiscalyear, qtr and fiscalqtr, you can also show the datadate equivelent in your table if you want.
With the table method users can enter new annual data as required.


----------



## krs1716 (Feb 10, 2012)

I like the idea of the query, because it seems to be a simple solution for me. However, staff get nervous when using queries. How would I be able to use the values in a different query so that those other queries return only information in the specific fiscal quarter/fiscal year?

P.S. For learning purposes, why would an error message regarding improper use of the Null property appear for this function:
Public Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
?


----------



## OBP (Mar 8, 2005)

does it highlight a line of code?
it could be the x being passed to the function module.

The query does not need to be used just the calculations with the fiscal table, it can be incorporated in to any other query.

Queries are very reliable.


----------



## krs1716 (Feb 10, 2012)

It highlights the following line:
If (x) < DateSerial(Year(x), FMonthStart, FDayStart) Then

I changed the line to read: 
"If Year(x) < DateSerial(Year(x), FMonthStart, FDayStart) Then"

and it still gives me problems with a null value.

I think I'm not understanding what is happening behind the query (or perhaps how to use it?). I can see that it works, but if I were to copy it and put it in my database, I would have no idea what to do from there (you're surprised, right?).


----------



## krs1716 (Feb 10, 2012)

The reason for my excess trepidation is because I am getting pressure to complete the database, including inputting thousands of customers, by the end of the month. Plus, the IT Department has now told me that the database is too complicated, and if I can't make things easier to add objects and use queries, they will shut the whole thing down. I've been spending the last few days sifting through ideas to find ways that when I'm gone (which is the end of June) staff will be able to add features and create queries/reports without me.

Thus...the third degree over every step.


----------



## OBP (Mar 8, 2005)

The response from your IT dept. is typical, plus there is some "not invented here" syndrome as well.
The reason that they say it is too complicated is because they do not want to have to supply support for it.
I don't want to be too off putting but I don't think you have any chance of completing all of that by the end of the month, it sounds like they want you to fail.
Access and most other relational databases are not designed for "users" to just create Queries and Reports without some serious study, other than basic ones. You only have to look at how complicated combining Tables and getting the joins correct in a query is.

Check that the query does not have any blank records in the field that supplies the "x" value.


----------



## krs1716 (Feb 10, 2012)

Thanks for the words of advice. I figured it was a territorial kind of thing. The reason I've been forging ahead with user interface ideas is because I have seen it done. At my last job, the Access programmer created a series of forms that allowed the user to create queries that were automatically saved in a report format that looked like labels, but contained various pieces of information. I tried to access the database to see how the programmer mastered such a feat, but the database is saved as an application and the supporting documents are much beyond my expertise, so there went that idea.

As for the query having null values in that field, I know that there are null values. If I added "IfIsNotNull" before the phrase would that work?


----------



## OBP (Mar 8, 2005)

I would either use Not Is Null in the fields Criteria or try the NZ() function, but if the module needs an actual value the NZ' zero probably won't work.

I have also seen the type of interface you mention, in fact I remember someone on VBAX, another forum that I used to post on, creating one.
The key to it would be using Table and Query Defs, which can provide lists of tables & fields and SQL to to create the Query def using the fields, but you should also provide choice of Join information, which average users wouldn't have a clue about.
If the reports were to be kept fairly simple it wouldn't be too hard to do.
But how your programmer arranged for report formatting with grouping, sorting, headers, footers & totals etc I have no idea, they are bad enough to do manually.


----------



## krs1716 (Feb 10, 2012)

I don't think that the NotIsNull Criteria would work because I am using the function in a query that feeds subsequent queries (like the STATs Main). Will writing the module like this work:
Public Function GetFiscalYear(ByVal x As Variant)
IfNotIsNull(Year(x)) Then
If Year(x) < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End If
End Function

As far as the user interface...the database that I saw using the interface didn't ask about averages and things like that. It created a report that listed every person that fit the specified conditions, then at the top it automatically had a total at the top that just counted everybody on that list. I'll admit it wouldn't be very sophisticated, but it could get the job done, I think. However, once again, I may have bitten off more than I can chew. Is this do-able?

As far as my deadline is concerned, I think that if I can get some major things working and fixed by the end of the month, I will be ok. I have some co-workers that are willing to help with the data entry, and I think that I can take all demographic information for the customers (name, address, phone number, etc.) from another database. That way it won't have to be typed individually. 

P.S. Did you notice my post# 303 above. I know that if those issues aren't fixed by the end of the month, I might as well just stop now.


----------



## OBP (Mar 8, 2005)

Using the if not isnull() function may work, have you tried it?

I have been trying to find the information on VBAX, but no success yet and I am back to 2007, I will look a bit further back.


Re #303 question 2, I have no experience of " allowing Value List Edits", I have always used the Combo "Not in List" event procedure to add the current value to the underlying table and the combo and current form.
The code for that has an "Are you sure" type message.

I will take a look question 1, that has always been an awkward way to enter the data.


----------



## krs1716 (Feb 10, 2012)

I think the If Not IsNull function worked. (a note for anybody reading this for this function, the spacing is very important. I had no spaces between the words in my original idea and that created an error.)

Thank you for looking for this information. You truly go above and beyond.

I thought allowing Value List Edits was the Not In List event. Apparently, I'm mistaken.

I'm wondering if at least for the Training Providers, we should separate them into OJT Training Provider and ITA Training Provider, because one of them will be an employer the other will be a school of some sort. So, they really aren't the same thing, now that I think about it.


----------



## OBP (Mar 8, 2005)

OK I have had a look at the Training subform and re 1st issue:
I'm having difficulty with each record associating itself with the proper person and/or record. For instance, if I type a Training Provider into the OJT section of the Training subform, the same Training Provider appears on the ITA side.
As there is only one "Training Provider" field that provider must show up in both sub sub forms, it can't do anything else, I thought the subforms were going to be shown/hidden depending on the Training Category selection?

I have added a CustomerID to the main Training subform and the TrainingID to the top subform and it seems to be working OK apart from showing the common provider.


----------



## krs1716 (Feb 10, 2012)

Okay, I think that I understand. However, my version already had the TrainingID on the top subform on the main subform, but I added CustomerID to the main training subform, and I think that it is working ok. Thank you.

I had been considering putting the two subform's fields on the main subform and then just putting the top subform as a subform on the main subform, so that there wasn't three subforms on a subform on a main form.


----------



## OBP (Mar 8, 2005)

At this stage I would do whatever is quickest and easiest that works. 
If I remember correctly we tried quite a few versions of the training subform.


----------



## OBP (Mar 8, 2005)

Most of the items I have found talk about queryDefs, there is also something called CreateReport, but it using a Template, so is probably no use in this instance.
I did find this interesting piece of code that someone I was helping on VBAX which he found elsewhere, I am not sure what it does, but he was happy with it.
I can see it uses a listbox for contact data.
So I will post it on here so we know where it is.

Declare variables 
Dim db As DAO.Database 
Dim qdf As DAO.QueryDef 
Dim varItem As Variant 
Dim strCriteria As String 
Dim strSQL As String 

Set db = CurrentDb() 
Set qdf = db.QueryDefs("qryMultiSelect") 

For Each varItem In Me!lstContactGroupType.ItemsSelected 
strCriteria = strCriteria & ",'" & Me!lstContactGroupType.ItemData(varItem) & "'" 
Next varItem 

If Len(strCriteria) = 0 Then 
MsgBox "You did not select anything from the list" _ 
, vbExclamation, "Nothing to find!" 
Exit Sub 
End If 

strCriteria = Right(strCriteria, Len(strCriteria) - 1) 

strSQL = "SELECT * FROM tblGroup " & _ 
"WHERE tblGroup.Group_Name IN(" & strCriteria & ");" 

qdf.SQL = strSQL 

Set db = Nothing 
Set qdf = Nothing


----------



## OBP (Mar 8, 2005)

As you seem to like VBA, here is a very interesting Module written by geekgirlau on VBAX, who is a super programmer.
It documents the objects in a database and puts the info in a table, may be for dynamic report generation\\\\\\\\\\\/
http://www.vbaexpress.com/kb/getarticle.php?kb_id=695


----------



## krs1716 (Feb 10, 2012)

I'm not so sure that the CreateReport with a Template isn't what I was talking about. It's difficult to explain. Let me see if I can do a series of Print Screens to show you what it looks like.


----------



## krs1716 (Feb 10, 2012)

Yay! VBA!!!

That code is so far over my head, I have little hope that I would be able to implement that. I don't understand anything. The concept sounds great. That might be what my other program did, because I know that there is a table that contains all the queries SQL statements...even the queries that I customly created.


----------



## krs1716 (Feb 10, 2012)

I sent an explanation of what happens in the program I was using that does what I want my database to do to your private e-mail.


----------



## krs1716 (Feb 10, 2012)

New Question: I tried to write a code for the NotInList Event in my Subform CustomerServicesC (for the combo box), and it is telling me that my "INSERT INTO" statement contains Invalid syntax. I have no idea where to begin to find this error. Here is the statement:

strSQL = "INSERT INTO Services([Service Description])" & "VALUES (" '& NewData &'");"

Any idea what's wrong with it?


----------



## krs1716 (Feb 10, 2012)

Okay. I answered my own question again. My Quotation marks around & NewData & were wrong. Sorry for bugging you.

On a side note: how would I get it so that the added options aren't at the bottom of the list, but instead in alphabetical order?


Ignore this question: I answered it myself (again). All I had to do was change the source query so that it sorted the records in ascending order.


----------



## OBP (Mar 8, 2005)

See how good it is to ask the question of someone, the answer just comes to you via telepathy


----------



## krs1716 (Feb 10, 2012)

I have a real question now. In my database, what is the purpose of Customer SSOptions Table? I keep looking at it, but I can't figure it out. I know that I deleted at one point, and it caused problems, but I don't remember what kind of problems.

I need to explain the relationships to IT, but I'm stumped on this one.


----------



## OBP (Mar 8, 2005)

That is the Many to Many Linking Tables that allows you to select as many Customer Services as you like and as many Support Service options for those Customer Services.
The Link from the Customer Services Table should actually be a 1 to Many as well, ie set Referential Integrity so that only Customer Services in the table can be used.


----------



## krs1716 (Feb 10, 2012)

So, I just checked all the boxes (Enforce Referential Integrity, Cascade Updates and Deletions) on the relationship from Customer Services to CustomerSSOptions. Is that what you mean?


----------



## OBP (Mar 8, 2005)

Yes if you delete a Customer Service those records will be deleted from the CustomerSSOptions table (not the Support Service Options).

The Report Generator pdf you sent me is quite interesting, but the report generation is a bit simple compared to what would normally be supplied by a programmer during the production of the database.
I can see it has it's uses to quickly generate a report and store it for future use though.

We have not even discussed "Searching" in your database, which is normally a priority for users.


----------



## krs1716 (Feb 10, 2012)

I agree that the report generation is simplified. To be fair, that was just one of the options available. There were other choices to pick from. I know for a fact that you can create a report based on dates and such. Also, that particular database was an inventory database, so most currency was calculated somewhere else.

As far as "Searching" in my database, I'm not certain what you mean. I have certain search fields in some of my forms.

P.S. Having slight problem with my NotInList event when it comes to Industry Clusters. I used the same code I've been using for other fields, but this one is different because it's on the Exit Subform, which only shows certain fields if they're relevant to the customer. After adding the new option to the Industry Clusters combo box, the subform is requeried (is that a word?) and all the information is erased from view (it's still there, but staff will go ballistic if this happens to them each time they try to add a new Industry Cluster).


----------



## OBP (Mar 8, 2005)

Try only requerying the combo.


----------



## krs1716 (Feb 10, 2012)

I'm not sure how to do that in this case because I'm not actually using the word "Requery" (once again, I obtained the code from a source, but actually have limited knowledge of what is happening...other than the fact that it works (or in this case, doesn't)).

It uses this phrase (I think this is the one that requeries it, I think):
Response = acDataErrAdded


----------



## OBP (Mar 8, 2005)

That should only requery the combo, not requery the form


----------



## krs1716 (Feb 10, 2012)

Well, I don't know if it has something to do with the code I was using to show certain fields.

This is the entire code that I'm using for the NotInList event on Subform-Exit Information on the Industry Cluster Combo:
Private Sub Industry_Cluster_NotInList(NewData As String, Response As Integer)
On Error GoTo Industry_Cluster_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Industry Cluster: " & Chr(34) & NewData & Chr(34) & " is not currently listed." & vbCrLf & "Would you like to add it to the list now?", vbQuestion + vbYesNo, "CareerLink Database")
If intAnswer = vbYes Then
strSQL = "INSERT INTO [Industry Clusters]([Industry Cluster])" & "VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Industry Cluster has been added to the list.", vbInformation, "CareerLink Database"
Response = acDataErrAdded
Else
MsgBox "Please choose an Industry Cluster from the list.", vbInformation, "CareerLink Database"
Response = acDataErrContinue
End If
Industry_Cluster_NotInList_Exit:
Exit Sub
Industry_Cluster_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Industry_Cluster_NotInList_Exit
End Sub


----------



## OBP (Mar 8, 2005)

That code looks OK, why not the code to show the other fields on the end to make the fields re-appear?


----------



## krs1716 (Feb 10, 2012)

That never occurred to me...that's why you're the expert. Thanks, again!

Any ideas on that lovely idea I had about a user interface for query creation?

Also, because more than two minutes has passed, I have another question: Have you heard or do you know anything about grouping in a report based on values in a multivalue field?


----------



## OBP (Mar 8, 2005)

Re the new question, no, as I don't normally use multivalue fields, when multiple selections are required I normally use a Many to Many subtable, (as you know).

I haven't given the query/report generator much more thought as I don't think even with my assistance you could get it up and running, fully tested before the end of the month and maybe not even by the end of June.
I can provide you with an examples of creating Dynamic SQL and even QueryDefs, but I have never done anything on the report generation side. If you could manage with a few reports with fixed fields (but dynamic headings) it would be quite straightforward.

I mentioned "Searching" before, the king of thing I was thinking of was "how many clients Attained High school standard in CA"
Or "how many were placed with a particular Business".
This involves having an Unbound Form with Unbound Fields, using Combos wherever possible and users fill in which ever fields they want data on and the query provides the data, but the output is restricted to a form, where the users can click back to the original record in the mainform.
I created a couple of really large ones in the past,


----------



## krs1716 (Feb 10, 2012)

I guess the query could work. Can staff print a query? Could I make the queries that they already have the total row?

As far as the multivalued field, I have detested that thing for a while now because it's so hard to work with, but it is already up and running in the database, so I'm not sure what to do with it. Big "whoops!" on my part.


----------



## OBP (Mar 8, 2005)

I have never tried printing a Query.


----------



## OBP (Mar 8, 2005)

Here is an example of the multi-search form that I was talking about, it was created a couple of years ago for a forum poster.
The form you want to look at is the frmCriteriaSearch.
It will give you an idea of how to use multi lists & combos for inputting search criteria and then finding it using VBA SQL and various form outputs.
There aren't many records in the table, but you could always add some if you wanted to do more searching.


----------



## krs1716 (Feb 10, 2012)

Okay. For the most part, I think that could work in my database. I have a lot of paperwork to complete on my desk right now, so I'll look at it more closely later to determine how I should go about implementing it.


----------



## krs1716 (Feb 10, 2012)

I've looked over the search form. I like it!

I don't know how to implement it. Surprise!!

P.S. This form is different than query by form, right?


----------



## OBP (Mar 8, 2005)

It is query by form, but is different to Filter by form.
You implement by using the form with the fields that you want from your table(s) and modify the VBA code accordingly.
If you design the form that you want I can hep with code conversion.


----------



## krs1716 (Feb 10, 2012)

I'm having some trouble creating the form. I have some fields that are combo boxes, but the lists are unchanging (i.e. Gender is Male and Female). I can't figure out what row source to make the combo boxes.


----------



## OBP (Mar 8, 2005)

If they are definitely unchanging you can use a Value list with the values typed in, but if they can change I would use the same table/query row source as the real forms use.


----------



## krs1716 (Feb 10, 2012)

But they won't be unbound then.

Also, how would I account for dates? Just put the unbound field?


----------



## OBP (Mar 8, 2005)

The combos are Unbound, it is the Control Source not the Row Source that controls whether a field or object is "Bound".
Yes with dates just a text box but set up with an Input mask.


----------



## krs1716 (Feb 10, 2012)

Either I'm not understanding what you mean or that isn't going to work with the combo boxes. When I have the row source as the table, it shows multiple entries of the same thing because some people have the same information. (the combo box shows: male, male, female, female, female, male)


----------



## OBP (Mar 8, 2005)

What table are you using?
It should be a table with just Male & Female in it (Not a main data table). But I would use Value list in that case because it should not change.


----------



## krs1716 (Feb 10, 2012)

Okay. That makes sense. I was actually working with counties, but for whatever reason (the truth is that no logical one applies) I chose to use gender as an example.

The problem that I'm having is that when I made those lists, I had no idea what I was doing, and so I did it the easy way: typing in the options instead of creating a table which supplied the options. So, now I'm thinking that I should reformat that so that if the counties and/or states change, staff will be able to change them.

Ahhhhhhhhh!!! Still paying for stupid early mistakes!


----------



## OBP (Mar 8, 2005)

Every body does it.


----------



## krs1716 (Feb 10, 2012)

I'm still working on the search criteria form, but I've been bouncing between the Customer Report Dialog form and the Search Criteria form.

I've hit a roadblock for the Customer Report Dialog form. I can't figure out how to generate the lists. I want there to be the following fields:

List of Reports available
Type of Report : Monthly, Quarterly (Calendar), Yearly (Calendar), Fiscal Quarterly, Fiscal Yearly
Then a drop down menu for each of the Report Types

I think I can probably just type the values into some of the Report Types, but my major issue right now is how to get the list of reports to generate automatically so that if new reports are created, they automatically show up.

Ideas?


----------



## OBP (Mar 8, 2005)

Place this code in a Button's On Click event.

Dim obj As AccessObject, dbs As Object
On Error GoTo errorcatch

Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllReports
MsgBox obj.Name

Next obj
Exit Sub
errorcatch:
MsgBox Err.Description


----------



## OBP (Mar 8, 2005)

I would like you to make a change to the Auditing Module code please replace this

If ctl.ControlType = acTextBox Then

with this

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then


----------



## krs1716 (Feb 10, 2012)

I replaced it in my version. I will do a few changes and let you know if any issues arise.

Is there a limit to the number of fields I should include on the Search Criteria form, because mine is getting rather large?


----------



## OBP (Mar 8, 2005)

I would ask the users what they are likely to look for.

What code do you want written on the Search form you have sent me?


----------



## krs1716 (Feb 10, 2012)

I would like the code to result in a list of customers that meet whatever combination of criteria users might use. Just a note...all the lists (not combo boxes) allow multiple selections. I don't know if that answers your question or not.

I'd like it so that users can choose criteria and find out how many people meet that criteria. For instance the question "How many active customers completed ITA in the month of June?" would be searched by checking the "Active" check box, choosing ITA from the Training Categories list box, and filling in the Training between as follows: 06/01/2012 "And" 06/30/2012.

If somewhere on the result we could have ID counted that would be great. I honestly have no idea what the premise for this is. I haven't read much on this topic, yet.

I hope that my explanation answers your question.


----------



## krs1716 (Feb 10, 2012)

I also created a Customer Report Dialog. I incorporated your button's On-Click Event, but how do I get those results into the list box that I created. I am so lost as to how to do these last two major objects, I feel like I'm spinning my wheels. I've created the form, but it does nothing since I don't know how to begin writing the code.

If you have advice, I am more than willing to take it.


----------



## OBP (Mar 8, 2005)

To get you started on the reports required for a list box per post #362 (362 already) take a look at attached Database with a form called list access objects.
It has 3 buttons and a list box. Only the Reports button has the extra code to add the list of reports to the list box called reports.

The Search form that I posted has all the code that you need, it just needs modifying to work with your new search form's fields.
I probably won't have time to do all of them, but I can probably provide examples fro each type.


----------



## krs1716 (Feb 10, 2012)

Thanks for the help on the Search Criteria form. I completely understand.

I tried to take the code from the List Access Objects database and put it into the on click event of my button, but nothing happens when I click. I made sure that the List box was titled properly, but still, nothing happened. When I click on the button the options should be listed in the list box, right?


----------



## krs1716 (Feb 10, 2012)

I changed my question in my internet search, and I found a solution that lists all the reports into my list box on form load. However, after viewing the list, I realized that having all the reports listed wasn't what I wanted after all. Surprise!

What I want is a list of all customer reports. So, I was thinking (a dangerous practice, I know), and I realized that all of my customer reports contain the word "Customer". So, is there a way to get the following code to show only reports that contain the word Customer (in any format- i.e. "Customers" or "customer")?

Dim intNumOfReports As Integer
Dim intRptCounter As Integer
Dim str As String
intNumOfReports = CurrentDb.Containers("Reports").Documents.count
If intNumOfReports <> 0 Then
Me![List0].RowSourceType = "Value List"
For intRptCounter = 0 To intNumOfReports - 1
str = str & CurrentDb.Containers("Reports").Documents(intRptCounter).Name & ";"
Next
Me![List0].RowSource = Left$(str, Len(str) - 1)
End If


----------



## OBP (Mar 8, 2005)

Do you have a list box called "List3"?


----------



## krs1716 (Feb 10, 2012)

No, I have a list box called List0, so I changed List3 to List0. Was that right?


----------



## OBP (Mar 8, 2005)

Yes, I have it working in your database as it is, I copied it from there.
Have you tried importing the Form to test it?


----------



## krs1716 (Feb 10, 2012)

For whatever reason, your form works in my database, but the button wouldn't work on my form until I copied and pasted your list box. So, it works now.

How would I tweak the code to show only Reports that contain the word "Customer"?


----------



## OBP (Mar 8, 2005)

Contain Customer or start with Customer?


----------



## krs1716 (Feb 10, 2012)

Contain Customer


----------



## OBP (Mar 8, 2005)

you will need a variable, an Instr() function and an If statement.

Dim a as integer

For Each obj In dbs.AllReports
a = 0
a = InStr(1, obj.name, "Customer")

if a>0 then
If strstring = "" Then
strstring = obj.Name
Else
strstring = strstring & ";" & obj.Name
End If
Next obj
end if


----------



## krs1716 (Feb 10, 2012)

Sorry, but where would I place that in the main code?


----------



## OBP (Mar 8, 2005)

I didn't get time to test it, but it should replace the current For Each Loop.


----------



## krs1716 (Feb 10, 2012)

Okay. I tried to replace the former code with the new one and the following error message appears: 

Run-Time Error '424':

Object Required


----------



## krs1716 (Feb 10, 2012)

Nevermind. My brain finally kicked in and realized what you meant. I was trying to replace the whole code with the new code. Sorry about that. I think it's working now.


----------



## krs1716 (Feb 10, 2012)

Any way to sort the list by name?


----------



## OBP (Mar 8, 2005)

I did that on the fly and got it slightly wrong as the last end if should have been inside the For each loop, the whole code should be

Dim obj As AccessObject, dbs As Object, strstring As String, a As Integer
On Error GoTo errorcatch
strstring = ""
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllTables collection.
Me.List3.RowSource = ""

For Each obj In dbs.AllReports
a = 0
a = InStr(1, obj.Name, "Customer")

If a > 0 Then
If strstring = "" Then
strstring = obj.Name
Else
strstring = strstring & ";" & obj.Name
End If
End If
Next obj

Me.List3.RowSource = strstring

Exit Sub
errorcatch:
MsgBox Err.Description

You could probably sort the strstring using VBA before assigning it to the rowsource but it would not be very easy to achieve.


----------



## krs1716 (Feb 10, 2012)

Okay, then. On the list of things to do, that will be on the bottom.

On the top, how to get the whole thing up and running. Please tell me that somewhere in your past you created a dialog box to generate reports, and I can just tweak some code to implement it in my database. My meeting with IT has been moved to tomorrow morning, and I am scrambling to look as if everything is functioning smoothly...it's not.


----------



## OBP (Mar 8, 2005)

Sorry, I can't do anything tonight I am going out.
I will see if I can find anything tomorrow, although your program you sent me should have VBA code.


----------



## krs1716 (Feb 10, 2012)

That's okay. Enjoy your evening.

I'll figure something out. I'm pretty good at smoothing things over.


----------



## krs1716 (Feb 10, 2012)

I've been reviewing the VBA for the Search Criteria form and I don't see any examples of how to deal with checkboxes or my date ranges.


----------



## OBP (Mar 8, 2005)

Checkboxes are a special case in queries and SQL, they work best when you use 

-1 
equals ticked and 
0 equals unticked after being ticked and 
Is Null 
has not been ticked at all.

2 Dates use the "Between firstdate and second date" function like this
"WHERE Date1 between #" & Format(Me.Date, "mm/dd/yyyy") & "# And #" & Format(Me.Date, "mm/dd/yyyy") & "#"
NOT TESTED



You might find this database utility useful it converts a Query's SQL to VBA code, so once you get a query working you can convert it to VBA generated SQL.


----------



## krs1716 (Feb 10, 2012)

Can you post it as a zip?


----------



## OBP (Mar 8, 2005)

The software to unzip it is a free download.


----------



## krs1716 (Feb 10, 2012)

Work computer...not allowed to do software downloads. Thanks for reposting it.


----------



## OBP (Mar 8, 2005)

Then your IT department should get it for you


----------



## krs1716 (Feb 10, 2012)

Yeah...let me suggest that at the meeting. I'm sure it will go over extremely well.

I wish, though.


----------



## krs1716 (Feb 10, 2012)

Subject brought up at meeting: Is there a way to automatically add a new field to particular forms and reports upon it's addition to a table?


----------



## OBP (Mar 8, 2005)

Not normally as users are not supposed to be able access any part of the design.
You could do it all with VBA, but most users wouldn't have a clue how to set up a field, ie field type, indexed, no duplicates etc.
Who is asking such questions, IT?
They certainly wouldn't allow any users access to their program designs.


----------



## krs1716 (Feb 10, 2012)

IT is concerned because I leave at the end of June, and they want "Administrators" to be able to access the design so that it can be perpetuated, and that was one concern: that each time a field is added to a table, it needs to be added to forms, queries, and reports.

P.S. Found out minutes ago that Support Service doesn't work. It doesn't associate the person with the service.


----------



## OBP (Mar 8, 2005)

Support Service does not associate with a person?
How then does it show the correct Support for each Person?
I just entered a support service (Day Care) for Kirstie Allie and it appears each time I select her.

By the way your Find a Contact combo could do with a bit of work, regards sort order and keeping the selection visible.


----------



## krs1716 (Feb 10, 2012)

Support Service isn't working on my database. Maybe I made changes, and it isn't working anymore. Can you send me a shot of the relationships window?

Didn't even notice the whole issue with the Find a contact combo. Thanks for pointing that out.


----------



## OBP (Mar 8, 2005)

It looks like this.


----------



## krs1716 (Feb 10, 2012)

Okay. I have it back up and running now. I'm not entirely sure what I did, but I erased the relationships and remade them. Thank you, thank you, thank you.

Okay, back to other things. Any ideas for satisfying IT's request?

Also, I'm still having issues with the Search Criteria. I can't tell where some of the fields (i.e. "Candidates ID") come from in the statements. Also, some of my lists/combos don't have unique IDs. Is there any way you could write one example of each using my database, so that I can see which fields to reference. I really, really tried, but I don't quite get it.


----------



## OBP (Mar 8, 2005)

To satisfy the IT request I would write an Admin Brochure on how to add the field to the table/queries/forms and Reports.
I don't think you have the time to develop the VBA to do this, the table is easy enough, the query not too bad, but the form and report are much harder and your level of expertise in VBA is not high enough and the time too short for all the development & testing involved. I am not I could do it in the time you have left. 

Can you send me a copy of what you do have, with a list of what is still needed?


----------



## krs1716 (Feb 10, 2012)

I sent you the newest version of the database in an e-mail. However, I noticed that Support Service isn't working again. I tried to delete the relationships and then re-create them, but that didn't work. I know that I ask a lot, but could you look over that aspect, and maybe tell me what I'm missing?


----------



## krs1716 (Feb 10, 2012)

OBP,

I don't mean to bug you...I'm sure you have a lot on your plate, but I was just wondering if you received my e-mail, and if you had a chance to look over the database.

P.S. Is there a reason why the following SQL Statement would say that a "Reserved word is misspelled or missing, or the punctuation is incorrect":

SELECT 
FROM [General Information Query], [CustomerServices Query], [Training Query], [Exit Information Query];


----------



## krs1716 (Feb 10, 2012)

Nevermind about the SQL Statement. I figured it out. Really, really, really stupid mistake and question.

Sorry about that.


----------



## OBP (Mar 8, 2005)

Yes, I have the database, but I have been too busy on 2 other databases to get around to it yet, I will take a look now.


----------



## krs1716 (Feb 10, 2012)

Okay. I didn't mean to rush you. I just wanted to be sure the e-mail went through. I had some trouble attaching it.

Thank you.


----------



## OBP (Mar 8, 2005)

I have sent back your database with 3 new sets of code for the Search Form.


----------



## krs1716 (Feb 10, 2012)

Ok. I am not able to work on it today, but I'll look at it tomorrow. Thank you.


----------



## krs1716 (Feb 10, 2012)

I looked over the coding for the Search Criteria form. I think I understand it. The only thing that I didn't see in the coding is an example of a checkbox.

P.S. On the subject of checkboxes, would it be plausible for me to create a checkbox for each of the date ranges that denotes that something is in the customer's profile for that date? For example, if staff doesn't need customers that are registered between a date range, but the customer has been registered. Therefore, I would make a checkbox titled "Registered" that would denote that the customers they are looking for do not contain a null value in the Registration Date field.


----------



## OBP (Mar 8, 2005)

I didn't get around to a Check Box, I will do it later.
Do you mean you want to be able to find customers outside the date range?


----------



## krs1716 (Feb 10, 2012)

Okay. Thanks.

No, I mean sometimes the date range doesn't matter. They just need to know that the person is registered no matter what the date range is. So, instead of typing a date range, they want a checkbox that declares that they want people who would have some value in the date field.

For instance if they want all customers that are Registered, in Cambria County, and Female. There is no current way for them to retrieve that information. But with the checkbox, they could.

I don't know if I'm explaining this very well.


----------



## OBP (Mar 8, 2005)

One of the advantages of this method is that you do not need the dates if you are looking for other things, I have just added the county and Gender and it works without dates being selected.


----------



## krs1716 (Feb 10, 2012)

That's not quite what I mean. They are searching for customers who have registered. It doesn't matter _when_ they registered, just that they _did_ register. Thus...the checkbox.


----------



## OBP (Mar 8, 2005)

Are they in the Database if they didn't register?


----------



## krs1716 (Feb 10, 2012)

Yes.


----------



## OBP (Mar 8, 2005)

Then you will need a Check box.


----------



## OBP (Mar 8, 2005)

Here is the code for the Checkbox, this one is for the Drivers Licence

If Me.chkDriverLicense = -1 Then
swhere = swhere & " AND [Driver's License] = -1"
End If


----------



## krs1716 (Feb 10, 2012)

Okay. Thank you.


----------



## krs1716 (Feb 10, 2012)

Well, I finished writing the code, but when I click on "Search", the following error message appears: "Error # 2501: The OpenForm Action was canceled."

This is the code that I have behind the "Search" button:
Private Sub Search_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([ID])"
'If Not IsNull(Me.LstWIAFundingStream) Then
' swhere = swhere & " AND [WIA Funding Stream] = " & Me.LstWIAFundingStream
' MsgBox Me.LstWIAFundingStream
'End If
If Me.LstWIAFundingStream.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstWIAFundingStream.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [WIA Funding Stream] IN (SELECT [WIA Funding Stream] FROM CustomerServices WHERE [WIA Funding Stream] = " & Me.LstWIAFundingStream.ItemData(var) & " )"
Else
swhere = swhere & " or [WIA Funding Stream] IN (SELECT [WIA Funding Stream] FROM CustomerServices WHERE [WIA Funding Stream] = " & Me.LstWIAFundingStream.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCity.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCity.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [City] IN (SELECT [City] FROM General_Information WHERE [City] = " & Me.LstCity.ItemData(var) & " )"
Else
swhere = swhere & " or [City] IN (SELECT [City] FROM General_Information WHERE [City] = " & Me.LstCity.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCounty.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCounty.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [County] IN (SELECT [County] FROM General_Information WHERE [County] = " & Me.LstCounty.ItemData(var) & " )"
Else
swhere = swhere & " or [County] IN (SELECT [County] FROM General_Information WHERE [County] = " & Me.LstCounty.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSpecialCircumstances.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Special Circumstances] IN (SELECT [Special Circumstances] FROM General_Information WHERE [Special Circumstances] = " & Me.LstSpecialCircumstances.ItemData(var) & " )"
Else
swhere = swhere & " or [Special Circumstances] IN (SELECT [Special Circumstances] FROM General_Information WHERE [Special Circumstances] = " & Me.LstSpecialCircumstances.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstPreviousEducation.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstPreviousEducation.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Previous Education] IN (SELECT [Previous Education] FROM General Information WHERE [Previous Education] = " & Me.LstPreviousEducation.ItemData(var) & " )"
Else
swhere = swhere & " or [Previous Education] IN (SELECT [Previous Education] FROM General Information WHERE [Previous Education] = " & Me.LstPreviousEducation.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstState.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstState.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [State] IN (SELECT [State] FROM General_Information WHERE [State] = " & Me.LstState.ItemData(var) & " )"
Else
swhere = swhere & " or [State] IN (SELECT [State] FROM General_Information WHERE [State] = " & Me.LstState.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstGender.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstGender.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Gender] IN (SELECT [Gender] FROM General_Information WHERE [Gender] = " & Me.LstGender.ItemData(var) & " )"
Else
swhere = swhere & " or [Gender] IN (SELECT [Gender] FROM General_Information WHERE [Gender] = " & Me.LstGender.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstRace.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstRace.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Race] IN (SELECT [Race] FROM General_Information WHERE [Race] = " & Me.LstRace.ItemData(var) & " )"
Else
swhere = swhere & " or [Race] IN (SELECT [Race] FROM General_Information WHERE [Race] = " & Me.LstRace.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCareerPlanner.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCareerPlanner.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Career Planner] IN (SELECT [Career Planner] FROM CustomerServices WHERE [Career Planner] = " & Me.LstCareerPlanner.ItemData(var) & " )"
Else
swhere = swhere & " or [Career Planner] IN (SELECT [Career Planner] FROM CustomerServices WHERE [Career Planner] = " & Me.LstCareerPlanner.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstProgram.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstProgram.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Program] IN (SELECT [Program] FROM CustomerServices WHERE [Program] = " & Me.LstProgram.ItemData(var) & " )"
Else
swhere = swhere & " or [Program] IN (SELECT [Program] FROM CustomerServices WHERE [Program] = " & Me.LstProgram.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstSupportService.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSupportService.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Support Service] IN (SELECT [Support Service] FROM CustomerServices WHERE [Support Service] = " & Me.LstSupportService.ItemData(var) & " )"
Else
swhere = swhere & " or [Support Service] IN (SELECT [Support Service] FROM CustomerServices WHERE [Support Service] = " & Me.LstSupportService.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstTrainingCategory.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstTrainingCategory.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Training Category] IN (SELECT [Training Category] FROM Training WHERE [Training Category] = " & Me.LstTrainingCategory.ItemData(var) & " )"
Else
swhere = swhere & " or [Training Category] IN (SELECT [Training Category] FROM Training WHERE [Training Category] = " & Me.LstTrainingCategory.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstExitType.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstExitType.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Exit Type] IN (SELECT [Exit Type] FROM Exit_Information WHERE [Exit Type] = " & Me.LstExitType.ItemData(var) & " )"
Else
swhere = swhere & " or [Exit Type] IN (SELECT [Exit Type] FROM Exit_Information WHERE [Exit Type] = " & Me.LstExitType.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstIndustryCluster.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstIndustryCluster.ItemsSelected
If itemcount < 1 Then
 swhere = swhere & " And [Industry Cluster] IN (SELECT [Industry Cluster] FROM Exit_Information WHERE [Industry Cluster] = " & Me.LstIndustryCluster.ItemData(var) & " )"
Else
swhere = swhere & " or [Industry Cluster] IN (SELECT [Industry Cluster] FROM Exit_Information WHERE [Industry Cluster] = " & Me.LstIndustryCluster.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstReasonforExit.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstReasonforExit.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Reason for Exit] IN (SELECT [Reason for Exit] FROM Exit_Information WHERE [Reason for Exit] = " & Me.LstReasonforExit.ItemData(var) & " )"
Else
swhere = swhere & " or [Reason for Exit] IN (SELECT [Reason for Exit] FROM Exit_Information WHERE [Reason for Exit] = " & Me.LstReasonforExit.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If

If Not IsNull(Me.txtRegistrationStart) And Not IsNull(Me.txtRegistrationEnd) Then
swhere = swhere & " AND [Registration Date] Between #" & Me.txtRegistrationStart & "# And #" & Me.txtRegistrationEnd & "#"
End If
If Not IsNull(Me.txtOrientationStart) And Not IsNull(Me.txtOrientationEnd) Then
swhere = swhere & " AND [Orientation Date] Between #" & Me.txtOrientationStart & "# And #" & Me.txtOrientationEnd & "#"
End If
If Not IsNull(Me.txtPREPStart) And Not IsNull(Me.txtPREPEnd) Then
swhere = swhere & " AND [PREP Entrance Date] Between #" & Me.txtPREPStart & "# And #" & Me.txtPREPEnd & "#"
End If
If Not IsNull(Me.txtWorkKeysStart) And Not IsNull(Me.txtWorkKeysEnd) Then
swhere = swhere & " AND [WorkKeys Completion Date] Between #" & Me.txtWorkKeysStart & "# And #" & Me.txtWorkKeysEnd & "#"
End If
If Not IsNull(Me.txtLastServiceStart) And Not IsNull(Me.txtLastServiceEnd) Then
swhere = swhere & " AND [Case Management Estimated End Date] Between #" & Me.txtLastServiceStart & "# And #" & Me.txtLastServiceEnd & "#"
End If
If Not IsNull(Me.txtPlacementStart) And Not IsNull(Me.txtPlacementEnd) Then
swhere = swhere & " AND [Placement Date] Between #" & Me.txtPlacementStart & "# And #" & Me.txtPlacementEnd & "#"
End If
If Not IsNull(Me.txtProgramStartStart) And Not IsNull(Me.txtProgramStartEnd) Then
swhere = swhere & " AND CustomerServices.[Start Date] Between #" & Me.txtProgramStartStart & "# And #" & Me.txtProgramStartEnd & "#"
End If
If Not IsNull(Me.txtProgramEndStart) And Not IsNull(Me.txtProgramEndEnd) Then
swhere = swhere & " AND CustomerServices.[End Date] Between #" & Me.txtProgramEndStart & "# And #" & Me.txtProgramEndEnd & "#"
End If
If Not IsNull(Me.txtSupportServiceStartStart) And Not IsNull(Me.txtSupportServiceStartEnd) Then
swhere = swhere & " AND CustomerServices.[Start Date] Between #" & Me.txtSupportServiceStartStart & "# And #" & Me.txtSupportServiceStartEnd & "#"
End If
If Not IsNull(Me.txtSupportServiceEndStart) And Not IsNull(Me.txtSupportServiceEndEnd) Then
swhere = swhere & " AND CustomerServices.[End Date] Between #" & Me.txtSupportServiceEndStart & "# And #" & Me.txtSupportServiceEndEnd & "#"
End If
If Not IsNull(Me.txtTrainingStartStart) And Not IsNull(Me.txtTrainingStartEnd) Then
swhere = swhere & " AND Training.[Start Date] Between #" & Me.txtTrainingStartStart & "# And #" & Me.txtTrainingStartEnd & "#"
End If
If Not IsNull(Me.txtTrainingEndStart) And Not IsNull(Me.txtTrainingEndEnd) Then
swhere = swhere & " AND Training.[End Date] Between #" & Me.txtTrainingEndStart & "# And #" & Me.txtTrainingEndEnd & "#"
End If
If Not IsNull(Me.txtExitStart) And Not IsNull(Me.txtExitEnd) Then
swhere = swhere & " AND [Exit Date] Between #" & Me.txtExitStart & "# And #" & Me.txtExitEnd & "#"
End If
If Not IsNull(Me.txt90DayFollowUpStart) And Not IsNull(Me.txt90DayFollowUpEnd) Then
swhere = swhere & " AND [Exit Date] Between #" & Me.txt90DayFollowUpStart & "# And #" & Me.txt90DayFollowUpEnd & "#"
End If
If Not IsNull(Me.txtJobStartStart) And Not IsNull(Me.txtJobStartEnd) Then
swhere = swhere & " AND [Job Start Date] Between #" & Me.txtJobStartStart & "# And #" & Me.txtJobStartEnd & "#"
End If
If Me.chkDriverLicense = -1 Then
swhere = swhere & " AND [Driver's License] = -1"
End If
If Me.chkSatisfactionSurvey = -1 Then
swhere = swhere & " AND [Customer Survey Sent] = -1"
End If
If Me.chkTradeFunded = -1 Then
swhere = swhere & " AND [Trade-Funded] = -1"
End If
If Me.ChkYouth = -1 Then
swhere = swhere & " AND [Youth] = -1"
End If
If Me.ChkActiveStatus = -1 Then
swhere = swhere & " AND [Active Status] = -1"
End If
If Me.chkRetention = -1 Then
swhere = swhere & " AND [Retention] = -1"
End If
If Me.chkEmployedatRegistration = -1 Then
swhere = swhere & " AND [Employed at Registration] = -1"
End If
If Me.chkPREP = -1 Then
swhere = swhere & " AND [PREP] = -1"
End If
If Me.chkWorkKeys = -1 Then
swhere = swhere & " AND [WorkKeys Completed] = -1"
End If
If Me.ChkObtainedUnsubsidizedEmployment = -1 Then
swhere = swhere & " AND [Employment Obtained] = -1"
End If
If Me.chkBenefits = -1 Then
swhere = swhere & " AND [Benefits] = -1"
End If
If Me.chkCareerLinkReferral = -1 Then
swhere = swhere & " AND [CareerLink Referral] = -1"
End If
'If Not IsNull(Me.cboMilitaryAreaInvolved) Then
' swhere = swhere & " AND [WhatMilitaryareaInvolvedin] = " & Me.cboMilitaryAreaInvolved
'End If
'If Not IsNull(Me.cboCountryServed) Then
' swhere = swhere & " AND [CountryServedUnder?] = " & Me.cboCountryServed
'End If
'If Not IsNull(Me.cboPoliceRank) Then
' swhere = swhere & " AND [PoliceRank] = " & Me.cboPoliceRank
'End If
strsql = "SELECT [ID], [FullName] , [City]" & _
"FROM GeneralInfoDetails " & swhere & ";"
Set dbs = Application.CurrentData
tempquery = "no"
For Each obj In dbs.AllQueries
If obj.Name = "Search Query" Then
tempquery = "yes"
End If
Next obj
If tempquery = "yes" Then
DoCmd.DeleteObject acQuery, "Search Query"
End If
With CurrentDb
Set qdfNew = .CreateQueryDef("Search Query", strsql)
End With
DoCmd.OpenForm "frmCriteriaResults"
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub
End Sub


----------



## OBP (Mar 8, 2005)

Check that the query called Search Query actually works by trying to open it.


----------



## krs1716 (Feb 10, 2012)

No, the Search Query isn't working. It seems to have some kind of problem with the Criteria for "City" in the query.


----------



## OBP (Mar 8, 2005)

The "City" code that you are using is not the code that I provided for the city, or for the multi select list.


----------



## krs1716 (Feb 10, 2012)

Well...I changed the city combo to a multi-select list. I don't understand how I changed the multi-select list. I copied the original and just changed the name. What did I do wrong?


----------



## OBP (Mar 8, 2005)

You have mixed the 2 without one ingredient, in the original City Combo the code was

swhere = swhere & " AND [City] = '" & Me.cboCity & "'"

Note the 2 ' (apostrophes) that convert the data in the combo to Text

You used the code from the WIA Funding correctly but neglected to use the apostrophes to convert the list item to text.

I think that will give you an error.


----------



## krs1716 (Feb 10, 2012)

Can I ask why City needed that added where WIA Funding Stream didn't?


----------



## OBP (Mar 8, 2005)

If I remember correctly it is the way that the data is stored in the Main & Sub Table or used in a Combo Value List.
If it is stored as ID & Value in the sub table and ID in the maintable you would use the ID which probably would not need translating.
But if it is stored as Text value in both it is a quirk of Access that you need to convert the unbound text to text.
Have you tried it?


----------



## krs1716 (Feb 10, 2012)

I tried it, and I think it works. It does pop up a message box with Error#0 and no description, but the people show in the Search Results form.

However, do I need to include all the tables in the GeneralInfoDetails Query in order for the Search Criteria Form to work, because only the fields that you had in the form to start are working properly, and I can't help but wonder if that is the reason?


----------



## OBP (Mar 8, 2005)

GeneralInfoDetails Query should have all the necessary tables and fields in it for the search query to pull them in from the SQL, so each time you add a field to the Search Form's VBA SQL you need to add it to the GeneralInfoDetails Query.
The idea of the Search output form is to just show the minimum of data and have a button to take the user to the actual record in the main data form.

Did you test each search field as you added it to the Search form?


----------



## krs1716 (Feb 10, 2012)

No. Didn't test a thing...genius that I am. I actually wrote the code in Word, so that I could work on it in pieces and mark where I finished. Then, I copied and pasted (my signature move).

I'll keep working and see if I can get things to work. Any thoughts on the Error#0?


----------



## krs1716 (Feb 10, 2012)

The Criteria Results form is showing multiple records, probably because the queries do. Is there a way to disallow this?


----------



## OBP (Mar 8, 2005)

Are the Records Duplicates?
If so that is probably the Joins between the tables in the Query.


----------



## krs1716 (Feb 10, 2012)

What join should I be using? I believe I have it so that all records from he
General Information show and only the records from others where the fields match.


----------



## OBP (Mar 8, 2005)

That is the correct Join, have you tried setting the Unique Records or Unique Values?


----------



## krs1716 (Feb 10, 2012)

I tried both in the GeneralInfoDetails, but neither works. I also tried both in the Search Query, and Unique Values works there, but since the query is overwritten each time a new search is done, it doesn't work every time. Is there a way to write that into the VBA that writes the SQL for the Search Criteria Results?


----------



## krs1716 (Feb 10, 2012)

I added the word "DISTINCT" to the strsql statement in the VBA of Search button, and that seems to have worked. Does that sound correct?

Where would I put an ORDER BY statement in the strsql, so that the records on the results form are in alphabetical order?


----------



## OBP (Mar 8, 2005)

Yes, when you have added to the Search Query open the query in SQL View and we should be able to see the difference between before and after.
Plus I have a form that converts Query derived SQL in to VBA created SQL.


----------



## krs1716 (Feb 10, 2012)

SELECT DISTINCT GeneralInfoDetails.[ID], GeneralInfoDetails.[FullName], GeneralInfoDetails.[Participant ID], GeneralInfoDetails.[City]
FROM GeneralInfoDetails
WHERE (((IsNull([ID]))=False) AND ((GeneralInfoDetails.[Active Status])=True))
ORDER BY GeneralInfoDetails.[FullName];

The above is the SQL Statement after I've made the changes to the Search Query. I tried entering the ORDER BY statement myself, but I kept misplacing it in some way.


----------



## OBP (Mar 8, 2005)

Can you show me the piece of code where you have tried adding it?


----------



## krs1716 (Feb 10, 2012)

I was almost ready to give up. But here is what I typed:
strsql = "SELECT DISTINCT [ID], [FullName] , [Participant ID], [City]" & _
"FROM GeneralInfoDetails" & swhere & " " & _
"ORDER BY GeneralInfoDetails.[FullName]" & ";"

It seems to be working right now, but I'm still getting that Error#0 any time I use any Criteria.


----------



## OBP (Mar 8, 2005)

Do you get that from the query or from the code?


----------



## krs1716 (Feb 10, 2012)

Well, what is above, I typed into the VBA code for the Search button based upon what the Search Query SQL stated. It took some trial and error, though. The space after swhere is apparently very important.

The error message is after I click on Search.


----------



## OBP (Mar 8, 2005)

What do you do with error trapping in your VBA code?


----------



## krs1716 (Feb 10, 2012)

It's that errorcatch thing that was in the original.
On Error GoTo errorcatch
_VBA Code, VBA Code, VBA Code_
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub


----------



## OBP (Mar 8, 2005)

Does it highlight a line of code when you get the error message?
The only other thing that I can suggest is to rem out all the lines and then re-introduce them one at a time to find the offending line of code.


----------



## krs1716 (Feb 10, 2012)

No highlighting of the code.

I don't know what you mean by rem out all the lines.


----------



## OBP (Mar 8, 2005)

You place an apostrophe at the start of the line and it will turn green, which means it has become a "REMark", hence Rem.


----------



## krs1716 (Feb 10, 2012)

Ahh...I see. Is it possible that even if there isn't an error that box shows up anyhow. Thus the error # is 0 because there is no error.


----------



## OBP (Mar 8, 2005)

Do you still have an Exit Sub before the Errorcatch: ?


----------



## krs1716 (Feb 10, 2012)

I do now. That was the problem...somewhere along the way the Exit Sub disappeared (I'm sure it couldn't have been me)


----------



## krs1716 (Feb 10, 2012)

Search form seems to be working well. Thank you so much.

There are a couple of issues, slight I hope. The first issue I'm having is with Special Circumstances, the multiple select combo box that I regret with every passing day. Apparently, multiple select combo boxes cannot be used in WHERE statements. So, I'm stuck deciding how to allow users to search if I can't use a WHERE statement.

The second issue is those checkboxes for the dates we discussed a while back. I had mentioned making a checkbox where the value was that the date field under scrutiny was not null. For example, to find someone who is registered (no matter the registration time period), I would have a box titled "Registered" that signified that the Registration Date field was not null. I'm not entirely sure how to write that into my code. I was thinking something along the lines of the following:

If Me.chkRegistered = -1 Then
swhere = swhere & " AND [Registration Date] Is Not Null"
End If

Thoughts...ideas...general exasperation?


----------



## OBP (Mar 8, 2005)

I think it is 
If Me.chkRegistered = -1 Then
swhere = swhere & " AND Not Isnull([Registration Date]) "
End If
When you have a Multiselect Combo you have to either work with the Combo or the Control Source(table field).
How does it store the data that you want to search for?


----------



## krs1716 (Feb 10, 2012)

Store the data?

I think it just stores it as the text. For instance, when I query the field, I don't type the associated Autonumber into the criteria. Instead, I have to type the text. Is that what you mean?


----------



## krs1716 (Feb 10, 2012)

What if I made checkboxes on the search form that represented the fields in the multiple select combo box?


----------



## OBP (Mar 8, 2005)

Can you show me a Screenprint of a field in the table with multiple selections.


----------



## krs1716 (Feb 10, 2012)

Sure.


----------



## OBP (Mar 8, 2005)

OK, the data is separated by commas, so it should be possible to search for them using the "Like" function.
Will you need to search for more than one at a time, ie look for combinations?


----------



## krs1716 (Feb 10, 2012)

I've never worked with the "Like" function before.

I believe that being able to search for combinations will be necessary.


----------



## OBP (Mar 8, 2005)

OK, your Search Listbox also needs to be set to Multi-select.
If there is more than one selection you will have use an "And" in the where statement so that the query checks for each one.
The Like structure looks like this

If Not IsNull(Me.CLINIC) Then sWHERE = sWHERE & " AND CLINIC like '*' & '" & Me.CLINIC & "' " & " & '*' "

That will find the text string embedded anywhere in the field being searched.


----------



## krs1716 (Feb 10, 2012)

I understand the statement, but I am not sure about the implementation. I already have the list box on the search form as a Multi-Select List box. The following is the VBA statement that is currently in place for the field in question (it does not work):

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSpecialCircumstances.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Special Circumstances] IN (SELECT [Special Circumstances] FROM [General Information] WHERE [Special Circumstances] = " & Me.LstSpecialCircumstances.ItemData(var) & " )"
Else
swhere = swhere & " or [Special Circumstances] IN (SELECT [Special Circumstances] FROM [General Information] WHERE [Special Circumstances] = " & Me.LstSpecialCircumstances.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If

How would I re-word this to contain the "Like" function? I started writing it but got befuddled by when it came to placement of the function.


----------



## OBP (Mar 8, 2005)

When you say the code does not work, what does it do or not do?
Basically you would replace this section
[Special Circumstances] = " & Me.LstSpecialCircumstances.ItemData(var)
with 
[Special Circumstances] like '*' & '" &Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' "

But you may have to be careful with the placement of the "s in the statement.


----------



## krs1716 (Feb 10, 2012)

Well, the code does not work because it will not accept using the field in a where statement as it is set up now. My hope is that implementing your statement from above will override this issue.


----------



## OBP (Mar 8, 2005)

If the field in the table only has one value in it I would have thought the original code would work, but when there have been multiple selections.
You could test the "Like" function in a query first if you want.


----------



## krs1716 (Feb 10, 2012)

I now get an error message that some element is is missing.


----------



## OBP (Mar 8, 2005)

Did you try the "Like" function i a query?
as that will provide the SQL.


----------



## krs1716 (Feb 10, 2012)

Okay. I figured out the error message. When I pasted the new code, I overwrote the ending parentheses. The search works with the code, but if I choose two, it brings up anybody that has either instead of anybody that has both. I know that you mentioned incorporating "And." Where would I include that?


----------



## OBP (Mar 8, 2005)

The code that I originally provided is set to look for only 1 when the item count is ,1 and then if it is 1 or more it uses the "Or" statement.
So in this case you don't need the 
If itemcount < 1 Then
or the else and the code after it including the End if so you end up with just the first statement that has the And in it.


----------



## krs1716 (Feb 10, 2012)

I'm not sure now. Maybe the OR is best. In these kinds of situations, which would you recommend? My concern is if staff would want to search for anybody who has one of three Special Circumstances, how would they do that if I made it "And"?


----------



## OBP (Mar 8, 2005)

Perhaps you need an Option Group, "find records with all Selected items" or "Find records with any of the Selected items".
You can then use both pieces of code chosen by the user.


----------



## krs1716 (Feb 10, 2012)

Now you are talking in a foreign language to me . How would I use that within my VBA, then?


----------



## OBP (Mar 8, 2005)

The Option Group uses a value for the choice (Description) and you use the Value ie
if me.optiongroup0 = 1 then
run the first set of code here
else 
run the second set of code here
end if

or you could use 

if me.optiongroup0 = 1 then
run the first set of code here
end if

if me.optiongroup0 = 2 then
run the second set of code here
end if


----------



## OBP (Mar 8, 2005)

If I was going to do that I would put the List and the Option Group in a Rectangle box to show that they are connected.


----------



## krs1716 (Feb 10, 2012)

Okay. I included the option group and implemented the coding that we discussed. The AND statement is working, but the OR statement is not. I think that I may have missed something. The problem I have is that when I use the OR statement, everybody is included, even if they have no information in that field whatsoever. The code that I implemented is as follows:

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSpecialCircumstances.ItemsSelected
If Me.OptGrpSpecialCircumstances = 1 Then
swhere = swhere & " And [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*') "
End If
If Me.OptGrpSpecialCircumstances = 2 Then
swhere = swhere & " or [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
End If
itemcount = itemcount + 1
Next var
End If


----------



## OBP (Mar 8, 2005)

If you don't select anything else does it still list everybody?
If it does it is probably because the swhere starts with the ID field so that you do not need to worry about whether there is an swhere or not.


----------



## krs1716 (Feb 10, 2012)

Yes, it does. How would I fix that?


----------



## OBP (Mar 8, 2005)

The only way is to remove the original select of the ID field and replace it with the code that you are using for the "Or".
But you would have to make sure the code is placed right at the beginning of the code so that it does not over write any other selections.


----------



## krs1716 (Feb 10, 2012)

I apologize, but I do not know what you mean by "remove the original select of the ID field and replace it with the code".

Where would that be located?


----------



## OBP (Mar 8, 2005)

The swhere code starts with this line

swhere = " WHERE not isnull([ID])"

You need to place this code
If Me.OptGrpSpecialCircumstances = 2 Then
swhere = swhere & " or [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
End If

Directly after that line and change it to

swhere = " [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"

So that it becomes the first item in the where statement.


----------



## krs1716 (Feb 10, 2012)

So the first lines of the code would read like this: 
Private Sub Search_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([ID])"
If Me.OptGrpSpecialCircumstances = 2 Then
swhere = " [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
End If

Then, the remainder of my code would follow? I would completely take out the OR statement further down in the code though, right?


----------



## OBP (Mar 8, 2005)

That is correct, including taking out the old or statement further down


----------



## krs1716 (Feb 10, 2012)

I implemented the code, but I get an error message that tells me that I have a syntax error in the FROM clause. I don't know much about strings in VBA, but I was wondering if there should be a space between the selections? If so, where would that go in the VBA?


----------



## OBP (Mar 8, 2005)

Can you post the code that have for that new first line?


----------



## krs1716 (Feb 10, 2012)

The new first line of code is:

Private Sub Search_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([ID])"
If Me.OptGrpSpecialCircumstances = 2 Then
swhere = " [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
End If


----------



## krs1716 (Feb 10, 2012)

Does the code need to have that itemcount?


----------



## OBP (Mar 8, 2005)

Can you send me a copy of the database with the option group and list box etc?


----------



## krs1716 (Feb 10, 2012)

I e-mailed you the database. Did it go through?


----------



## OBP (Mar 8, 2005)

Yes I have it thanks, I am working on it now.


----------



## OBP (Mar 8, 2005)

OK, I have reworked the Special Circumstances code and added some code at the end to remove the "or" on the end which was causing the syntax error.
It appears to working OK.
PS I also corrected the GeneralInfoDetails query join as it was causing the record to not show up in the query.

I have sent it back to you.


----------



## krs1716 (Feb 10, 2012)

I seem to be having some issues. I still get a syntax error when I choose the second option in the option group: "Find records with any of the selected Special Circumstances."


----------



## OBP (Mar 8, 2005)

I do not get a Syntax Error, are you sure you are not looking at My message telling you what the string consists of?
It is showing the or) on the end that should be just the )


----------



## krs1716 (Feb 10, 2012)

I get an error # 3075, and the "or" is still there in the message box before the error.


----------



## OBP (Mar 8, 2005)

I did send you version 0.0?
As I do not get that error with the second option.
The or should be in the message, which you can delete.


----------



## krs1716 (Feb 10, 2012)

I have version 0.0, but I still get a message.


----------



## OBP (Mar 8, 2005)

OK< I get the error message if I select 2 items.
I will rework it.


----------



## krs1716 (Feb 10, 2012)

You also might want to try selecting one that you know nobody has and see if somebody shows up for it anyway. I had the problem...not sure why.


----------



## OBP (Mar 8, 2005)

OK this code does all 3 problems

Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String, totalcount As Integer
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([ID])"
If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then
swhere = ""
itemcount = 0
totalcount = 0
For Each var In Me.LstSpecialCircumstances.ItemsSelected
totalcount = totalcount + 1
Next var
For Each var In Me.LstSpecialCircumstances.ItemsSelected
If Me.OptGrpSpecialCircumstances = 2 Then
If itemcount = 0 And totalcount = 1 Then
swhere = " where [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
Else
If itemcount = 0 Then
swhere = " where [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
Else
swhere = swhere & " or [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*' )"
End If
End If
End If
itemcount = itemcount + 1
Next var
'MsgBox swhere
End If

'If Not IsNull(Me.LstWIAFundingStream) Then
' swhere = swhere & " AND [WIA Funding Stream] = " & Me.LstWIAFundingStream
' MsgBox Me.LstWIAFundingStream
'End If
If Me.LstWIAFundingStream.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstWIAFundingStream.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [WIA Funding Stream] IN (SELECT [WIA Funding Stream] FROM CustomerServices WHERE [WIA Funding Stream] = " & Me.LstWIAFundingStream.ItemData(var) & " )"
Else
swhere = swhere & " or [WIA Funding Stream] IN (SELECT [WIA Funding Stream] FROM CustomerServices WHERE [WIA Funding Stream] = " & Me.LstWIAFundingStream.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCity.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCity.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [City] IN (SELECT [City] FROM [General Information] WHERE [City] = '" & Me.LstCity.ItemData(var) & "' )"
Else
swhere = swhere & " or [City] IN (SELECT [City] FROM [General Information] WHERE [City] = '" & Me.LstCity.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCounty.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCounty.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [County] IN (SELECT [County] FROM [General Information] WHERE [County] = '" & Me.LstCounty.ItemData(var) & "' )"
Else
swhere = swhere & " or [County] IN (SELECT [County] FROM [General Information] WHERE [County] = '" & Me.LstCounty.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSpecialCircumstances.ItemsSelected
If Me.OptGrpSpecialCircumstances = 1 Then
swhere = swhere & " And [Special Circumstances].[Value] IN (SELECT [Special Circumstances].[Value] FROM [General Information] WHERE [Special Circumstances].[Value] like '*' & '" & Me.LstSpecialCircumstances.ItemData(var) & "' " & " & '*') "
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstPreviousEducation.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstPreviousEducation.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Previous Education] IN (SELECT [Previous Education] FROM [General Information] WHERE [Previous Education] = '" & Me.LstPreviousEducation.ItemData(var) & "' )"
Else
swhere = swhere & " or [Previous Education] IN (SELECT [Previous Education] FROM [General Information] WHERE [Previous Education] = '" & Me.LstPreviousEducation.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstState.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstState.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [State/Province] IN (SELECT [State/Province] FROM [General Information] WHERE [State/Province] = '" & Me.LstState.ItemData(var) & "' )"
Else
swhere = swhere & " or [State/Province] IN (SELECT [State/Province] FROM [General Information] WHERE [State/Province] = '" & Me.LstState.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstGender.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstGender.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Gender] IN (SELECT [Gender] FROM [General Information] WHERE [Gender] = '" & Me.LstGender.ItemData(var) & "' )"
Else
swhere = swhere & " or [Gender] IN (SELECT [Gender] FROM [General Information] WHERE [Gender] = '" & Me.LstGender.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If

If Me.LstRace.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstRace.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Race] IN (SELECT [Race] FROM [General Information] WHERE [Race] = '" & Me.LstRace.ItemData(var) & "' )"
Else
swhere = swhere & " or [Race] IN (SELECT [Race] FROM [General Information] WHERE [Race] = '" & Me.LstRace.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstCareerPlanner.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstCareerPlanner.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Career Planner] IN (SELECT [Career Planner] FROM CustomerServices WHERE [Career Planner] = " & Me.LstCareerPlanner.ItemData(var) & " )"
Else
swhere = swhere & " or [Career Planner] IN (SELECT [Career Planner] FROM CustomerServices WHERE [Career Planner] = " & Me.LstCareerPlanner.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstProgram.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstProgram.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [ServiceID] IN (SELECT [ServiceID] FROM CustomerServices WHERE [ServiceID] = " & Me.LstProgram.ItemData(var) & " )"
Else
swhere = swhere & " or [ServiceID] IN (SELECT [ServiceID] FROM CustomerServices WHERE [ServiceID] = " & Me.LstProgram.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstSupportService.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstSupportService.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [SSOptionID] IN (SELECT [SSOptionID] FROM CustomerSSOptions WHERE [SSOptionID] = " & Me.LstSupportService.ItemData(var) & " )"
Else
swhere = swhere & " or [SSOptionID] IN (SELECT [SSOptionID] FROM CustomerSSOptions WHERE [SSOptionID] = " & Me.LstSupportService.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstTrainingCategory.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstTrainingCategory.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Training Category] IN (SELECT [Training Category] FROM Training WHERE [Training Category] = " & Me.LstTrainingCategory.ItemData(var) & " )"
Else
swhere = swhere & " or [Training Category] IN (SELECT [Training Category] FROM Training WHERE [Training Category] = " & Me.LstTrainingCategory.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstExitType.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstExitType.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Exit Type] IN (SELECT [Exit Type] FROM [Exit Information] WHERE [Exit Type] = '" & Me.LstExitType.ItemData(var) & "' )"
Else
swhere = swhere & " or [Exit Type] IN (SELECT [Exit Type] FROM [Exit Information] WHERE [Exit Type] = '" & Me.LstExitType.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstIndustryCluster.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstIndustryCluster.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Industry Cluster] IN (SELECT [Industry Cluster] FROM [Exit Information] WHERE [Industry Cluster] = " & Me.LstIndustryCluster.ItemData(var) & " )"
Else
swhere = swhere & " or [Industry Cluster] IN (SELECT [Industry Cluster] FROM [Exit Information] WHERE [Industry Cluster] = " & Me.LstIndustryCluster.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
If Me.LstReasonforExit.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstReasonforExit.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Reason for Exit] IN (SELECT [Reason for Exit] FROM [Exit Information] WHERE [Reason for Exit] = '" & Me.LstReasonforExit.ItemData(var) & "' )"
Else
swhere = swhere & " or [Reason for Exit] IN (SELECT [Reason for Exit] FROM [Exit Information] WHERE [Reason for Exit] = '" & Me.LstReasonforExit.ItemData(var) & "' )"
End If
itemcount = itemcount + 1
Next var
End If

If Not IsNull(Me.txtRegistrationStart) And Not IsNull(Me.txtRegistrationEnd) Then
swhere = swhere & " AND [Registration Date] Between #" & Me.txtRegistrationStart & "# And #" & Me.txtRegistrationEnd & "#"
End If
If Not IsNull(Me.txtOrientationStart) And Not IsNull(Me.txtOrientationEnd) Then
swhere = swhere & " AND [Orientation Date] Between #" & Me.txtOrientationStart & "# And #" & Me.txtOrientationEnd & "#"
End If
If Not IsNull(Me.txtPREPStart) And Not IsNull(Me.txtPREPEnd) Then
swhere = swhere & " AND [PREP Entrance Date] Between #" & Me.txtPREPStart & "# And #" & Me.txtPREPEnd & "#"
End If
If Not IsNull(Me.txtWorkKeysStart) And Not IsNull(Me.txtWorkKeysEnd) Then
swhere = swhere & " AND [WorkKeys Completion Date] Between #" & Me.txtWorkKeysStart & "# And #" & Me.txtWorkKeysEnd & "#"
End If
If Not IsNull(Me.txtLastServiceStart) And Not IsNull(Me.txtLastServiceEnd) Then
swhere = swhere & " AND [Case Management Estimated End Date] Between #" & Me.txtLastServiceStart & "# And #" & Me.txtLastServiceEnd & "#"
End If
If Not IsNull(Me.txtPlacementStart) And Not IsNull(Me.txtPlacementEnd) Then
swhere = swhere & " AND [Placement Date] Between #" & Me.txtPlacementStart & "# And #" & Me.txtPlacementEnd & "#"
End If
If Not IsNull(Me.txtProgramStartStart) And Not IsNull(Me.txtProgramStartEnd) Then
swhere = swhere & " AND CustomerServices.[Start Date] Between #" & Me.txtProgramStartStart & "# And #" & Me.txtProgramStartEnd & "#"
End If
If Not IsNull(Me.txtProgramEndStart) And Not IsNull(Me.txtProgramEndEnd) Then
swhere = swhere & " AND CustomerServices.[End Date] Between #" & Me.txtProgramEndStart & "# And #" & Me.txtProgramEndEnd & "#"
End If
If Not IsNull(Me.txtSupportServiceStartStart) And Not IsNull(Me.txtSupportServiceStartEnd) Then
swhere = swhere & " AND CustomerServices.[Start Date] Between #" & Me.txtSupportServiceStartStart & "# And #" & Me.txtSupportServiceStartEnd & "#"
End If
If Not IsNull(Me.txtSupportServiceEndStart) And Not IsNull(Me.txtSupportServiceEndEnd) Then
swhere = swhere & " AND CustomerServices.[End Date] Between #" & Me.txtSupportServiceEndStart & "# And #" & Me.txtSupportServiceEndEnd & "#"
End If
If Not IsNull(Me.txtTrainingStartStart) And Not IsNull(Me.txtTrainingStartEnd) Then
swhere = swhere & " AND Training.[Start Date] Between #" & Me.txtTrainingStartStart & "# And #" & Me.txtTrainingStartEnd & "#"
End If
If Not IsNull(Me.txtTrainingEndStart) And Not IsNull(Me.txtTrainingEndEnd) Then
swhere = swhere & " AND Training.[End Date] Between #" & Me.txtTrainingEndStart & "# And #" & Me.txtTrainingEndEnd & "#"
End If
If Not IsNull(Me.txtExitStart) And Not IsNull(Me.txtExitEnd) Then
swhere = swhere & " AND [Exit Date] Between #" & Me.txtExitStart & "# And #" & Me.txtExitEnd & "#"
End If
If Not IsNull(Me.txt90DayFollowUpStart) And Not IsNull(Me.txt90DayFollowUpEnd) Then
swhere = swhere & " AND [Exit Date] Between #" & Me.txt90DayFollowUpStart & "# And #" & Me.txt90DayFollowUpEnd & "#"
End If
If Not IsNull(Me.txtJobStartStart) And Not IsNull(Me.txtJobStartEnd) Then
swhere = swhere & " AND [Job Start Date] Between #" & Me.txtJobStartStart & "# And #" & Me.txtJobStartEnd & "#"
End If
If Me.chkDriverLicense = -1 Then
swhere = swhere & " AND [Driver's License] = -1"
End If
If Me.chkSatisfactionSurvey = -1 Then
swhere = swhere & " AND [Customer Survey Sent] = -1"
End If
If Me.chkTradeFunded = -1 Then
swhere = swhere & " AND [Trade-Funded] = -1"
End If
If Me.ChkYouth = -1 Then
swhere = swhere & " AND [Youth] = -1"
End If
If Me.ChkActiveStatus = -1 Then
swhere = swhere & " AND [Active Status] = -1"
End If
If Me.chkRetention = -1 Then
swhere = swhere & " AND [Retention] = -1"
End If
If Me.chkEmployedatRegistration = -1 Then
swhere = swhere & " AND [Employed at Registration] = -1"
End If
If Me.chkPREP = -1 Then
swhere = swhere & " AND [PREP] = -1"
End If
If Me.chkWorkKeys = -1 Then
swhere = swhere & " AND [WorkKeys Completed] = -1"
End If
If Me.ChkObtainedUnsubsidizedEmployment = -1 Then
swhere = swhere & " AND [Employment Obtained] = -1"
End If
If Me.chkBenefits = -1 Then
swhere = swhere & " AND [Benefits] = -1"
End If
If Me.chkCareerLinkReferral = -1 Then
swhere = swhere & " AND [CareerLink Referral] = -1"
End If
'If Not IsNull(Me.cboMilitaryAreaInvolved) Then
' swhere = swhere & " AND [WhatMilitaryareaInvolvedin] = " & Me.cboMilitaryAreaInvolved
'End If
'If Not IsNull(Me.cboCountryServed) Then
' swhere = swhere & " AND [CountryServedUnder?] = " & Me.cboCountryServed
'End If
'If Not IsNull(Me.cboPoliceRank) Then
' swhere = swhere & " AND [PoliceRank] = " & Me.cboPoliceRank
'End If
If Right(swhere, 3) = "or)" Then
swhere = Left(swhere, Len(swhere) - 3) & ")"
End If
'MsgBox swhere
strsql = "SELECT DISTINCT [General Information].[ID], [FullName] , [General Information].[Participant ID], [General Information].[City], [Home Phone], [Address], [Address2], [Contact Name]" & _
"FROM GeneralInfoDetails" & swhere & " " & _
"ORDER BY GeneralInfoDetails.[FullName]" & ";"
Set dbs = Application.CurrentData
tempquery = "no"
For Each obj In dbs.AllQueries
If obj.Name = "Search Query" Then
tempquery = "yes"
End If
Next obj
If tempquery = "yes" Then
DoCmd.DeleteObject acQuery, "Search Query"
End If
With CurrentDb
Set qdfNew = .CreateQueryDef("Search Query", strsql)
End With
DoCmd.OpenForm "frmCriteriaResults"
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


----------



## krs1716 (Feb 10, 2012)

I hate to type this, but now there is a syntax error in the FROM clause when I choose the first option: "Find records with all selected Special Circumstances."

Also, if I don't choose any option it brings up everybody, is that the way it is supposed to be? Maybe it is.


----------



## krs1716 (Feb 10, 2012)

I really don't want to come to you with yet another problem, and perhaps I should start a new thread, but I worked on creating a customer profile report for staff to be able to print all information (except case notes) about a customer. I had one almost complete when my version was corrupted, and so I no longer had that reference. I have tried to re-create it, but honestly do not remember what I did before.

The problem I keep running into is when a customer has more that one record, such as when using continous form to put multiple entries into the database, more than one report will appear. Have you ever created something along those lines before?


----------



## OBP (Mar 8, 2005)

Create a query & report for the main data and a query and Subreport for the multiple records.


----------



## OBP (Mar 8, 2005)

Yes when you do not select anything it will show all records, you can prevent this by checking the swhere string if it is null then do not open the form, put up a message saying nothing selected.
It is odd that it has affected the first option as that has not been changed at all.


----------



## OBP (Mar 8, 2005)

OK change this line of code

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 Then

to

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 And Me.OptGrpSpecialCircumstances = 2 Then


----------



## krs1716 (Feb 10, 2012)

That line of code seems to have fixed the problem. Thank you!

Do you think that I should include the message if an option group selection is not selected? If so, where should the line of code be placed? I'm concerned with placing code now that we got everything to work.


----------



## OBP (Mar 8, 2005)

I would replace the code here

If Right(swhere, 3) = "or)" Then
swhere = Left(swhere, Len(swhere) - 3) & ")"
End If

which is no longer needed due to the re-arranged code and use instead

If inull(swhere) or swhere = "" Then
msgbox "You have not selected anything to search for"
Exit Sub
End if


----------



## krs1716 (Feb 10, 2012)

I tried that piece of code, but if I include another string with it, the code does not recognize that no option is chosen. For instance, if I search for all females with a disability (no option group chosen), the search returns all females and ignores the disability.


----------



## OBP (Mar 8, 2005)

Well it won't once you choose anything else it will be added to the swhere string.
If you are worried about the option group not being checked that is another problem.
You need to check if one or more disabilities has been selected and if so that the Option has also been checked.
Isn't being a good programmer fun


----------



## krs1716 (Feb 10, 2012)

Would this line of code work instead:

If NotIsNull([LstSpecialCircumstance]) AND IsNul([OptGrpSpecialCircumstances]) Then
MsgBox "You must select an Option for Special Circumstances."
Exit Sub
End If

?


----------



## OBP (Mar 8, 2005)

That looks good as long as you spell the second Isnull with 2 LLs otherwise you will get a syntax error,
but it should not be instead of, it should as well as the other code


----------



## krs1716 (Feb 10, 2012)

I added the code that I wrote (fixing the Null spelling) and I get a message telling me that my Sub or Function is not defined and it highlights "NotIsNull".

Why would that be happening?


----------



## krs1716 (Feb 10, 2012)

I fixed the spacing for "Not Is Null". Now, no message comes up about that, but now it tells me that it can't find the field referenced in my expression.


----------



## OBP (Mar 8, 2005)

It is 
Not isnull()


----------



## krs1716 (Feb 10, 2012)

I fixed it. However, the error message is still appearing, and it does not seem to be related to Special Circumstances because it occurs no matter what selection I make.


----------



## krs1716 (Feb 10, 2012)

I figured out the error message. I misspelled a word in the code, but no message box appears if I do not choose from the Option Group.

Can I just say: Ahhhhhhhhhhhhhhhhhhhhh!!!!!


----------



## OBP (Mar 8, 2005)

can you post the new section of code?


----------



## krs1716 (Feb 10, 2012)

Here it is:

If IsNull(swhere) Or swhere = "" Then
MsgBox "You have not selected anything to search for"
Exit Sub
End If
If Not IsNull([LstSpecialCircumstances]) And IsNull([OptGrpSpecialCircumstances]) Then
MsgBox "You must select an Option for Special Circumstances."
Exit Sub
End If


----------



## OBP (Mar 8, 2005)

You need to check that the names are exactly correct, you can test it by typing in
me.lst
and me.opt
the editor should provide the name if it sees it.
You should also Prefix the name with me.


----------



## OBP (Mar 8, 2005)

OK, place this code directly after the

swhere = " WHERE not isnull([ID])"

If Me.LstSpecialCircumstances.ItemsSelected.count > 0 And IsNull(Me.[OptGrpSpecialCircumstances]) Then
MsgBox "You must select an Option for Special Circumstances."
Exit Sub
End If


----------



## krs1716 (Feb 10, 2012)

That worked. Now all I have to do for that form is make provisions for Null or Not Null Values (search based on that kind of criteria).

New Question: (because I can't seem to figure anything out on my own) I created a Search form and all the subsequent objects for Events, but most of the fields are text boxes, and I can't get the text boxes to work. What is the coding language for Searching a text field?


----------



## OBP (Mar 8, 2005)

You either use the Exact text or use the Like "*" & "*" function


----------



## krs1716 (Feb 10, 2012)

I don't know what I did now, but my results query for the event search form does not provide any results. The query ends up false?


----------



## OBP (Mar 8, 2005)

What does the code look like?
You should only work on one field at a time


----------



## krs1716 (Feb 10, 2012)

This is the code:
Private Sub Search_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String, totalcount As Integer
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([EventID])"
If Not IsNull(Me.txtEventTitle) Then
swhere = swhere & " AND [Events].[Title]" = Me.txtEventTitle
End If
If Not IsNull(Me.TxtLocation) Then
swhere = swhere & " AND [Events].[Location]" = Me.TxtLocation
End If
If Not IsNull(Me.TxtContactName) Then
swhere = swhere & " AND [Event Attendees].[Contact]" Like "*" & Me.TxtContactName
End If
If Not IsNull(Me.txtStartTimeStart) And Not IsNull(Me.txtStartTimeEnd) Then
swhere = swhere & " AND [Start Time] Between #" & Me.txtStartTimeStart & "# And #" & Me.txtStartTimeEnd & "#"
End If
If Not IsNull(Me.txtEndTimeStart) And Not IsNull(Me.txtEndTimeEnd) Then
swhere = swhere & " AND [End Time] Between #" & Me.txtEndTimeStart & "# And #" & Me.txtEndTimeEnd & "#"
End If
If Me.LstEventType.ItemsSelected.count > 0 Then
itemcount = 0
For Each var In Me.LstEventType.ItemsSelected
If itemcount < 1 Then
swhere = swhere & " And [Event Type] IN (SELECT [Event Type] FROM [Events] WHERE [Event Type] = " & Me.LstEventType.ItemData(var) & " )"
Else
swhere = swhere & " or [Event Type] IN (SELECT [Event Type] FROM [Events] WHERE [Event Type] = " & Me.LstEventType.ItemData(var) & " )"
End If
itemcount = itemcount + 1
Next var
End If
strsql = "SELECT DISTINCT [Events].[EventID], [Events].[Title], [Events].[Event Type], [Events].[Start Time], [Events].[End Time], [Events].[Location]" & _
"FROM EventsQuery" & swhere & " " & _
"ORDER BY [Event Type], [Start Time]" & ";"
Set dbs = Application.CurrentData
tempquery = "no"
For Each obj In dbs.AllQueries
If obj.Name = "Events Search Query" Then
tempquery = "yes"
End If
Next obj
If tempquery = "yes" Then
DoCmd.DeleteObject acQuery, "Events Search Query"
End If
With CurrentDb
Set qdfNew = .CreateQueryDef("Events Search Query", strsql)
End With
DoCmd.OpenForm "frmEventCriteriaResults"
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

You are so wise...work on one thing at a time! I just don't think of those kind of simple solutions.


----------



## OBP (Mar 8, 2005)

So do you know which parts do work ?

I will be going out soon.


----------



## krs1716 (Feb 10, 2012)

The list works and the dates work, but the text boxes have never worked.


----------



## OBP (Mar 8, 2005)

I think I would probably need the database to get it working for you, I think it would take too long trying to figure out what is happening otherwise.


----------



## krs1716 (Feb 10, 2012)

Okay. I will send you what I have.


----------



## krs1716 (Feb 10, 2012)

I sent an e-mail to you that contains the event search form. While the majority of the database will no longer be used, per IT's and staff's decision (I was not included in the discussion), I still need this event search to work. So, whatever suggestions you can offer will be greatly appreciated.

Thanks!


----------



## OBP (Mar 8, 2005)

OK, I will take a look and get back to you.
Users really don't like new ways to do the job do they?


----------



## krs1716 (Feb 10, 2012)

Thanks.

No, they don't. They expect miracles in seconds.


----------



## OBP (Mar 8, 2005)

I have just worked on one Text Box and it is Location, which should really use a combo to select the one that you want like the the table does, however it works using the Like function.
So you can find all classroom 103 records by just entering 103 etc or the county library by entering library.


----------



## krs1716 (Feb 10, 2012)

That will be great. I actually realized today that I had never added a Location table with the combo box connected to it. I have since added the table and altered the combo box's source. So, I will be sure to do that as a combo box or a list box. However, the coding will be great for Contact Name and Title.


----------



## krs1716 (Feb 10, 2012)

I implemented the code, and it works great! Thank you so much for the help. I have one last little favor to ask .

If I send you the new diluted version of the database, can you look at it and tell me if you can think of any reason why I should not delete the _Employer Survey Data_ table and the _EmployerSurveyID_ field from the _Employer Data_ table?


----------



## OBP (Mar 8, 2005)

OK, but you should be able to tell by the data that is to be used, you should really have more idea than me.


----------



## krs1716 (Feb 10, 2012)

Thank you!

Do you know of a reason why a combo box would open the choices on the side of a form instead of directly under the combo box's location?


----------



## OBP (Mar 8, 2005)

Can you provide a screenprint, I can't envisage it.


----------



## krs1716 (Feb 10, 2012)

I wish I could, but every time I change the names (for confidentiality's sake), the problem goes away. Will a combo box open differently if all of the fields don't fit in the size I have the combo box? I ask this because I noticed that if I make the combo box extremely large, the problem ceases.


----------



## OBP (Mar 8, 2005)

Yes that is probably the problem, or it is too close to the edge of the Form, it can be the right edge or the bottom, which will make it go above the combo.


----------



## krs1716 (Feb 10, 2012)

Okay. That helped. The form is fine now.


----------



## krs1716 (Feb 10, 2012)

If a table is used as a lookup for another table, does there need to be a relationship between those two tables?

For example, I have the city, state and county for customers looked up in corresponding tables. However, I have a one-to-one relationship also. I don't know if that is correct. Also, for events, I have Event Type and Event Locations tables connected to the Events table via one-to-many relationships (no relational integrity upheld), and I don't know if that is correct, either. Database relationships are still a little confusing to me.


----------



## OBP (Mar 8, 2005)

I don't think you should have a 1 to 1 relationship, it means that the records are locked together and the data might just as well be in the main table. So it should probably be a 1 to many with the one on the City side.
I think the event relationships are correct, although to have a 1 to many you must have Referential Integrity (but Not cascading data).


----------



## krs1716 (Feb 10, 2012)

Well, I must not have been paying attention very well. The city has a one to many relationship (no referential integrity enforced), but the state and county have indeterminate relationships (I am now extremely concerned). I thought that I should not have referential integrity because then if one is deleted in the General Information table, then it would delete the corresponding information in the connected table?

P.S. Did you mean that you cannot create a one-to-many relationship without having referential integrity or that you should not create one that way?


----------



## OBP (Mar 8, 2005)

It does not actually become a 1 to many table until Referential Integrity is enforced, because you can enter any value that you like in the table on the Many side if it has not been enforced.
Cascade Deletions only work the other way, ie the record is deleted from the 1 side of the relationship.
But you do not have to click the Cascade Deletions to get referential integrity just the first check box.


----------



## krs1716 (Feb 10, 2012)

Okay. I think that I understand. What can I do about the indeterminate relationships?


----------



## krs1716 (Feb 10, 2012)

Do I need relationships between City and General Information, State and General Information, and County and General Information if they are just used for lookup values?


----------



## OBP (Mar 8, 2005)

Not if you use Combos with the "Limit to List" set to Yes.
because users can't use anything other than those items in the list.


----------



## krs1716 (Feb 10, 2012)

Should I have the Not In List Event set up for those fields?


----------



## OBP (Mar 8, 2005)

I would otherwise a user could just type any old data in to the combo and it would try to accept it without it being added to the sub table or the combo list.
But if you want the users to be able to add items directly in to the sub Table using the Combo, you have to set up VBA code in the "Not In List" event procedure.


----------



## krs1716 (Feb 10, 2012)

I think I have all that up and working now. Thank you, thank you, thank you!!


----------



## OBP (Mar 8, 2005)

Do you think you will be working Access again or have you had enough of tearing your hair out?


----------



## krs1716 (Feb 10, 2012)

I honestly don't know. The ironic part is that I have about 4 databases that I manage at home, but none have given me the headaches that working on this one created. I think that with a break, I would consider doing it again. I also think that if I were to do it again, I probably would be better at it because, knowing what I know now, my knowledge was very limited when I started out. I still have a while to go before I would be completely comfortable, but I feel I have come a long way. I actually know what most of the terminology means now .

So...long answer short: I don't know !


----------



## OBP (Mar 8, 2005)

Well you know where to come if you do.


----------



## krs1716 (Feb 10, 2012)

Definitely. You have been so incredibly helpful. I am beyond grateful.
THANK YOU!


----------

