SQL select query not working. in ms access 2010

Question

here are my two charts and my query. Obviously you do see that some parameter names match. but my query shows up as no results. WHY IS THAT?

Thank you .

Altho this query was made in access 2010 it also works in 2003 and 2007

if you cant read the query in detail. just zoom in on this webpage

UPDATEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE: ****************************8

thanks for all your help but i have solved it my own way. and because im doing this for work i needed it to be done fast and didnt have too much time to dela with why access trim does nto work =( ... i still dunno why.

but altho my source table is big that one did not have trialing spaces. only the parameter table which i had uploaded from another table that was an excel file and 99 rows.

i went back into the orignal excel file. tried a trim there. copy pasted the trimmed column and paste special values to rid of the formula and it still has the space!! is this jsut an error on the trim function or what?

anyways instead i used a =left(cell,len(cell)-1) to rid of the one trailign space

then reuploaded the excel parameter table and now its working. lengthy story but. if anyone has another suggestions on why TRIM doesnt work please explain . i used LTRIM RTRIM. BOTH together and jsut TRIM

Because of trailing spaces, maybe (which are possibly not ignored in MS Access like they are, for example, in SQL Server)? — Andriy M
– Andriy M, Commented Nov 1, 2011 at 20:22
Wow, big table for Access at 11m rows! Is this a linked table? I suspect Andriy M is correct. Curious as to whether you get the same results if you use ".. from parametertable as t1 inner join t1_sourcetable as t2 on t1.parameter_name = t2.parameter_name.."? — Lord Peter
– Lord Peter, Commented Nov 1, 2011 at 20:33
same results lol. but i was curious too and tried. nope it wasnt a linked table thank gosh. — Chaostryder
– Chaostryder, Commented Nov 1, 2011 at 21:06
Glad you sorted it! Wonder whether your Excel data had a non ascii whitespace character in it e.g. tab character or non-breaking space which may not have been trimmed off. What does =code(right(YOUR_ORIGINAL_EXCEL_CELL, 1)) give you? If it's not 32 (ascii for space character) then maybe that's the problem? — Lord Peter
– Lord Peter, Commented Nov 1, 2011 at 21:18
U got it right on the dot ! it was not even close to 32 . i got 160 — Chaostryder
– Chaostryder, Commented Nov 1, 2011 at 21:28

HansUp · Accepted Answer · 2011-11-01 21:24:10Z

2

In a reply to @flayto you said "*turns out there is one trailing space behind each Parameter_Name value in ParameterTable. But i tried trimming in it does say it updates all of the entries in that table yet the one space sitll does not go away!*"

A few times I have copied a range of cells from an Excel spreadsheet into Access tables. The data in Access included invisible trailing characters after the text I expected. Since those characters were not spaces, they were not removed by the Trim() function. See what you actually have following the Paramater_Name values.

SELECT
    Parameter_Name,
    Len(Parameter_Name) AS Length_of_Parameter_Name
    Asc(Right(Parameter_Name, 1)) AS rightmost_character
FROM ParameterTable;

The fix will depend on what you find. For example, if there is always one and only one unwanted character, you could discard it with this UPDATE statement:

UPDATE Parameter_Table
SET Parameter_Name = Left(Parameter_Name, Len(Parameter_Name) -1);

Whatever UPDATE you try, make sure to backup the database first in case anything goes wrong.

edited Nov 1, 2011 at 21:24

answered Nov 1, 2011 at 21:19

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chaostryder Over a year ago

Thank you Hans. See also @Lord Peter s comment. you guys both have the right idea altho it looked like a space it was not. and the query you gave does work. same as my excel formula but in access. i didnt kno they had the same function lol. thank you so much.

bv8z · Accepted Answer · 2011-11-01 20:20:57Z

1

Are you sure there are no leading or trailing spaces in the field values? Try wrapping each field with LTRIM/RTRIM as in LTRIM(RTRIM(T1_SourceTable.Parameter_Name)) just to be sure that isn't the problem.

answered Nov 1, 2011 at 20:20

bv8z

9753 gold badges9 silver badges19 bronze badges

1 Comment

Chaostryder Over a year ago

Hello, turns out there is one trailing space behind each Parameter_Name value in ParameterTable. But i tried trimming in it does say it updates all of the entries in that table yet the one space sitll does not go away ! what could be the problem?

AllenG · Accepted Answer · 2011-11-01 20:27:49Z

1

Because you're comparing ParameterTable.Full_Name to T1_SourceTable.Parameter_Name.

You should be comparing ParameterTable.Parameter_Name to T1_SourceTable.Parameter_Name.

***NOTE: I'm leaving this up while I look at this, but I'm suspecting the leading/trailing spaces (as mentioned by a couple of others).

***Possible other solution: Modify this into a specific Inner Join. The links I'm finding from MS don't specifically you can't do what you've done, but they do all use the explicit INNER JOIN Syntax.

So you might try:

SELECT T1.Full_Name, T2.Parameter_Name
FROM ParameterTable T1
INNER JOIN T1_SourceTable T2
  ON T1.Parameter_Name = T2.Parameter_Name

This shouldn't, as far as I know, actually change anything- but, based on the links I'm finding, you might try it out.

edited Nov 1, 2011 at 20:27

answered Nov 1, 2011 at 20:14

AllenG

8,20831 silver badges40 bronze badges

3 Comments

Marco Over a year ago

...but in his query he's already using WHERE T1.Parameter_Name = T2.Parameter_Name...

AllenG Over a year ago

Whoops- Misread it. Looked like it said t1.Full_Name... Wait for an update.

Chaostryder Over a year ago

hey ya i ran your query and same problem no results =(

Nikki9696 · Accepted Answer · 2011-11-01 20:35:46Z

1

Are they the exact same data type? If not, for Access, try using LIKE instead of equals.

t1.parameter_name like t2.parameter_name

See the example here. http://office.microsoft.com/en-us/access-help/access-sql-where-clause-HA010278156.aspx

answered Nov 1, 2011 at 20:35

Nikki9696

6,3681 gold badge32 silver badges24 bronze badges

1 Comment

Chaostryder Over a year ago

yes they are the same data type but this tidbit of information will be helpful in the future thanks

Lord Peter · Accepted Answer · 2011-11-01 20:41:57Z

1

If your table is linked and the underlying datatype is char rather than varchar, then it will be blank padding even after you think you have trimmed it. You'll have to change the underlying data type or join on trim(parameter_name) which will be expensive!

answered Nov 1, 2011 at 20:41

Lord Peter

3,5012 gold badges36 silver badges35 bronze badges

3 Comments

Chaostryder Over a year ago

Hey no my parameter table is not linked and all the data types are in text. im nto sure what the problem is =( trying the trailing spaces right now with no luck so far

Lord Peter Over a year ago

Try select parameter_name, len(parameter_name), trim (parameter_name), len(trim(parameter_name)) from each table in turn (limit to top eg 100 from your big table) and see what you get?

Chaostryder Over a year ago

the len (field) or len(trim(field)) both end up giving me a number and the same number so the trim does nothing in access.. i have no idea why but i ahve solved my problem and ill update it to the question thanks

Collectives™ on Stack Overflow

SQL select query not working. in ms access 2010

5 Answers 5

1 Comment

1 Comment

3 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related