Nested query?

[Joan]

Hi,
I am trying to build an update query whereby I set the value of field
[OnInvoice] to the value in Forms!InvoiceForm!txtInvoiceNumber where (the
criteria) [DateSold] is greater than the result of another query. The SQL
of the other query is:

SELECT Max(Invoices.DateSold) AS MaxOfDateSold
FROM Invoices
GROUP BY Invoices.Type, Invoices.Store, Invoices.[Invoice Number]
HAVING (((Invoices.Type)="INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]));


How do I write the update query so that the [DateSold] field in this query
is greater than MaxOfDateSold. What do I put on the criteria line? I tried
putting : >qryUpdPreAdjustments.[MaxOfDateSold] but then I get a message
asking for the value of Forms!InvoiceForm!DateSold. This expression is not
in either query.

Thanks ahead for any help with this.

Joan

 

[Michel Walsh]

Hi,


UPDATE Invoices SET DateSold=DMax("DateSold", "Invoices",
"Store=FORMS!InvoiceForm!StoreCode")



and execute it with


DoCmd.RunSQL "..."


If you use

CurrentDb.Execute "...", then the FORMS!InvoiceForm!StoreCode won't be
solved automatically for you (while it is with DoCmd) and you would be
prompted for it.


Note that is it possible that the third argument of DMax may involve
something like AND Type='inv', the complete criteria was not clear from
the original message, but I did not include it to not overload the syntax,
which would have obscure the basic idea.


Hoping it may help,
Vanderghast, Access MVP

 

[Joan]

Hi Michel,

Thanks for replying to my post. It looks like the Update SQL you sent is
setting the value of the DateSold field. I am not trying to update the
DateSold field, but the OnInvoice field instead. To explain further what I
am trying to do: I have an InvoiceForm where with the OnClick event of a
button on the form, the Invoice is recorded and several action queries run.
One of which is called qryUpdateAdjustments. The purpose of this query is to
look at the Invoice table and see if there were any adjustments (Type = ADJ,
or CR or DB) since the last invoice to this particular customer( known as
[Store]) was sent. If there were, then those invoice adjustments need to be
applied to this invoice. (The Invoice [Type] can be either "INV" for
Invoice or "ADJ", "CR" or "DB" for different types of adjustments. Both
invoices and adjustments are recorded in the Invoices table. I should have
more aptly named this the Transactions table) The purpose of the
qryUpdateAdjustments query is to record the Invoice Number of the Invoice
that the adjustments are being applied to in the OnInvoice field of the
adjustment record . The adjustment record's Type is <> "INV" and the Store
field is the same as the one on the InvoiceForm. For this Type where [Type]
<> "INV" , DateSold is the date the Adjustment record is recorded. The date
in the adjustment record's DateSold field must be later than the DateSold of
the last invoice sent to that particular customer([Store]).

Hope I am explaining this so you can see what I am trying to do. Following
is the SQL for qryUpdateAdjustments like I originally had it. As you can
see, I had the criteria for [DateSold] > [Forms]![InvoiceForm]![DateSold]-7.
This just updated any adjustments made in the last week. However, I thought
this did not accurately reflect what could happen. Invoices are sent out on
Mondays of every week to stores that have made purchases within that week.
However, a store may not make purchases every week, in which case an
Invoice may not be generated. This same store though may have a credit or
adjustment made in that time and it would not be reflected in the next
billing so I am trying to include all adjustments made since the last time
an Invoice was sent to that [Store].

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 ),
[Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice =
[Forms]![InvoiceForm]![txtInvoiceNumber]
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));

I so appreciated your help with this.

Joan

Hi,


UPDATE Invoices SET DateSold=DMax("DateSold", "Invoices",
"Store=FORMS!InvoiceForm!StoreCode")



and execute it with


DoCmd.RunSQL "..."


If you use

CurrentDb.Execute "...", then the FORMS!InvoiceForm!StoreCode won't be
solved automatically for you (while it is with DoCmd) and you would be
prompted for it.


