Using OR with .FindFirst in VBA

[dloskot]

I am trying to find a record in an Access Data Base. There might be three email address for each record, Primary, Secondary and Tertiary

I have it working without any Or operators
.FindFirst "[Email-Primary]= '" & Email_Key & "'" (This line works)
I have tried the below code but it gives me an error
.FindFirst "[Email-Primary]= '" & Email_Key & "'" & " or "[Email-Secondary] = '" & Email_Key & "'" or "[Email-Tertiary] = '" & Email_Key & "'" (This gives me a compile error expended end of statement.

Can you please tell me the correct format to do what I am trying to do.
Thanks
Doug

 

[jasonb75]

Looks like the & " before the first Or is the problem, try

.FindFirst "[Email-Primary]= '" & Email_Key & "'" Or "[Email-Secondary] = '" & Email_Key & "'" Or "[Email-Tertiary] = '" & Email_Key & "'"

 

[dloskot]

Jasonb75 thank for your response. It compiles ok but now I get a RunTime Error 13 Type Mismatch. All fields in Access are defined as Text. As Email_Key is a String.
What do I need to change or check on to make this work? If you need to see more of the code let me know and I can upload it.
Thanks Doug

 

[jasonb75]

Is the error on that line when you debug or somewhere else? If everything is text / string as you say then there us no reason for a type mismatch error.

I'm not familiar with access, just picked up your question from the generic unanswered threads page. From what I can see, if the first line works correctly without the Or operators then the rest should work equally as well.

 

[Micron]

You could include And & Or operators within the beginning double quotes that encapsulate your single quotes as in

.FindFirst "[Email-Primary]= '" & Email_Key & "' Or [Email-Secondary] = '" & Email_Key & "' Or [Email-Tertiary] = '" & Email_Key & "'"

Hopefully I copied that correctly. If all of those email references are text fields and values, then the error is likely referencing something else. If you are absolutely sure the table fields are all text I suggest you post the procedure, but I have to agree that if the first works, the rest should.

 

[jasonb75]

You could include And & Or operators within the beginning double quotes that encapsulate your single quotes as in

That wouldn't work, with your syntax the Or operators are parts of the text strings, not valid vba commands.

 

[Micron]

Try it. Don't now what you consider a text string to be in this case, but in what I wrote, the OR is a part of the function Where clause.
Replace the concatenation (ctrl+H; " & Email_Key & ") with a value and you get
"[Email-Primary]= 'stringValue' Or [Email-Secondary] = 'stringValue' Or [Email-Tertiary] = 'stringValue'"

I don't see anything wrong with that and that's the syntax I always use in concatenation.

 

[jasonb75]

the OR is a part of the function Where clause.

That might be where I was going wrong earlier, I was working on the basis of If - Or rather than a function clause.

 

[Micron]

I don't think so as I agree with your solution (to paraphrase) - it wasn't concatenated properly. All I intended to show was that it didn't need as many parts.
The Where clause might be more appropriately referred to as the criteria. However, FindFirst is a recordset method, not a function as I incorrectly stated.

 

[welshgasman]

I tend to put criteria (if more than one) into a string variable and then can debug.print it, to see if it is correct?
When correct then I can use that in the function/code.

Never used it with FindFirst, but would think it would still work?