Access 2010, I would be really appreciated if anyone could help on this one.
I am now creating a Access for My transport routing. database.
I have a list of Truckers and each Trucker only goes to specific locations, such as from IL, CO, CA, MO. I have a table noted down with all the Trucker Information, And a copy of the same Table noted down with all the location they go to.
The Problem is:
In my search Form, I can only find those trucker from One Area. But I want to find those Trucker who could goes From (Area) and To (Area). And the From_Area and To_Area is the same.Here is my Form for Trucker info: The Location Table is where I type the area that the Trucker served.Here is my form for Search Suitable Trucker: (Now I can only search one State Area, But I want to search Two State Area) Such as The Trucker that goes to IL and MO too.
I am not sure how am I am able to do that.
I have tried inputting expression in Criteria of the Query but it didn't show, IL and MO
The closest I could get is a copy of the Area Table and the result will show which Trucker goes to IL and MO too. But I can't find the method that I could update both different table at the same time. Since all the Area.State and the Copy of Area.State is the Same. Is there an alternative way to do it? Please Help! Thanks.
Here is the table for the above Search
Vendor ID Table:
Vendor ID= Auto Number
Type = Text
Sales Name = Text
Email Address = Hyperlink
Area Table:
AreaID = AutoNumber
Vendor ID = Number
State = Text
Copy of Area Table:
AreaID = AutoNumber
Vendor ID = Number
State = Text
Query Right Vendor SQL
SELECT [Vendor ID].VendorName, [Vendor ID].Type, [Vendor ID].Email, Area.State, [Copy Of Area].State, [Vendor ID].Website, [Vendor ID].User, [Vendor ID].Password, [Vendor ID].SalesName
FROM ([Vendor ID] INNER JOIN Area ON [Vendor ID].[VendorID] = Area.VendorName) INNER JOIN [Copy Of Area] ON [Vendor ID].VendorID = [Copy Of Area].VendorName
WHERE ((([Vendor ID].Type) Like "*" & [Forms]![Right Vendor]![TextEnterType] & "*") AND ((Area.State) Like "*" & [Forms]![Right Vendor]![TextEnterState] & "*") AND (([Copy Of Area].State) Like "*" & [Forms]![Right Vendor]![Text94] & "*")) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L3","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L4","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L3","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L4","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L5","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L5","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94]));
I am now creating a Access for My transport routing. database.
I have a list of Truckers and each Trucker only goes to specific locations, such as from IL, CO, CA, MO. I have a table noted down with all the Trucker Information, And a copy of the same Table noted down with all the location they go to.
The Problem is:
In my search Form, I can only find those trucker from One Area. But I want to find those Trucker who could goes From (Area) and To (Area). And the From_Area and To_Area is the same.Here is my Form for Trucker info: The Location Table is where I type the area that the Trucker served.Here is my form for Search Suitable Trucker: (Now I can only search one State Area, But I want to search Two State Area) Such as The Trucker that goes to IL and MO too.
I am not sure how am I am able to do that.
I have tried inputting expression in Criteria of the Query but it didn't show, IL and MO
The closest I could get is a copy of the Area Table and the result will show which Trucker goes to IL and MO too. But I can't find the method that I could update both different table at the same time. Since all the Area.State and the Copy of Area.State is the Same. Is there an alternative way to do it? Please Help! Thanks.
Here is the table for the above Search
Vendor ID Table:
Vendor ID= Auto Number
Type = Text
Sales Name = Text
Email Address = Hyperlink
Area Table:
AreaID = AutoNumber
Vendor ID = Number
State = Text
Copy of Area Table:
AreaID = AutoNumber
Vendor ID = Number
State = Text
Query Right Vendor SQL
SELECT [Vendor ID].VendorName, [Vendor ID].Type, [Vendor ID].Email, Area.State, [Copy Of Area].State, [Vendor ID].Website, [Vendor ID].User, [Vendor ID].Password, [Vendor ID].SalesName
FROM ([Vendor ID] INNER JOIN Area ON [Vendor ID].[VendorID] = Area.VendorName) INNER JOIN [Copy Of Area] ON [Vendor ID].VendorID = [Copy Of Area].VendorName
WHERE ((([Vendor ID].Type) Like "*" & [Forms]![Right Vendor]![TextEnterType] & "*") AND ((Area.State) Like "*" & [Forms]![Right Vendor]![TextEnterState] & "*") AND (([Copy Of Area].State) Like "*" & [Forms]![Right Vendor]![Text94] & "*")) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L3","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L4","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L3","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L4","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L1","L5","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94])) OR ((([Vendor ID].Type)=IIf([Forms]![Right Vendor]![TextEnterType]="L2","L5","0")) AND ((Area.State)=[Forms]![Right Vendor]![TextEnterState]) AND (([Copy Of Area].State)=[Forms]![Right Vendor]![Text94]));
