Multiple Parameters

[TheCaptain]

I am using a form to run a query with parameters. Below is what the form
looks like------- <--represents a text box and there is a search button)
Age: -----------

Salary From: -----
To: ------

Date Hired From:----------
To:-----------

Employee Number: -------------------

So, as you can see, I have two parameters with multiple inputs and two
parameters with only one input. My problem is that I can code for Age and
Employee Number using this example:

WHERE (Table1.Number=Forms!Form1.number Or
Forms!Form1.number Is Null) AND (Table1.Age=Forms!Form1.age Or
Forms!Form1.age IS NULL);

The code above works great and gives me the correct records. But, how do I
code for the multiple input parameters? I have tried and failed miserably.
I really don't know Access that well so if you have an answer, please be
descriptive

 

[KARL DEWEY]

You did not post the table1 field names for the others nor the form object
names.

Why do you need to input all of these parameters? If you are looking for
someone then the employee number is enough. It seems that you are trying to
use one form to do too many things.

 

[TheCaptain]

The table1 field names are as follows: Number, First Name, Last Name, Salary,
Date Hired

If by form object you mean form name it is simply "Form1".

I do not know if I made myself clear, this is just a test data base, my real
one will have hundreds of entries and different search parameters. I just
need help with the coding for the multiple input parameters.

Thanks

 

[KARL DEWEY]

You used AND which means that both must be true. Your table has a field for
Salary and Date Hired but form has two entries for each of these.

How would you want the two Salary of the form to interface with the table
single field?

As I said it appears you are trying to make one form do too much.

 

[TheCaptain]

Using AND makes it so that both conditions must be met and therefore my
records will be returned with the correct data.

Entering this into my criteria field (in design view) for salary:
Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto]

(salaryfrom and salaryto referring to my text box in the form)

and then for date:
Between [Forms]![Form1].[datefrom] AND [Forms]![Form1].[dateto]

(datefrom and dateto referring to my text box in the form)

The above works with the form giving me parameters to enter data in and
search through. The problem though is that there is no IS NULL to make the
statement false. It just gives me records that meet any of the conditions.
For example entering the below into the forms:

Salary From: $0
To: $1000

Date Hired From:1/1/2005
To:1/1/2008

This will give me results with Salaries between $0 and $1000 ( and different
dates) and Dates Hired from 2005 to 2008 (with different salaries). I just
only want records that have salaries between $0 and $1000 and Dates Hired
from 2005 to 2008.

Thanks

 

[KARL DEWEY]

This should work with your other part of the WHERE statement ---
AND ([salary] Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto] OR [salary] Like "*" OR [salary] Is Null)
AND ([Date Hired] Between [Forms]![Form1].[datefrom] AND
[Forms]![Form1].[dateto] OR [Date Hired] Like"*" OR [Date Hired] Is Null)


--
KARL DEWEY
Build a little - Test a little

Using AND makes it so that both conditions must be met and therefore my
records will be returned with the correct data.

Entering this into my criteria field (in design view) for salary:
Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto]

(salaryfrom and salaryto referring to my text box in the form)

and then for date:
Between [Forms]![Form1].[datefrom] AND [Forms]![Form1].[dateto]

(datefrom and dateto referring to my text box in the form)

The above works with the form giving me parameters to enter data in and
search through. The problem though is that there is no IS NULL to make the
statement false. It just gives me records that meet any of the conditions.
For example entering the below into the forms:

Salary From: $0
To: $1000

Date Hired From:1/1/2005
To:1/1/2008

This will give me results with Salaries between $0 and $1000 ( and different
dates) and Dates Hired from 2005 to 2008 (with different salaries). I just
only want records that have salaries between $0 and $1000 and Dates Hired
from 2005 to 2008.

Thanks

You used AND which means that both must be true. Your table has a field for
Salary and Date Hired but form has two entries for each of these.

How would you want the two Salary of the form to interface with the table
single field?

As I said it appears you are trying to make one form do too much.

 

[TheCaptain]

I will try that tomorrow and let you know if it works. I appreciate your help.

Thanks

This should work with your other part of the WHERE statement ---
AND ([salary] Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto] OR [salary] Like "*" OR [salary] Is Null)
AND ([Date Hired] Between [Forms]![Form1].[datefrom] AND
[Forms]![Form1].[dateto] OR [Date Hired] Like"*" OR [Date Hired] Is Null)


--
KARL DEWEY
Build a little - Test a little

Using AND makes it so that both conditions must be met and therefore my
records will be returned with the correct data.

Entering this into my criteria field (in design view) for salary:
Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto]

(salaryfrom and salaryto referring to my text box in the form)

and then for date:
Between [Forms]![Form1].[datefrom] AND [Forms]![Form1].[dateto]

(datefrom and dateto referring to my text box in the form)

The above works with the form giving me parameters to enter data in and
search through. The problem though is that there is no IS NULL to make the
statement false. It just gives me records that meet any of the conditions.
For example entering the below into the forms:

Salary From: $0
To: $1000

Date Hired From:1/1/2005
To:1/1/2008

