Criteria if 1st Character might be a space

[Guest]

I have set up a query that accepts input from a text box. It appears some of
the data in the criteria field be start with a space and some do not. I tried
to concatenate a space " " & [Forms]![Form1].[Form]![Text1], but of course
that only works if there is a space. What is the syntax to find a record if
this field does or does not have a leading space?

 

[SusanV]

Like " *" should do the trick for you. If this will be run in VBA, use " %"
instead.

 

[Guest]

Whether you use * or % has nothing to do with VBA. It depends on the
database engine. * is for Jet % for SQL Server.
Another way would be to use the Trim funtion on both the table field and the
criteria:

WHERE Trim([FIELD1]) = Trim([Forms]![Form1].[Form]![Text1])

--
Dave Hargis, Microsoft Access MVP

Like " *" should do the trick for you. If this will be run in VBA, use " %"
instead.
--
hth,
SusanV

I have set up a query that accepts input from a text box. It appears some
of
the data in the criteria field be start with a space and some do not. I
tried
to concatenate a space " " & [Forms]![Form1].[Form]![Text1], but of course
that only works if there is a space. What is the syntax to find a record
if
this field does or does not have a leading space?

 

[SusanV]

Hi Dave,

Ok, mis-spoken then - using CurrentDb.Execute, use %, if using DoCmd.RunSQL
use *
Is that correct?

Whether you use * or % has nothing to do with VBA. It depends on the
database engine. * is for Jet % for SQL Server.
Another way would be to use the Trim funtion on both the table field and
the
criteria:

WHERE Trim([FIELD1]) = Trim([Forms]![Form1].[Form]![Text1])

--
Dave Hargis, Microsoft Access MVP

Like " *" should do the trick for you. If this will be run in VBA, use "
%"
instead.
--
hth,
SusanV

I have set up a query that accepts input from a text box. It appears
some
of
the data in the criteria field be start with a space and some do not. I
tried
to concatenate a space " " & [Forms]![Form1].[Form]![Text1], but of
course
that only works if there is a space. What is the syntax to find a
record
if
this field does or does not have a leading space?

 

[Guest]

No, Susan. It depends on the database engine.

The Jet database engine uses * for a wild card
The SQL Server engine used % for a wild card
--
Dave Hargis, Microsoft Access MVP

Hi Dave,

Ok, mis-spoken then - using CurrentDb.Execute, use %, if using DoCmd.RunSQL
use *
Is that correct?

Whether you use * or % has nothing to do with VBA. It depends on the
database engine. * is for Jet % for SQL Server.
Another way would be to use the Trim funtion on both the table field and
the
criteria:

WHERE Trim([FIELD1]) = Trim([Forms]![Form1].[Form]![Text1])

--
Dave Hargis, Microsoft Access MVP

Like " *" should do the trick for you. If this will be run in VBA, use "
%"
instead.
--
hth,
SusanV

I have set up a query that accepts input from a text box. It appears
some
of
the data in the criteria field be start with a space and some do not. I
tried
to concatenate a space " " & [Forms]![Form1].[Form]![Text1], but of
course
that only works if there is a space. What is the syntax to find a
record
if
this field does or does not have a leading space?