VBA Script to search fields and display contents

Jwill1014 · May 10th, 2005, 07:36 AM

I need to create a report that the user can enter a company name and it will show all the part numbers and then job number pertaining to the company.

Sounds pretty simple, but the problem is the job number record has parts in the part field, but parts in 5 other custom text boxes. (This is the way it was setup before me.)

So I am thinking I need to do this:

Enter company name

Display Part number
Job # Description
If Text1 is not null Display
Display Job# Description
If Text2 is not null Display
Display Job# Description
If Text3 is not null
Display Job# Description
If Text4 is not null Display
Display Job# Description
If Text5 is not null Display
Display Job# Description

Can anyone help me get started with some code on how to do this?

Thanks,

Bill

rjweers · May 15th, 2005, 10:19 AM

How about just fixing the database? :D

I would focus on creating the query that sets up the data for the report.

I'm not clear about what you want to accomplish from your psuedo code. But I think you're trying to display parts if they exist and finally display the job number. So it would be something like:

Code:

part, Job
part, part, job
part, part, part, job
part, part, job

I don't know why it can't be:

part, empty, empty, empty, empty, job

But if you can't have empty text boxes in the report, try this for each of your fields in the query:

Text#: iif(isnull([Text#]),[Job#],[Text#])

Where Text# is the various text fields. In fact, you can put

Code:

=Iif(isnull([Text#]),[Job#],[Text#])

as the Control Source for the fields in the report instead of building the query. I tend to start with the query because once that is correct I can reformat the report without worrying about getting all of the data right. Plus the query can be used for other purposes (e.g. fixing the database :)).

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Jwill1014 · May 16th, 2005, 01:03 PM

I am not explaining it correctly. We have an application which keeps track of all our jobs. The reporting capabilities were very limited. So my predecessor created an access database and then linked the tables, so he could create custom reports.

The way the job tracking app works is a user would enter a job number into the application. Then they would enter a part number into a field called Part#.

This is great. I can query the part number and see all the job numbers.

The problem is we are not using this database as intended. Besides the part# filed, there are custom files in the same table that are Text1, Text2, Text3, Text4, and text5.
Users enter part number in those fields too.

The application intended that for every part# there is a job number. We are trying to have multiple part numbers to 1 job number.

So the report I need to create is:

Customer
Part number
Job number

My problem is how to I group by text1-5 and keep the report consistent?

Can this even be accomplished?

rjweers · May 16th, 2005, 10:32 PM

I get the impression that you want us to say you can't do it so you'll have an excuse to get people to use the database the way it was designed and/or redesign the database. :) I say that because the solution I proposed is somewhat applicable.

Ultimately the only thing wrong with the solution I proposed was that I didn't notice that Job # Description was repeating the same information over and over. Odd that you would need to do that? I mean, why not just invert the relationship....

  Customer
     Job Number
        Part Number
        Part Number

But whatever...

You with have to have five fields for the Job Numbers on your report. Either build the query to have values for the five job number fields, or set the Control Source using the "Iif" (Immediate If). The syntax will be either:

Code:

JobNo#: Iif(isnull([Text#]),null,[JobNumber])

in the query, or

Code:

=Iif(isnull([Text#]),null,[JobNumber])

in the Control Source.

If you want the report not to use space for empty values, set the "Can Shrink" property to Yes for the Text# fields and the JobNo# fields. This will hide the field if nothing is present.

So, unfortunately, despite the fact that the database should be redesigned as it should be instead of having to come up with a kludge solution, you can still work around the way the database is being used. Sorry about that. But there are ways that you can accomplish almost anything in Access. It just takes time to figure it out.

Jwill1014 · May 18th, 2005, 02:27 PM

It does not work.

If I run the query, this is what you will see

Customer Part# Text1 Text2 Text3 Text4 Text5 Job Description

Mycustomer 848 848B 848C 848D 111 This is the job
Mycustomer 848 222 Test

Now I can group by customer, then part number which will give me

Mycustomer

          Part # 848
        Job# 111 This is the job
                Job# 222 Test

But what I canât figure out is how to make it look like the following

Mycustomer

           Part # 848
        Job# 111 This is the job
                Job# 222 Test

           Part# 848B
                Job#111 This is the job

           Part# 848C
            Job#111 This is the job

           Part# 848D
            Job# 111 This is the job

I tried creating 5 individual queries and them added them to a master query, but itâ¦lol crashed.

rjweers · May 25th, 2005, 07:33 AM

Now I have a better idea of what you're looking for.

Use a Union like this:

Code:

Select customer, partnumber, jobnumber from table
union
Select customer, text1, jobnumber from table where text1 is not null
union
...
union
Select customer, text5, jobnumber from table where text5 is not null
Order by Customer, Partnumber, JobNumber

When you see the results of that Union I think you'll understand how to build the report.

rjweers · May 25th, 2005, 07:44 AM

BTW, sorry for the delayed response. Big week for me last week. I got hired at Microsoft and it was my birthday!

Jwill1014 · June 2nd, 2005, 07:22 AM

IT WORKED!!!!!!

Thank you so much for your help!!! I have heard of a union query, never needed to create one before.

Happy Birthday!!
Congratulations on your new job!

rjweers · June 2nd, 2005, 07:33 AM

Congrats to you too!

And thank you.