Access 2003 and SQL 2003 data source 1

[bettyfurr]

I have a statement in a query that uses
LIKE '###[4-7]####'

This runs great on a SQL 6.5 and Access 2003. Give me this error.

You tried to execute a query that does not include the specified expression 'fieldname' as part of an aggregate function.


I appreciate any suggestions.

Betty

 

[BuGlen]

Hi Betty,

Given the error, it sounds like you tried to use a column in the WHERE clause that you didn't include in the aggregate expresion portion of the query. You can either include it in the SELECT clause if you want to include the results or you can include it in the Group By clause if you do not. In either case, the column must be included somewhere in the aggregate function to be accessable in your WHERE clause.

For example:

Select p.FirstName, p.LastName, Sum(p.Salary) As TotalSalary
From Person As p
Where p.PersonId Between 100 and 150
Group By p.FirstName, p.LastName, p.PersonId

In this example, I included the PersonId column in the Group By clause because I didn't want it returned in the result set, but I did want to be able to filter on it's value.

Hope this helps.

- Glen

Know thy data.

 

[bettyfurr]

What I have is a query in Access 2003, that is a filter for an account code. I need all accounts that have a 4, 5, 6, or 7 in the 4th position.

SQL 2003 does not seem know the # as a wild card. Do you know a wild card that both SQL and Access knows?

Betty (-:

 

[BuGlen]

Hi Betty,

In Access, when using the pound (#) in a LIKE operator pattern, it represents a single digit.

SQL Server does not have a digit pattern indicator within it's LIKE operator. To duplicate this functionality in SQL Server (or pass-through query), you could use the range indicator [0-9] which should pretty much do the same as the # in Access.

Hope this helps.

- Glen

Know thy data.

 

[bettyfurr]

I am sorry I don't understand. If I use the

[0-9] how would that say that I want any characters that is 1-3 but the 4 character must be a 4, 5, 6, or 7 and the remaining 4 character can be anything.

I used to use the function like (###[4-7]####) in Access 2000 and SQL 6.5. But SQL will not give any results from this command. Access will not give an error.

Betty (-:

 

[BuGlen]

Hi Betty,

Well basically, the # character in the Access LIKE operator signifies a single digit (0-9). So the following should provide the same functionality in SQL server:

LIKE '[0-9][0-9][0-9][4-7][0-9][0-9][0-9][0-9]'

Essentially, you just replace each # indicator with a [0-9] range indicator which tells SQL server you want anything with digits 0 through 9 in that position of the string.

Also, if you don't already have it, you can download SQL Server Books OnLine from Microsoft.com. It's a great resource, especially when trying to convert Access queries to SQL queries. Here's the link:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&displaylang=en

Hope this helps.

- Glen

Know thy data.

 

[bettyfurr]

I download the file. Installed and through the start programs, I tried to run the help. Got a error.

CANNOT OPEN THE FILE SQL80EN.COL. Do you know what this is about?

Betty

 

[BuGlen]

Which version of Windows are you using (ver, service pack)?

- Glen

Know thy data.

 

[bettyfurr]

XP PRO Service pak 1


Betty

 

[BuGlen]

OK, first check to make sure there is a SQL80EN.COL file in the folder in which you installed Books OnLine. There have been some reported cases that the file is actually called SQL80.COL (without the EN). If this is the case, you can simple change the shortcut properties to remove the 'EN' from the file name and it should work.

BTW, the default installation path is usually: C:\Program Files\Microsoft SQL Server\80\Tools\Books

If it exists, then you can try to "tickle" the HTMLHelp installation on your PC by installing the HTMLHelp Workshop from Microsoft:

http://msdn.microsoft.com/library/d...tmlhelp/html/hwMicrosoftHTMLHelpDownloads.asp

Hope this helps.

- Glen

Know thy data.