Access Query with $ in field

[BKer]

I am trying to create a query that will return all rows from a field that do not contain a word that starts with $. There is a field that contains words such as $System, etc. I can't figure out the proper syntax to get the query to work.

Thanks in advance
BK

 

[cghoga]

try this...

in the query view within the criteria of the field your querying, enter something like this...

Not Like "$*"

or if you're using straight SQL it would look something like this...

SELECT YourTableName.YourFieldName
FROM YourTableName
WHERE (((YourTableName.YourFieldName) Not Like "$*&quot);

hope this helps you.

 

[BKer]

I tried to do that along with a lot of other things and it still returns all the rows containing "$System". I think it has ot do something with the $ and how to bracket it or something.

Thanks anyway,
BK

 

[shannonp]

If you're field is of type 'string' I can't think why cgHoga's solution doesn't work??? Maybe you have leading spaces...which if this is true then alter your code using the Trim function:

SELECT Trim([YourTableName].[YourFieldName]) AS [YourFieldName]
FROM [YourTableName]
WHERE (((Trim([YourTableName].[YourTextName])) Not Like "$*&quot);

 

[BKer]

I tried the trim function and still not luck. Here is my query if it helps.

SELECT dbo_v_AlarmEventHistory.Area, dbo_v_AlarmEventHistory.Description, Trim([dbo_v_AlarmEventHistory].[Area])
FROM dbo_v_AlarmEventHistory
WHERE (((dbo_v_AlarmEventHistory.Description) Like '*P202*') AND ((Trim([dbo_v_AlarmEventHistory].[Area])) Not Like "$*&quot);

Thanks,
BKer

 

[shannonp]

Hrmmmm, add another column to your query with the following:

CheckFirstChar: Left(Trim([dbo_v_AlarmEventHistory].[Area]),1)

This will essentially only show the first character of 'Area'...where you can then put in criteria to exclude "$"...but first things first...just check that there are certainly text values that start with the dollar symbol ($).

Let me know how you get on...

 

[BKer]

I tried what you suggested and it still did not work

SELECT dbo_v_AlarmEventHistory.Area, dbo_v_AlarmEventHistory.Description, Trim([dbo_v_AlarmEventHistory].[Area])
FROM dbo_v_AlarmEventHistory
WHERE (((dbo_v_AlarmEventHistory.Description) Like '*P202*') AND ((left(Trim([dbo_v_AlarmEventHistory].[Area]),1)) Not Like "$*&quot);


I used this query in SQLServer and it works fine. I am using Access as the front end and linking tables to SQLServer. I tried to just use this query in Access to and the same results.

select area, tagname, description, eventstamp
from v_alarmeventhistory
where area <> '$System'

Thanks for the help,
BKer

 

[BKer]

Anybody have any insight on why this will work in SQLServer and no Access?

Thanks,
BKer

 

[Driep]

This might look complex but try the following:

where Mid([Naam],(InStr(1,[Naam],&quot;$&quot),1) <> &quot;$&quot;

replacing the field &quot;Naam&quot; with your fields

Good luck

Phlip

 

[BKer]

Driep,

I tried this and still no luck.

SELECT dbo_v_AlarmEventHistory.Area, dbo_v_AlarmEventHistory.Description
FROM dbo_v_AlarmEventHistory
WHERE (Mid((dbo_v_AlarmEventHistory.area),(InStr(1,(dbo_v_AlarmEventHistory.area),&quot;$&quot),1) <> &quot;$&quot;
AND ((dbo_v_AlarmEventHistory.Description) Like '*P202*'));

Any other suggestions?

Thanks for the help. Starting to get frustrated with access.

BK

 

[lienyi]

try '$*' instead of &quot;$*&quot;

 

[BKer]

I feel like I have tried everything. '$*' does not work either. I'm sure that it is some sort of syntax mix up that I can't figure out. Thanks for the help and if anyone has any more tips to try let me know.

Thanks,
BKer

 

[rajeshraghunath]

Try This....n it shud work defy.

SELECT Temp.*, Mid$([Test],1,1) AS Expr1
FROM Temp
WHERE (((Mid$([Test],1,1))<>&quot;$&quot);

 

[lienyi]

how about using escape code like this:

SELECT dbo_v_AlarmEventHistory.Area,
dbo_v_AlarmEventHistory.Description
FROM dbo_v_AlarmEventHistory
WHERE dbo_v_AlarmEventHistory.Description Like '*P202*' AND dbo_v_AlarmEventHistory.Area Not Like '[$]*'