Leaving out blank fields

[ptrifile]

I have a report that shows "Punch In" and "Punch Out" times for a number of dates, what do i need to do to get either of the 2(punch in or punch out) to be suppressed if there is a "" value (or a nothing for that field). I hope this makes sense.....

Thanks for any help

Paul

 

[jiqjaq]

In the VBA code for the section where these fields print (e.g. Detail_Format):
(where PunchIn is the name of the field)

Me.PunchIn.Visible = (NZ(Me.PunchIn,"")<> "")

The NZ will force a "" value if that field is null. Then the visible property will be set based on that value. If it's not "", it will print; otherwise not.

 

[ptrifile]

Thanks Jigjag, however when i enter this into the onopen event of my report I get the following error when i run the report:

"Run-time error '-2147352567 (80020009)':
You entere an expression that has no value

Any ideas?


Thanks again

Paul

 

[ptrifile]

I put this code in on the "On Activate" event but it makes the "punchin" time invisible whether or not there is a time input. Any suggestions?

Paul

 

[dhookom]

ptrifile,
This doesn't really make sense since a value of "" or null doesn't show anything. Why would you want to make something invisible if it doesn't display anything?

What are you not telling us? Is there a label that must be made invisible? Do you want other controls to move up if a value is null or ""?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ptrifile]

This is a very simple "timeclock" database where people click punch in or punch out for work. So for each record that is created either the punchin or punchout field is blank, so when reporting on it i would like to suppress whichever one is blank instead of seeing the following:

Punchin 8:00am
Punchout
Punchin
Punchout 5:00pm

Punchin 8:00am
Punchout
Punchin
Punchout 5:00pm

Hope this makes sense of what im trying to do.
Thank you for your help!

Paul

 

[dhookom]

So the actual text "Punchin" and "Punchout" are labels, not text boxes?
If:
- the time values are actually Null values if empty
- the "Punchin" and "Punchout" are labels
You can remove the labels and set the control sources to:
="Punchin " + [Punchin]
="Punchout " + [Punchout]
Set the controls to Can Shrink and make sure the name of the text boxes are changed so they are not also the name of a field.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ptrifile]

Thanks Duane but I only seem to get an error when I set the controls as you suggested. thoughts?

Thanks again!

Paul

 

[dhookom]

ptrifile,
"get an error"? What error? The text boxes show something wrong or the report closes or an error dialog opens?

The closest guess I have should have been corrected by my last sentence in a previous post [blue]"make sure the name of the text boxes are changed so they are not also the name of a field"[/blue].

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ptrifile]

Thanks Duane, your solution actually worked fine it was user error! thanks again!

Paul

 

[dhookom]

Consider normalizing your table with a union query like:
SELECT IDField, "A" as YesNo
FROM tblYourTable
WHERE YesNoA = True
UNION ALL
SELECT IDField, "B"
FROM tblYourTable
WHERE YesNoB = True
UNION ALL
SELECT IDField, "C"
FROM tblYourTable
WHERE YesNoC = True
UNION ALL
..etc...;

You can then use a subreport to display only the checked boxes/fields.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]