DCount not working

[Ashfaque]

I placed a text box called TxtTotSo on sales order form F_SOHeader to see number of purchase orders I processed on a particular date.

The below code I need to work on after update event of a combo box that displays customercode and brings po details of a particular customer with Sales Order Date (SODate)

TxtTotSo = DCount("*", "T_SOHeader", _
"[CustomerName] = '" & CustomerName & _
"' AND [SODate] = #" & SODate & "#")

After complilation, looks good without error but doesnt show result in TxtTotSo box after event executed.

SODate & CustomerName are the same field names on form and table.

Whats wrong here? Any help.......

Regards,
Ashfaque

 

[sparks80]

Hi,

It is quite possible this is not working because the date format is being altered by the code. VBA expects the date to be in the format mm/dd/yyyy (the American date format). When handling dates in VBA you either need to convert to this format, or one that is non ambiguous (say dd-mmm-yyyy).

TxtTotSo = DCount("*", "T_SOHeader", _
                  "[CustomerName] = '" & CustomerName & _
                  "' AND [SODate] = #" & Format(SODate, "mm\/dd\/yyyy") & "#")

It is worth having a look at this page from Allen Browne, it explains what is happening very well:

http://allenbrowne.com/ser-36.html

 

[Ashfaque]

Thanks,

But Appearing 0 in text box...

I tried with below putting directly into textbox TxtTotSo.

= DCount("*", "[T_SOHeader]", "[SODate] =#" & Format([Forms]![F_SOHeader]![SODate], "dd/mm/yyyy") & "# and [Forms]![F_SOHeader]![Customername] = '" & [CustomerName] & "'")

It show total number of purchase order processed previous for that date BUT for all customers. Not for the customer that currently appearing on the form. Moreover sometime it works sometime not but that might be the refreshing require.

any other thoughts?

Ashfaque

 

[sparks80]

I think you need the field and form control for the customer name the other way round. Try this:

= DCount("*", "[T_SOHeader]", "[SODate]=#" & Format([Forms]![F_SOHeader]![SODate], "dd/mm/yyyy") & "# and [CustomerName]='" & [Forms]![F_SOHeader]![Customername] & "'")

For both the customername and date controls you can use the AfterUpdate event to force the calculated field to update.

 

[Ashfaque]

Thanks again Sparks.

This working for most of customers but it displays zero for particular customer. i.e. I selected ABC Project W.L.L. (customer name) but text box displays 0. But when select other customer, it displaying....strange....

Any reason why?

Thanks,

 

[Ashfaque]

Moreover, even when there are more than 1 order entered in the system, it shows only 0. This is happening with some other customers as well....

Please advise....

 

[sparks80]

Is it possible that the customer name contains a single quotation mark? Unless it was escaped Access would interpret this as the end of the string and you would not get a match.

Beyond that it is not obvious what is causing the problem. Could you try posting a small sample of the table (anonymised of course) so that I can take a look for you?

 

[Ashfaque]

Yes, the customer name may contain ' .

I am attaching a snapshot of my forms that shows the differences. When I select ABC Project W.L.L. Customer on a particular date it shows 0 But when I select other Customer like Trass Building Materials in other date, it displays POs for this customer is 4 and total purchase orders on that date is 9 which is absolutel correct.

Pls have a look at the pics.



Regards,
Ashfaque

 

[sparks80]

Hi,

It is difficult to diagnose the problem without a bit more information about how the form works.

Try entering the following SQL into a new query. Does this return a number greater than zero?

SELECT COUNT(*) FROM T_SOHeader WHERE SODate = #09/04/2012# AND [CustomerName] = 'ABC Project W.L.L.'

If not it would appear that there are no records for this customer on that specific date.

 

[Ashfaque]

I put into query and found that it displays displays zero.
Off course there is data for the customer.

Better i will attached my db with respective form & tbl with minimum records WITHIN FEW MINUTES so that might help to understand the problem.

Thanks,

 

[Ashfaque]

Hi,

Here is the database attached that included with necessary forms, modules and tables.

Please open the db holding with SHIFT key and run form F_SOHeader and then select below SO numbers from combo CboSearchBySONum to know what the result is being displayd.

SO#
975 - wrong result display
972 - wrong result display
969 - wrong result display
948 - Correct result display
952 - Correct result display

If you select so # 975 which has 3 po entered on 10/04/2012 but it is displaying 0 as in my 2 text boxes I placed to know Total Po and Pos for displying customer.

Simillarly if you select all first three pos, you will find the same result.

But with you select last 2 from the above list, it displays correct.

Please have a look and advice me if I went wrong.

Regards,
Ashfaque

 

[Ashfaque]

Hi,

Still waiting for someone to help me ..

Rgds,

 

[bob fitz]

Do you get the desired result if you use this in Text108.

=DCount("*","[T_SOHeader]","[SODate]=#" & Format(Forms!F_SOHeader!SODate,"[COLOR=red][B]mm[/B][/COLOR]/[B][COLOR=red]dd[/COLOR][/B]/yyyy") & "# and [CustomerName]='" & Forms!F_SOHeader!Customername & "'")

and this in TxtTotSo.

=DCount("*","[T_SOHeader]","[SODate] =#" & Format(Forms!F_SOHeader!SODate,"[COLOR=red][B]mm[/B][/COLOR]/[COLOR=red][B]dd[/B][/COLOR]/yyyy") & "# and [Forms]![F_SOHeader]![Customername] = '" & [CustomerName] & "'")

 

[Ashfaque]

Thanks Bob,

It worked.

It means there was nothing related with ' or / or * mark in customer names which I was thinking of initially.

But I didnt understand why bcz my defalut date format I set is dd/mm/yyyy and in DCount why the diff format is giving the correct result?

Regards,
Ashfaque

 

[bob fitz]

But I didnt understand why bcz my defalut date format I set is dd/mm/yyyy and in DCount why the diff format is giving the correct result?

Over the years, I've had and seen this kind of problem many times. Take a look at Allen Browne's site for an explanation of the cause and remedy.
http://allenbrowne.com/ser-36.html

 

[Ashfaque]

Thanks Again Bob.....

Ashfaque