ProperCase

[JaniceKL]

Hello. I have typed in a Query: SELECT FirstName, STRCONV(FirstName,3) as FirstName_in_Proper_Case FROM DVAClientListTable.

FirstName is the field name and DVAClientListTable is the name of the table.

Before doing this I researched to discover the correct (or one of) code - I followed the instructions in a test table and it worked, now with the changes to the field and table name, I receive the following message:

Compile error, in query expression: 'STRCONV(FirstName,3'

I would appreciate it if someone can tell me what is wrong with the code. Ta in advance.

 

[CJ_London]

Don't see anything wrong in principle - strconv is pretty robust but presumably the firstname field in your table are all populated and there aren't any blanks or non alpha characters which might cause an issue.

You have shown us what you typed in a query - is this a copy/paste or have you retyped it in the post? If not copy/pasted please do so - you may have inadvertently retyped it correctly

 

[JaniceKL]

Don't see anything wrong in principle - strconv is pretty robust but presumably the firstname field in your table are all populated and there aren't any blanks or non alpha characters which might cause an issue.

You have shown us what you typed in a query - is this a copy/paste or have you retyped it in the post? If not copy/pasted please do so - you may have inadvertently retyped it correctly

Thank you CJ. It was a copy/paste and then I altered the field and tables names, which I have checked numerous times. However, I will start from scratch and redo the copy/paste. Once again, thank you for your very prompt response. Kind regards. Janice

 

[The_Doc_Man]

You said it was a Copy/Paste - but of what? If it is a stored query or a dynamically created string, I might suggest that you perform a Debug.Print on it. If there is a non-printing character there, Debug.Print might not actually display the bad character but it might show the anomaly.

Before I made the suggestion, I checked. STRCONV is valid in Access SQL - which means the query parser would know the required syntax of that function. To get it to stop at the "3" rather than an earlier or later place in that line, SQL would have to think it was facing an unknown function (thus not recognizing the need for a 2nd argument).

 

[oleronesoftwares]

STRCONV(FirstName,3) should be STRCONV([FirstName],3)

 

[CJ_London]

If the original has spaces then [] certainly required

 

[Pat Hartman]

The square brackets are required when a name contains invalid (per VBA) characters such as embedded spaces and special characters. Otherwise they are optional.

There is ONE place in the interface where you MUST use square brackets for a field name and that is in the query builder's where line. If you just type SomeName, Access will helpfully enclose the name in quotes and your query will return strange results.

 

[The_Doc_Man]

If the original had embedded spaces in the field name but no brackets, the SQL parser would have stopped earlier. It stopped on the 3. EITHER it thinks it needs the third argument of STRCONV (which is optional!) or there is a bad character there, or STRCONV is ALSO the name of a function elsewhere in the project.

 

[JaniceKL]

You said it was a Copy/Paste - but of what? If it is a stored query or a dynamically created string, I might suggest that you perform a Debug.Print on it. If there is a non-printing character there, Debug.Print might not actually display the bad character but it might show the anomaly.

Before I made the suggestion, I checked. STRCONV is valid in Access SQL - which means the query parser would know the required syntax of that function. To get it to stop at the "3" rather than an earlier or later place in that line, SQL would have to think it was facing an unknown function (thus not recognizing the need for a 2nd argument).

Thank you for responding - much appreciated. If what you say is happening, how do I fix it please?

 

[JaniceKL]

STRCONV(FirstName,3) should be STRCONV([FirstName],3)

Thank you so much - I had previously used the ([fieldname]), and just retired, but I still get errors.

If the original has spaces then [] certainly required

Thank you very much - I have tried with and without the {} - always get an error. I am at a total loss, and it is bugging me to no end. Maybe I need to debug my brain and start again. lol

 

[JaniceKL]

If the original had embedded spaces in the field name but no brackets, the SQL parser would have stopped earlier. It stopped on the 3. EITHER it thinks it needs the third argument of STRCONV (which is optional!) or there is a bad character there, or STRCONV is ALSO the name of a function elsewhere in the project.

Thank you for taking the time to help. I understand, but not understand (if that makes sense) to what you are saying. How do I check and then fix the problem please.

 

[JaniceKL]

If the original has spaces then [] certainly required

Thank you again CJ - but I have used the [] but still receive an error.

 

[CJ_London]

so far as I can see you have still not posted your actual code without alteration

 

[JaniceKL]

so far as I can see you have still not posted your actual code without alteration

Hello - I have not reposted the code with the [] because it still did not work. Apart from either of the two following codes, can you advise how I can fix this problem please.

1) SELECT FirstName, STRCONV([FirstName],3) as FirstName_in_Proper_Case FROM DVAClientListTable
2) SELECT FirstName, STRCONV(FirstName,3) as FirstName_in_Proper_Case FROM DVAClientListTable

Ta

 

[CJ_London]

If that is your actual sql (looks the same as in post #1 which you said you had changed field names etc) the only other thing I can suggest is you have a missing library in VBA. In the VBA window go to Tools>References. As a minimum you should have

Visual Basic for Applications
Microsoft Access xx.x Object Library
OLE Automations
Microsoft Office xx.x Access Database Engine Object Library

where xx.x relates to the access version (e.g. 16.0)

Edit: Presume you have done a compact repair? you might also try a decompile of your vba and then recompile.

And per Doc's suggestion or STRCONV is ALSO the name of a function elsewhere in the project - have you done a find in your vba to see if you there is a function call StrConv?

 

[Isaac]

You must have a misspelling in the column name - as you yourself said changes were made to table design.

First step is always to reproduce the problem.

I just created the same table and column name, and your #1 sql worked fine.

However, you said compile error. This has nothing to do with a query...this has to do with vba code.

POST ALL YOUR CODE