Query....Queries!

[Guest]

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its possible)
create a query where - Criteria is Between [start date] and [end date] but
for all four of them at the same time (I dont want to have to do it for each
field separately).
If anyone can help I would appreciate it - and please note I am new at this
so please keep the explanation simple if possible.

Thanks

 

[John Spencer]

Assuming that you are looking for a match if ANY one of the fields matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of putting the
criteria on separate rows (in design view) put the criteria all on the same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John and thank you very much it works great ! how simple!

Assuming that you are looking for a match if ANY one of the fields matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of putting the
criteria on separate rows (in design view) put the criteria all on the same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its possible)
create a query where - Criteria is Between [start date] and [end date] but
for all four of them at the same time (I dont want to have to do it for
each
field separately).
If anyone can help I would appreciate it - and please note I am new at
this
so please keep the explanation simple if possible.

Thanks

 

[Guest]

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however when I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

Assuming that you are looking for a match if ANY one of the fields matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of putting the
criteria on separate rows (in design view) put the criteria all on the same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its possible)
create a query where - Criteria is Between [start date] and [end date] but
for all four of them at the same time (I dont want to have to do it for
each
field separately).
If anyone can help I would appreciate it - and please note I am new at
this
so please keep the explanation simple if possible.

Thanks

 

[John Spencer]

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names CUSTOMER ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or all of the
fields Option1 to Option4.

If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.

If you want a range of dates that are defined when the query is executed you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and is
not available from the database, then it must be manually input. Do you or
the users have some specific way they want to input the data - and telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

Assuming that you are looking for a match if ANY one of the fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end date]
but
for all four of them at the same time (I dont want to have to do it for
each
field separately).
If anyone can help I would appreciate it - and please note I am new at
this
so please keep the explanation simple if possible.

Thanks

 

[Guest]

Okay what I am saying is this when the Between [start date] and [end date]
is on the same line of the query - if all 4 options have data in them
command works fine - But if only one option has data then it does not work.
originally I had entered in the criteria stair-stepped - but the users did
not like that because they had to enter the date range for every option.
What they want is to enter the date range just once for all four options -
1/12/07, is an example of how the date field is. Telepathy would be suit me -
I wouldnt have to ask dum questions!

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names CUSTOMER ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or all of the
fields Option1 to Option4.

If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.

If you want a range of dates that are defined when the query is executed you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and is
not available from the database, then it must be manually input. Do you or
the users have some specific way they want to input the data - and telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

Assuming that you are looking for a match if ANY one of the fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end date]
but
for all four of them at the same time (I dont want to have to do it for
each
field separately).
If anyone can help I would appreciate it - and please note I am new at
this
so please keep the explanation simple if possible.

Thanks

 

[John Spencer]

Do you want the record returned if ANY of the option fields meets the
criteria? If so then you need to stair-step. IF you enter EXACTLY the same
criteria statement in each criteria then you only get prompted ONCE and the
same date range will be applied to each option.

IF the criteria has to be met for each option field then you put the
criteria under each option on the same row.

If the criteria has to be met for each option field UNLESS the field is
empty then the process becomes much more complex.

SO are you trying to do
-- Match in any field
-- Match in all fields
-- Match in all fields (but blanks also match)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay what I am saying is this when the Between [start date] and [end date]
is on the same line of the query - if all 4 options have data in them
command works fine - But if only one option has data then it does not
work.
originally I had entered in the criteria stair-stepped - but the users did
not like that because they had to enter the date range for every option.
What they want is to enter the date range just once for all four options -
1/12/07, is an example of how the date field is. Telepathy would be suit
me -
I wouldnt have to ask dum questions!

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names CUSTOMER
ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or all of
the
fields Option1 to Option4.

If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.

If you want a range of dates that are defined when the query is executed
you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and
is
not available from the database, then it must be manually input. Do you
or
the users have some specific way they want to input the data - and
telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however
when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users
dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

:

Assuming that you are looking for a match if ANY one of the fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of
putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end
date]
but
for all four of them at the same time (I dont want to have to do it
for
each
field separately).
If anyone can help I would appreciate it - and please note I am new
at
this
so please keep the explanation simple if possible.

Thanks

 

[Guest]

OKAY THAT IS WHAT I WANT BUT I MUST BE DOING SOMETHING WRONG BECAUSE THAT IS
WHAT I DID THE FIRST TIME AND IT PROMPTS ME FOR OPTION 1, OPTION 2, OPTION 3,
OPTION 4 - IS THERE SOMETHING ELSE I HAVE TO ENTER IN ONE OF THE OTHER
FIELDS? HONESTLY IT PROMPTS ME 4 TIMES!

