SearchForRecord where conditions for two fields

[fishfishburn]

Hi All,

Struggling a bit with a 'Where' condition on a 'SearchForRecord' macro that I'm trying to build in Access 2010.

I have a column in a query called 'Company' and another called 'Model Code'. I have two corresponding combo boxes on a form called 'CompanyCB' and 'ModelCode'.

I am trying to return the first record where both the 'Company' and 'Model Code' columns in the query match the contents of the 'CompanyCB' and 'ModelCode' combo boxes.

I have code for matching one at a time which works for both.

Model Code

="[Model Code] = " & "'" & [Forms]![Account and Model Costs Query]![ModelCode] & "'"

Company

="[Company] = " & "'" & [Forms]![Account and Model Costs Query]![CompanyCB] & "'"

The problem comes when I try and combine them with 'AND'. My code is as follows:

="[Model Code] = " & "'" & [Forms]![Account and Model Costs Query]![ModelCode] & "'" AND "[Company] = " & "'" & [Forms]![Account and Model Costs Query]![CompanyCB] & "'"

However this doesn't return a result. I'm guessing that I'm not joining the two conditions correctly as they both work on their own.

Any steer gratefully received.

Thanks

Richard

 

[pbaldy]

The double quotes on both sides of And need to be removed.

 

[fishfishburn]

Thanks Paul, worked a charm:

="[Company] = " & "'" & [Forms]![Account and Model Costs Query]![CompanyCB] & "' And [Model Code] = " & "'" & [Forms]![Account and Model Costs Query]![ModelCode] & "'"

 

[pbaldy]

No problem. This would be microscopically more efficient:

="[Company] = '" & [Forms]![Account and Model Costs Query]![CompanyCB] & "' And [Model Code] = '" & [Forms]![Account and Model Costs Query]![ModelCode] & "'"

Note the reduced concatenation of single quotes. You won't see a difference, so it's somewhat a personal preference. Also, the spaces in your names will prove to be more trouble than they're worth in the long run.

 

[mariekeb]

Hi all,

I know this is an old post, but I have a similar issue.

I am trying to do this same thing but with 10 field instead of 2. However, the Where condition box does not allow for such a long entry. Is there a way to shorten it?

What I have and what works is:

[Field1] Like "*" & [Formulieren]![Query2]![Tekst24] & "*" Or [Field2] Like "*" & [Formulieren]![Query2]![Tekst24] & "*"

However, if I do this with 10 fields it doesn't fit. I tried this to shorten it:

[Field1] Or [Field2] Like "*" & [Formulieren]![Query2]![Tekst24] & "*"

but that doesn't work...

Any advice is very welcome, thanks a lot in advance

Best wishes,
Marieke