Like in Dlookup

[msp4422]

Hi

Could you tell me how we can achieve the below req:

SV="Russia"
DeptId = DLookup("city","Lok","country like 'SV'")

Thanks.

 

[MarkK]

You need to get clear on the distinction between
1) the name of a VBA variable, and
2) the value it contains.

The code you posted passes the name of the variable into the Like expression. What you need to do is evaluate the variable, and pass the value into the criteria of the DLookup(). Consider how this differs from what you posted ...

SV="Russia"
DeptId = DLookup("city","Lok","Country LIKE '*" & SV & "*'")

See how the value of SV is concatenated into the criteria string?

 

[msp4422]

Thanks Labbolt..

Could you explain me the meaning of the '*" & .. How and where we need to use them.. thanks once again

 

[MarkK]

If you want a comparison that returns True for an exact match you would use an equals sign ...

dim criteria as string
criteria = "Country = 'Russia'"

You use LIKE when you want to match using wildcards, so ...

criteria = "Country LIKE 'Russia*'"

... would return True for Russia, Russian, Russian Federation, Russiageles, which isn't a real place. So the asterisk * matches any number of unknown characters, and question mark matches a single unknown character. So ..

criteria = "Country LIKE '*Russia?'"

... would match Russian, Prussian, and HeyWasThataRussian, but not Russia or Russian Federation.
Make sense?