Query

[Akshay]

I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[DDM]

Akshay, this parameter prompt will return all the data for that field if the
user enters no criteria:

Like [What is your response?] & "*"

 

[Akshay]

Thank you very much.. Im going to try it out right now!!!.. hope this
solves my problem

Akshay, this parameter prompt will return all the data for that field if the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Akshay]

Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

Akshay, this parameter prompt will return all the data for that field if the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Brendan Reynolds]

The wild card ("*") would work OK with text fields, but is unlikely to work
reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR ([Start Date]
Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that field if the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Akshay]

I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
... the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!

The wild card ("*") would work OK with text fields, but is unlikely to work
reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR ([Start Date]
Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that field if the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Brendan Reynolds]

.... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is unlikely to work
reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR ([Start Date]
Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that

field if
the

user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Akshay]

Brendan.. thanks for your help till now. but its still not workingi ve
put follwoing criterias....

Under Date field...
(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL

Under Depository ID
[Enter Depository ID] OR [Enter Depository ID] IS NULL

UNder Deposit ID Field
[Enter Deposit ID] OR [Enter Deposit ID] IS NULL

Still dosnt work for example if enetr the begin date , the end date
and the depository ID and leave the deposit id dialog box blank
(because i need all the deposit types) and press enter.. it gives me
zero records...

... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is unlikely to work
reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR ([Start Date]
Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that

field if
the

user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Brendan Reynolds]

The date criteria is not right ...

"(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL"

To keep things simple for now, let's assume that the user will either enter
both dates, or neither. We can look at the more complex situation where the
user may enter one date but not both later.

In the criteria row in the date field column enter the following ...

(Between [Enter Start Date] And [Enter End Date]) OR ([Enter Start Date] IS
NULL And [Enter End Date] IS NULL)

Access will probably move things around a bit when you save the query,
creating new columns for the parameter criteria, but it will allow you to
enter the criteria this way, which is, in my opinion, easier.

If you're still stuck after that, try posting the actual SQL instead of
trying to describe what the query looks like in design view. (Choose SQL
View from the View menu, make sure all the text is selected, press Ctrl+C to
copy it, then paste it into your newsreader).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan.. thanks for your help till now. but its still not workingi ve
put follwoing criterias....

Under Date field...
(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL

Under Depository ID
[Enter Depository ID] OR [Enter Depository ID] IS NULL

UNder Deposit ID Field
[Enter Deposit ID] OR [Enter Deposit ID] IS NULL

Still dosnt work for example if enetr the begin date , the end date
and the depository ID and leave the deposit id dialog box blank
(because i need all the deposit types) and press enter.. it gives me
zero records...

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is unlikely

to
work

reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR

([Start
Date]

Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it

impossible
for

me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that

field if
the

user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all

the
data

if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[MaxZeta]

I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all the data
if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

Check Stany component : http://www.RealTimeInformatica.it/stany/

It's a Sql Code Manager: Remove unwanted parameters...

 

[Akshay]

Brendan,
I think I have it right this time.... like i said i have three
diffrent parameters... about the dates.. well it would either both the
dates or neither of them... no one is going to put one date.. so thats
not an issue...

here is the sql view with all three parameters... please see if there
is something wrong...i really need this thing to work..

SELECT [DAILY DEPOSITS FY2004].Date, Depositories.Depositories, [DAILY
DEPOSITS FY2004].Amount, [Transaction Type].[Transaction Type]
FROM [Transaction Type] INNER JOIN (Depositories INNER JOIN [DAILY
DEPOSITS FY2004] ON Depositories.ID = [DAILY DEPOSITS
FY2004].Depositories) ON [Transaction Type].ID = [DAILY DEPOSITS
FY2004].[Transaction Type]
WHERE ((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End
Date]) AND ((Depositories.Depositories)=[Enter Depository ID]) AND
(([Transaction Type].[Transaction Type])=[Enter Transaction Type])) OR
((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End Date])
AND (([Transaction Type].[Transaction Type])=[Enter Transaction Type])
AND (([Enter Depository ID]) Is Null)) OR ((([DAILY DEPOSITS
FY2004].Date) Between [Start Date] And [End Date]) AND
((Depositories.Depositories)=[Enter Depository ID]) AND (([Enter
Transaction Type]) Is Null)) OR ((([DAILY DEPOSITS FY2004].Date)
Between [Start Date] And [End Date]) AND (([Enter Depository ID]) Is
Null) AND (([Enter Transaction Type]) Is Null)) OR ((([Start Date]) Is
Null) AND (([End Date]) Is Null));

The date criteria is not right ...

"(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL"

To keep things simple for now, let's assume that the user will either enter
both dates, or neither. We can look at the more complex situation where the
user may enter one date but not both later.

In the criteria row in the date field column enter the following ...

(Between [Enter Start Date] And [Enter End Date]) OR ([Enter Start Date] IS
NULL And [Enter End Date] IS NULL)

Access will probably move things around a bit when you save the query,
creating new columns for the parameter criteria, but it will allow you to
enter the criteria this way, which is, in my opinion, easier.

If you're still stuck after that, try posting the actual SQL instead of
trying to describe what the query looks like in design view. (Choose SQL
View from the View menu, make sure all the text is selected, press Ctrl+C to
copy it, then paste it into your newsreader).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan.. thanks for your help till now. but its still not workingi ve
put follwoing criterias....

Under Date field...
(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL

Under Depository ID
[Enter Depository ID] OR [Enter Depository ID] IS NULL

UNder Deposit ID Field
[Enter Deposit ID] OR [Enter Deposit ID] IS NULL

Still dosnt work for example if enetr the begin date , the end date
and the depository ID and leave the deposit id dialog box blank
(because i need all the deposit types) and press enter.. it gives me
zero records...

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is unlikely

to
work

reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR

([Start
Date]

Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it

impossible
for

me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that
field if
the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all

the
data

if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Akshay]

Brendan,
I think I have it right this time.... like i said i have three
diffrent parameters... about the dates.. well it would either both the
dates or neither of them... no one is going to put one date.. so thats
not an issue...

here is the sql view with all three parameters... please see if there
is something wrong...i really need this thing to work..

SELECT [DAILY DEPOSITS FY2004].Date, Depositories.Depositories, [DAILY
DEPOSITS FY2004].Amount, [Transaction Type].[Transaction Type]
FROM [Transaction Type] INNER JOIN (Depositories INNER JOIN [DAILY
DEPOSITS FY2004] ON Depositories.ID = [DAILY DEPOSITS
FY2004].Depositories) ON [Transaction Type].ID = [DAILY DEPOSITS
FY2004].[Transaction Type]
WHERE ((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End
Date]) AND ((Depositories.Depositories)=[Enter Depository ID]) AND
(([Transaction Type].[Transaction Type])=[Enter Transaction Type])) OR
((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End Date])
AND (([Transaction Type].[Transaction Type])=[Enter Transaction Type])
AND (([Enter Depository ID]) Is Null)) OR ((([DAILY DEPOSITS
FY2004].Date) Between [Start Date] And [End Date]) AND
((Depositories.Depositories)=[Enter Depository ID]) AND (([Enter
Transaction Type]) Is Null)) OR ((([DAILY DEPOSITS FY2004].Date)
Between [Start Date] And [End Date]) AND (([Enter Depository ID]) Is
Null) AND (([Enter Transaction Type]) Is Null)) OR ((([Start Date]) Is
Null) AND (([End Date]) Is Null));

