Query (Microsoft Access Database)

Query
message from Akshay on 6 May 2004
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 replied to Akshay on 13 May 2004

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

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

DDM replied to Akshay on 6 May 2004

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

Akshay replied to DDM on 7 May 2004

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" <DDMara@YaNOSPAMhoo.com> wrote in message news:<uVJIKv8MEHA.3972@TK2MSFTNGP10.phx.gbl>...

Brendan Reynolds replied to Akshay on 7 May 2004

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

Akshay replied to Brendan Reynolds on 11 May 2004

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 news:<eR$EC5DNEHA.684@TK2MSFTNGP09.phx.gbl>...

Brendan Reynolds replied to Akshay on 12 May 2004

... 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.

Akshay replied to Brendan Reynolds on 13 May 2004

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 news:<u0I6#PAOEHA.1312@TK2MSFTNGP12.phx.gbl>...

Brendan Reynolds replied to Akshay on 13 May 2004

"(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).

Akshay replied to Brendan Reynolds on 17 May 2004

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
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 news:<#fG#imPOEHA.3712@TK2MSFTNGP10.phx.gbl>...

Akshay replied to Brendan Reynolds on 17 May 2004

Akshay replied to Akshay on 18 May 2004

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

Brendan Reynolds replied to Akshay on 17 May 2004

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.

Archived message: Query (Microsoft Access Forms)