Query filtering

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I have linked a table to sql server database and
written a query in Access to filter records with
employee code starting with 'X', it does not filter
out those records. Can someone let me know what I
am doing wrong, here is the query -

SELECT [EMPCODE], [REGHRS]*[RATE] AS TOTALAMT, [REGHRS]*
[RATE]*2.7
FROM EMPTABLE
WHERE LEFT(EMPCODE,1)<>'X';

Thanks for your help!
 
A couple of possibilities:
1. Check for leading spaces in the EMPCODE field in the
source table (ascend sort on EMPCODE and see if any
records float to the top out of apparent alpha sequence -
those will have the leading spaces). If there are any
(and they can be very sneaky!), the WHERE test will fail.
2. I notice you set off field names with [] prior to the
WHERE; try WHERE LEFT([EMPCODE],1)<>'X' - Access may want
that after you've referred to it that way in the SELECT
phrase.
 
I don't know for some reason, it stated working,

Thanks for your help!
-Kim
-----Original Message-----
A couple of possibilities:
1. Check for leading spaces in the EMPCODE field in the
source table (ascend sort on EMPCODE and see if any
records float to the top out of apparent alpha sequence -
Click to expand...
those will have the leading spaces). If there are any
(and they can be very sneaky!), the WHERE test will fail.
2. I notice you set off field names with [] prior to the
WHERE; try WHERE LEFT([EMPCODE],1)<>'X' - Access may want
that after you've referred to it that way in the SELECT
phrase.

-----Original Message-----
I have linked a table to sql server database and
written a query in Access to filter records with
employee code starting with 'X', it does not filter
out those records. Can someone let me know what I
am doing wrong, here is the query -

SELECT [EMPCODE], [REGHRS]*[RATE] AS TOTALAMT, [REGHRS]*
[RATE]*2.7
FROM EMPTABLE
WHERE LEFT(EMPCODE,1)<>'X';

Thanks for your help!


.
Click to expand...
.
Click to expand...
 
Back
Top