The date criteria is not right ...

"(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL"

To keep things simple for now, let's assume that the user will either enter
both dates, or neither. We can look at the more complex situation where the
user may enter one date but not both later.

In the criteria row in the date field column enter the following ...

(Between [Enter Start Date] And [Enter End Date]) OR ([Enter Start Date] IS
NULL And [Enter End Date] IS NULL)

Access will probably move things around a bit when you save the query,
creating new columns for the parameter criteria, but it will allow you to
enter the criteria this way, which is, in my opinion, easier.

If you're still stuck after that, try posting the actual SQL instead of
trying to describe what the query looks like in design view. (Choose SQL
View from the View menu, make sure all the text is selected, press Ctrl+C to
copy it, then paste it into your newsreader).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan.. thanks for your help till now. but its still not workingi ve
put follwoing criterias....

Under Date field...
(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL

Under Depository ID
[Enter Depository ID] OR [Enter Depository ID] IS NULL

UNder Deposit ID Field
[Enter Deposit ID] OR [Enter Deposit ID] IS NULL

Still dosnt work for example if enetr the begin date , the end date
and the depository ID and leave the deposit id dialog box blank
(because i need all the deposit types) and press enter.. it gives me
zero records...

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is unlikely

to
work

reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR

([Start
Date]

Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it

impossible
for

me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date]. Now do I
add & "*" to this expression.I wrote the following expression and even
after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I leave the
"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that
field if
the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank or "null" if
a parameter box is left balnk by the user so that I get all

the
data

if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Brendan Reynolds]

