Fast way to cut a long string

[cheekybuddha]

That is what no CrLf's means. NO record delimiters. The whole file was one humongous record as far as Access was concerned.

Sorry, Pat, I hadn't taken in your specific use case - I was replying to the OP.

But see my reply above yours also

 

[Uncle Gizmo]

Fast way to cut a long string.

Scissors, carton cutter, bread knife....

But don't make any snap decisions....

 

[Gasman]

That is where computers come into their own, no need to measure twice, cut once.
Can do it as many times as needed until you get it right.

 

[sonic8]

don't include VBA functions, just functions "instrinsic" to SQL query, like some aggregate functions (sum(), avg(), count(), etc).
then what are the others and i don't believe SQL has Mid() function. it belongs to VBA.
i need documentation, proof, reference material, not just "i think i know..."

I think , there once was such documentation at the times of Access 97. However, I'm not able to prove the existence of such documentation let alone produce its contents.

The intrinsic functions in database engine are listed in the Expression Builder for calculated columns in tables. The functions listed there should be implemented in the database engine itself because the calculated column must also work in the database independently of Access.
You can either confirm or disprove my claim by running a query containing any function in question against a database on a computer where no VBA is installed.

 

[Pat Hartman]

The Expression builder includes VBA functions as well as direct SQL and directly convertible VBA functions. Which makes sense. You are after all using the QBE inside Access so presumably, VBA will be present when the query runs.

 

[ebs17]

because the calculated column must also work in the database independently of Access

If you want to select a query as a data source for a Word mail merge, only queries that do not have such functions and other problems such as parameters through form references are offered. In other words, only those queries that Jet/ACE can execute all by itself because the scope of the language is known.

 

[sonic8]

The Expression builder includes VBA functions as well as direct SQL and directly convertible VBA functions.

I explicitly mean the Expression Builder for calculated columns in tables.
In that context the Expression Builder only offers a very restricted list of functions, which I believe are those built-in to the ACE-Engine.

 

[sonic8]

[...]Word mail merge, [...] In other words, only those queries that Jet/ACE can execute all by itself because the scope of the language is known.

I'm not so sure about that. I think Word Mail Merge also includes queries with built-in VBA functions, which are not built-into the database engine. To be really sure, one must use an application for verification that has no access to VBA at all.

 

[Pat Hartman]

From word/Excel, you can't see action queries or any queries that use UDF or that reference a form or anything not available in Word/Excel.

 

[Gasman]

From word/Excel, you can't see action queries or any queries that use UDF or that reference a form or anything not available in Word/Excel.

I seem to recall I had that issue when trying to mailmerge from Excel, but cannot remember the exact details, sorry.

 

[sonic8]

From word/Excel, you can't see action queries or any queries that use UDF or that reference a form or anything not available in Word/Excel.

Can you still get external Data into Excel without using Power Query? If not, the above (the "anything not available"-part) is not true for Excel.
Power Query (in Excel) will at least list some queries containing functions, which are not available. If such a query is selected, it will display an error message and not show any data.

 

[NauticalGent]

Fast way to cut a long string.

Scissors, carton cutter, bread knife....

But don't make any snap decisions....

I was restraining myself from replying in the same way, good to know I'm not the only kid in the room...