How to limit displayed data by another field's criteria

[kjamieson]

This is likely a newbie question, but I need an expression that will only display a string from a field if another field in the same row generated by the query has a certain numeris value.

I'm using Access 97 BTW.

Thanks.

 

[Trendsetter]

Hi,
It's difficult to know how to word this expression because there can be so many variables that have not been mentioned.

- we don't know where the field will be displayed
- we don't know how the query output is displayed
etc.. etc..
If Me.txtField = "26" Then
Me.AnotherField.Visible = False
Else
Me.AnotherField.visible = true
End if

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[evilmousse]

textbox2.controlsource =
iif([textbox1] = X,"",[textbox2'sIntendedDatasource])

 

[kjamieson]

Hmm, might need more info on this.

The longer version is below.

I am trying to generate a report that displays a staffing schedule. I want it to look like a crosstab query, but cannot get that to work as I need to return string data (employee names and hours) rather than numeric values.

I also cannot use a report with unrelated comumns.

Across the top, I'd like to have 14 column headers for each day in a 2-week period. The rows would be sorted, first by a filed labeled ShiftName, then by a field labeled LocationName. I then need 4 fields in every column for LastName, FirstName, StartTime, and EndTime. Not every day will have the same shifts and/or Locations, which is why I need them related and not just separate columns.

I can get all this to display top to bottom. In other words, the columns of days display under each other but not accros the page. My idea is to use an expression in the Control Source area of the field in the report that will only display data if it is frmo a certain day. Something like =[LastName]=IIf([Day]=1,[LastName],"&quot (derived from evilmousse's post), which would hopefully display all LastNames where the Day field is 1.

I do not know VBA (haven't gotten to it yet, I've only done Access for 3 weeks now), but I know that is another possiblility.

Thanks.

Kevin

 

[evilmousse]

use this expression in last name's control source:
=IIf([Day]=1,[LastName],"&quot


vba, if you investigate it, is not all that different from typing expressions into the properties window.

the vba equivalent of this would roughly be

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
LastName.ControlSource = IIf([Day] = 1, [LastName], "&quot
End Sub

both ways do the exact same thing.
don't let the detail_format scare you either,
you just click the detail, go to the events
tab on the properties window, click on format
and choose to make code, and the basis of the
function is premade for you.
most of the lines in the properties window can
be taken to be the second half of an assignment.
if it's simple enough (ie, a direct bind) it can skip the equals sign.
eg:
a textbox's control source setting can be
[data1]
or
=[data1]
same thing.

but when it gets more complicated than that, the = is neccessary. eg:
= "the data is " & [data1]