Urgent help on code

[Ashfaque]

Hi,

One of my sub form named F_SalesInvFoot based on table T_SalesInvFoot. This footer table having below fields.

AccNewCode (Text)
InvDate(Date)
SalesQty(Number)

The sub form has been placed in main form called F_SalesInvHead with one to many relationship.

These all works well.

The other form called F_Gift_C have few fields on it where I am displaying
AccNewCode (Text)
InvDate(Date)
etc....

I m using this form to issue gift to customer depend on thier purchase of cartons.

This main form (F_Gift_C) have another subform called SF_GiveAways which has some bound fields along with a combo box. This combo is used select

What I am trying to do is like this....I want to have sum of SalesQty from table called T_SalesInvFoot.

I am displaying From - To date and AccNewCode on header from (F_Gift_C) based on which my above record set code should generate sum of SalesQty (carton qty) for the selected period and specific customer (AccNewCode) . Off course this will be triggered once the combo box is updated in footer form.

The below code I wrote behind after update of combo.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim TotCarton: TotCarton = 0

Set rst = CurrentDb.OpenRecordset("Select AccNewCode, InvDate, Sum(SalesQty) As TotCarton " & _
"FROM T_SalesInvFoot Where Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And InvDate <= #" & Forms!F_Gift_C!TxtTo & "# And AccNewCode = '" & Forms!F_Gift_C!AccNewCode & "'" & _
"GROUP BY AccNewCode;")

TotCarton = rst!TotCarton

But rst!TotCarton generates nothing while there is data in footer table and I dont find error in the code.

Any help will be appreciated....

 

[pbaldy]

Does it error out? I would expect it to, as it looks like invalid SQL. InvDate is in the SELECT clause, but not the GROUP BY clause nor aggregated. Try taking that field out of the SELECT clause. You're also missing a space between the single quote and GROUP.

 

[Ashfaque]

Thanks Pbaldy,

This one is simpy works...

Set rst = CurrentDb.OpenRecordset("Select AccNewCode, SUM(SalesQty) AS CartonQty " & _
"FROM T_SalesInvFoot Where AccNewCode = '" & Forms!F_Gift_C!AccNewCode & "'" & _
"GROUP BY AccNewCode;")

But this one returns nothing.....

Set rst = CurrentDb.OpenRecordset("Select AccNewCode, SUM(SalesQty) AS CartonQty " & _
"FROM T_SalesInvFoot Where AccNewCode = '" & Forms!F_Gift_C!AccNewCode & "' And (Invdate>= " & Forms!F_Gift_C!TxtFrom & " And InvDate <= " & Forms!F_Gift_C!TxtTo & ") GROUP BY AccNewCode;")

If rst.RecordCount >= 1 Then

For I = 1 To rst.RecordCount

TxtBalCartQty = TxtBalCartQty + rst!CartonQty

rst.MoveNext
Next I
End If

while there is data and no error in the code.

(TxtBalCartQty is unbound text box on sub form.)

My idea is to calculate one by one salesqty from footer table accordingly to criteria and please it into ubbound text TxtBalCartQty on the same sub form.

One then total sold cartons is calculated for a particular customer in given date range, the gift item can be issued to him as per the gift issueing system. I have attached a jpg pic for your ref. I can upload my db with limited records if needed.

If customer purchased from us 125 cartons then according to gift table diff items would be issued to him.

Example

On 5 cartons purchase, gift item will be Mixy (Juicer) 1 set
ON 10 cartons purchase, gift item will be Streio (big) 1 pc..and so on...

And once the gift it issued, I need decreasing sum of total purchased cartons to apprear in TxtBalCartQty which is temporary unbound text box. If needed, we can convert it to actual field in table.

Thanks in advance

 

[pbaldy]

Well, on that one you've taken out the # around the date values, which would be required for a date/time data type. I also notice you use a non-US date format, so you might run into this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[Ashfaque]

Again thanks Paul,

I added this # for date factor as below.

Set rst = CurrentDb.OpenRecordset("Select AccNewCode, SUM(SalesQty) AS CartonQty " & _
"FROM T_SalesInvFoot Where AccNewCode = '" & Forms!F_Gift_C!AccNewCode & "' And (Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And InvDate <= #" & Forms!F_Gift_C!TxtTo & "#) GROUP BY AccNewCode;")

If rst.RecordCount >= 1 Then

For I = 1 To rst.RecordCount

TxtBalCartQty = TxtBalCartQty + rst!CartonQty

rst.MoveNext
Next I
End If

But still it is retruning zero record white there is data in footer table. I mean rst.RecordCount = 0 although there I dont find any error..

Please extend your help...

 

[Ashfaque]

I just noticed that if I am keeping only AccNewCode criteria then it works. But if I adds more criterias like dates even with # as advised by Paul, it wont works..I even changed the order of keeping date criteria first and AccNewCode criteria as second and vice versa but no use...

Why like this...

Any idea and solution....

 

[pbaldy]

Did you review the link about dates having to be in US format in VBA?

 

[Ashfaque]

Yes, I read.

Does it mean that if I copy the code into a module and use it in query, it will retrive the data which already stored in whatsoever format?

Better I will try it....then I will come back to you

 

[Ashfaque]

I tried and want to know my subform is based on table. The combo placed in subform is based on query but that is receiveing data from other table called T_GiftMaster. So in my below criteria how can I use the function.

Where AccNewCode = '" & Forms!F_Gift_C!AccNewCode & "' And (Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And InvDate <= #" & Forms!F_Gift_C!TxtTo & "#) GROUP BY AccNewCode;")

Please extend help....

 

[Ashfaque]

Hi,

I have attached the db with min records....please select date range as 01-01-09 to 30-04-09 and customer from combo AL MASA2. The customer purchased 125 cartons during these period.

Then issue gift to him let us say cordless phone. To receive cordless phone customer must purchase min 10 cartons so we can issue him 1 set of cordlessphone. Once issued, then Green textbox should show / store 115 cartons balance and so on....I need advise on one more thing..this TxtBalCartQty text box should unbound or I should make a field in the table? Which is better...

I am stuck up....

Please extend help..

 

[Ashfaque]

I am still waiting for reply from forum....

Thanks..

 

[Darth Vodka]

use debug.print to stick your sql in the immediate window, then copy the sql into a query

 

[Ashfaque]

Thanks..but this doesnt suit....