requery on a query

[blackcat]

i have a query which selects from my original table all with a price of zero.
this is then passed to a form to allow the user to enter a price. after
this price has been entered, i need to then be able to select from this 1st
query all with a price of greather than zero to pass these into an excel
spreadsheet. any ideas?

 

[Steve Schapel]

Blackcat,

If I understand you correctly, you are talking about identifying the
records that have just had a price entered. Right? Well, there would
be a number of approaches to this, but I don't think the concept of a
query on a query with contradictory criteria will do it for you.

Possibly the way I would do it is to add a new field to the table,
Date/Time data type, and on the Before Update event of the form you can
set the value of this field to Now(). Then your second query can use
this as the criteria to return all records modified in the last x
minutes. Would that work? Or you could make it a Yes/No data type
field, and use the Before Update event of the form to set it to -1 and
then use this as the criteria for the follow-up query for your Excel export.

Another possible approach would be to make your first query an Append
Query, to add the no-price records to a temporary table, and then base
your form on this temporary table. The second query for the Excel
export will be based on this table, and in this case you will be able to
use the price>0 criteria, but then you will also need to write code to
update the main table with the newly entered price values.

 

[blackcat]

Hi Steve, followed your advice and about adding a new field with date/time,
that has done the trick. Many thanks for your help