Functions

[Sed]

Somebody help. I have 2 columns, one is OriginCity and the other is DestinationCity. I want to filter out where origincity is like destinationcity.
for example. I have origin "Baltimore" however, if I have a destination "Baltimore" I do not want to see it in the results. Is this possible.

 

[pbaldy]

In SQL view:

WHERE OriginCity <> DestinationCity

Or if you mean once you choose an origin, you don't want to see it as a destination, it would be similar to a cascading combo:

http://www.baldyweb.com/CascadingCombo.htm

but with the appropriate change to the criteria.

 

[Sed]

Pbaldy,
below is my SQL. If the destination is the same as my origin, then I don't want to see that data. I got the origin right but I don't know how to omit the data where destination is the same as the origin.


SELECT DF_Bills.Locn, DF_Bills.Carr AS SCAC, DF_Bills.Mode, DF_Bills.Pro AS [Bill #], DF_Bills.CkDate AS [Date], DF_Bills.Acct AS Dept, DF_Bills.Paid AS [Amt Paid], DF_Bills.Billed AS Amt, DF_Bills.Fsc, DF_Bills.Acc, DF_Bills.Disc AS Misc, DF_Bills.Wgt AS Weight, DF_Bills.Bol AS BOL, DF_Bills.OrigName, DF_Bills.OrigCity, DF_Bills.OrigSt, DF_Bills.OrigZip AS ZIP, DF_Bills.DestName, DF_Bills.DestCity, DF_Bills.DestSt AS DestState, DF_Bills.DestZip, DF_Bills.IO
FROM DF_Bills
WHERE (((DF_Bills.Locn)<>"AGSC") AND ((DF_Bills.Mode)="TL") AND ((DF_Bills.CkDate)>#1/1/2009#) AND ((DF_Bills.OrigCity) In ("BALTIMORE","DUPONT","EDGEWOOD","HESSTON","JACKSON","LAPORTE")) AND ((DF_Bills.OrigSt)<>"ZZ") AND ((DF_Bills.OrigZip) Is Not Null) AND ((DF_Bills.DestZip) Is Not Null) AND ((DF_Bills.IO)="O"))
ORDER BY DF_Bills.CkDate;

 

[pbaldy]

I'm not sure I'm understanding. Wouldn't this do it?

...AND DF_Bills.DestCity Not In ("BALTIMORE","DUPONT","EDGEWOOD","HESSTON","JACKSON","LAPORTE")

 

[Sed]

I tried that, but that didn't work. I had 8019 results and 3 had the same origin and destination. I expected to see 8016, when I did you suggest. for some reason, it omit everything that have that city name. for example it omit Jackson to baltimore, because destination city have baltimore in there.

I hope I explain this clearly.

 

[SOS]

Actually I think you want to add this to your WHERE clause (and get rid of the Not In):

AND [DF_Bills.DestCity] <> [DF_Bills.OrigCity]

 

[Sed]

SOS,
Not sure if I understand you...my where clause is below. What do suggest

WHERE (((DF_Bills.Locn)<>"AGSC") AND ((DF_Bills.Mode)="TL") AND ((DF_Bills.CkDate)>#1/1/2009#) AND ((DF_Bills.OrigCity) In ("BALTIMORE","DUPONT","EDGEWOOD","HESSTON","JACKSON","LAPORTE")) AND ((DF_Bills.OrigSt)<>"ZZ") AND ((DF_Bills.OrigZip) Is Not Null) AND ((DF_Bills.DestZip) Is Not Null) AND ((DF_Bills.IO)="O"))

 

[SOS]

I hate the parens Access puts in (you don't need them most of the time - only when mixing OR in with the ANDs).

WHERE DF_Bills.Locn <>"AGSC" AND (DF_Bills.Mode)="TL" AND DF_Bills.CkDate>#1/1/2009# AND DF_Bills.OrigCity In ("BALTIMORE","DUPONT","EDGEWOOD","HESSTON","JACKSO N","LAPORTE") AND DF_Bills.OrigSt <>"ZZ" AND DF_Bills.OrigZip Is Not Null AND DF_Bills.DestZip Is Not Null AND DF_Bills.IO ="O" AND [DF_Bills.DestCity] <> [DF_Bills.OrigCity]

 

[Sed]

You are the MAN!!!! results is actually 8013, what I expected. Have a beer on me.

 

[SOS]

Glad we could help.

 

[pbaldy]

I guess I assumed that had been tried and failed, since I suggested it in post 2. Glad you found your solution.

 

[SOS]

I guess I assumed that had been tried and failed, since I suggested it in post 2. Glad you found your solution.

I've noticed that seems to happen quite frequently around here.