Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access Report Coding 2

Status
Not open for further replies.
MichaelF81

MichaelF81

Programmer
Sep 20, 2005
178
US
Ok, I have a report in Access. When it runs, I want some actions to take place.

Essentially, on the report the 2 labels are text71 and text 72.

Code: 
Dim strValue1
Dim strValue2

strValue1 = Me.Text71
strValue2 = Me.Text71a

If strValue1 < strValue2
Me.Text71 = strValue2
Else
Me.Text71 = strValue1
End If

Since Text71 can be null, it errors out. I am having issues with the IsNull command. Any ideas?







"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Sort by date Sort by votes
Try this. I'm not sure what you mean by "I'm having trouble with the IsNull command." Also, I'm not sure if you like the logic that I inserted. Perhaps this suggestion will help.

Code: 
Dim strValue1
Dim strValue2

strValue1 = Me.Text71
strValue2 = Me.Text71a

If not IsNull(me.Text71) then
  If strValue1 < strValue2 
     Me.Text71 = strValue2
  Else
     Me.Text71 = strValue1
  End If
Else
      Me.Text71 = 0
End If
 
Upvote 0 Downvote
How about Nz?

[tt]strValue1 = Nz(Me.Text71,0)
strValue2 = Nz(Me.Text71a,0)[/tt]
 
Upvote 0 Downvote
@AlanJordan

Thanks, the logic is fine actually, I had played with the same code.

@Remou

What is the Nz?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
From Help:

Nz Function

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

 
Upvote 0 Downvote
None of the following work...

Code: 
Dim strValue1
Dim strValue2

strValue1 = Me.Text71
strValue2 = Me.Text71a

If not IsNull(me.Text71) then
  If strValue1 < strValue2
     Me.Text71 = strValue2
  Else
     Me.Text71 = strValue1
  End If
Else
      Me.Text71 = 0
End If

Code: 
Dim strValue1
Dim strValue2

If not IsNull(me.Text71) then
  strValue1 = Me.Text71
  strValue2 = Me.Text71a
  If strValue1 < strValue2
     Me.Text71 = strValue2
  Else
     Me.Text71 = strValue1
  End If
Else
      Me.Text71 = 0
End If

Should I try

Code: 
Dim strValue1
Dim strValue2

strValue1 = nz(Me.Text71,0)
strValue2 = nz(Me.Text71a,0)

If strValue1 not = 0 then
  Me.Text71 = strValue2
Else
  Me.Text71a = strValue1
End If




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Code: 
Dim strValue1
Dim strValue2

strValue1 = nz(Me.Text71,0)
strValue2 = nz(Me.Text71a,0)

If strValue1 not = 0 then
  Me.Text71 = strValue2
Else
  Me.Text71a = strValue1
End If

This gives me an error "You entered an expression that has no value, and sends me to the line
Code: 
strValue1 = Nz(Me.Text71, 0)






"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
What happens if you change this:
If strValue1 not = 0 then

To:
If strValue1 <> 0 then

After you have changed it, can you compile?
 
Upvote 0 Downvote
Private Sub Report_Open(Cancel As Integer)

[/code]
Dim strVal1 As String
Dim strVal2 As String

strVal1 = Nz([Text71], 0)
strVal2 = Nz([Text71a], 0)

If strVal1 <> 0 Then
If strVal1 < strVal2 Then
Me.lblCalc = strVal2
Else
Me.lblCalc = strVal1
End If
End If

End Sub
[/code]

Ok, what I did was make a new label for text71 since it is a calc field. This way it does not fight over the calculation. What I get now is the same error though (there is no way to compile this in a report via access, I tried).

It still says that "You entered an expression that has no value" For

Code: 
strVal1 = Nz(Me.Text71, 0)




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Is strVal1 the name of a control in your report? Can you compile if you comment out the line? Can you compile if you change the line to:

strXX = Nz(Me.Text71, 0)
 
