Help with date diff

[badboy2005]

Hello, am trying to make a code, that will set a value in the specific field only if the current value is more than 0, but i cant get it to work... here's the code:

If Me![DaysLoaned] <= 0 Then
Me![DaysLoaned] = Int(DateDiff("d", Me![DueReturnDate], Date))
Else
Me![DaysLoaned] = "Doesnt Apply"
End If



It always returns the "Doesnt Apply"

 

[Mile-O]

If Me![DaysLoaned] <= 0 Then
Me![DaysLoaned] = Int(DateDiff("d", Me![DueReturnDate], Date))
Else
Me![DaysLoaned] = "Doesnt Apply"
End If

• Get out of the habit of using ! when referring to controls. Use . instead. ie. Me.DaysLoaned;
• Get into the habit of using prefixes for controls i.e. txtDaysLoaned for a textbox;
• What sort of data type is the DaysLoaned field? I'm guessing Text as you are either assigning a numeric value or a text value to it. I'd change it to a Numeric field type and remove the "Doesn't Apply" text. If the field is blank then you can assume that it does not apply. Saves space too.
• Is it possible that the field currently equals Doesn't Apply so that when you do your <= evaluation the text doesn't matter and it drops through to Doesn't Apply

 

[Brianwarnock]

I may have misunderstood but shouldn't it be

If Me![DaysLoaned] <= 0 Then
Me![DaysLoaned] = "Doesnt Apply"
Else
Me![DaysLoaned] = Int(DateDiff("d", Me![DueReturnDate], Date))
End If

Brian

 

[badboy2005]

ok.. followed your steps, still quite doesnt work ( nothing appears now ).

1) Its a text box (manually created in design view so no field in a table)
2) Its set to "unbound"
3) i think brian was right about the syntax.. but still doesnt work
3) here's the modified code:

If Me.DaysLoaned >= "0" Then
Me.DaysLoaned = Int(DateDiff("d", Me.DueReturnDate, Date))
End If

thnx for the help again.. much appreciated

 

[Rich]

If days loaned is an unbound field, where's the value you're testing for coming from?

 

[badboy2005]

lol .. oh sh*t .. good point

 

[badboy2005]

still doesnt work

i get #Name? now

 

[Brianwarnock]

The 1st point is that the 1st line of your code is incorrect it should be

If Me.DaysLoaned > 0 Then

However I am having difficulty trying to understand what you are doing, I think that you are trying to put a calculated field into a Table, which you shouldn't and don't need to do, but I could be way off the mark here.

Brian

 

[badboy2005]

ok.. lemme try explain again..

1) Am trying to find the difference between one date and the today's date (both fields found in a table)
2) I wanna make this written down in the text box of the form as an integer (field is not in a table, but custom created in the form)
3) If this value is negative (i.e today's date is less than "that" date) then show 0 or nothing instead of the negative value

 

[Brianwarnock]

Wont that be
If DateDiff("d", Me.DueReturnDate, Date()) > 0 Then
Me.DaysLoaned = DateDiff("d", Me.DueReturnDate, Date())
Else Me.DaysLoaned = 0

Brian

 

[Brianwarnock]

Actually that was rubbish, assuming your form is based on the table or query then
If DateDiff("d", Date,DueReturnDate) > 0 Then
Me.DaysLoaned = DateDiff("d", Date,DueReturnDate)
Else Me.DaysLoaned = 0

should give you what you want.

Brian

 

[badboy2005]

still not working

i was wondering.. shall the text box be left unbound? or shall i enter a value?

 

[Brianwarnock]

Im a part timer who doesn't work Thursday & Friday but after a day in the garden took a look at the site hence the delay in replying not that I'm sure that I can help further as I must be on a different wavelength.
What I've done is create a small table of 3 fields Id datereturn bookname
A form based on this table but with the addition of an unbound text box called daysleft, then added the code below to the forms on current event, you can see that I've improved the code but yesterdays approach works fine, as I navigate through the records the text box shows 0 or days left as expected.

Private Sub Form_Current()
Me.daysleft = DateDiff("d", Date, Me.datereturn)
If Me.daysleft < 1 Then Me.daysleft = 0
End Sub

Brian

 

[modest]

You're using the wrong function to get today's date. Instead of Date(), which formats, use Now() to get today's date

Dim lngDaysDiff As Long
lngDaysDiff = DateDiff("d", Now(), Me.[Field Name].Value)


I've attached a sample db.
Only one form, one buttong, and two textboxes.
No tables.
Push Alt+F11 to look at the VBA.

 

[Rich]

You're using the wrong function to get today's date. Instead of Date(), which formats, use Now()

.

No he isn't, Date is the correct one when using date comparisons

 

[modest]

You're right, I got screwed up because it wasn't working for me. The problem is that my project was called Date and Access was getting confused. Instead, I had to use the long call.

Dim blah As Long
blah = DateDiff("d", DateTime.Date, Me.[Field Name].Value)

Select Case blah
    Case Is > 0
        Me.[Set Field].Value = blah
    case Else
        Me.[Set Field].Value = "Doesn't Apply"
End Select

 

[badboy2005]

You're right, I got screwed up because it wasn't working for me. The problem is that my project was called Date and Access was getting confused. Instead, I had to use the long call.

Dim blah As Long
blah = DateDiff("d", DateTime.Date, Me.[Field Name].Value)

Select Case blah
    Case Is > 0
        Me.[Set Field].Value = blah
    case Else
        Me.[Set Field].Value = "Doesn't Apply"
End Select

Tried this code, but all i get is -15 for all fields and i think the -15 comes from the 1st record in the table.

Also tried Brian's code, same problem.. all have a -15 value (regardless if its more than 0 or less)

 

[badboy2005]

ok.. trying to do a workaround now which i think its working..

ive just created a "DaysLoaned" field in the table, with no value in it and i think its working now

now, am not sure if i can make calculations from the value in "DaysLoaned" but ill have a go..

thnx for all the help

if i can continue this thread.. i would like to ask whether Yes/No field values can be used in VB or if i must change my field value to 0 (no) or 1 (yes) to do what i want to

Like if lala = Yes Then
do this

 

[badboy2005]

bleah.. somethin went wrong and it aint working any more

there's an empty value

 

[Brianwarnock]

perhaps you ought to attach your DB

Brian