How do I use a Wildcard with a field reference

  • Thread starter Thread starter Robin Tancer
  • Start date Start date
R

Robin Tancer

I have a simple query in which I am selecting records from a table based on
information entered on a form.

This is the SQL for it :

SELECT DISTINCTROW Facultative.[Policy#], Facultative.[Certificate#],
Facultative.[Eff Date], Facultative.[Exp Date], Facultative.[Rein Code],
[Estate Table].[Estate Name], Facultative.[Interest%], Facultative.[Use
Flag], Facultative.[Estate Code]
FROM Facultative INNER JOIN [Estate Table] ON Facultative.[Estate Code] =
[Estate Table].[Estate Code]
WHERE (((Facultative.[Policy#]) Like [Forms]![Claim Info]![PCAPOLNBR] ) AND
(([Estate Table].[Estate Name]) Like [Forms]![Claim Info]![Estate Name]) AND
(([Forms]![Claim Info]![Field51]) Between [Facultative]![Eff Date] And
[Facultative]![Exp Date]) AND (([Forms]![Claim Info]![Financial Info]![Paid
Loss])>=[Facultative]![AttachmentPoint]));


My problem is that I would like to add a wildcard (*) before and after the
policy# . However no matter how I try and add it I keep getting errors. I
know how to use the * when I have an exact value - but not when I am
referencing a field on a form.

Any help is greatly appreciated

Thanks

Robin
 
Robin,

Do you mean like this?...

WHERE (((Facultative.[Policy#]) Like "*" & [Forms]![Claim
Info]![PCAPOLNBR] & "*" ) ...

By the way, as an aside, it is not a good idea to use a # or a % as part
of the name of a field or control or database object.
 
My problem is that I would like to add a wildcard (*) before and after the
policy# . However no matter how I try and add it I keep getting errors. I
know how to use the * when I have an exact value - but not when I am
referencing a field on a form.
Click to expand...

Try concatentating it as a string literal:

WHERE (((Facultative.[Policy#]) Like "*" & [Forms]![Claim
Info]![PCAPOLNBR] & "*") AND
 
Thank you both very much - I tried everything but that. I either had too
many quotes or I was leaving the & out.

Thanks again

Robin
Steve Schapel said:
Robin,

Do you mean like this?...

WHERE (((Facultative.[Policy#]) Like "*" & [Forms]![Claim
Info]![PCAPOLNBR] & "*" ) ...

By the way, as an aside, it is not a good idea to use a # or a % as part
of the name of a field or control or database object.

--
Steve Schapel, Microsoft Access MVP


Robin said:
I have a simple query in which I am selecting records from a table based on
information entered on a form.

This is the SQL for it :

SELECT DISTINCTROW Facultative.[Policy#], Facultative.[Certificate#],
Facultative.[Eff Date], Facultative.[Exp Date], Facultative.[Rein Code],
[Estate Table].[Estate Name], Facultative.[Interest%], Facultative.[Use
Flag], Facultative.[Estate Code]
FROM Facultative INNER JOIN [Estate Table] ON Facultative.[Estate Code] =
[Estate Table].[Estate Code]
WHERE (((Facultative.[Policy#]) Like [Forms]![Claim Info]![PCAPOLNBR] ) AND
(([Estate Table].[Estate Name]) Like [Forms]![Claim Info]![Estate Name]) AND
(([Forms]![Claim Info]![Field51]) Between [Facultative]![Eff Date] And
[Facultative]![Exp Date]) AND (([Forms]![Claim Info]![Financial Info]![Paid
Loss])>=[Facultative]![AttachmentPoint]));


My problem is that I would like to add a wildcard (*) before and after the
policy# . However no matter how I try and add it I keep getting errors. I
know how to use the * when I have an exact value - but not when I am
referencing a field on a form.

Any help is greatly appreciated

Thanks

Robin
Click to expand...
Click to expand...
 
Back
Top