Upvote 0 Downvote
strVal1 is not a value in my report, it is a variable I am declaring now.

I also tried

Code: 
Private Sub Report_Open(Cancel As Integer)

If Not IsNull([Text71]) Then
  If [Text71] < [Text71a] Then
   Me.lblCalc.Caption = Me.Text71a
  Else
   Me.lblCalc.Caption = Me.Text71
  End If
End If
  
  End Sub

same error.

It compiles fine with the above code, but it does not work...




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
You cannot use the Report Open event to assign values to controls. Try the Detail Format event.
 
Upvote 0 Downvote
Worked like a charm, thanks a lot!




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not IsNull(Me.Text71) Then
  If Me.Text71 < Me.Text71a Then
   Me.lblCalc.Caption = Me.Text71a
  Else
   Me.lblCalc = ""
  
  End If
End If
End Sub

Problem now is that when Text71 is null, it shows 30 in lblCalc... Ideas?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
nm, this

Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not IsNull(Me.Text71) Then
  If Me.Text71 < Me.Text71a Then
   Me.lblCalc.Caption = Me.Text71a
  Else
   Me.lblCalc.Caption = Me.Text71a
  End If
Else
Me.lblCalc.Caption = ""
End If
End Sub

Does not work




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
never mind, that fixed it




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Hi Michael,

I had a thought, and though your issue seems to be fixed, I thought that I'd mention it:

There are times when Access gets confused if you accept the default naming conventions for controls. As long as you don't write any VBA code, there is not much of a problem, but if you do any extensive programming, it is wise to use conventions such as txtMeaningfulName and cboMeaningful name for your controls. This prevents corruption, which almost always hits at the worst possible time. It may also prevent issues like the IsNull() function misbehaving.

Just a thought.

Alan
 
Upvote 0 Downvote
I Agree, this is not actually report, I use proper naming conventions txtDept an lblDept and what not, no Text77 and Text77a's. I gave him a bunch of crap for it too, but he is an engineer playing in the IT world, and he is doing it for a project, so I had to help, and his English is terrible so I had to struggle.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not IsNull(Me.Text71) Then
  If Me.Text71 < Me.Text71a Then
   Me.lblCalc.Caption = Me.Text71a
  Else if Me.Text71a < Me.Text71 Then
   Me.lblCalc.Caption = Me.Text71
  End If
Else
Me.lblCalc.Caption = ""
End If
End Sub

Here is what I ended up using as an FYI




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Man, these people need to make up their minds, they had the idea completely backwards. Here is my end all be all code (for now)

Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Not IsNull(Me.Text71a) Then
Dim v1, v2 As Integer
v1 = Me.Text71
v2 = Me.Text71a
  If v1 < v2 Then
   Me.lblCalc.Caption = Round(v1, 0)
  Else
   Me.lblCalc.Caption = Round(v2, 0)
  End If
Else
Me.lblCalc.Caption = ""
End If

If Not IsNull(Me.Text77a) Then
Dim v3, v4 As Integer
v3 = Me.Text77
v4 = Me.Text77a
  If v3 < v4 Then
   Me.lblCalc1.Caption = Round(v3, 0)
  Else
   Me.lblCalc1.Caption = Round(v4, 0)
  End If
Else
Me.lblCalc1.Caption = ""
End If

End Sub




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Knicks
  • Locked
  • Question
Microsoft Access VBA Coding to Excel (early binding) 1
Replies
2
Views
356
Knicks
Knicks
Knicks
  • Locked
  • Question
Microsoft Access VBA Coding to Excel Password Protect Fail
Replies
5
Views
493
combo
combo
Moss100
  • Question
Access send email attachments - problem when null or empty
Replies
7
Views
329
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Using Report Header (Form control) as Variable in Report Expression 1
Replies
11
Views
641
dhookom
dhookom
M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
329
SoftwareRT
SoftwareRT

Part and Inventory Search

Sponsor

Back
Top