Any MS Access whizzes out there? - Solution found, thanks

Marc

Marc

TPer Emeritus
Messages
34,282
Edit My Images
Yes
I am trying to do something in MS Access and have hit a brick wall. I have created a form into which users can enter data with which to bring back results from other tables. The input data is placed in a table, then a query is run which uses the input data as a parameter for certain fields. So far so good.

The problems occur when the user leaves a field on the form blank. If I place a "*" in the criteria field of a query then all data is returned. If I point to another table/field which has data then all matching data is returned. Does anybody know how I can get the query to return data where the lookup field is blank? I have tried an IF statement but have failed miserably.

I should point out that I have no vba or SQL knowledge and only use the basic query functions so please go easy on me where code is concerned. It's MS Access 2003 if that makes a difference.

Would be most greatful for any assistance. (y)
 
What exactly is the lookup field Marc?

It it name etc (string) or an int etc?

Also what data do you want the query to return?

edit: - what happens if you put "" in the criteria

it's been a while since I have worked with vba
 
fabs said:
I am trying to do something in MS Access and have hit a brick wall. I have created a form into which users can enter data with which to bring back results from other tables. The input data is placed in a table, then a query is run which uses the input data as a parameter for certain fields. So far so good.

The problems occur when the user leaves a field on the form blank. If I place a "*" in the criteria field of a query then all data is returned. If I point to another table/field which has data then all matching data is returned. Does anybody know how I can get the query to return data where the lookup field is blank? I have tried an IF statement but have failed miserably.

I should point out that I have no vba or SQL knowledge and only use the basic query functions so please go easy on me where code is concerned. It's MS Access 2003 if that makes a difference.

Would be most greatful for any assistance. (y)
Click to expand...

I believe what you're looking for is 'Is Null'

http://www.techonthenet.com/access/functions/advanced/isnull.php
 
That's just it Chris, I'm not using vba, just the normal query interface. Basically the user will input a parameter ( A usual one would be a country). That would go into a table. A query will then run using that entry as a criteria. Unfortunately when the field is left blank, no data is returned.

I have tried "" and * to no avail.
 
fabs said:
Just had a rethink and I don't think that's going to help. I don't want to get data back for where fields are blank, I need the query to return all data where a lookup field in another table is blank.
Click to expand...


Can you post the query you're trying, or PM it to me??
 
HI mate give this as go, there may be a quicker way but it works for me.

4098046393_263aeda824_o.jpg


you will need a query for each of the fields where you need to filter, ensuring each query is based on the last filter query you made.

It will filter the field if a value is entered or show all if blank.
 
robrevens said:
Can you post the query you're trying, or PM it to me??
Click to expand...

Just got it and the access is on my work computer. Will PM to you tomorrow if I get no joy from what haggerma has posted below. (y)

haggerma said:
HI mate give this as go, there may be a quicker way but it works for me.

4098046393_263aeda824_o.jpg


you will need a query for each of the fields where you need to filter, ensuring each query is based on the last filter query you made.

It will filter the field if a value is entered or show all if blank.
Click to expand...

Looks great, will try it tomorrow.

Cheers. (y)
 
I'm a software developer by profession, so if all avenues fail PM me the sql query. You can do this by changing the query into SQL mode, let me know if you're not sure how to switch to this mode.
 
Cheers Connor, if only I knew that on Sunday! ;)
 
John (Darkstar) knew. He told me to keep it a secret in case you started on about MS Access :LOL:

:popcorn:
 
Ok, an update. Sort of a good news/bad news scenario.

Good News: Haggerna's solution works a treat. (y)

Bad News: I need 9 of those in one query (it's an append query) and it appears to be all or nothing. It seems to require that either all the lookup fields are populated or none of them.

So :help:
 
fabs said:
Bad News: I need 9 of those in one query (it's an append query) and it appears to be all or nothing. It seems to require that either all the lookup fields are populated or none of them.

So :help:
Click to expand...

You need to split them out into separate queries and the run append off the last one.

Create a query on the data with the 1st filter.
The next query is run off the previous query with the 2nd filter
the 3rd query is run off the 2nd query with the 3rd filter ...etc

So you end up with 9 queries, each running off 1 other query, each query filtering one field.

I know this is messing and would be best run with sql but as you have no sql or vba knowledge this will be easier for you.
 
Thanks Mark, will give it a go (Probably be Monday now). Not too worried about the faff of extra queries, I'm used to it. ;)
 
All sorted now. Many thanks to Mark (Heggerma) for the solution and also thanks to all those who have replied. (y)
 
You must log in or register to reply here.
Back
Top