MID function fails in SQL

I have a database that I use to capture records and create tables by date.
The current setup has 31 make table queries that are chosen by VBA code based
on the day of the month. I have decided to streamline my process and pass
sql language into a make table that creates a table with the correct name.
Part of this query relies on the "Mid" function to help parse some data in
certain fields. I created a separate database to do the development of
moving from 31 static queries to 1 query with variable sql passed from a
form. Now my new database gives me an error and states that 'Mid' is not
recognized as a built-in function. But the mid function works in the old
database. Any idea why it fails in one and works in the other? I checked
the "References" and they both have the same libraries selected. Thank you
in advance for you help.

Are you saying that you use a separate table for each "day-of-the-month"?

If so, both you and Access are having to work overtime to overcome your ...
spreadsheets!

In a well-normalized relational database, it isn't necessary to have one
table per day (or week, or month, or year). In fact, to get the best use of
Access' relationally-oriented features and functions, you DON'T want your
data structured like that.

You can keep coming up with work arounds, or you can spend a bit of time
getting your data into a structure that lets Access really work ... you'll
pay now or pay later g.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"WildlyHarry" wrote in message
...
I have a database that I use to capture records and create tables by date.
The current setup has 31 make table queries that are chosen by VBA code
based
on the day of the month. I have decided to streamline my process and pass
sql language into a make table that creates a table with the correct name.
Part of this query relies on the "Mid" function to help parse some data in
certain fields. I created a separate database to do the development of
moving from 31 static queries to 1 query with variable sql passed from a
form. Now my new database gives me an error and states that 'Mid' is not
recognized as a built-in function. But the mid function works in the old
database. Any idea why it fails in one and works in the other? I checked
the "References" and they both have the same libraries selected. Thank
you
in advance for you help.

"WildlyHarry" wrote in message
...
I have a database that I use to capture records and create tables by date.
The current setup has 31 make table queries that are chosen by VBA code
based
on the day of the month. I have decided to streamline my process and pass
sql language into a make table that creates a table with the correct name.
Part of this query relies on the "Mid" function to help parse some data in
certain fields. I created a separate database to do the development of
moving from 31 static queries to 1 query with variable sql passed from a
form. Now my new database gives me an error and states that 'Mid' is not
recognized as a built-in function. But the mid function works in the old
database. Any idea why it fails in one and works in the other? I checked
the "References" and they both have the same libraries selected. Thank
you
in advance for you help.

lilmmùù

"WildlyHarry" a écrit dans le
message de groupe de discussion :
...
I have a database that I use to capture records and create tables by date.
The current setup has 31 make table queries that are chosen by VBA code
based
on the day of the month. I have decided to streamline my process and pass
sql language into a make table that creates a table with the correct name.
Part of this query relies on the "Mid" function to help parse some data in
certain fields. I created a separate database to do the development of
moving from 31 static queries to 1 query with variable sql passed from a
form. Now my new database gives me an error and states that 'Mid' is not
recognized as a built-in function. But the mid function works in the old
database. Any idea why it fails in one and works in the other? I checked
the "References" and they both have the same libraries selected. Thank
you
in advance for you help.