This will give me results with Salaries between $0 and $1000 ( and different
dates) and Dates Hired from 2005 to 2008 (with different salaries). I just
only want records that have salaries between $0 and $1000 and Dates Hired
from 2005 to 2008.

Thanks

You used AND which means that both must be true. Your table has a field for
Salary and Date Hired but form has two entries for each of these.

How would you want the two Salary of the form to interface with the table
single field?

As I said it appears you are trying to make one form do too much.

--
KARL DEWEY
Build a little - Test a little


:

The table1 field names are as follows: Number, First Name, Last Name, Salary,
Date Hired

If by form object you mean form name it is simply "Form1".

I do not know if I made myself clear, this is just a test data base, my real
one will have hundreds of entries and different search parameters. I just
need help with the coding for the multiple input parameters.

Thanks

:

You did not post the table1 field names for the others nor the form object
names.

Why do you need to input all of these parameters? If you are looking for
someone then the employee number is enough. It seems that you are trying to
use one form to do too many things.
--
KARL DEWEY
Build a little - Test a little


:

I am using a form to run a query with parameters. Below is what the form
looks like------- <--represents a text box and there is a search button)
Age: -----------

Salary From: -----
To: ------

Date Hired From:----------
To:-----------

Employee Number: -------------------

So, as you can see, I have two parameters with multiple inputs and two
parameters with only one input. My problem is that I can code for Age and
Employee Number using this example:

WHERE (Table1.Number=Forms!Form1.number Or
Forms!Form1.number Is Null) AND (Table1.Age=Forms!Form1.age Or
Forms!Form1.age IS NULL);

The code above works great and gives me the correct records. But, how do I
code for the multiple input parameters? I have tried and failed miserably.
I really don't know Access that well so if you have an answer, please be
descriptive

 

[TheCaptain]

Good news. Unfortunately your coding did not work, but I played around with
it and came up with this (you were so close):

WHERE ([salary] Between Forms!Form1.salaryfrom And Forms!Form1.salaryto OR
[salary] Between Forms!Form1.salaryfrom And Forms!Form1.salaryto IS NULL);

Works like a charm! I really appreciate the help. Hopefully I won't have
any more questions but if I do I will post them

Thanks,

Ethan

I will try that tomorrow and let you know if it works. I appreciate your help.

Thanks

This should work with your other part of the WHERE statement ---
AND ([salary] Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto] OR [salary] Like "*" OR [salary] Is Null)
AND ([Date Hired] Between [Forms]![Form1].[datefrom] AND
[Forms]![Form1].[dateto] OR [Date Hired] Like"*" OR [Date Hired] Is Null)


--
KARL DEWEY
Build a little - Test a little

Using AND makes it so that both conditions must be met and therefore my
records will be returned with the correct data.

Entering this into my criteria field (in design view) for salary:
Between [Forms]![Form1].[salaryfrom] AND
[Forms]![Form1].[salaryto]

(salaryfrom and salaryto referring to my text box in the form)

and then for date:
Between [Forms]![Form1].[datefrom] AND [Forms]![Form1].[dateto]

(datefrom and dateto referring to my text box in the form)

The above works with the form giving me parameters to enter data in and
search through. The problem though is that there is no IS NULL to make the
statement false. It just gives me records that meet any of the conditions.
For example entering the below into the forms:

Salary From: $0
To: $1000

Date Hired From:1/1/2005
To:1/1/2008

This will give me results with Salaries between $0 and $1000 ( and different
dates) and Dates Hired from 2005 to 2008 (with different salaries). I just
only want records that have salaries between $0 and $1000 and Dates Hired
from 2005 to 2008.

Thanks

:

You used AND which means that both must be true. Your table has a field for
Salary and Date Hired but form has two entries for each of these.

How would you want the two Salary of the form to interface with the table
single field?

As I said it appears you are trying to make one form do too much.

--
KARL DEWEY
Build a little - Test a little


:

The table1 field names are as follows: Number, First Name, Last Name, Salary,
Date Hired

If by form object you mean form name it is simply "Form1".

I do not know if I made myself clear, this is just a test data base, my real
one will have hundreds of entries and different search parameters. I just
need help with the coding for the multiple input parameters.

Thanks

:

You did not post the table1 field names for the others nor the form object
names.

Why do you need to input all of these parameters? If you are looking for
someone then the employee number is enough. It seems that you are trying to
use one form to do too many things.
--
KARL DEWEY
Build a little - Test a little


:

I am using a form to run a query with parameters. Below is what the form
looks like------- <--represents a text box and there is a search button)
Age: -----------

Salary From: -----
To: ------

Date Hired From:----------
To:-----------

Employee Number: -------------------

So, as you can see, I have two parameters with multiple inputs and two
parameters with only one input. My problem is that I can code for Age and
Employee Number using this example:

WHERE (Table1.Number=Forms!Form1.number Or
Forms!Form1.number Is Null) AND (Table1.Age=Forms!Form1.age Or
Forms!Form1.age IS NULL);

The code above works great and gives me the correct records. But, how do I
code for the multiple input parameters? I have tried and failed miserably.
I really don't know Access that well so if you have an answer, please be
descriptive