problem with instr function and field in db.

[Rone]

I'm having trouble with the instr function.

I have a report that is based off of a query. I want to get everything to
the Left of a "," in a field called "initial_terms" and display that result
in the report, but I keep getting an error.

I've tried the following in the Control Source for the field:

=instr(1,[initial_terms],",",vbtextcompare) ---------> This generates an error
=instr(1,"Aaron","a",vbtextcompare) ----------->this works fine.
=Left_string([initial_terms]) --------> I wrote a function to use the instr
and return the value, but this also returned an error.
=Left_string("Aaron")---------> If I place a string in there the function
works just fine.

So, my two problems are this. When I try to use the name of the field for
the "instr" function or the "Left_string" function I wrote, I get problems.

Any ideas on how to solve this problem will be appreciated.

Thanks,

rone

 

[Kat Roedell]

Rone,

I probably use the "hack" method because I've always had problems using
functions in my report fields, but generally if I want to do something of
this sort in a report, I write the query to actually perform the operation,
then call the query field into the report instead of trying to do it in the
report.

 

[Brendan Reynolds]

Perhaps the textbox control is also named 'initial_terms'? If so, try
changing the name of the textbox.

BTW: Did using the VBA intrinsic constant 'vbTextCompare' really work for
you in the Control Source property? In my experience these constants work
only in VBA code and you have to use the numeric values when assigning
property values in design view. This is what worked for me in the Control
Source property ...

=InStr(1,[initial_terms],",",1)

1 is the numeric value of the constant vbTextCompare. (You can find these
values by looking up the constant in the Object Explorer).

In this particular example, you could also leave out the Compare argument,
as there are not upper and lower case versions of a comma, the result will
be the same regardless of which compare method is used.

 

[Rone]

Brendan, thanks that was my problem. The text box and the field name were
exactly the same. I changed the text box name and it worked great.

About the vba constant...no it doesn't work for me in the Constrol
Source...that was my "pasting error"

Thanks for the help.

--
Rone

Perhaps the textbox control is also named 'initial_terms'? If so, try
changing the name of the textbox.

BTW: Did using the VBA intrinsic constant 'vbTextCompare' really work for
you in the Control Source property? In my experience these constants work
only in VBA code and you have to use the numeric values when assigning
property values in design view. This is what worked for me in the Control
Source property ...

=InStr(1,[initial_terms],",",1)

1 is the numeric value of the constant vbTextCompare. (You can find these
values by looking up the constant in the Object Explorer).

In this particular example, you could also leave out the Compare argument,
as there are not upper and lower case versions of a comma, the result will
be the same regardless of which compare method is used.

--
Brendan Reynolds
Access MVP

I'm having trouble with the instr function.

I have a report that is based off of a query. I want to get everything to
the Left of a "," in a field called "initial_terms" and display that
result
in the report, but I keep getting an error.

I've tried the following in the Control Source for the field:

=instr(1,[initial_terms],",",vbtextcompare) ---------> This generates an
error
=instr(1,"Aaron","a",vbtextcompare) ----------->this works fine.
=Left_string([initial_terms]) --------> I wrote a function to use the
instr
and return the value, but this also returned an error.
=Left_string("Aaron")---------> If I place a string in there the function
works just fine.

So, my two problems are this. When I try to use the name of the field for
the "instr" function or the "Left_string" function I wrote, I get
problems.

Any ideas on how to solve this problem will be appreciated.

Thanks,

rone