HELP.....problem with left (and right) string function

[EdRev]

I am in desperate need of help, I've been struggling with this problem for two days:

I have to text boxes on my report. One text box (txtBox1) holds a DB field and the second text box holds a string extracted from the value of txtBox1, so on the controlSource of txtBox2 = left([txtBox1],2).

When I run this report in Access 97, I got this error:

"syntax error (comma) of query expression 'left([txtBox1],2)'"

I converted the database to Access 2000, although I did not get an error message, the report would not run when I previewed it, it just goes back to the design view. I tried the left$ to no avail. I even tried the right function and prompt me to enter a parameter value?
I tried the len function and that works.

I am at my wit's end and I need help desperately.

 

[CajunCenturion]

Did you try using the Mid function?

=Mid([txtBox1], 1, 2)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cranebill]

Have you tried:

left(Me![txtBox1],2)

Bill

 

[EdRev]

Thanks guys for your immediate response.

I just tried the mid and mid$ functions and the preview prompts me to enter a parameter value. I enter any value and the report did not display.

I am using this on the expression builder, but I tried the Me! keyword, it did not work either.

 

[RoyVidar]

On a little guessing tour here.

1 - if you, thru the regional settings you use, use comma as decimal separator, in stead of dot (.), then you'd need to use semicolon ( in control sources, queries (argument separator in Excel...) - but still use comma in VBA code.

[tt]=left([txtBox1];2)[/tt]

2 - you'd usually get a "Function not defined" or something similar, if there's reference issues, but you might check if one or more of the references are marked as "missing"... (in the VBE - Tools | References)

3 - the text control you're referring to, does it have the same name as the field it's bound to? If so, rename it, so that the the name and controlsource of the control differs. Ensure the names are correct.

And, yes, the Me keyword, would not work in the controlsource of controls, but in coding. You might try using the on format event of the section where the control resides, and add something like this:

[tt]Me!txtBox2.Value=left$(Me!txtBox1.Value,2)[/tt]

Roy-Vidar

 

[JohnLowell]

Try Using:

Left([Report].[txtBox1],2)

 

[EdRev]

Thanks for all the feedback. After a few days of banging my head on the wall, I believe I have found the problem (though not the solution). The Access application sits on a server (with no Access installed). When I import the report and the source table to an Access database on my PC, it (left([textBox1],2)) works like a charm.

I do not know if I need to install Access to the server where the application is, because some of the functions works regardless (i.e len).

Does anybody has a take on this?