Invoice Receiving Date

[junker1234]

Hi

I searched around the web and this group for an answer to this problem
but I was unable to find one. I apologize if this topic (or similar
ones) have been posted a million times.

I have a database set up to predict the receiving date of various items
in invoices. Thus each item in an invoice has different expected date.

I would like to have another field on each item that lists the furthest
out date for any item on that invoice. So that means that if an
invoice has 3 items with expected dates of 8/30, 8/31, and 9/4 I want
all 3 items to display 9/4 in a column (so that we have some idea of
when we should schedule delivery).

I found this site: http://www.thescripts.com/forum/thread82144.html
which might be close to what I want. I tried to use the SQL but
couldn't get it to work.

Any assistance is greatly appreciated!

 

[pietlinden]

use DMAX on the date field and use the same invoice no as the =
!

 

[junker1234]

Thanks for the help!

I can see how this would work but I am having trouble figuring out how
to properly write the"criteria" section.

Last: DMax("[FinalExp]","Query2","[POs.Num]"="[POs.Num]")

FinalExp=Expected Date
POs.Num= Invoice Number

This makes the Last field for every record have the furthest out date
for the entire set of records, not just for invoices with the same
number.

Thanks in advance for any help!

 

[John Vinson]

Thanks for the help!

I can see how this would work but I am having trouble figuring out how
to properly write the"criteria" section.

Last: DMax("[FinalExp]","Query2","[POs.Num]"="[POs.Num]")

FinalExp=Expected Date
POs.Num= Invoice Number

The criterion should be a text string that is a valid SQL WHERE
clause. You're trying to search for records where the [Pos.Num] is
equal to itself... and using incorrect bracketing to do so.

What you wand is to have the field within Query2 equal to some field
in the outer query. I'm not sure what your table and fieldnames are -
I hope you don't have a field NAMED Pos.Num, since a period in a
fieldname is a definite no-no! Guessing that there is a field named
Num in a table named POs, try

DMax("[FinalExp]", "[Query2]", "[Num] = " & [Pos].[Num])

Thus if Pos.Num is equal to (say) 345 in a particular record, the
third argument would be

[Num] = 345

since you're concatenating its value, rather than its name.

John W. Vinson[MVP]

 

[junker1234]

Thanks very much for your help!

I think I am getting closer to getting it working but I am still having
trouble. Here is what I'm using:

GroupDate: DMax([FinalExp],"joinfordeliv","[Number] =" & POs.Num)

FinalExp is the field which I want to know the maximum of.
joinfordeliv is the query that I am tring to run
Number is the PO Number (it resides in joinfordeliv)
Num is a field in a table POs

When I run this I get a "Data type mismatch in criteria expression"

I looked online and in help but couldn't really find anything to help.
Since both Number and POs.Num are based on the same underlying field in
the PO table I can't imagine that there would be any kind of formatting
error.

Any help is definetly appreciated!

PS Here's the SQL it generates, I'm not sure if that makes anything
clearer!

SELECT DISTINCTROW POs.Date, POs.Memo, POs.[Deliv Date], POs.Num,
POs.Name, POs.FOB, POs.Via, POs.[Due Date], POs.[qb by], POs.[date of
last revision], POs.Item, POs.Qty, POs.[Source Name], POs.[Rcv'd],
VendorDays.Vendor, VendorDays.[Days until Ready], VendorDays.[will
call], Sales.*, ZipCode.*, [POs.Date]+[Days until Ready] AS
EstBuiltDate, IIf([POs.Date]<>[Deliv Date],[Deliv Date],[EstBuiltDate])
AS SelectMachineorConf, Weekday([SelectMachineorConf]) AS WeekdayNum,
IIf([Source Name]="Equipment Distributors
Inc",Choose(1,0,0,0,0,0,2),IIf([Source Name]="Desk
Makers",Choose([WeekdayNum],1,0,1,0,4,3,2),IIf([will
call],Choose([WeekdayNum],4,3,2,1,0,6,5),0))) AS RoundUp,
DateAdd('d',[RoundUp],[SelectMachineorConf]) AS FinalExp, Sales.[S# O#
#], IIf([POs.Date]<>[Deliv Date],"Confirmed","") AS Confirm, [POs.Num]
AS [Number]

FROM ((POs RIGHT JOIN Sales ON POs.[FOBdate of last
revision]=Sales.Numcost) LEFT JOIN ZipCode ON Sales.[Name
Zip]=ZipCode.zip) LEFT JOIN VendorDays ON POs.[Source
Name]=VendorDays.Vendor

WHERE (((POs.Date)>DateAdd('d',-70,Date())) AND ((Sales.[S# O# #]) Is
Null));

Thanks for the help!

I can see how this would work but I am having trouble figuring out how
to properly write the"criteria" section.

Last: DMax("[FinalExp]","Query2","[POs.Num]"="[POs.Num]")

FinalExp=Expected Date
POs.Num= Invoice Number

The criterion should be a text string that is a valid SQL WHERE
clause. You're trying to search for records where the [Pos.Num] is
equal to itself... and using incorrect bracketing to do so.

What you wand is to have the field within Query2 equal to some field
in the outer query. I'm not sure what your table and fieldnames are -
I hope you don't have a field NAMED Pos.Num, since a period in a
fieldname is a definite no-no! Guessing that there is a field named
Num in a table named POs, try

DMax("[FinalExp]", "[Query2]", "[Num] = " & [Pos].[Num])

Thus if Pos.Num is equal to (say) 345 in a particular record, the
third argument would be

[Num] = 345

since you're concatenating its value, rather than its name.

John W. Vinson[MVP]

 

[John Vinson]

Thanks very much for your help!

I think I am getting closer to getting it working but I am still having
trouble. Here is what I'm using:

GroupDate: DMax([FinalExp],"joinfordeliv","[Number] =" & POs.Num)

FinalExp is the field which I want to know the maximum of.
joinfordeliv is the query that I am tring to run
Number is the PO Number (it resides in joinfordeliv)
Num is a field in a table POs

When I run this I get a "Data type mismatch in criteria expression"

Two problems I can see: first, the [FinalExp] needs to be in quotes;
and - if [Number] is of Text datatype rather than Number datatype, as
I guess from the error, you need quotes around it. Try

GroupDate: DMax("[FinalExp]","joinfordeliv","[Number] ='" & POs.Num &
"'")

I've got a lot of puzzlement about your SQL but let's take one thing
at a time. At the very least, try putting

?DMax("[FinalExp]","joinfordeliv","[Number] ='" & POs.Num & "'")

in the Immediate window (type Ctrl-G), substituting a valid POs Number
for the POs.Num, and see if you get the desired value of FinalExp.

John W. Vinson[MVP]

 

[junker1234]

It worked!!

I just copied in what you typed and it started working!!

Thank you very much for your assistance!