Please help with Update query

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,

I am trying to update the field ,OnInvoice, of an adjustment record in the
Invoices table by
running an update query(qryUpdateAdjustments) via a button on the
InvoiceForm. I am having
trouble with the criteria of the Update query.

To explain further: On the InvoiceForm there is a button where 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 a
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 someone can understand 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 would appreciate any help with this. The last line of this SQL is where
I need help.

Joan
 
Hi Joan,

If I understand your message correctly, you are looking
for help with adjusting your WHERE statement to set the
date portion of the condition to be anything later than
the prior invoice date, rather than just the 7 day period
that is currently listed.

If this is correct, you should be able to use the DMax()
funtion to return the latest invoice date for a
particular customer. By working this into your WHERE
statement, you could restrict the range of your query
based on the result.

Hope this helps.

-Ted
-----Original Message-----
Hi,

I am trying to update the field ,OnInvoice, of an adjustment record in the
Invoices table by
running an update query(qryUpdateAdjustments) via a button on the
InvoiceForm. I am having
trouble with the criteria of the Update query.

To explain further: On the InvoiceForm there is a button where 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 a
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
Click to expand...
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 someone can understand 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 would appreciate any help with this. The last line of this SQL is where
I need help.

Joan



.
Click to expand...
 
Ted,

Yes, you understand perfectly what I need help with. I tried doing as you
suggested but I am having trouble working the DMax() function into my WHERE
statement because of trying to get the correct syntax. Would you mind help
me out with this?

I thought that because of syntax problems. I would just try putting the
function on the criteria line of the [DateSold] field of the QBE pane in
Design view, however I am even having trouble with this since the third
argument of the function has two conditions. Below is what I have typed on
this criteria line:
DMax("DateSold","Invoices","Store = ' " & [Forms]![InvoiceForm]![StoreCode]
Click to expand...
& " ' " And "Type = 'INV' ")

When I switch to the SQL view, the entire SQL of the query looks like this:

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)>DMax("DateSold","Invoices",(Invoices.DateSold)="Store =
' " & [Forms]![InvoiceForm]![StoreCode] & " ' " And
(Invoices.DateSold)="Type = 'INV' ")));


Notice how in the SQL above in the latter part of the WHERE statement , two
extra (Invoices.DateSold) = were inserted before each criteria of the DMax
function.

How should the criteria in the DMax function be written ?

Thanks for your help.

Joan


Ted said:
Hi Joan,

If I understand your message correctly, you are looking
for help with adjusting your WHERE statement to set the
date portion of the condition to be anything later than
the prior invoice date, rather than just the 7 day period
that is currently listed.

If this is correct, you should be able to use the DMax()
funtion to return the latest invoice date for a
particular customer. By working this into your WHERE
statement, you could restrict the range of your query
based on the result.

Hope this helps.

-Ted
-----Original Message-----
Hi,

I am trying to update the field ,OnInvoice, of an adjustment record in the
Invoices table by
running an update query(qryUpdateAdjustments) via a button on the
InvoiceForm. I am having
trouble with the criteria of the Update query.

To explain further: On the InvoiceForm there is a button where 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 a
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
Click to expand...
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 someone can understand 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 would appreciate any help with this. The last line of this SQL is where
I need help.

Joan



.
Click to expand...
Click to expand...
 
Hi Joan,

In looking at your DMax statement, I am thinking that
maybe the problem is that your AND statement is kind of
all by itself. I think that it should be moved inside
one of the string statements. Currently you have:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' " And "Type = 'INV' ")

I was thinking maybe it should be:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' And Type = 'INV' ")

These statements are kind of tricky to build. Often what
I do is test the statement in a simple select query to
get it right and then paste that into a more complicated
query knowing that at least the statement is correct.

Let me know if that helps.
-----Original Message-----
Ted,

Yes, you understand perfectly what I need help with. I tried doing as you
suggested but I am having trouble working the DMax() function into my WHERE
statement because of trying to get the correct syntax. Would you mind help
me out with this?

I thought that because of syntax problems. I would just try putting the
function on the criteria line of the [DateSold] field of the QBE pane in
Design view, however I am even having trouble with this since the third
argument of the function has two conditions. Below is what I have typed on
this criteria line:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' " And "Type = 'INV' ")

