Invalid use of Null

[Alain]

Hi,
I am getting this error 94 in my Dmax but cannot understand why, If I remove
the criteria, everything is working well. The strPa value changes on regular
basis but for my testing I assign it a perm value and stil lget the same
error

strPa = "BE"

'recherche du > num
num = DMax("Right([ReferenceNo],3)", "tblProperty", "Mid([ReferenceNo],2,2)=
'& strPa &'")


Can anyone explain me why I get the error 94

Thanks

 

[Alain]

Sorry found the solution which I do not understand because I use that
solotion to begin with !!!!

num = DMax("Right([ReferenceNo],3)", "tblProperty", "Mid([ReferenceNo],2,2)
= '"& strPa &"'")

 

[Graham Mandeno]

Hi Alain

Your code is not substituting the value of strPa into the criteria string.
Instead, it is looking for records where the second and third characters
of [ReferenceNo] are /& strPa &/.
This can't return any records because a two-character string can't equal
a nine-character string!

You need to terminate the string after the opening single-quote and start it
again before the closing single-quote:

"Mid([ReferenceNo],2,2)='" & strPa & "'"