Parameter Query using a form

G

Guest

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
 
G

Guest

Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
 
G

Guest

WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

Ofer said:
Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
 
G

Guest

When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

Ofer said:
Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Right... now it works for some fields but not for others, this was the same
problem that I was having with the other code I was using. So, basically this
query should return all records if all search fields are left blank. If I
search only for the county field and leave the other fields blank it will
only return the the records with that county in. Below is the entire code
that I am using at the moment. is there any obvious mistakes??

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.City Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.group Like
IIf(Forms![search Form]!group Is Null,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is
Null,"*",Forms![search Form]![current supplier]);

Thanks again!!

Ofer said:
When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

Ofer said:
Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Try this, just incase the fields are not null, but empty

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.City Like
IIf(Len("" & Forms![search Form]!city) =0,"*",Forms![search Form]!city) And
Details.county Like IIf(Len("" & Forms![search
Form]!county)=0,"*",Forms![search
Form]!county) And Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.group Like
IIf(Len("" & Forms![search Form]!group)=0,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Len("" & Forms![search Form]![current
supplier]) = 0 ,"*",Forms![search Form]![current supplier]);
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
Right... now it works for some fields but not for others, this was the same
problem that I was having with the other code I was using. So, basically this
query should return all records if all search fields are left blank. If I
search only for the county field and leave the other fields blank it will
only return the the records with that county in. Below is the entire code
that I am using at the moment. is there any obvious mistakes??

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.City Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.group Like
IIf(Forms![search Form]!group Is Null,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is
Null,"*",Forms![search Form]![current supplier]);

Thanks again!!

Ofer said:
When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

:

Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I have pasted in this query but it doesn't bring up any results at all. With
the old query, it was like it only had access to a few records (if that makes
sense).

Ofer said:
Try this, just incase the fields are not null, but empty

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.City Like
IIf(Len("" & Forms![search Form]!city) =0,"*",Forms![search Form]!city) And
Details.county Like IIf(Len("" & Forms![search
Form]!county)=0,"*",Forms![search
Form]!county) And Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.group Like
IIf(Len("" & Forms![search Form]!group)=0,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Len("" & Forms![search Form]![current
supplier]) = 0 ,"*",Forms![search Form]![current supplier]);
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
Right... now it works for some fields but not for others, this was the same
problem that I was having with the other code I was using. So, basically this
query should return all records if all search fields are left blank. If I
search only for the county field and leave the other fields blank it will
only return the the records with that county in. Below is the entire code
that I am using at the moment. is there any obvious mistakes??

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.City Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.group Like
IIf(Forms![search Form]!group Is Null,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is
Null,"*",Forms![search Form]![current supplier]);

Thanks again!!

Ofer said:
When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

:

Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I started off from scratch using this code. I only inserted the category
statement to begin with and the query worked. I then started adding the
others and they all worked. I then searched for a record that I hadn't
searched for before and it didn't show any results when it should have
brought up one. Now when I try and search for something which used to bring
up results it now no longer does. I just can't work it out????


Ofer said:
Try this, just incase the fields are not null, but empty

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.City Like
IIf(Len("" & Forms![search Form]!city) =0,"*",Forms![search Form]!city) And
Details.county Like IIf(Len("" & Forms![search
Form]!county)=0,"*",Forms![search
Form]!county) And Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.group Like
IIf(Len("" & Forms![search Form]!group)=0,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Len("" & Forms![search Form]![current
supplier]) = 0 ,"*",Forms![search Form]![current supplier]);
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
Right... now it works for some fields but not for others, this was the same
problem that I was having with the other code I was using. So, basically this
query should return all records if all search fields are left blank. If I
search only for the county field and leave the other fields blank it will
only return the the records with that county in. Below is the entire code
that I am using at the moment. is there any obvious mistakes??

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.City Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.group Like
IIf(Forms![search Form]!group Is Null,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is
Null,"*",Forms![search Form]![current supplier]);