When I switch to the SQL view, the entire SQL of the query looks like this:

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)>DMax("DateSold","Invoices", (Invoices.DateSold)="Store =
' " & [Forms]![InvoiceForm]![StoreCode] & " ' " And
(Invoices.DateSold)="Type = 'INV' ")));


Notice how in the SQL above in the latter part of the WHERE statement , two
extra (Invoices.DateSold) = were inserted before each criteria of the DMax
function.

How should the criteria in the DMax function be written ?

Thanks for your help.

Joan


Hi Joan,

If I understand your message correctly, you are looking
for help with adjusting your WHERE statement to set the
date portion of the condition to be anything later than
the prior invoice date, rather than just the 7 day period
that is currently listed.

If this is correct, you should be able to use the DMax ()
funtion to return the latest invoice date for a
particular customer. By working this into your WHERE
statement, you could restrict the range of your query
based on the result.

Hope this helps.

-Ted
-----Original Message-----
Hi,

I am trying to update the field ,OnInvoice, of an adjustment record in the
Invoices table by
running an update query(qryUpdateAdjustments) via a button on the
InvoiceForm. I am having
trouble with the criteria of the Update query.

To explain further: On the InvoiceForm there is a button where 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 a
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
Click to expand...
field is the same as the one on the InvoiceForm. For this Type where [Type]
<> "INV" , DateSold is the date the Adjustment record
Click to expand...
is
recorded. The date
in the adjustment record's DateSold field must be
Click to expand...
later
than the DateSold of
the last invoice sent to that particular customer ([Store]).

Hope I am explaining this so someone can understand 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
Click to expand...
purchases
within that week.
However, a store may not make purchases every week,
Click to expand...
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 would appreciate any help with this. The last
Click to expand...
line
of this SQL is where
I need help.

Joan



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Ted,

Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:

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' ")));

Below is the QBE of the Select query to first see if I can just retrieve the
record that I would like to update:

Field: [Invoice Number] [Type] [Store]
[DateSold]
Table: Invoices Invoices Invoices
Invoices
Show:
Criteria: said:
DMax("DateSold","Invoices","Store = ' " & [Forms]![InvoiceForm]![StoreCode]
Click to expand...
& " ' And Type = 'INV' ")


Cannot figure out why this won't work. Will Jet-SQL allow an aggregate such
as DMax in the WHERE clause if it is not in a sub-select statement?

Joan




Ted said:
Hi Joan,

In looking at your DMax statement, I am thinking that
maybe the problem is that your AND statement is kind of
all by itself. I think that it should be moved inside
one of the string statements. Currently you have:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' " And "Type = 'INV' ")

I was thinking maybe it should be:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' And Type = 'INV' ")

These statements are kind of tricky to build. Often what
I do is test the statement in a simple select query to
get it right and then paste that into a more complicated
query knowing that at least the statement is correct.

Let me know if that helps.
-----Original Message-----
Ted,

Yes, you understand perfectly what I need help with. I tried doing as you
suggested but I am having trouble working the DMax() function into my WHERE
statement because of trying to get the correct syntax. Would you mind help
me out with this?

I thought that because of syntax problems. I would just try putting the
function on the criteria line of the [DateSold] field of the QBE pane in
Design view, however I am even having trouble with this since the third
argument of the function has two conditions. Below is what I have typed on
this criteria line:
DMax("DateSold","Invoices","Store = ' " & [Forms]!
Click to expand...
[InvoiceForm]![StoreCode]
& " ' " And "Type = 'INV' ")

When I switch to the SQL view, the entire SQL of the query looks like this:

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)>DMax("DateSold","Invoices", (Invoices.DateSold)="Store =
' " & [Forms]![InvoiceForm]![StoreCode] & " ' " And
(Invoices.DateSold)="Type = 'INV' ")));


Notice how in the SQL above in the latter part of the WHERE statement , two
extra (Invoices.DateSold) = were inserted before each criteria of the DMax
function.

How should the criteria in the DMax function be written ?

Thanks for your help.

Joan


Hi Joan,

