A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

requery on a query



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2005, 04:57 PM
blackcat
external usenet poster
 
Posts: n/a
Default requery on a query

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?
  #2  
Old February 25th, 2005, 10:22 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

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 price0 criteria, but then you will also need to write code to
update the main table with the newly entered price values.

--
Steve Schapel, Microsoft Access MVP

blackcat wrote:
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?

  #3  
Old February 28th, 2005, 12:03 PM
blackcat
external usenet poster
 
Posts: n/a
Default

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

"Steve Schapel" wrote:

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 price0 criteria, but then you will also need to write code to
update the main table with the newly entered price values.

--
Steve Schapel, Microsoft Access MVP

blackcat wrote:
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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested in-line Query laura Running & Setting Up Queries 0 February 11th, 2005 01:17 AM
Parameter query - nested queries laura Running & Setting Up Queries 3 February 10th, 2005 05:09 PM
Return repeats info in "8s" Joy Rose Running & Setting Up Queries 14 October 13th, 2004 10:07 PM
Big number gives error! Sara Mellen Running & Setting Up Queries 8 October 11th, 2004 02:48 AM
Display Parameter from Form on Report sara Setting Up & Running Reports 10 July 19th, 2004 04:54 PM


All times are GMT +1. The time now is 04:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.