My query doesn't reset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a parameter query and it seems to be saving previous queries. For
example, if I want to find records with A in a particular field, it will find
it ok. If I search for records with A and B in a particular field it will
bring up only records with A in them (hope this make sense). If I were to
rebuild the query from scratch it works for the first time then the above
happens.

What can I do???
 
I'd love to tell you, but I'm not a prophet. Would you mind doing a copy-and-
paste on your query's SQL statement so we can see what's happening? Also, if
it's a SQL string that you're building, and execution is in a loop, the VBA
code would also be very helpful.

Thanks.
 
The query is a follows:

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.group Like IIf(Forms![turnover Form]!group Is
Null,"*",Forms![turnover Form]!group) And Details.[current supplier] Like
IIf(Forms![turnover Form]![current supplier] Is Null,"*",Forms![turnover
Form]![current supplier]);

I did have 10 different criteria but I have reduced it to 2. I thought that
it might have been a bit too complex but it doesn't work with 2!
 
Using the Nz() function in the WHERE clause would simplify your query
immensely. Try changing the clause as follows:

WHERE Details.group = Nz(Forms![turnover Form]!group, " ") And Details.
[current supplier] = Nz(Forms![turnover Form]![current supplier]," ");

What this does is the same thing you were doing before, supplying either
valid data or " " in case of a null entry.

See the Nz() function in the Help file for details.

However, there's another problem: your data is not normalized. You have
customer data and order data in the same table. You should have customer data
in a separate table, maybe called tblCustomers, and give the table a Primary
Key, which might be a unique Auto Number, and add that number as a Foreign
Key into the Details table. Othwise, you'll be repeating customer data every
time they make an order.

HTH
The query is a follows:

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.group Like IIf(Forms![turnover Form]!group Is
Null,"*",Forms![turnover Form]!group) And Details.[current supplier] Like
IIf(Forms![turnover Form]![current supplier] Is Null,"*",Forms![turnover
Form]![current supplier]);

I did have 10 different criteria but I have reduced it to 2. I thought that
it might have been a bit too complex but it doesn't work with 2!
Post your SQL statement so that it can be analyzed.
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
Thank you for your help so far, I am still having trouble though. When I
insert the code which you supplied the query returns nothing. What could I be
doing wrong?


OfficeDev18 via AccessMonster.com said:
Using the Nz() function in the WHERE clause would simplify your query
immensely. Try changing the clause as follows:

WHERE Details.group = Nz(Forms![turnover Form]!group, " ") And Details.
[current supplier] = Nz(Forms![turnover Form]![current supplier]," ");

What this does is the same thing you were doing before, supplying either
valid data or " " in case of a null entry.

See the Nz() function in the Help file for details.

However, there's another problem: your data is not normalized. You have
customer data and order data in the same table. You should have customer data
in a separate table, maybe called tblCustomers, and give the table a Primary
Key, which might be a unique Auto Number, and add that number as a Foreign
Key into the Details table. Othwise, you'll be repeating customer data every
time they make an order.

HTH
The query is a follows:

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.group Like IIf(Forms![turnover Form]!group Is
Null,"*",Forms![turnover Form]!group) And Details.[current supplier] Like
IIf(Forms![turnover Form]![current supplier] Is Null,"*",Forms![turnover
Form]![current supplier]);

I did have 10 different criteria but I have reduced it to 2. I thought that
it might have been a bit too complex but it doesn't work with 2!
Post your SQL statement so that it can be analyzed.
Click to expand...
[quoted text clipped - 6 lines]
What can I do???
Click to expand...
Click to expand...
Click to expand...
 
I can get the query running if I swap the "=" for "like". This however, still
has the same problem, where it will only show the results of previous
searches.

OfficeDev18 via AccessMonster.com said:
Using the Nz() function in the WHERE clause would simplify your query
immensely. Try changing the clause as follows:

WHERE Details.group = Nz(Forms![turnover Form]!group, " ") And Details.
[current supplier] = Nz(Forms![turnover Form]![current supplier]," ");

What this does is the same thing you were doing before, supplying either
valid data or " " in case of a null entry.

See the Nz() function in the Help file for details.

However, there's another problem: your data is not normalized. You have
customer data and order data in the same table. You should have customer data
in a separate table, maybe called tblCustomers, and give the table a Primary
Key, which might be a unique Auto Number, and add that number as a Foreign
Key into the Details table. Othwise, you'll be repeating customer data every
time they make an order.

HTH
The query is a follows:

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.group Like IIf(Forms![turnover Form]!group Is
Null,"*",Forms![turnover Form]!group) And Details.[current supplier] Like
IIf(Forms![turnover Form]![current supplier] Is Null,"*",Forms![turnover
Form]![current supplier]);

I did have 10 different criteria but I have reduced it to 2. I thought that
it might have been a bit too complex but it doesn't work with 2!
Post your SQL statement so that it can be analyzed.
Click to expand...
[quoted text clipped - 6 lines]
What can I do???
Click to expand...
Click to expand...
Click to expand...
 
Is there some way to upload your DB on this forum? If yes, please do so - at
least the various forms, tables and queries that were mentioned. I could then
download them and try to to some elementary troubleshooting offline.
I can get the query running if I swap the "=" for "like". This however, still
has the same problem, where it will only show the results of previous
searches.
Using the Nz() function in the WHERE clause would simplify your query
immensely. Try changing the clause as follows:
Click to expand...
[quoted text clipped - 41 lines]
Click to expand...
 
NO!

Do not post attachments to a newsgroup without Binaries in
its name.

If you want to see the problem mdb file and you have a way
to protect yourself from any potentially malicious code it
might contain, then use private email to negotiate a
mechanism for transmitting the file directly to you.
--
Marsh
MVP [MS Access]

Is there some way to upload your DB on this forum? If yes, please do so - at
least the various forms, tables and queries that were mentioned. I could then
download them and try to to some elementary troubleshooting offline.
I can get the query running if I swap the "=" for "like". This however, still
has the same problem, where it will only show the results of previous
searches.
Using the Nz() function in the WHERE clause would simplify your query
immensely. Try changing the clause as follows:
Click to expand...
[quoted text clipped - 41 lines]
What can I do???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top