Null values in parameter query

[Nitesh9999]

Hi first time here goes…

I’m querying a table to filter by month using a parameter. For example when you run the query the parameter box pops up and you enter the month for which you want the data from eg month 11 for November’s records. However there are cases where I want all the records and for this I used:

Criteria Like [Enter Category ID:]
Or [Enter Category ID:] Is Null

This meant that if I entered nothing in the parameter box all the records for all months appeared and this worked great.

The problem occurred when I tried using the above for 2 fields. What I wanted was to filter by both month and city (basically the city where the transaction took place). But I also wanted the ability to enter a month value in the first parameter box and leave the city parameter box blank. This should return all records for a particular month from all cities. But when I left the second box blank (or vice versa and left the first blank and entered a city in the second) it returned no records.

Is there a way of resolving this so I can filter by only entering a value in one parameter box and leaving the other blank?

I know its messy and I haven’t explained myself very well but any help would be appreciated, thanks.

 

[KenHigg]

When it ask for a date, could you enter an asterisk?


Ken

 

[Nitesh9999]

Thanks mate, but putting in an asterisk (*) in one of the boxes returns no records grrr.

Anyone else??

 

[KenHigg]

I would just bite the bullet and do a pop up form to drive this...


Ken

 

[Nitesh9999]

Now you've got me!! Was that a joke or a serious suggestion? Would a form of some sort help me?

 

[KenHigg]

I would think so. When you press the print button, a form pops up with a text box for a date and maybe a couple of button that lets print different versions of your report. Then the report uses stuff off the pop up form as criteria for the report's data source...

It's a good concept - reusing reports, etc, instead of doing a separate report when there very little difference in the two...

Ken

 

[Nitesh9999]

I think I get you, any resources/examples you could point me to??

Thanks.

 

[KenHigg]

Hum....

Sorry, that's about my .2 cents

Ken

 

[Jon K]

Take a look at the first sample database in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=78326

.

 

[Nitesh9999]

Thanks Jon. That would work fine (here it comes) but...

I need to put the data into a report (which will be predesigned). So pulling up the results this way wouldnt work. However...


I have been researching and an IIF statement should be able to carry out what I need to do.

Something like this isd what someone on another forum suggested
iif(isnull([Month value]),"*",[Month value])

but this returns no results at all ever.. I was wondering if there is an error because the theory behind this statement is right.

 

[Jon K]

If the query works, the report based on the query should also work. I have added a report in the database.

.

..... someone on another forum suggested
iif(isnull([Month value]),"*",[Month value])

but this returns no results at all ever..

To use "*", you need the Like operator.
But Like "*" will not work if the field contains Null values. The Null values will not be returned. That's why I didn't use IIF in the criteria.
.

 

[Nitesh9999]

You are simply amazing!!!

That has worked a treat. Thank You Very Much!!


Now one final improvement if possible.


The database i am building has alot of tables holding information from different districts. Is there anyway to select the table I want to select the fields from? I think I would need a dropdown menu at the top of the form to select the table then the relevant fields could be selected in the next 2 boxes which are already in the form (field A and field B in your example).

This would mean I wouldn’t have to create around 15 different forms.

Thanks again.

Edit I think i need to change the rowsource on the 2 field select dropdowns. They would need to link to the new dropdown box at the top of the form which selects the table. I have attached the DB below with a new form to show the extra dropdown which i would need.