I need help working with parameters!!!!

[Guest]

Hello and thanks in advance for answering my question!

I am working on a query involving 3 seperate fields. What I want to do, is
to be able to use parameters to pull information from the three different
fields. What I am working on is a database that shows the different forms and
regulations for different lines of business my company deals with. The query
is regarding a specific form that varies from state to state. For example:

State "A" uses ABC form
State "B" uses XYZ form
State "C" uses 123 form
State "D" uses ABC form and XYZ form
State "E" uses XYZ and 123 form
State “F†uses ABC, XYZ and 123 form

What I want to be able to do is to pull the information on which states use
XYZ form. I've been able to build a parameter for the first form field, but
in the cases where multiple forms are used, I am having issues. The problem
I am having is that I can't seem to find a way to "skip" the first form field
to get information from the second (&/or third) form fields without entering
information in the first form field. (I’m still not very knowledgeable
with Visual Basic Editor and other code based applications, but can sort of
piece my way through them with help)

Thanks again!

 

[Carma]

Hello and thanks in advance for answering my question!

I am working on a query involving 3 seperate fields. What I want to do, is
to be able to use parameters to pull information from the three different
fields. What I am working on is a database that shows the different forms and
regulations for different lines of business my company deals with. The query
is regarding a specific form that varies from state to state. For example:

State "A" uses ABC form
State "B" uses XYZ form
State "C" uses 123 form
State "D" uses ABC form and XYZ form
State "E" uses XYZ and 123 form
State “F†uses ABC, XYZ and 123 form

What I want to be able to do is to pull the information on which states use
XYZ form. I've been able to build a parameter for the first form field, but
in the cases where multiple forms are used, I am having issues. The problem
I am having is that I can't seem to find a way to "skip" the first form field
to get information from the second (&/or third) form fields without entering
information in the first form field. (I’m still not very knowledgeable
with Visual Basic Editor and other code based applications, but can sort of
piece my way through them with help)

Thanks again!

Can you tell me how the data for forms used is captured? Ie. Are there three
separate fields?

 

[Carma]

Hello and thanks in advance for answering my question!

[quoted text clipped - 21 lines]

Thanks again!

Can you tell me how the data for forms used is captured? Ie. Are there three
separate fields?

the reason I ask this is if there are 3 separate fields you can create a
simple query which includes the field 'State' and in your Criteria just
create three separate conditions like, [tblState]![FormUsed1]="ABC",
[tblState]![FormUsed2]="ABC", [tblState]![FormUsed3]="ABC" Then if either
one of the fields used the form you wanted it will tell you. Now in this
simply example you have to manually enter in the form name three times but
you can create ways so that the query gets this info automatically(ie from a
form you create etc)

 

[John W. Vinson]

Hello and thanks in advance for answering my question!

I am working on a query involving 3 seperate fields. What I want to do, is
to be able to use parameters to pull information from the three different
fields. What I am working on is a database that shows the different forms and
regulations for different lines of business my company deals with. The query
is regarding a specific form that varies from state to state. For example:

State "A" uses ABC form
State "B" uses XYZ form
State "C" uses 123 form
State "D" uses ABC form and XYZ form
State "E" uses XYZ and 123 form
State “F” uses ABC, XYZ and 123 form

What I want to be able to do is to pull the information on which states use
XYZ form. I've been able to build a parameter for the first form field, but
in the cases where multiple forms are used, I am having issues. The problem
I am having is that I can't seem to find a way to "skip" the first form field
to get information from the second (&/or third) form fields without entering
information in the first form field. (I’m still not very knowledgeable
with Visual Basic Editor and other code based applications, but can sort of
piece my way through them with help)

Thanks again!

Sounds like you have a Many to Many relationship: each state uses
multiple forms, and each form uses multiple states. It sounds like you
have one *FIELD* per form - that's not a correct design! Instead, you
need one *RECORD* per form. This needs three tables: a table of
States; a table of Forms; and a table of FormsUsed. E.g.

States
AK, Alaska
AL, Alabama
AR, Arkansas
AZ, Arizona
...

Forms
ABC, This is the ABC form for filing....
XYZ, ...
123, ...

FormsUsed
AL ABC
AK ABC
AK 123
AL XYZ
AL 123

On a Form you can use a Form (Access form here, not one of your state
forms) based on the States table or on your main data table, with a
Subform linked to it by the state code to display the forms used in
that state. No VBA code whatsoever is needed to do this.

John W. Vinson [MVP]