If I understand your message correctly, you are looking
for help with adjusting your WHERE statement to set the
date portion of the condition to be anything later than
the prior invoice date, rather than just the 7 day period
that is currently listed.

If this is correct, you should be able to use the DMax ()
funtion to return the latest invoice date for a
particular customer. By working this into your WHERE
statement, you could restrict the range of your query
based on the result.

Hope this helps.

-Ted
-----Original Message-----
Hi,

I am trying to update the field ,OnInvoice, of an
adjustment record in the
Invoices table by
running an update query(qryUpdateAdjustments) via a
button on the
InvoiceForm. I am having
trouble with the criteria of the Update query.

To explain further: On the InvoiceForm there is a
button where 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 a
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 someone can understand
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 would appreciate any help with this. The last line
of this SQL is where
I need help.

Joan



.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Joan,

I noticed also that your DMax statement didn't have []'s
around the fieldnames. I know that these are generally
optional when the fieldnames don't include spaces but I
have also noticed that the D functions always include
them in their examples even when the fieldnames have no
spaces.

Another thing that I thought of is that the DMax
statement may be returning the formatted date result, so
you may need to enclose it in #'s, similar to the way
that text values are enclosed in quotes. You could try
the following, with and without the #'s.

"#" & DMax("[DateSold]","Invoices","[Store] = ' " &
[Forms]![InvoiceForm]![StoreCode] & " ' And [Type]
Click to expand...
= 'INV' ") & "#"

One last idea. You could consider putting this DMax
statement in a control on the form (it could be hidden if
you didn't want the user to see it). That way, the
statement would be a little simpler (you wouldn't have to
include the form names), and your update query could just
pull the value returned from the DMax function from the
control on the form. It would also be a little easier to
see what values were being passed along.

Let me know if any of this works.

-Ted
 
I think that you have a logic problem here. You trying to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think whatever it is that you are
trying to achieve.
 
I thought that too at first, but if you look closer she
is actually comparing the date sold to the maximum date
of a subset of the records (only those that are of the
type "INV").

I would agree though, that it may be worth making the
update query a little more simple by just inserting
paramaters and then passing the form control values to
the query paramaters via VB, it would be a little easier
to follow, but then again it would require writing code
and I'm not sure if you are comfortable with that (it
would be pretty simple if you have written code before).

-----Original Message-----
I think that you have a logic problem here. You trying to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think whatever it is that you are
trying to achieve.

--
HTH
John

Joan said:
Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:
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
Click to expand...
= 'INV' ")));


.
Click to expand...
 
Ted,

Thanks so much for sticking with me on this. I have written code before,
but am still fairly new at it. I think I could do that if you would get me
started.

In attempting to break this problem down in steps, I tried making a SELECT
query where I first just try to get the last invoice ("INV") date (DateSold)
of the store currently on the InvoiceForm.(in textbox StoreCode). The SQL is
below. This works fine if the InvoiceForm is not open. It asks me for the
parameter, Forms!InvoiceForm!StoreCode and it returns the last invoice
(Type: INV) date. But when I have the Invoice Form open, there is a new
wrinkle. It returns today's date because the InvoiceForm is a data entry
form and when the user types in the StoreCode, the Invoice Number which is
an auto-number appears in it's corresponding textbox and is recorded in the
table. Since I am wanting to run an update query on adjustment (Type = ADJ,
CR or DB) records recorded in the Invoices table since the last invoice sent
to the store, this will not work. I guess I need the second to the last
Invoice DateSold for the particular store. How do I work around this? Here
is the SQL of the query to retrieve the last invoice's DateSold: (called
qryULastInvoiceDate). This query shows the Totals row in QBE.

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


My thought then is that in my update query I need to restrict the records
to the store's (Forms!InvoiceForm!StoreCode) adjustment records (Type <>
"INV") where the [DateSold] is > the result of my above query, assuming I
can somehow get it to return the second to the last Invoice DateSold.

How do I go about getting this second to the last invoice DateSold? Am
thinking that I will have to put this in code now but need help.

Thanks

Joan


Ted said:
I thought that too at first, but if you look closer she
is actually comparing the date sold to the maximum date
of a subset of the records (only those that are of the
type "INV").

