Yes/No problems! Please help!!!

[TehSparrow via AccessMonster.com]

Is there any way to set up a report so that only records with a ticked field
will be displayed on the report?

I was thinking something like this in the query, but i dont know the correct
structure:

IIf([Customer used]=-1,"Show value","Hide value")

Any help is greatly needed, as my boss wants this ASAP!!!!

Sparrow

 

[Wayne Morgan]

To limit it to only records where the field in question is True, you could
do that in the WHERE clause of the query or in the Filter property of the
report. In the query, it would look like:

WHERE [Customer Used] = True

(-1 will also work instead of True)

This will remove the entire record if the value isn't True. Is that what you
want or are you just trying to hide certain fields in the record if the
value isn't true? If the latter, then you could use the Format event of the
report section to do this.

Example:
If Not Me.[Customer Used] Then
Me.txtMyTextbox.Visible = False
Me.txtMyTextbox2.Visible = False
Else
Me.txtMyTextbox.Visible = True
Me.txtMyTextbox2.Visible = True
End If

 

[TehSparrow via AccessMonster.com]

Basically, i want the query to run, and two message boxes come up. one asking
which county is required ( i know how that one works)

And second i want to read "please enter supplier's name" and when the name is
entered, only records that have that suppliers name ticked are shown,

i.e. "please enter suppliers name"

I enter Proctors.

I want the query to only display the values where proctors have had the boxes
ticked.

im working with this idea at the moment, which would be put under the proctrs
checkbox field in the query:

IIf([Supplier Name]=“Proctors”,[Proctors],IsNull)

Im Trying to do access when i specialise in excel, its a difficult transition!
!!

thanks for help, any more greatly needed!

Sparrow

 

[Rick B]

The problem is that you are building a spreadsheet in Access. Access is a
relational database product, not a spreadsheet. You have to change your way
of thinking.

If you have a column called "Proctors" then your data structure is flawed.
You don't enter data in a field name. You have a one-to-many relationship.
You need a second table with the record's key field (vendornumber,
customernumber, productnumber, etc.) and a "Supplier number (or name). If
one product can be obtained from four suppliers, then it would have four
records in this table. If it can only be obtained from one supplier, then
it would only have one record in this table.

Normalize your data, then we can help you get what you need.


TblParts
PartNumber
PartDescription
PartRetail
PartBinLocation
etc.


TblSuppliers
SupplierNumber
SupplierName
SupplierAddress1
etc.

TblPartsSuppliers
PartNumber
SupplierNumber



In the above, each part would have one record in TblParts, each Supplier
would have one record in TblSuppliers. Each part would have one or MORE
records in TblPartsSuppliers.

 

[Wayne Morgan]

I can't tell by what you're written what your table structure is like, but
Rick has made a good comment for you to follow if the table structure is as
he suspects. If so, that needs to be fixed first.

Once that is done, if you're wanting to filter of 2 fields, you put criteria
in the query for each of the 2 fields. You want the first one to be the
supplier's name. If you aren't using a popup form to supply this, then you
would need a parameter in the query. You would then check the value of the
Yes/No field also. Again, this will use the WHERE clause of the query. The
WHERE clause will show up if you look at the query in SQL view. If you're in
the design grid, it would be what you put in the Criteria row under the
associated field.

Example:
WHERE SupplierName Like [Please enter a supplier's name] And YesNoField =
True

Yes, Access is different that Excel. As Rick mentioned, you don't just put
everything in a single table as if it is a spreadsheet. While this may be a
little harder to learn, it is also what give a database its power. Please
look at the sample databases, such as Northwind, that come with Access. They
have many examples of how to set up a database.

 

[John Spencer]

If you can't get out of the spreadsheet structure then you will will have to
do something very, very messy. You will need to build criteria like the
following.

Field: Proctors
Criteria: True and [Enter Supplier] = "Proctors"

Then for the next supplier move down one row on the criteria
Field: Reggies
Criteria: <leave blank>
Criteria(2): True and [Enter Supplier] = "Reggies"

And so on, moving down one criteria row each time. AND if you ever add
another supplier, you have to modify this query and any reports etc. Even
worse, eventually this will be so cumbersome/complex that the query won't
even run.

As you can see you are much better off changing your structure as others
have already pointed out.