By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Multiple Field Filtering

P: 17
I want to be able to double click on a field called Weight on CurrentLoadListFrm and have it open another form called FreightProviderMatchFrm and filter on six different fields that are on both of the forms listed above. The six fields are: ShipperCode1, ShipperCode2, ShipperCode3, ConsigneeCode1, ConsigneeCode2, and ConsigneeCode3. The net result will hopefully give me a list of Carriers that have done the exact same loads in the past that I am looking for a truck for the future. Any Ideas? Access 2003
Jul 16 '07 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,315
The DoCmd.OpenForm()'s 4th parameter is a SQL Where string that can be used to filter the form's records when it opens.
Jul 16 '07 #2

P: 17
Here is the Where Statement I came up with, Can someone tell me why this doesn't work! I am a novice at this whole thing.

Where FreightProviderMatchFrm![ShipperCode1] = CurrentLoadListFrm![ShipperCode1], FreightProviderMatchFrm![ShipperCode2]=CurrentLoadListFrm![ShipperCode2], FreightProviderMatchFrm![ShipperCode3]=CurrentLoadListFrm![ShipperCode3], FreightProviderMatchFrm![ConsigneeCode1]=CurrentLoadListFrm![ConsigneeCode1], FreightProviderMatchFrm![ConsigneeCode2]=CurrentLoadListFrm![ConsigneeCode2], FreightProviderMatchFrm![ConsigneeCode3]=CurrentLoadListFrm![ConsigneeCode3]
Jul 17 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
Here is the Where Statement I came up with, Can someone tell me why this doesn't work! I am a novice at this whole thing.

Where FreightProviderMatchFrm![ShipperCode1] = CurrentLoadListFrm![ShipperCode1], FreightProviderMatchFrm![ShipperCode2]=CurrentLoadListFrm![ShipperCode2], FreightProviderMatchFrm![ShipperCode3]=CurrentLoadListFrm![ShipperCode3], FreightProviderMatchFrm![ConsigneeCode1]=CurrentLoadListFrm![ConsigneeCode1], FreightProviderMatchFrm![ConsigneeCode2]=CurrentLoadListFrm![ConsigneeCode2], FreightProviderMatchFrm![ConsigneeCode3]=CurrentLoadListFrm![ConsigneeCode3]
The correct syntax would be:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FreightFroviderMatchFrm", , , "ShipperCode1 = Forms!CurrentLoadListFrm![ShipperCode1] AND ShipperCode2 = Forms!CurrentLoadListFrm![ShipperCode2] AND ...."
  2.  
This assumes your fields are numbers, if not then the syntax will be different.
Jul 17 '07 #4

P: 17
ShipperCode is not a number it is a code like this: "ABC-ABC-" How would the syntax change for this type of code.
Jul 17 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
Expand|Select|Wrap|Line Numbers
  1. "ShipperCode1 = '" & Forms!CurrentLoadListFrm![ShipperCode1] & "'"
Jul 17 '07 #6

P: 17
Any idea why I am getting a Type Mismatch error after entering the code above?
Jul 18 '07 #7

P: 17
VB Code:

DoCmd.OpenForm "FreightProviderMatchFrm", , , "ShipperCode1='" & Forms!CurrentLoadListFrm![ShipperCode1] & "'" And "ShipperCode2='" & Forms!CurrentLoadListFrm![ShipperCode2] & "'"

This code works if you only include the first ShipperCode1 and leave off the second ShipperCode2. Any ideas as to how to add more than one variable.
Jul 18 '07 #8

Rabbit
Expert Mod 10K+
P: 12,315
The And logical comparison operator has to be within the the quotes.

"Expr1 AND Expr2"

Not "Expr1" AND "Expr2"

The former is a string to be evaluated. It will evaluate whether or not expression 1 and 2 are both true.

The latter attempts to evalute the whether or not the 2 strings are true.
Jul 18 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.