If Then Else struggle

[kupe]

The task:

If there's nothing in textbox 2 (SecondDate) then Calculate the days since the date (FirstDate) in textbox1.

But if there is a date in textbox 2 (SecondDate), then calculate the days since that date.

My code is not right, I can see that (and so can Access!). Be grateful for guidance.

If IsNull([SecondDate]) Then
MsgBox "A message"
Me.txtBox4 = Date - ([Me.FirstDate]) & " days"
Else
MsgBox "Another message"
Me.txtBox4 = Date - ([Me.SecondDate]) & " days"
End If

 

[Mile-O]

It's fine other than the unnecessary square brackets which were in an incorrect place:

If IsNull(Me.SecondDate) Then
    MsgBox "A message"
    Me.txtBox4 = (Date - Me.FirstDate) & " day(s)"
Else
    MsgBox "Another message"
    Me.txtBox4 = (Date - Me.SecondDate) & " day(s)"
End If

 

[kupe]

Thanks very much, MoP - but it doesn't want to oblige. Nothing at all shows in txtBox4.

Could it be because I am using "Private Sub txtBox4_Change()"?
(I mean, not even the message box appears.)

 

[Mile-O]

How about this, instead, in the ControlSource of txtBox4?

=IIf(IsNull([SecondDate]), DateDiff("d", [FirstDate], Date()), DateDiff("d", [SecondDate], Date())) & " day(s)"

 

[kupe]

Sadly, no. It chops up your statement, in fact. Is this because it is bound to the field? Only it has to be to record the number of days.

 

[Mile-O]

Okay, so why are you wanting to store a calculated value?

 

[kupe]

For a query for a report. These day totals are the crucial part of the report ...

 

[kupe]

You see, the User wants to see the total on the form, going from record to record. Every week, I need to print a report based on the total of days.

 

[Mile-O]

Use the AfterUpdate event.

 

[KenHigg]

If IsNull(me.SecondDate) Then
MsgBox "A message"
Me.txtBox4 = Date() - (Me.FirstDate) & " days"
Else
MsgBox "Another message"
Me.txtBox4 = Date() - (Me.SecondDate) & " days"
End If


???
ken

 

[kupe]

Another dynamite IIf job from you does the trick. (The calculation doesn't want to work from the code, no matter what I do.)

So I'll use your IIf on the form, and on the query that feeds the report.

Hats off to you, MoP. Wish I could aford you, wish I had half your knowledge. (Thinking of your history for a moment, my father gave me Wind in the Willows, not being as wise as yours.) Many thanks

 

[philljp390]

I just did up a form to test this, and this is what i got which worked for me:

Private Sub CmdCalc_Click()

Dim NowDate As Date

NowDate = Date

If IsNull(Me.txtdate2) Then
    MsgBox "A message"
    Me.txtResult = NowDate - Me.txtdate1 & " day(s)"
Else
    MsgBox "Another message"
    Me.txtResult = NowDate - Me.txtdate2 & " day(s)"
End If

End Sub

Make sure txtDate1 and txtDate2 are formatted as Short Date (Right click the textbox, click properties, on Format click short date).

Hope this helps

 

[kupe]

PhilpJP390, it looks promising, but maybe the hot afternoon is getting to the program as well because on your line

Me.txtResult = NowDate - Me.txtdate1 & " day(s)"

I receive this error message for Me.txtdate1 (which now has my field's name)

"Method or data member not found"

I wonder if this says something to you, please?

 

[KenHigg]

Me.txtResult = (NowDate - Me.txtdate1) & " day(s)"


???
ken

 

[kupe]

If only, but unfortunately not. This is so crazy. I've spent, well, quite a long time on what should be so simple. Even so, any other ideas would be welcome. Many thanks.

 

[KenHigg]

Can you put together enough of this in a seperate db to post?

ken

 

[philljp390]

I didnt get any errors with the code i posted, im using Access 2000.

It would be helpful if you could post the db here.

 

[Rich]

Me.txtResult = NowDate - Me.txtdate1 & " day(s)"

I receive this error message for Me.txtdate1 (which now has my field's name)

"Method or data member not found"

I wonder if this says something to you, please?

you need the name of the control as it appears on the property sheet and Not the name of the field

 

[philljp390]

Yes, make sure u right click on the text box, go to properties and make sure the Name: is the same as whats in the code

 

[kupe]

Your help is appreciated. It doesn't want to respond to your good advice. Here's a very basic stripped-down model, hoping that you might spot the error. TIA