Annoying problem

[rookery]

I have used the following code:

Dim dbs as Database
Dim MyRs as Recordset
Dim strCriteria as String

Set dbs = CurrentDb
Set MyRs = dbs.OpenRecordset("Contacts", dbOpenDynaset)

strCriteria = "[CustID]= " & (Forms!Clients!CustID) & ""

MyRs.FindFirst strCriteria

Does anyone know any reason why, when [CustID]=Null, it works fine in one datbase, and yet put the same code into another databse and I get the following error on the MyRs.FindFirst line:

"3077: Syntax Error (missing operator) in expression"

I can see that it is because [CustID]="" but why it works fine in one situation and not another makes me want to scream. All references are the same and in identical order.

 

[GrantDB]

Have you checked the database field for the settings for 'Required' and/or Allow Zero Length'

Grant

 

[danvlas]

Are you sure both databases have Null values? May it be because of a zero-length string ""? They look the same, but behave differently.
Anyway, try:

strCriteria = "[CustID]= '" & (Forms!Clients!CustID) & "'"

This should work...

Dan

 

[rookery]

Thanks for your help guys but

a) CustID is an AutoNumber and as far as I can see, the Required and Allow Zero Length dont apply.

b) I tried your code Dan but I'm getting a 3464 error:
"Data Type mismatch error in criteria expression" instead.

I can do a workaround but this sort of thing does your head in.

 

[danvlas]

Then you should drop some double quotes:

strCriteria = "[CustID]= " & Forms!Clients!CustID

and that's all.

dan

 

[rookery]

Dan you are the man! Works a treat! Still curious why this wasnt necessary in the other, identical, database though....