Type Missmatch error

[Greyowlsl]

Hi,

The code below is pretty self explanatory, but i cant seem to get it to work, learning everything from access help so...

Private Sub Form_Load()
Dim W_C As Integer
Dim W_C2 As Currency

If Me.WARRANTY_RETURNED Then
Me.warrantycontrol = "No warranty card supplied"
    ElseIf Me.WARRANTY_RETURNED > "01-Jan-09" Then
    W_C = 5
    Else
    W_C = 3
End If

    W_C2 = DateDiff("yyyy", "Date recieved", (DateAdd("y", W_C, Me.WARRANTY_RETURNED)))

If W_C2 >= W_C Then
    Me.warrantycontrol = "Yes"
    Else: Me.warrantycontrol = "No"
End If
End Sub

It is the datediff line which is yellow highlighted as the error.

Thanks for your time,
Leon

 

[pbaldy]

The second argument is supposed to be a date, not a string. If that's a field name, it needs to be bracketed, not in quotes.

 

[Greyowlsl]

Unfortunately i now have a new problem, there is now an error saying 'Invalid use of null' on that datediff line. This was after i changed

If Me.WARRANTY_RETURNED Then
Me.warrantycontrol = "No warranty card supplied"

to

If Me.WARRANTY_RETURNED = Null Then
Me.warrantycontrol = "No warranty card supplied"

, when i change it back the error still comes up.

 

[missinglinq]

If Me.WARRANTY_RETURNED = Null Then

in VBA syntax would be

If IsNull(Me.WARRANTY_RETURNED) Then

Linq ;0)>

 

[Greyowlsl]

Thanks missinglinq,

Im sure if i did that at the start it would have worked, before i started messing with the null command. The error i have is "invalid use of null" on this line of code

W_C2 = DateDiff("yyyy", [Date recieved], (DateAdd("y", W_C, Me.WARRANTY_RETURNED)))

. If i get rid of all the null commands i still get this error message.

,Leon

 

[pbaldy]

Is it possible one of the date fields contains a Null?

 

[missinglinq]

The error i have is "invalid use of null" on this line of code

W_C2 = DateDiff("yyyy", [Date recieved], (DateAdd("y", W_C, Me.WARRANTY_RETURNED)))

.

To be honest, I'm having a hard time following

DateAdd("y", W_C, Me.WARRANTY_RETURNED)

The "y" Interval argument means 'day of the year' and I've never seen this used in this context before. I assume that WARRANTY_RETURNED is defined as a Date/Time Field. Could this field be Null?

Also, since "Date recieved" is a Field and has a Space in its name, you have to enclose it in Brackets, like [Date recieved].

Finally, are all of the names of Fields spelled correctly? For instance, recieved in Date recieved is misspelled and should be received. IS the name of this Field actually misspelled?

Linq ;0)>

 

[Greyowlsl]

Is it possible one of the date fields contains a Null?

Only 'me.WARRANTY_RETURNED', but it is linked to a table which has a record, just a blank field under WARRANTY_RETURNED, because that data has not yet been entered. 'Date Recieved' (i know its spelt wrong) has a date in it.

 

[Greyowlsl]

To be honest, I'm having a hard time following

DateAdd("y", W_C, Me.WARRANTY_RETURNED)

The "y" Interval argument means 'day of the year' and I've never seen this used in this context before. I assume that WARRANTY_RETURNED is defined as a Date/Time Field. Could this field be Null?

Also, since "Date recieved" is a Field and has a Space in its name, you have to enclose it in Brackets, like [Date recieved].

Finally, are all of the names of Fields spelled correctly? For instance, recieved in Date recieved is misspelled and should be received. IS the name of this Field actually misspelled?

Linq ;0)>

Hi Linq,

'Date recieved' has been spelt wrong in the database so that isn't the problem. You were right with the 'y', it should be 'yyyy'. My new code looks like this but it still receives the same error on that date diff line.

Private Sub Form_Load()
Dim W_C As Integer
Dim W_C2 As Currency

If IsNull(Me.WARRANTY_RETURNED) Then
Me.warrantycontrol = "No card"
    ElseIf Me.WARRANTY_RETURNED > "01-Jan-09" Then
    W_C = 5
    Else
    W_C = 3
End If

    W_C2 = DateDiff("yyyy", [Date recieved], (DateAdd("yyyy", W_C, Me.WARRANTY_RETURNED)))

If W_C2 >= W_C Then
    Me.warrantycontrol = W_C2
    Else: Me.warrantycontrol = W_C2
End If

If Me.[PRODUCT ID] = "PVE1200" Or Me.[PRODUCT ID] = "PVE2500" Then
Else: Me.warrantycontrol = "Non PVE"
End If

End Sub

Thanks again for you time and help,
Leon

 

[missinglinq]

Only 'me.WARRANTY_RETURNED', but it is linked to a table which has a record, just a blank field under WARRANTY_RETURNED, because that data has not yet been entered.

Since you're using

DateAdd("y", W_C, Me.WARRANTY_RETURNED)

as a Date in the last argument for your DateDiff() function, and since adding anything to a Null gives you a Null, if Me.WARRANTY_RETURNED is Null (hasn't had data entered into it) you'd be using a Null where you need to have a Date Field in your DateDiff() function. This could be causing your error.

Linq ;0)>

 

[Greyowlsl]

Thanks Linq!

That makes total sence. All i had to do was add an if statement to it, because it should only be running the datediff line if the WARRANTY_RETURNED is NOT null. Now it works!

If IsNull(Me.WARRANTY_RETURNED) Then
Else: W_C2 = DateDiff("yyyy", [Date recieved], (DateAdd("yyyy", W_C, Me.WARRANTY_RETURNED)))
End If

.

Thanks so much.
Leon

 

[missinglinq]

Glad we could help you, Leon!

Good luck with your project!

Linq ;0)>