Get last sold price for item

[Ashfaque]

My SODate is Short Date format (Date/Time) field with input mask 00/00/0000;0;_

I will try with (Max(T_SOHeader.SODate) with the T_SOHeader table and see what it returns.

I will come back to you shortly....

Regards,
Ashfaque

 

[Ashfaque]

I tried with header table only and started giving '3075 syntax error' over below code

Set rst1 = CurrentDb.OpenRecordset("Select Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.CustCode " & _
"Where T_SOHeader.CustCode =" & Forms!F_SOHeader!CustCode)

 

[jdraw]

The syntax of your Set rst1 line is wrong. You have to identify the Table from which to select the field values.

You are missing the FROM... part of your query.

 

[Ashfaque]

Uh...........sorry.

I tried with below

Set rst1 = CurrentDb.OpenRecordset("Select Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.CustCode " & _
"From T_SOHeader " & _
"Where T_SOHeader.CustCode=" & Forms!F_SOHeader!CustCode & _
" GROUP BY T_SOHeader.SODate, T_SOHeader.CustCode")

Me.TxtOldPrice = rst1!InvNum

But after verifying custcode, this is giving first record date as Maxdate

Strange.....

 

[JHB]

Put the below it in a "Query (by Example)" instead of a recordset, because you can se the result much cleare there, replace ???? with a number:

Select Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.CustCode
From T_SOHeader
Where T_SOHeader.CustCode=???? GROUP BY T_SOHeader.SODate, T_SOHeader.CustCode;

 

[Ashfaque]

Yes it is giving now maxdate from one single table......good move....

 

[JHB]

Then try with the table linked together, but still in the "Query (by Example)" window.

 

[Ashfaque]

I modified Query2 as below:

SELECT Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.InvNum, T_SOHeader.CustCode, T_SOFooter1.productcode, T_SOFooter1.salesitemprice
FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum
WHERE T_SOHeader.CustCode=1100001
GROUP BY T_SOHeader.SODate, T_SOHeader.InvNum, T_SOHeader.CustCode, T_SOFooter1.productcode, T_SOFooter1.salesitemprice;

And it produced attached data from header and footer table.

Please see the attached file.

Now what I need is ProdCode 0101 sold many times with diff price. If you see 1st 2 rows, the sold price was 15 and later on we sold it with less price and that price was 12 (see the last record in sheet for prodcode 0101).

It should display last sold price (whatever less or more).

Regards,
Ashfaque

 

[Ashfaque]

Below will give short list :

SELECT Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.InvNum, T_SOHeader.CustCode, T_SOFooter1.productcode, T_SOFooter1.salesitemprice
FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum
WHERE T_SOHeader.CustCode=1100001 And T_SOFooter1.productcode='0101'
GROUP BY T_SOHeader.SODate, T_SOHeader.InvNum, T_SOHeader.CustCode, T_SOFooter1.productcode, T_SOFooter1.salesitemprice;

But still it display all prices of all dates

 

[JHB]

Yes I see what you're up against, so we must use a little trick.

SELECT TOP 1 Max(T_SOHeader.SODate) AS MaksOfSODate, T_SOFooter1.salesitemprice
FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum = T_SOFooter1.InvNum
GROUP BY T_SOFooter1.salesitemprice, T_SOHeader.CustCode, T_SOFooter1.productcode
HAVING (((T_SOHeader.CustCode)=1100001) AND ((T_SOFooter1.productcode)='0101'))
ORDER BY Max(T_SOHeader.SODate) DESC;

 

[Ashfaque]

Thats excellent....

I will arrange it with forms and come back to you.....

 

[Ashfaque]

With a small db to convert SQL to VBA the below:

strSql = "SELECT TOP 1 Max(T_SOHeader.SODate) AS MaksOfSODate, T_SOFooter1.salesitemprice " & vbCrLf & _
"FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & vbCrLf & _
"GROUP BY T_SOFooter1.salesitemprice, T_SOHeader.CustCode, T_SOFooter1.productcode " & vbCrLf & _
"HAVING (((T_SOHeader.CustCode)=Forms!F_SOHeader!CustCode) And ((T_SOFooter1.productcode)=CboProdByCode)) " & vbCrLf & _
"ORDER BY Max(T_SOHeader.SODate) DESC;"

In query form it is giving correct result. But how about above VBA? How can I get the value from StrSql ?

Therefore, I modified a bit as below:

Set rst1 = CurrentDb.OpenRecordset("SELECT TOP 1 Max(T_SOHeader.SODate) AS MaksOfSODate, T_SOFooter1.salesitemprice " & _
"FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"GROUP BY T_SOFooter1.salesitemprice, T_SOHeader.CustCode, T_SOFooter1.productcode " & _
"HAVING T_SOHeader.CustCode= Forms!F_SOHeader!CustCode And T_SOFooter1.productcode=CboProdByCode " & _
"ORDER BY Max(T_SOHeader.SODate) DESC")

But it produces Run-time error 3061. Too few parameteres. Expected 2.

Please help.

Thanks,
Ashfaque

 

[JHB]

Because you are not getting the value from "Forms!F_SOHeader!CustCode" and "CboProdByCode" into the string.
Sorry for asking, but do you not have any experinces with VBA?

Set rst1 = CurrentDb.OpenRecordset("SELECT TOP 1 Max(T_SOHeader.SODate) AS MaksOfSODate, T_SOFooter1.salesitemprice " _
& "FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " _
& "GROUP BY T_SOFooter1.salesitemprice, T_SOHeader.CustCode, T_SOFooter1.productcode " _
& "HAVING T_SOHeader.CustCode=" & "" & Forms!F_SOHeader!CustCode & "" & " And T_SOFooter1.productcode="& "'" & CboProdByCode & "'" & " " _
& "ORDER BY Max(T_SOHeader.SODate) DESC")

 

[Ashfaque]

Yes I have but not like you expert people.

These "", '" are always confusing. I need to pay attention and hanldle carefully into my next code problem

Thanks a lot. It worked as desired

Ashfaque

 

[JHB]

You're welcome, good you got it finally.