Note that is it possible that the third argument of DMax may involve
something like AND Type='inv', the complete criteria was not clear from
the original message, but I did not include it to not overload the syntax,
which would have obscure the basic idea.


Hoping it may help,
Vanderghast, Access MVP

Hi,
I am trying to build an update query whereby I set the value of field
[OnInvoice] to the value in Forms!InvoiceForm!txtInvoiceNumber where (the
criteria) [DateSold] is greater than the result of another query. The SQL
of the other query is:

SELECT Max(Invoices.DateSold) AS MaxOfDateSold
FROM Invoices
GROUP BY Invoices.Type, Invoices.Store, Invoices.[Invoice Number]
HAVING (((Invoices.Type)="INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]));


How do I write the update query so that the [DateSold] field in this query
is greater than MaxOfDateSold. What do I put on the criteria line? I tried
putting : >qryUpdPreAdjustments.[MaxOfDateSold] but then I get a message
asking for the value of Forms!InvoiceForm!DateSold. This expression is not
in either query.

Thanks ahead for any help with this.

Joan

 

[Michel Walsh]

Hi,


If I understand, you DSUM( ) should then occur from the last date_time
where an operation occurred:


DSUM( "amount", "transactionTable", "DateTimeStamp > DMAX(
'DateTimeOfOperation', 'appropriateTablename', 'ItemID= " & ItemID & " ' )
AND ItemID=" & ItemID )



where

DMAX( 'DateTimeOfOperation', 'appropriateTablename', 'ItemID= " & ItemID &
" ' )


find the latest date where a registered "update" did occur, so, DSUM make
the sum of the amounts not yet incorporated in a "sum" ( credit are just an
amount with a negative sign ), AFTER that date, for that item ( I just
replace DMAX by its "result" ) :


DSUM( "amount", ... , "DateTimeStamp > LastDateWeUpdatedThisItem AND
ItemID=" & ItemID )




Just SET the appropriate field with that DSUM expression.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Thanks for replying to my post. It looks like the Update SQL you sent is
setting the value of the DateSold field. I am not trying to update the
DateSold field, but the OnInvoice field instead. To explain further what I
am trying to do: I have an InvoiceForm where with the OnClick event of a
button on the form, the Invoice is recorded and several action queries run.
One of which is called qryUpdateAdjustments. The purpose of this query is to
look at the Invoice table and see if there were any adjustments (Type = ADJ,
or CR or DB) since the last invoice to this particular customer( known as
[Store]) was sent. If there were, then those invoice adjustments need to be
applied to this invoice. (The Invoice [Type] can be either "INV" for
Invoice or "ADJ", "CR" or "DB" for different types of adjustments. Both
invoices and adjustments are recorded in the Invoices table. I should have
more aptly named this the Transactions table) The purpose of the
qryUpdateAdjustments query is to record the Invoice Number of the Invoice
that the adjustments are being applied to in the OnInvoice field of the
adjustment record . The adjustment record's Type is <> "INV" and the Store
field is the same as the one on the InvoiceForm. For this Type where [Type]
<> "INV" , DateSold is the date the Adjustment record is recorded. The date
in the adjustment record's DateSold field must be later than the DateSold of
the last invoice sent to that particular customer([Store]).

Hope I am explaining this so you can see what I am trying to do. Following
is the SQL for qryUpdateAdjustments like I originally had it. As you can
see, I had the criteria for [DateSold] > [Forms]![InvoiceForm]![DateSold]-7.
This just updated any adjustments made in the last week. However, I thought
this did not accurately reflect what could happen. Invoices are sent out on
Mondays of every week to stores that have made purchases within that week.
However, a store may not make purchases every week, in which case an
Invoice may not be generated. This same store though may have a credit or
adjustment made in that time and it would not be reflected in the next
billing so I am trying to include all adjustments made since the last time
an Invoice was sent to that [Store].

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 ),
[Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice =
[Forms]![InvoiceForm]![txtInvoiceNumber]
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));

I so appreciated your help with this.

Joan

Hi,


UPDATE Invoices SET DateSold=DMax("DateSold", "Invoices",
"Store=FORMS!InvoiceForm!StoreCode")



and execute it with


DoCmd.RunSQL "..."


If you use

CurrentDb.Execute "...", then the FORMS!InvoiceForm!StoreCode won't be
solved automatically for you (while it is with DoCmd) and you would be
prompted for it.


Note that is it possible that the third argument of DMax may involve
something like AND Type='inv', the complete criteria was not clear from
the original message, but I did not include it to not overload the syntax,
which would have obscure the basic idea.


Hoping it may help,
Vanderghast, Access MVP

Hi,
I am trying to build an update query whereby I set the value of field
[OnInvoice] to the value in Forms!InvoiceForm!txtInvoiceNumber where (the
criteria) [DateSold] is greater than the result of another query. The SQL
of the other query is:

SELECT Max(Invoices.DateSold) AS MaxOfDateSold
FROM Invoices
GROUP BY Invoices.Type, Invoices.Store, Invoices.[Invoice Number]
HAVING (((Invoices.Type)="INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]));


How do I write the update query so that the [DateSold] field in this query
is greater than MaxOfDateSold. What do I put on the criteria line? I tried
putting : >qryUpdPreAdjustments.[MaxOfDateSold] but then I get a message
asking for the value of Forms!InvoiceForm!DateSold. This expression is not
in either query.

Thanks ahead for any help with this.

Joan

 

[Joan]

Hi Michel,

I am sorry I do not understand why you are using a DSUM function.
Earlier in my posts, I was thinking that I would have to use another SQL on
the criteria line of [DateSold] in the QBE pane but instead realized that I
could just use a DMax function on this line instead.

I am looking for help with adjusting my WHERE statement to set the
date portion of the condition to be anything later than the prior invoice
date,
instead of just the 7 day period that is currently in the SQL below.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 ),
[Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice = Forms!InvoiceForm!txtInvoiceNumber
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));


I have tried using the DMax() function to return the latest invoice date for
a particular customer
by working this into my WHERE statement, however I am having problems with
the syntax.
Below is what I have typed on the criteria line of the [DateSold] field in
the QBE pane or design view
of the query.

DMax("DateSold","Invoices","Store = ' " &

[Forms]![InvoiceForm]![StoreCode] & " ' " And "Type = 'INV' ")

What is the correct syntax for this line? I really struggle with the syntax
, especially where there is more than 1 criteria.

Thanks,
Joan

Hi,


If I understand, you DSUM( ) should then occur from the last date_time
where an operation occurred:


DSUM( "amount", "transactionTable", "DateTimeStamp > DMAX(
'DateTimeOfOperation', 'appropriateTablename', 'ItemID= " & ItemID & " ' )
AND ItemID=" & ItemID )



where

DMAX( 'DateTimeOfOperation', 'appropriateTablename', 'ItemID= " & ItemID &
" ' )


find the latest date where a registered "update" did occur, so, DSUM make
the sum of the amounts not yet incorporated in a "sum" ( credit are just an
amount with a negative sign ), AFTER that date, for that item ( I just
replace DMAX by its "result" ) :


DSUM( "amount", ... , "DateTimeStamp > LastDateWeUpdatedThisItem AND
ItemID=" & ItemID )




Just SET the appropriate field with that DSUM expression.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Thanks for replying to my post. It looks like the Update SQL you sent is
setting the value of the DateSold field. I am not trying to update the
DateSold field, but the OnInvoice field instead. To explain further

what

I
am trying to do: I have an InvoiceForm where with the OnClick event of a
button on the form, the Invoice is recorded and several action queries run.
One of which is called qryUpdateAdjustments. The purpose of this query

is

to
look at the Invoice table and see if there were any adjustments (Type = ADJ,
or CR or DB) since the last invoice to this particular customer( known as
[Store]) was sent. If there were, then those invoice adjustments need

to

be
applied to this invoice. (The Invoice [Type] can be either "INV" for
Invoice or "ADJ", "CR" or "DB" for different types of adjustments. Both
invoices and adjustments are recorded in the Invoices table. I should have
more aptly named this the Transactions table) The purpose of the
qryUpdateAdjustments query is to record the Invoice Number of the Invoice
that the adjustments are being applied to in the OnInvoice field of the
adjustment record . The adjustment record's Type is <> "INV" and the Store
field is the same as the one on the InvoiceForm. For this Type where [Type]
<> "INV" , DateSold is the date the Adjustment record is recorded. The date
in the adjustment record's DateSold field must be later than the

DateSold

of
the last invoice sent to that particular customer([Store]).

Hope I am explaining this so you can see what I am trying to do. Following
is the SQL for qryUpdateAdjustments like I originally had it. As you can
see, I had the criteria for [DateSold] > [Forms]![InvoiceForm]![DateSold]-7.
This just updated any adjustments made in the last week. However, I thought
this did not accurately reflect what could happen. Invoices are sent out on
Mondays of every week to stores that have made purchases within that week.
However, a store may not make purchases every week, in which case an
Invoice may not be generated. This same store though may have a credit or
adjustment made in that time and it would not be reflected in the next
billing so I am trying to include all adjustments made since the last time
an Invoice was sent to that [Store].

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 ),
[Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice =
[Forms]![InvoiceForm]![txtInvoiceNumber]
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));

I so appreciated your help with this.

Joan

Hi,


UPDATE Invoices SET DateSold=DMax("DateSold", "Invoices",
"Store=FORMS!InvoiceForm!StoreCode")



and execute it with


DoCmd.RunSQL "..."


If you use

CurrentDb.Execute "...", then the FORMS!InvoiceForm!StoreCode

won't

be

solved automatically for you (while it is with DoCmd) and you would be
prompted for it.


Note that is it possible that the third argument of DMax may involve
something like AND Type='inv', the complete criteria was not clear from
the original message, but I did not include it to not overload the syntax,
which would have obscure the basic idea.


Hoping it may help,
Vanderghast, Access MVP


Hi,
I am trying to build an update query whereby I set the value of field
[OnInvoice] to the value in Forms!InvoiceForm!txtInvoiceNumber where (the
criteria) [DateSold] is greater than the result of another query.

The
SQL

of the other query is:

SELECT Max(Invoices.DateSold) AS MaxOfDateSold
FROM Invoices
GROUP BY Invoices.Type, Invoices.Store, Invoices.[Invoice Number]
HAVING (((Invoices.Type)="INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]));


How do I write the update query so that the [DateSold] field in this query
is greater than MaxOfDateSold. What do I put on the criteria line? I tried
putting : >qryUpdPreAdjustments.[MaxOfDateSold] but then I get a message
asking for the value of Forms!InvoiceForm!DateSold. This expression

is
not

in either query.

Thanks ahead for any help with this.

Joan

 

[Michel Walsh]

Hi,


Close. You have to remove two spaces and two double-quotes, to get:

DMax("DateSold","Invoices","Store = '" &
Forms!InvoiceForm!StoreCode & "' And Type = 'INV' ")


You remove spaces beacause "Something" = " Something " is wrong ( the
right term begin and end with a space, absent in the left term).


If you use DoCmd.RunSQL, to run your query, you can even further simplify
to:



DMax("DateSold","Invoices","Store = Forms!InvoiceForm!StoreCode And Type =
'INV' ")


since then, FORMS!formName!ControlName would be automatically solved for
you, and no delimiter is required (since Forms!InvoiceForm!StoreCode is not
a "constant", like 'INV', but the container/place where is a value). If you
use CurrentDb.Execute, you need the first syntax ( the second will produce
an error with a missing parameter, Forms!InvoiceForm!StoreCode, since
CurrentDb do not solve it automatically). CurrentDb.Execute is probably
faster than DoCmd, but DoCmd, doing nice stuff for you, allows you to
simplify the syntax, somehow.




Hoping it may help,
Vanderghast, Access MVP

 

[Joan]

Michel,

I tried the DMax statement that you sent and I still can't get the record
to update.
So then I changed the Update query to a select query to see if I got the
record that I wanted to update and I didn't get any records at all. Then I
tried taking out the [DateSold] criteria and I got some records to appear in
the datasheet of the query. So I surmised there is still a
problem with the [DateSold] criteria.

Will Jet-SQL allow an aggregate such
as DMax in the WHERE clause if it is not in a sub-select statement?

Below is the SQL of the update query that I last tried.
What should I try next? I am at a loss and have already spent several days
on this.

SELECT Invoices.[Invoice Number], Invoices.OnInvoice, Invoices.Type,
Invoices.Store, Invoices.DateSold
FROM Invoices
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>DMax("DateSold","Invoices","Store = '
"&[Forms]![InvoiceForm]![StoreCode]&" ' And Type = 'INV' ")));

Thanks for all of you help.

Joan

 

[Michel Walsh]

Hi,


Try the DMax in the immediate debug window ( add a space before and after
the &, remove the space between the ' and the " (twice) ) :



? DMax("DateSold","Invoices","Store = '" & Forms!InvoiceForm!StoreCode & "'
And Type = 'INV' ")


The statement should work, in the immediate debug window ( assuming the form
InvoiceForm is open) and return something.


Hoping it may help,
Vanderghast, Access MVP

Michel,

I tried the DMax statement that you sent and I still can't get the record
to update.
So then I changed the Update query to a select query to see if I got the
record that I wanted to update and I didn't get any records at all. Then I
tried taking out the [DateSold] criteria and I got some records to appear in
the datasheet of the query. So I surmised there is still a
problem with the [DateSold] criteria.

Will Jet-SQL allow an aggregate such
as DMax in the WHERE clause if it is not in a sub-select statement?

Below is the SQL of the update query that I last tried.
What should I try next? I am at a loss and have already spent several days
on this.

SELECT Invoices.[Invoice Number], Invoices.OnInvoice, Invoices.Type,
Invoices.Store, Invoices.DateSold
FROM Invoices
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>DMax("DateSold","Invoices","Store = '
"&[Forms]![InvoiceForm]![StoreCode]&" ' And Type = 'INV' ")));

Thanks for all of you help.

Joan

Hi,


Close. You have to remove two spaces and two double-quotes, to get:

DMax("DateSold","Invoices","Store = '" &
Forms!InvoiceForm!StoreCode & "' And Type = 'INV' ")


You remove spaces beacause "Something" = " Something " is wrong ( the
right term begin and end with a space, absent in the left term).


If you use DoCmd.RunSQL, to run your query, you can even further simplify
to:



DMax("DateSold","Invoices","Store = Forms!InvoiceForm!StoreCode And

Type

=
'INV' ")


since then, FORMS!formName!ControlName would be automatically solved for
you, and no delimiter is required (since Forms!InvoiceForm!StoreCode is not
a "constant", like 'INV', but the container/place where is a value). If you
use CurrentDb.Execute, you need the first syntax ( the second will produce
an error with a missing parameter, Forms!InvoiceForm!StoreCode, since
CurrentDb do not solve it automatically). CurrentDb.Execute is probably
faster than DoCmd, but DoCmd, doing nice stuff for you, allows you to
simplify the syntax, somehow.




Hoping it may help,
Vanderghast, Access MVP

 

[Joan]

Michel,

The syntax, I discovered is fine. While trying to break this problem down
into steps, I made a SELECT query (qryULastInvoiceDate) to return first of
all just the last invoice's DateSold for the particular store on the
InvoiceForm. This works, ....sort of. See SQL below:

SELECT Invoices.DateSold
FROM Invoices
GROUP BY Invoices.DateSold
HAVING (((Invoices.DateSold)=DMax("[DateSold]","Invoices","Store = '" &
[Forms]![InvoiceForm]![StoreCode] & "' And Type = 'INV' ")));

It returns the last invoice's DateSold. However, a new little problem
popped up. Since the InvoiceForm is a data-entry form, after the user enters
a StoreCode on the form an Invoice Number (an auto-number) automatically
appears in its corresponding textbox and is recorded in the table.
Therefore, the query returns today's date (default value of
Forms!InvoiceForm!DateSold) instead of the date of the invoice sent out to
the store last, before today. If I can somehow retrieve the second to the
last invoice's (Type = "INV") DateSold for a particular store, then I can
restrict the adjustment records (Type <> "INV") that I want to update in my
update query to those recorded since the last invoice (Type = "INV") was
sent out and recorded for that particular store. Sorry if this is
confusing. I'm trying to explain it as best as I can and I forgot that the
Invoice Number of the InvoiceForm is recorded in the table when you begin
entering data on the form.

Where do I go from here?
Thanks for all of your help.


Joan

Hi,


Try the DMax in the immediate debug window ( add a space before and after
the &, remove the space between the ' and the " (twice) ) :



? DMax("DateSold","Invoices","Store = '" & Forms!InvoiceForm!StoreCode & "'
And Type = 'INV' ")


The statement should work, in the immediate debug window ( assuming the form
InvoiceForm is open) and return something.


Hoping it may help,
Vanderghast, Access MVP

Michel,

I tried the DMax statement that you sent and I still can't get the record
to update.
So then I changed the Update query to a select query to see if I got the
record that I wanted to update and I didn't get any records at all.

Then

I
tried taking out the [DateSold] criteria and I got some records to

appear

in
the datasheet of the query. So I surmised there is still a
problem with the [DateSold] criteria.

Will Jet-SQL allow an aggregate such
as DMax in the WHERE clause if it is not in a sub-select statement?

Below is the SQL of the update query that I last tried.
What should I try next? I am at a loss and have already spent several days
on this.

SELECT Invoices.[Invoice Number], Invoices.OnInvoice, Invoices.Type,
Invoices.Store, Invoices.DateSold
FROM Invoices
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>DMax("DateSold","Invoices","Store = '
"&[Forms]![InvoiceForm]![StoreCode]&" ' And Type = 'INV' ")));

Thanks for all of you help.

Joan

Hi,


Close. You have to remove two spaces and two double-quotes, to get:

DMax("DateSold","Invoices","Store = '" &
Forms!InvoiceForm!StoreCode & "' And Type = 'INV' ")


You remove spaces beacause "Something" = " Something " is wrong ( the
right term begin and end with a space, absent in the left term).


If you use DoCmd.RunSQL, to run your query, you can even further simplify
to:



DMax("DateSold","Invoices","Store = Forms!InvoiceForm!StoreCode And

Type

=
'INV' ")


since then, FORMS!formName!ControlName would be automatically solved for
you, and no delimiter is required (since Forms!InvoiceForm!StoreCode

is
not

a "constant", like 'INV', but the container/place where is a value).

If
you

use CurrentDb.Execute, you need the first syntax ( the second will produce
an error with a missing parameter, Forms!InvoiceForm!StoreCode, since
CurrentDb do not solve it automatically). CurrentDb.Execute is probably
faster than DoCmd, but DoCmd, doing nice stuff for you, allows you to
simplify the syntax, somehow.




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


That highly depends on the context. Maybe just excluding today is
enough? If so, just add AND DateSold<>Date() as in:


? DMax("DateSold","Invoices","Store = '" & Forms!InvoiceForm!StoreCode &
"' And Type = 'INV' AND DateSold<> Date( ) ")


Else, I would try a complete query about that, taking a TOP 2 ( to get
the two max ) dates ( a query with the keyword TOP does not necessary loose
its updateability in JET).



Hoping it may help,
Vanderghast, Access MVP

 

[Joan]

Thanks, Michel.
This gives me two options to try. So appreciate this.

Joan