There are potential problems with having a field named 'Date', because Date
is a keyword. I'd advise changing the field name if possible (something
like, e.g. DepositDate). If for whatever reason you can't change the name,
try enclosing it in square brackets.

The SQL is getting difficult to read at this stage, but as far as I can see,
the final condition - OR ((([Start Date]) Is Null) AND (([End Date]) Is
Null)) - ignores the other two parameters. That is to say, unless I'm
reading it wrong, I think if you leave Start Date and End Date Null, you'll
get all records, ignoring anything you might have entered for the other two
paramters, depository and transaction type, which is probably not what you
want? If I'm right, you'll need to add something like "AND DepositoryID =
[Depository ID] AND TransactionType = [Transaction Type]" to that last
condition.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan,
I think I have it right this time.... like i said i have three
diffrent parameters... about the dates.. well it would either both the
dates or neither of them... no one is going to put one date.. so thats
not an issue...

here is the sql view with all three parameters... please see if there
is something wrong...i really need this thing to work..

SELECT [DAILY DEPOSITS FY2004].Date, Depositories.Depositories, [DAILY
DEPOSITS FY2004].Amount, [Transaction Type].[Transaction Type]
FROM [Transaction Type] INNER JOIN (Depositories INNER JOIN [DAILY
DEPOSITS FY2004] ON Depositories.ID = [DAILY DEPOSITS
FY2004].Depositories) ON [Transaction Type].ID = [DAILY DEPOSITS
FY2004].[Transaction Type]
WHERE ((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End
Date]) AND ((Depositories.Depositories)=[Enter Depository ID]) AND
(([Transaction Type].[Transaction Type])=[Enter Transaction Type])) OR
((([DAILY DEPOSITS FY2004].Date) Between [Start Date] And [End Date])
AND (([Transaction Type].[Transaction Type])=[Enter Transaction Type])
AND (([Enter Depository ID]) Is Null)) OR ((([DAILY DEPOSITS
FY2004].Date) Between [Start Date] And [End Date]) AND
((Depositories.Depositories)=[Enter Depository ID]) AND (([Enter
Transaction Type]) Is Null)) OR ((([DAILY DEPOSITS FY2004].Date)
Between [Start Date] And [End Date]) AND (([Enter Depository ID]) Is
Null) AND (([Enter Transaction Type]) Is Null)) OR ((([Start Date]) Is
Null) AND (([End Date]) Is Null));



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The date criteria is not right ...

"(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL"

To keep things simple for now, let's assume that the user will either enter
both dates, or neither. We can look at the more complex situation where the
user may enter one date but not both later.

In the criteria row in the date field column enter the following ...

(Between [Enter Start Date] And [Enter End Date]) OR ([Enter Start Date] IS
NULL And [Enter End Date] IS NULL)