Do you want the record returned if ANY of the option fields meets the
criteria? If so then you need to stair-step. IF you enter EXACTLY the same
criteria statement in each criteria then you only get prompted ONCE and the
same date range will be applied to each option.

IF the criteria has to be met for each option field then you put the
criteria under each option on the same row.

If the criteria has to be met for each option field UNLESS the field is
empty then the process becomes much more complex.

SO are you trying to do
-- Match in any field
-- Match in all fields
-- Match in all fields (but blanks also match)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay what I am saying is this when the Between [start date] and [end date]
is on the same line of the query - if all 4 options have data in them
command works fine - But if only one option has data then it does not
work.
originally I had entered in the criteria stair-stepped - but the users did
not like that because they had to enter the date range for every option.
What they want is to enter the date range just once for all four options -
1/12/07, is an example of how the date field is. Telepathy would be suit
me -
I wouldnt have to ask dum questions!

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names CUSTOMER
ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or all of
the
fields Option1 to Option4.

If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.

If you want a range of dates that are defined when the query is executed
you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and
is
not available from the database, then it must be manually input. Do you
or
the users have some specific way they want to input the data - and
telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however
when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users
dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

:

Assuming that you are looking for a match if ANY one of the fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of
putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end
date]
but
for all four of them at the same time (I dont want to have to do it
for
each
field separately).
If anyone can help I would appreciate it - and please note I am new
at
this
so please keep the explanation simple if possible.

Thanks

 

[Jason Lepack]

Chill out. None of the people here are obligated to give you a
response. Most of the people who post on these newsgroups are people
who have "real jobs" as well, we do this on our own time. Getting
frustrated with them will not make it any eaiser for you to get help.

I suggest you take a course in Microsoft Access. What you are dealing
with at this point is in essence a very basic Access question, and
though we do see them daily, should be simple for anyone with basic
knowledge of Access.

With that out of the road, on to your problem:

If it's prompting you to input Option 1, Option 2, Option 3, and
Option 4, then it means that those are not actually the names of the
fields in the table. If they are then they may need [square brackets]
around the name. It is usually best to avoid spaces in field names.

Also make sure that you have selected your table by clicking "Query"-

"Show Table..."

Instead of typing in the field names in the query I would double click
the field in the table and it will automatically drop the field into
the field selector.

Cheers,
Jason Lepack

OKAY THAT IS WHAT I WANT BUT I MUST BE DOING SOMETHING WRONG BECAUSE THAT IS
WHAT I DID THE FIRST TIME AND IT PROMPTS ME FOR OPTION 1, OPTION 2, OPTION 3,
OPTION 4 - IS THERE SOMETHING ELSE I HAVE TO ENTER IN ONE OF THE OTHER
FIELDS? HONESTLY IT PROMPTS ME 4 TIMES!

Do you want the record returned if ANY of the option fields meets the
criteria? If so then you need to stair-step. IF you enter EXACTLY the same
criteria statement in each criteria then you only get prompted ONCE and the
same date range will be applied to each option.

IF the criteria has to be met for each option field then you put the
criteria under each option on the same row.

If the criteria has to be met for each option field UNLESS the field is
empty then the process becomes much more complex.

SO are you trying to do
-- Match in any field
-- Match in all fields
-- Match in all fields (but blanks also match)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay what I am saying is this when the Between [start date] and [end date]
is on the same line of the query - if all 4 options have data in them
command works fine - But if only one option has data then it does not
work.
originally I had entered in the criteria stair-stepped - but the users did
not like that because they had to enter the date range for every option.
What they want is to enter the date range just once for all four options -
1/12/07, is an example of how the date field is. Telepathy would be suit
me -
I wouldnt have to ask dum questions!
:
I'm not sure I understand what you are saying.
I understood you to say that you have 6 fields with the names CUSTOMER
ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.
Further you wanted to search for a date value existing in any or all of
the
fields Option1 to Option4.
If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.
If you want a range of dates that are defined when the query is executed
you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and
is
not available from the database, then it must be manually input. Do you
or
the users have some specific way they want to input the data - and
telepathy
is not an acceptable answer?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however
when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users
dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B
:
Assuming that you are looking for a match if ANY one of the fields
matches
then
Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):
Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):
Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):
Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]
If all four Options have to be between the range then instead of
putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I want to create a query where:
FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4
The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end
date]
but
for all four of them at the same time (I dont want to have to do it
for
each
field separately).
If anyone can help I would appreciate it - and please note I am new
at
this
so please keep the explanation simple if possible.
Thanks- Hide quoted text -