Thanks again!!

Ofer said:
When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

:

Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think I can see a pattern now. When I run a search and it doesn't bring
back the results that I expect it is as though it is still searching for the
critieria which I set at the last search plus the current search.

Martc said:
I started off from scratch using this code. I only inserted the category
statement to begin with and the query worked. I then started adding the
others and they all worked. I then searched for a record that I hadn't
searched for before and it didn't show any results when it should have
brought up one. Now when I try and search for something which used to bring
up results it now no longer does. I just can't work it out????


Ofer said:
Try this, just incase the fields are not null, but empty

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.City Like
IIf(Len("" & Forms![search Form]!city) =0,"*",Forms![search Form]!city) And
Details.county Like IIf(Len("" & Forms![search
Form]!county)=0,"*",Forms![search
Form]!county) And Details.category Like IIf(Len("" & Forms![search
Form]!category)=0,"*",Forms![search Form]!category) And Details.group Like
IIf(Len("" & Forms![search Form]!group)=0,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Len("" & Forms![search Form]![current
supplier]) = 0 ,"*",Forms![search Form]![current supplier]);
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Martc said:
Right... now it works for some fields but not for others, this was the same
problem that I was having with the other code I was using. So, basically this
query should return all records if all search fields are left blank. If I
search only for the county field and leave the other fields blank it will
only return the the records with that county in. Below is the entire code
that I am using at the moment. is there any obvious mistakes??

SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2],
Details.[Address 3], Details.City, Details.County, Details.[Post Code],
Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number],
Details.[Fax Number], Details.[Current Supplier], Details.Group,
Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep],
Details.Asian, Details.[Price - Pint], Details.[Price - Litre],
Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre],
Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs,
Details.Notes, Details.Category, Details.[Linked to]
FROM Details
WHERE Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.City Like
IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And
Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search
Form]!county) And Details.category Like IIf(Forms![search Form]!category Is
Null,"*",Forms![search Form]!category) And Details.group Like
IIf(Forms![search Form]!group Is Null,"*",Forms![search Form]!group) And
Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is
Null,"*",Forms![search Form]![current supplier]);

Thanks again!!

:

When you have a field that contains two name, you need to put it in square
brckets

And Details.[current supplier] Like IIf(Forms![search Form]![current
supplier]
Is Null,"*",Forms![search Form]![current supplier])

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

WOW, thanks for the quick reply, it now works!!! Thanks for that!

One thing that I cant get right though is this part:

And Details.current supplier Like IIf(Forms![search Form]!current supplier
Is Null,"*",Forms![search Form]!current supplier)

As there is a space in the field it pops up an error message but if I delete
the space or use an underscore and I then run the query it pops up with an
enter parameter value window for the current suppleirs field. Any ideas??

:

Whet is the reason for the IsNull in this filter, if you want to return all
the records if the city is null, then try this

WHERE Details.City Like Nz(Forms![search Form]![city], "*")

Or
WHERE Details.City Like IIF(Forms![search Form]![city] Is Null, "*",
Forms![search Form]![city])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a form (which has certain feilds from a table) that I can type in what
to search for from my table. I am using a parameter query which I did get
working but I changed it and I can't get it to work again.
The query is as follows:
WHERE ((Details.City)=Forms![search Form]!city Or
((Details.City)=Forms![search Form]!city) Is Null) **(I then have all the
other search fields repeated after this)**
If I don't have the null statement and I miss out one of the search fields
it doesn't return any records.
Have I typed the query in wrong or have I missed something (ps I am a
complete access novice - can you tell!!)
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Query 7
Multiple Parameter Crosstab query filter by form 1
Update Query with Parameter 3
Query by Form 3
If Statement with "*" in Query 2
Access Invalid bracketing of parameter name 1
Problem with a parameter query 7
Parameter Query based on Form 2

Top