Using OR with .FindFirst in VBA

dloskot

New Member
Joined
Oct 18, 2015
Messages
43
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Looks like the & " before the first Or is the problem, try
VBA Code:
.FindFirst "[Email-Primary]= '" & Email_Key & "'" Or "[Email-Secondary] = '" & Email_Key & "'" Or "[Email-Tertiary] = '" & Email_Key & "'"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You could include And & Or operators within the beginning double quotes that encapsulate your single quotes as in
VBA Code:
.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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?:unsure:
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top