I would agree though, that it may be worth making the
update query a little more simple by just inserting
paramaters and then passing the form control values to
the query paramaters via VB, it would be a little easier
to follow, but then again it would require writing code
and I'm not sure if you are comfortable with that (it
would be pretty simple if you have written code before).

-----Original Message-----
I think that you have a logic problem here. You trying to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think whatever it is that you are
trying to achieve.

--
HTH
John

Joan said:
Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:
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
Click to expand...
= 'INV' ")));


.
Click to expand...
Click to expand...
 
Hi Ted,

Please read my other reply first. I decided to follow your advice and pass
form control values to query parameters in VBA.

What do you think of running the qryULastInvoiceDate to get the last invoice
date for a particular store and storing the result in a variable and then
setting this value in a textbox on the InvoiceForm (txtLastDate)? I thought
that a work around to the problem of getting the correct date might be as
follows: In order to use the DMax() function, I tried running the query
from another form before opening the InvoiceForm. This way I won't get
today's date as the last invoice date like I would if I ran the query after
opening the InvoiceForm. (I know this because I tried running it from the
InvoiceForm first, using an unbound textbox (parameter) to first enter a
storecode and running the query from this control's AfterUpdate event, but
even then I got today's date.)

On this other form, the user can enter the store code in an unbound textbox
with an AfterUpdate event. The event code would open a recordset of the
QueryDef "qryULastInvoiceDate". I tried setting a variable LastDate to
the value in the DateSold field of this recordset and then opening the
InvoiceForm and inserting the value of the variable in a control on the
form. I am not sure if my code is right. Would you mind looking at it?
The result that I got in the control, (Forms!InvoiceForms!txtLastDate)
displayed the following: 12:00:00 AM. Do I need to reformat the date
here? I tried to but got an error message. My code is below.



Private Sub txtStore_AfterUpdate()
'On Error GoTo Err_txtStore_AfterUpdate()

Dim stQueryName As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Dim LastDate As Date

stQueryName = "qryULastInvoiceDate"
Set db = CurrentDb
Set qdf = db.QueryDefs(stQueryName)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.MoveLast

' There is only one field and one record in the resulting recordset.
Was not sure how to write the next line.
LastDate = DateSold
DoCmd.OpenForm "InvoiceForm", acNormal, , , acFormAdd, acWindowNormal
Forms!InvoiceForm!txtLastDate = LastDate
rs.Close

I so appreciate your help because I have been struggling with this for so
long.

Joan

Ted said:
I thought that too at first, but if you look closer she
is actually comparing the date sold to the maximum date
of a subset of the records (only those that are of the
type "INV").

I would agree though, that it may be worth making the
update query a little more simple by just inserting
paramaters and then passing the form control values to
the query paramaters via VB, it would be a little easier
to follow, but then again it would require writing code
and I'm not sure if you are comfortable with that (it
would be pretty simple if you have written code before).

-----Original Message-----
I think that you have a logic problem here. You trying to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think whatever it is that you are
trying to achieve.

--
HTH
John

Joan said:
Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:
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
Click to expand...
= 'INV' ")));


.
Click to expand...
Click to expand...
 
Hi Joan,

Unfortunately I just got your message and I have to run
to go get my son. I'll be off tomorrow, but I'll be
happy to help on Mon if you still need it.

In reading through your last message quickly, I'm not
sure if you saw my last suggestion about adding the extra
criteria to the DMax statement to eliminate the current
date from the domain. I really think that this will be a
good way to go, rather than doing a second form. But,
it's one of those things that can be done a number of
ways so you can do whatever is most comfortable to you.

If it were me, I would have a control right on the main
form with the DMax statement as the source. That way,
any time you are entering an invoice (or adjustment) you
would have a reference to the prior invoice date.

Then, I would handle the updates via VB as you are now
looking at. Unfortunately I don't have time to review
right now, but as I mentioned I will be happy to on Mon
if its still not working for you.

Best of Luck.

-Ted
-----Original Message-----
Hi Ted,

Please read my other reply first. I decided to follow your advice and pass
form control values to query parameters in VBA.