- Show quoted text -

 

[Guest]

Excuse me but I dont think any of my questions or responses were demanding
and I did not obligate anyone to give me a reply. I did not get frustrated
with anyone but myself! okay!? so with that out of the way - John thank you
very much for all your help - problem solved - the Access I am using is in
Greek ! so its my problem that I couldnt figure out the correct commands.

Chill out. None of the people here are obligated to give you a
response. Most of the people who post on these newsgroups are people
who have "real jobs" as well, we do this on our own time. Getting
frustrated with them will not make it any eaiser for you to get help.

I suggest you take a course in Microsoft Access. What you are dealing
with at this point is in essence a very basic Access question, and
though we do see them daily, should be simple for anyone with basic
knowledge of Access.

With that out of the road, on to your problem:

If it's prompting you to input Option 1, Option 2, Option 3, and
Option 4, then it means that those are not actually the names of the
fields in the table. If they are then they may need [square brackets]
around the name. It is usually best to avoid spaces in field names.

Also make sure that you have selected your table by clicking "Query"-

"Show Table..."

Instead of typing in the field names in the query I would double click
the field in the table and it will automatically drop the field into
the field selector.

Cheers,
Jason Lepack

OKAY THAT IS WHAT I WANT BUT I MUST BE DOING SOMETHING WRONG BECAUSE THAT IS
WHAT I DID THE FIRST TIME AND IT PROMPTS ME FOR OPTION 1, OPTION 2, OPTION 3,
OPTION 4 - IS THERE SOMETHING ELSE I HAVE TO ENTER IN ONE OF THE OTHER
FIELDS? HONESTLY IT PROMPTS ME 4 TIMES!

Do you want the record returned if ANY of the option fields meets the
criteria? If so then you need to stair-step. IF you enter EXACTLY the same
criteria statement in each criteria then you only get prompted ONCE and the
same date range will be applied to each option.

IF the criteria has to be met for each option field then you put the
criteria under each option on the same row.

If the criteria has to be met for each option field UNLESS the field is
empty then the process becomes much more complex.

SO are you trying to do
-- Match in any field
-- Match in all fields
-- Match in all fields (but blanks also match)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay what I am saying is this when the Between [start date] and [end date]
is on the same line of the query - if all 4 options have data in them
command works fine - But if only one option has data then it does not
work.
originally I had entered in the criteria stair-stepped - but the users did
not like that because they had to enter the date range for every option.
What they want is to enter the date range just once for all four options -
1/12/07, is an example of how the date field is. Telepathy would be suit
me -
I wouldnt have to ask dum questions!

"John Spencer" wrote:

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names CUSTOMER
ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or all of
the
fields Option1 to Option4.

If that is the case then you would need to place all four of the opton
fields in the query with the criteria stair-stepped as I have indicated.

If you want a range of dates that are defined when the query is executed
you
have to have some method of inputting that range or of getting the range
from some place. How do you (or your users) know what dates to use as a
start date and an end date? If the date range is decided by the user and
is
not available from the database, then it must be manually input. Do you
or
the users have some specific way they want to input the data - and
telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work because I was
working with test data where all 4 options fields had data. however
when
I
tested it with only one of the option fields filled in it did not work.
the first option you gave me is the one I tried first but the users
dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

"John Spencer" wrote:

Assuming that you are looking for a match if ANY one of the fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead of
putting
the
criteria on separate rows (in design view) put the criteria all on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if its
possible)
create a query where - Criteria is Between [start date] and [end
date]
but
for all four of them at the same time (I dont want to have to do it
for
each
field separately).
If anyone can help I would appreciate it - and please note I am new
at
this
so please keep the explanation simple if possible.

Thanks- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Jason's comment was based on the fact that you typed your message in ALL
CAPITAL LETTERS. That is the equivalent in the newsgroup of SHOUTING at
someone.

I'm glad that you solved the problem. Come back again, we really do try to
be helpful..

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Excuse me but I dont think any of my questions or responses were demanding
and I did not obligate anyone to give me a reply. I did not get
frustrated
with anyone but myself! okay!? so with that out of the way - John thank
you
very much for all your help - problem solved - the Access I am using is in
Greek ! so its my problem that I couldnt figure out the correct commands.

