Multiple Criteria Expressions

[smilla]

I need a little help please

I have just got this help from Access Visual Basic

stLinkCriteria = "[MID] = " & me![OID] ' this one works (numeric field)

stLinkCriteria = "[MText] = 'Text' ' this one also works (text field)

but as soon as I do this:-

stLinkCriteria = "[MID] = " & me![OID] And "[MText] = 'Text' (just joining them together because I need multiple criterias) it comes up with the following error:-

"Type Mismatch"

I'm sure it is so simple but just can't work it out.

Thanks

 

[Mile-O]

Be careful when opening and closing strings:

stLinkCriteria = "[MID] = " & Me.OID & " AND [MText] = ""Text"""

 

[smilla]

Thank you, i always get stuck on that one.

Is there any rules to following if I am putting more than one criteria together which are different data types? Just for future reference any way.

Thanks again

Smilla

 

[Mile-O]

Is there any rules to following if I am putting more than one criteria together which are different data types?

Numbers don't have delimiters (Integer, Long, Single, Double, Currency, Boolean)

Text has the delimiter ' or "" - "" is better as it stops a string from breaking down if, for example an ' appears in a name

Dates have the delimiter #" & date & "#

Also, the AND (and OR) is part of the string.

 

[smilla]

I shall try and remember that.

Thanks

 

[smilla]

I seem to have another problem.

I am using subforms so when I click on the command button to open the form with the double criteria, it is not recognising the ID field.

I am linking the ID from the sub form to the ID of the main form therefore what would I need to change to incorporate the sub form.

Example: stLinkCriteria = "[MID] = " & me.OID & " and [MText] = ""Otext"""

should be something like (but wont be)

stLinkCriteria = "[MID] = " & forms!frmProfiles!frmPolInfID & " and [MText] = ""Otext"""


but it doesnt work "Object doesn't support this property or method".

Any ideas?

 

[smilla]

Sorry, but I have sorted it. Very easy solution, just added the ID field (hidden) and it recognised it.

Thanks
Smilla