SQL in Report

[Dennis]

I am trying to write an event Procedure that when I open
my report, the report looks at a field in the select query
results and if the desired results are produced, the
reports puts an X in a nonbound field in the report. I
have been working on what I think is close but it is not
yet working. Below is what I have:

Private Sub Report_Open(Cancel As Integer)

Me!AM = Null
Me!PM = Null
Me!BH = Null
' Supposed to set unbound report text box to Null

If [SIG1] = "? QAM" Then
Me!AM = "X"

'Supposed to put an X in unbound Text Box named "AM".

End If

End Sub

? represents a wildcard that could be a 1,2, or 3. All "1
QAM", "2 QAM" and "3 QAM" values need an X for the return.
I have many codes that are similar to the above with only
leading numbers and a space that need an X returned in an
unbound Text Box. How can I group them in least amount of
statements?

thanks,

Dennis

 

[Gary Walter]

I am trying to write an event Procedure that when I open
my report, the report looks at a field in the select query
results and if the desired results are produced, the
reports puts an X in a nonbound field in the report. I
have been working on what I think is close but it is not
yet working. Below is what I have:

Private Sub Report_Open(Cancel As Integer)

Me!AM = Null
Me!PM = Null
Me!BH = Null
' Supposed to set unbound report text box to Null

If [SIG1] = "? QAM" Then
Me!AM = "X"

'Supposed to put an X in unbound Text Box named "AM".

End If

End Sub

? represents a wildcard that could be a 1,2, or 3. All "1
QAM", "2 QAM" and "3 QAM" values need an X for the return.
I have many codes that are similar to the above with only
leading numbers and a space that need an X returned in an
unbound Text Box. How can I group them in least amount of
statements?

Hi Dennis,

Just some suggestions, I may be completely off base...

You will have more than one record in your report, right?

Are the "many codes" all based on field [SIG1]?

Do you have more than the three unbound textboxes?

Are these codes a discrete set?

If you will have more than one record, all "codes" are
in [SIG1], only 3 unbound textboxes, and the codes
are a discrete set...

I might set up a lookup table

tblSIG
SigID (pk) SigCode txAM txPM txBH
1 1 QAM X
2 2 QAM X
3 3 QAM X
.......

Then, just left join the table or query that was
the source for your report to tblSIG on
[SIG1] = SigCode
and return txAM, txPM, txBH with
fields from your original source in a new
query that you use as source for report.
The textboxes will now be bound to your
new fields in your new query.

Otherwise, you probably will want to use
IIF stmts in the recordsource for each unbound
textbox

say...for txtAM

=IIF(Right(SPACE(3) & [SIG1],3) = "QAM","X","")

Or...you can move these IIF's to new fields in a query
based on your original report source that will serve as
your report's new recordsource.

Or...you might want to rethink the structure of your
tables, i.e., it would appear you are storing 2 pieces
of data in [SIG1].

But you know your data better than I do.

Well...just some suggestions....

Gary Walter

 

[Dennis]

Thanks for the great ideas Gary.


Dennis

-----Original Message-----

I am trying to write an event Procedure that when I open
my report, the report looks at a field in the select query
results and if the desired results are produced, the
reports puts an X in a nonbound field in the report. I
have been working on what I think is close but it is not
yet working. Below is what I have:

Private Sub Report_Open(Cancel As Integer)

Me!AM = Null
Me!PM = Null
Me!BH = Null
' Supposed to set unbound report text box to Null

If [SIG1] = "? QAM" Then
Me!AM = "X"

'Supposed to put an X in unbound Text Box named "AM".

End If

End Sub

? represents a wildcard that could be a 1,2, or 3. All "1
QAM", "2 QAM" and "3 QAM" values need an X for the return.
I have many codes that are similar to the above with only
leading numbers and a space that need an X returned in an
unbound Text Box. How can I group them in least amount of
statements?

Hi Dennis,

Just some suggestions, I may be completely off base...

You will have more than one record in your report, right?

Are the "many codes" all based on field [SIG1]?

Do you have more than the three unbound textboxes?

Are these codes a discrete set?

If you will have more than one record, all "codes" are
in [SIG1], only 3 unbound textboxes, and the codes
are a discrete set...

I might set up a lookup table

tblSIG
SigID (pk) SigCode txAM txPM txBH
1 1 QAM X
2 2 QAM X
3 3 QAM X
.......

Then, just left join the table or query that was
the source for your report to tblSIG on
[SIG1] = SigCode
and return txAM, txPM, txBH with
fields from your original source in a new
query that you use as source for report.
The textboxes will now be bound to your
new fields in your new query.

Otherwise, you probably will want to use
IIF stmts in the recordsource for each unbound
textbox

say...for txtAM

=IIF(Right(SPACE(3) & [SIG1],3) = "QAM","X","")

Or...you can move these IIF's to new fields in a query
based on your original report source that will serve as
your report's new recordsource.

Or...you might want to rethink the structure of your
tables, i.e., it would appear you are storing 2 pieces
of data in [SIG1].

But you know your data better than I do.

Well...just some suggestions....

Gary Walter







.