Datasheet view not displaying properly

[fisk]

I'm using access 2003 sp2 with all the current updates. I've got several tables linked to a sql server 2000 DB. We use it for an app called Altiris. Most of the queries i've written from these various tables work fine. However, a couple of them for no apparent reason, do not display the correct data in the data sheet view when the query is ran.

for example, I have a simple query with two tables linked. The first field is asking for the "name" column of one of the tables. In the criteria of that column, I put something in to limit the names of the programs i'm looking for. Lets say I use LIKE '%adobe acrobat 7.0%' When I run the query, it returns a certain number of records, but in the name column, it has all wrong info. I just ran it and it came up with 4 records: "webcast....", "abacast client", "Ibm websphere", and something else. Nothing even close to "adobe...."

So then I export the query to an excel spreadsheet and then open. Guess what? It has the correct info. Four records of adobe acrobat. The other column I have is from the other table and is just listing pc names. They displayed properly in both datasheet and excel file.

does anyone have any ideas what could cause this? I've deleted the table and linked to it several times. I've used this table for many other queries and it works most of the time. I just have no idea what is causing it to do this for only a few queries.

Thanks in advance for any help.

fisk

 

[RuralGuy]

Have you tried using the Jet wildcard character of '*' rather than the SQL wildcard character '%'?

 

[neileg]

Not sure if this will help, but the wildcard character in Access is * not %. Also, the syntax of the Like operator does not require the use of the single quotes. Now I don't know if you need to use correct SQL for Access or sql server, but you might try changing these.

Edit: Snap, RG!

 

[fisk]

Yes. I posted this same question on the ms newsgroups earlier this week and that was one of the suggestions. It produced the same results.

fisk

 

[fisk]

Ok guys, were finally getting somewhere. I changed the % to * and that didnt change anything. I changed the single quotes to double quotes and with the addition of asterisks, it finally SORT of worked. This time it produced around 80 records where it was only returning 4 from before. The bad news is that about half of them are wrong. The good news is that half of them were actually what they were supposed to be.

again, when I exported to excel, they were all correct.

I'm going to try to attach screen shots. I had to crop the crap out of them to get them under the size limits. One is of the data sheet view, the other is of the excel file with the correct data.

fisk

 

[RuralGuy]

I'm out of ideas and Neil keeps snapping me. "Now cut that out!"

 

[neileg]

Out of ideas, too.

How about posting the SQL of the query?

 

[RuralGuy]

I believe you said you are using an SQL Server BE so I'm not sure how much help we will be.

 

[fisk]

SELECT dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name, dbo_Wrksta.Name
FROM dbo_AeXInv_AeX_OS_Add_Remove_Programs INNER JOIN dbo_Wrksta ON dbo_AeXInv_AeX_OS_Add_Remove_Programs.WrkstaId = dbo_Wrksta.WrkstaId
WHERE (((dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name) Like "*acrobat 7.0*"));



This query returns two columns from two different tables. Overall, it's pretty simple. The sql server BE returns the correct data, it's just that the datasheet view displays it as something totally different.

by the way guys, thanks for the help with this. Regardless of the outcome, I appreciate it.

fisk