Access will probably move things around a bit when you save the query,
creating new columns for the parameter criteria, but it will allow you to
enter the criteria this way, which is, in my opinion, easier.

If you're still stuck after that, try posting the actual SQL instead of
trying to describe what the query looks like in design view. (Choose SQL
View from the View menu, make sure all the text is selected, press Ctrl+C to
copy it, then paste it into your newsreader).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan.. thanks for your help till now. but its still not workingi ve
put follwoing criterias....

Under Date field...
(Between [Enter Start Date] and [Enter End Date]) OR (Between [Enter
Start Date] and [Enter End Date])IS NULLL

Under Depository ID
[Enter Depository ID] OR [Enter Depository ID] IS NULL

UNder Deposit ID Field
[Enter Deposit ID] OR [Enter Deposit ID] IS NULL

Still dosnt work for example if enetr the begin date , the end date
and the depository ID and leave the deposit id dialog box blank
(because i need all the deposit types) and press enter.. it gives me
zero records...

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

... AND DepositID = [Enter Deposit ID] OR [Enter Deposit ID] IS NULL

It's the same principle that we used earlier with the dates. We want all
records where the field matches the parameter or the parameter is Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it

impossible
for

me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


I have tried that.. I think I did not explain my problem properly..
here is the struture.. its table that collects information on deposits
we have 89 diffrent depositories in over 33 states and 5 diffrent
type of deposits type... so when deposit comes i enetr the date..
choose the depoisitory ID where the deposit was made(A number) and
enter a deposit type (again a number) and thats it

Both the depository ID and Deposit Type ID are seprate tables with
look up in the main table (I use drop down in the main table to
choose)


NOw if I want know deposits between XXX and XXXX date from depository
number XX and only Deposit Type XX ...I enetr the criteria and boom
.. the problem is I want this to be A PARAMETER query where if I run
the query the following dialog boxes pop up

ENTER BEGIN DATE>
ENTER END DATE>
ENTER DEPOSITORY ID>
ENTER DEPOSIT ID>

if the user enters all the data.. the query works fine... however...
say i dont want to filter by deposit type...Example: I want all type
of deposits in depository ID# 10 between april 1st and april
30th......so i guess i leave the "ENTER DEPOSIT ID" dialog box empty
and press enter.. it gives me nothing... now HOW do I fix this!!!!!!


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

The wild card ("*") would work OK with text fields, but is

unlikely
to
work

reliably with dates. Try something like the following instead:

WHERE ([YourDateField] Between [Start Date] And [End Date]) OR ([Start
Date]
Is Null AND [End Date] Is Null)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail

replies
to

this post will be deleted without being read. Any e-mail

claiming to
be

from brenreyn at indigo dot ie that is not digitally signed by

me
with a

GlobalSign digital certificate is a forgery and should be

deleted
without

being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail,

you'll
find

a useable e-mail address at the URL above.


Dear Sir I tried ure expression and it didnt work. Actuall one the
parameters is Between [Enter Bgn Date] and [Enter End Date].

Now
do I

add & "*" to this expression.I wrote the following expression

and
even

after that If I left the two parameters blank.. I didnt return me
with any data..proly becoz I didnt enter dates..

Between [Enter Bgn Date] and [Enter End Date] & "*"


Now Iwant the query to return me data for every date.. if I

leave
the

"Enter Bgn Date" and "Enter End Date" blank

Thank you

"DDM" <[email protected]> wrote in message

Akshay, this parameter prompt will return all the data for that
field if
the
user enters no criteria:

Like [What is your response?] & "*"

--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


I want to build a parameter query assigning atleast three diffrent
parameters. I want the query to return criteria as blank

or
"null" if

a parameter box is left balnk by the user so that I get

all
the
data

if I leave a parameter blank for that field. Burnt around gazzlion
brain cells already.... i'm new to this.. some one help!!

 

[Akshay]

Thanks Brendan... Finallly it workss.....!!!!!thnx a ton