Undefined function in SQL

G

Guest

I have a query to make address labels. It has been working for years and all
of a sudden it came up with "Undefined function 'UCASE' in expression"
yesterday. I can only view it in SQL code, which reads as follows:

SELECT [Last Name] & " " &[First Name] AS Sort, [Prefix] & " " & [First
Name] & " " + [MI] & " " & [Last Name] & " " & [Suffix] AS FullName, Title,
Division, [COMPANIES TABLE_Company Name], Address1, Address2, City+ ",
"+UCASE(State) & " " &
Iif(LEN([Zip])>5,Format([Zip],"00000-0000"),Format([Zip],"00000")) As
CityStateZip

FROM tblAddressMatch

UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield,
blankfield, blankfield, blankfield, blankfield from tblOffset;

Is there something missing? Thanks.
 
G

Guest

There was no MISSING message, but I clicked a reference, closed the
References box, reopened the References box and unclicked that reference and
that seemed to solve the problem... until I closed Access and reopened it. I
had to do the same goofy toogle with the references after I opened the
database again. Will I have to do this everytime I open the file?

Tom Wickerath said:
You likely have a reference that is marked as MISSING. See the following two
articles:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MSU Sptn2 said:
I have a query to make address labels. It has been working for years and all
of a sudden it came up with "Undefined function 'UCASE' in expression"
yesterday. I can only view it in SQL code, which reads as follows:

SELECT [Last Name] & " " &[First Name] AS Sort, [Prefix] & " " & [First
Name] & " " + [MI] & " " & [Last Name] & " " & [Suffix] AS FullName, Title,
Division, [COMPANIES TABLE_Company Name], Address1, Address2, City+ ",
"+UCASE(State) & " " &
Iif(LEN([Zip])>5,Format([Zip],"00000-0000"),Format([Zip],"00000")) As
CityStateZip

FROM tblAddressMatch

UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield,
blankfield, blankfield, blankfield, blankfield from tblOffset;

Is there something missing? Thanks.
Click to expand...
Click to expand...
 
G

Guest

My database is on a server, would I have to do this through the server?

Tom Wickerath said:
You likely have a reference that is marked as MISSING. See the following two
articles:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MSU Sptn2 said:
I have a query to make address labels. It has been working for years and all
of a sudden it came up with "Undefined function 'UCASE' in expression"
yesterday. I can only view it in SQL code, which reads as follows:

SELECT [Last Name] & " " &[First Name] AS Sort, [Prefix] & " " & [First
Name] & " " + [MI] & " " & [Last Name] & " " & [Suffix] AS FullName, Title,
Division, [COMPANIES TABLE_Company Name], Address1, Address2, City+ ",
"+UCASE(State) & " " &
Iif(LEN([Zip])>5,Format([Zip],"00000-0000"),Format([Zip],"00000")) As
CityStateZip

FROM tblAddressMatch

UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield,
blankfield, blankfield, blankfield, blankfield from tblOffset;

Is there something missing? Thanks.
Click to expand...
Click to expand...
 
G

Guest

Ok, nevermind, I just did it a few more times and it seems to be sticking
now. Strange program. Thanks, Tom.

Tom Wickerath said:
You likely have a reference that is marked as MISSING. See the following two
articles:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MSU Sptn2 said:
I have a query to make address labels. It has been working for years and all
of a sudden it came up with "Undefined function 'UCASE' in expression"
yesterday. I can only view it in SQL code, which reads as follows:

SELECT [Last Name] & " " &[First Name] AS Sort, [Prefix] & " " & [First
Name] & " " + [MI] & " " & [Last Name] & " " & [Suffix] AS FullName, Title,
Division, [COMPANIES TABLE_Company Name], Address1, Address2, City+ ",
"+UCASE(State) & " " &
Iif(LEN([Zip])>5,Format([Zip],"00000-0000"),Format([Zip],"00000")) As
CityStateZip

FROM tblAddressMatch

UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield,
blankfield, blankfield, blankfield, blankfield from tblOffset;

Is there something missing? Thanks.
Click to expand...
Click to expand...
 
G

Guest

Glad to hear that you got it resolved. In your previous reply, you wrote:
"My database is on a server, would I have to do this through the server?"

You should split the database into two .mdb files: a front-end (FE) and
back-end (BE) database. A copy of the FE should be installed on each user's
local hard drive; this is the only copy that should require any checked
library references. The BE .mdb file includes tables only with the shared
data. You can read more about this here:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


MSU Sptn2 said:
Ok, nevermind, I just did it a few more times and it seems to be sticking
now. Strange program. Thanks, Tom.

Tom Wickerath said:
You likely have a reference that is marked as MISSING. See the following two
articles:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MSU Sptn2 said:
I have a query to make address labels. It has been working for years and all
of a sudden it came up with "Undefined function 'UCASE' in expression"
yesterday. I can only view it in SQL code, which reads as follows:

SELECT [Last Name] & " " &[First Name] AS Sort, [Prefix] & " " & [First
Name] & " " + [MI] & " " & [Last Name] & " " & [Suffix] AS FullName, Title,
Division, [COMPANIES TABLE_Company Name], Address1, Address2, City+ ",
"+UCASE(State) & " " &
Iif(LEN([Zip])>5,Format([Zip],"00000-0000"),Format([Zip],"00000")) As
CityStateZip

FROM tblAddressMatch

UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield,
blankfield, blankfield, blankfield, blankfield from tblOffset;

Is there something missing? Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query error: Undefined function 'Concatenate' in expression 3
undefined function replace in expression 3
Error: Enter Parameter Value 2
"Undefined Function" in Expression when using VBA module in query 1
undefined function 'Format' in expression 2
Undefined function in expression 4
SQL Help 8
UCase used in append query suddenly causing Undefined Function err 2

Top