Chill out. None of the people here are obligated to give you a
response. Most of the people who post on these newsgroups are people
who have "real jobs" as well, we do this on our own time. Getting
frustrated with them will not make it any eaiser for you to get help.

I suggest you take a course in Microsoft Access. What you are dealing
with at this point is in essence a very basic Access question, and
though we do see them daily, should be simple for anyone with basic
knowledge of Access.

With that out of the road, on to your problem:

If it's prompting you to input Option 1, Option 2, Option 3, and
Option 4, then it means that those are not actually the names of the
fields in the table. If they are then they may need [square brackets]
around the name. It is usually best to avoid spaces in field names.

Also make sure that you have selected your table by clicking "Query"-

"Show Table..."

Instead of typing in the field names in the query I would double click
the field in the table and it will automatically drop the field into
the field selector.

Cheers,
Jason Lepack

OKAY THAT IS WHAT I WANT BUT I MUST BE DOING SOMETHING WRONG BECAUSE
THAT IS
WHAT I DID THE FIRST TIME AND IT PROMPTS ME FOR OPTION 1, OPTION 2,
OPTION 3,
OPTION 4 - IS THERE SOMETHING ELSE I HAVE TO ENTER IN ONE OF THE OTHER
FIELDS? HONESTLY IT PROMPTS ME 4 TIMES!



:
Do you want the record returned if ANY of the option fields meets the
criteria? If so then you need to stair-step. IF you enter EXACTLY
the same
criteria statement in each criteria then you only get prompted ONCE
and the
same date range will be applied to each option.

IF the criteria has to be met for each option field then you put the
criteria under each option on the same row.

If the criteria has to be met for each option field UNLESS the field
is
empty then the process becomes much more complex.

SO are you trying to do
-- Match in any field
-- Match in all fields
-- Match in all fields (but blanks also match)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay what I am saying is this when the Between [start date] and
[end date]
is on the same line of the query - if all 4 options have data in
them
command works fine - But if only one option has data then it does
not
work.
originally I had entered in the criteria stair-stepped - but the
users did
not like that because they had to enter the date range for every
option.
What they want is to enter the date range just once for all four
options -
1/12/07, is an example of how the date field is. Telepathy would be
suit
me -
I wouldnt have to ask dum questions!

:

I'm not sure I understand what you are saying.

I understood you to say that you have 6 fields with the names
CUSTOMER
ID,
CUSTOMER NAME, OPTION 1, OPTION 2, OPTION 3, and OPTION 4.

Further you wanted to search for a date value existing in any or
all of
the
fields Option1 to Option4.

If that is the case then you would need to place all four of the
opton
fields in the query with the criteria stair-stepped as I have
indicated.

If you want a range of dates that are defined when the query is
executed
you
have to have some method of inputting that range or of getting the
range
from some place. How do you (or your users) know what dates to
use as a
start date and an end date? If the date range is decided by the
user and
is
not available from the database, then it must be manually input.
Do you
or
the users have some specific way they want to input the data - and
telepathy
is not an acceptable answer?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear John,
I did the second option you gave me and it seemed to work
because I was
working with test data where all 4 options fields had data.
however
when
I
tested it with only one of the option fields filled in it did
not work.
the first option you gave me is the one I tried first but the
users
dont
like filling in start date - end date -.
Is there anything else I can try?
thanx again
B

:

Assuming that you are looking for a match if ANY one of the
fields
matches
then

Field: Option 1
Criteria (1): Between [start date] and [end date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Option 2
Criteria (1):
Criteria (2): Between [start date] and [end date]
Criteria (3):
Criteria (4):

Field: Option 3
Criteria (1):
Criteria (2):
Criteria (3): Between [start date] and [end date]
Criteria (4):

Field: Option 4
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): Between [start date] and [end date]

If all four Options have to be between the range then instead
of
putting
the
criteria on separate rows (in design view) put the criteria all
on the
same
row.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query where:

FIELD 1 CUSTOMER ID
FIELD 2 CUSTOMER NAME
FIELD 3 OPTION 1
FIELD 4 OPTION 2
FIELD 5 OPTION 3
FIELD 6 OPTION 4

The option1-4 is date format - and what I want to do is (if
its
possible)
create a query where - Criteria is Between [start date] and
[end
date]
but
for all four of them at the same time (I dont want to have to
do it
for
each
field separately).
If anyone can help I would appreciate it - and please note I
am new
at
this
so please keep the explanation simple if possible.

Thanks- Hide quoted text -

- Show quoted text -