What do you think of running the qryULastInvoiceDate to get the last invoice
date for a particular store and storing the result in a variable and then
setting this value in a textbox on the InvoiceForm (txtLastDate)? I thought
that a work around to the problem of getting the correct date might be as
follows: In order to use the DMax() function, I tried running the query
from another form before opening the InvoiceForm. This way I won't get
today's date as the last invoice date like I would if I ran the query after
opening the InvoiceForm. (I know this because I tried running it from the
InvoiceForm first, using an unbound textbox (parameter) to first enter a
storecode and running the query from this control's AfterUpdate event, but
even then I got today's date.)

On this other form, the user can enter the store code in an unbound textbox
with an AfterUpdate event. The event code would open a recordset of the
QueryDef "qryULastInvoiceDate". I tried setting a variable LastDate to
the value in the DateSold field of this recordset and then opening the
InvoiceForm and inserting the value of the variable in a control on the
form. I am not sure if my code is right. Would you mind looking at it?
The result that I got in the control, (Forms! InvoiceForms!txtLastDate)
displayed the following: 12:00:00 AM. Do I need to reformat the date
here? I tried to but got an error message. My code is below.



Private Sub txtStore_AfterUpdate()
'On Error GoTo Err_txtStore_AfterUpdate()

Dim stQueryName As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Dim LastDate As Date

stQueryName = "qryULastInvoiceDate"
Set db = CurrentDb
Set qdf = db.QueryDefs(stQueryName)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.MoveLast

' There is only one field and one record in the resulting recordset.
Was not sure how to write the next line.
LastDate = DateSold
DoCmd.OpenForm "InvoiceForm", acNormal, , , acFormAdd, acWindowNormal
Forms!InvoiceForm!txtLastDate = LastDate
rs.Close

I so appreciate your help because I have been struggling with this for so
long.

Joan

I thought that too at first, but if you look closer she
is actually comparing the date sold to the maximum date
of a subset of the records (only those that are of the
type "INV").

I would agree though, that it may be worth making the
update query a little more simple by just inserting
paramaters and then passing the form control values to
the query paramaters via VB, it would be a little easier
to follow, but then again it would require writing code
and I'm not sure if you are comfortable with that (it
would be pretty simple if you have written code before).

-----Original Message-----
I think that you have a logic problem here. You
Click to expand...
trying
to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think
Click to expand...
whatever
it is that you are
trying to achieve.

--
HTH
John

Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:
SELECT Invoices.[Invoice Number],
Click to expand...
Invoices.OnInvoice,
Invoices.Type,
Invoices.Store, Invoices.DateSold
FROM Invoices
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>DMax
Click to expand...
("DateSold","Invoices","Store
= ' " &
[Forms]![InvoiceForm]![StoreCode] & " ' And Type = 'INV' ")));


.
Click to expand...
Click to expand...


.
Click to expand...
 
It may be just in the email, but the type='INV' constraint is in both the main
WHERE clause and in the DMax.

John

Ted said:
I thought that too at first, but if you look closer she
is actually comparing the date sold to the maximum date
of a subset of the records (only those that are of the
type "INV").
I would agree though, that it may be worth making the
update query a little more simple by just inserting
paramaters and then passing the form control values to
the query paramaters via VB, it would be a little easier
to follow, but then again it would require writing code
and I'm not sure if you are comfortable with that (it
would be pretty simple if you have written code before).
-----Original Message-----
I think that you have a logic problem here. You trying to find records where
the DateSold is greater than the maximum DateSold for the store. You are not
very likely to find one! You need to re-think whatever
Click to expand...
it is that you are trying to achieve.
Joan said:
Thanks for posting back. I tried the new DMax statement that you suggested
but the update doesn't work.
So then I changed the Update query to a select query to see if I got the
records 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.

Below is my SQL:
SELECT Invoices.[Invoice Number], Invoices.OnInvoice, Invoices.Type,
Invoices.Store, Invoices.DateSold
FROM Invoices
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode])
Click to expand...
Click to expand...
AND ((Invoices.DateSold)>DMax("DateSold","Invoices","Store
= ' " & [Forms]![InvoiceForm]![StoreCode] & " ' And Type
= 'INV' ")));
Click to expand...
 
Back
Top