Query missing rows?

[drazen]

Can anyone help?
I have a table of invoices, what i am trying to do is enter a date in a text box then get the total invoices up to and including that date. Sounds simple, and it all works fine until....
If I enter a date, say 27/8/16 and the last invoice raised is actually on 20/8/16 it totals them fine, if (and this does happen) I have say 3 invoices on 20/8/16 then it only adds the first one. the SQL statement i am using is :-

stat_date is the date I enter

SELECT DISTINCTROW [Invoice No].Amount, [Invoice No].Payment, [Invoice No].[Client Ref], [Invoice No].Date
FROM [Invoice No]
GROUP BY [Invoice No].Amount, [Invoice No].Payment, [Invoice No].[Client Ref], [Invoice No].Date
HAVING ((([Invoice No].[Client Ref])=[Forms]![Event Log]![Client Ref]) AND (([Invoice No].Date)<=[Forms]![Event Log]![stat_date]))
ORDER BY [Invoice No].Date;

what am i doing wrong?
thanks.

 

[JHB]

3 invoices for the same client?
Show some sample data, the result you get and the result you want.

 

[CJ_London]

I don't see any totals in your query so don't understand how you can say 'it totals fine'

Also, you need to include # around the date otherwise sql will assume an American format date where there is any ambiguity (basically dates in the range 1-12th of the month). At the moment 27/8 is fine because there aren't 27 months in the year but 9/8 will be interpreted as 8th Sept.

change to

....AND (([Invoice No].Date)<="#" format([Forms]![Event Log]![stat_date],"mm/dd/yyy") & "#"))

Using distinctrow is pointless because it is ignored when there is only one table - see this link

https://bytes.com/topic/access/answers/209027-distinct-vs-distinctrow

and anyway, will be overridden by the group by

I have say 3 invoices on 20/8/16 then it only adds the first one

so the inference from this is that your 3 invoices with identical values in 4 fields you are selecting so they are all grouped together.

Finally, Date is a reserved word (it means today) so recommend you change it to a more meaningful name such as InvoiceDate otherwise you will start to receive misleading error messages.

 

[drazen]

I have tried to 'retro fit' a 'Client Statement' to our database.
we have 400+ clients, our database has been invoicing for about 15 years, (Access has been converted to 2010), I was not part of the original programming but I have done quite a bit to it to bring it up to date.
The invoice table didn't have a 'Payment amount' field until I modified it in Dec 2014. As there has been 18 months of accurate Invoicing and payment records, its time to do the Statement retro-fit. I have gone through all the clients and put a manual payment to all the invoices raised up to Dec 14, so the balance of all the clients accounts is correct.

The query above gives me a brought forward figure at the beginning of the statement. Basically it creates a statement from day 1 to the start of the statement I want to print.

I have a text box (stat_date) and button, I enter the date I want the statement to start (always ends 'today')

I have 3 text boxes on my form (just to monitor) 'invoices' 'payments' and 'os' (os is the opening statement amount)

I have this VB code for the button

invoices.Value = Nz(DSum("amount", "StatQ2"), 0)
payments.Value = Nz(DSum("payment", "StatQ2"), 0)
os.Value = Nz(invoices.Value - payments.Value, 0)
DoCmd.OpenReport "statQ1", acViewPreview

The os.value is then passed to the actual statement (statQ1) as the opening amount

Then testing, it works great. But one client has 3 invoices, all on the same day, 2 are the same amount. It is not adding the last invoice.
The invoices are the last 3 in the selection and its actually only the last one its not adding

It seems bazaar

 

[drazen]

I think I have sorted it. I have no idea why it was doing it but I re-wrote the SQL statement to :-

SELECT *FROM [Invoice No]
WHERE ((([Invoice No].[Client Ref])=[Forms]![Event Log]![Client Ref]) AND (([Invoice No].Date)<=Format([Forms]![Event Log]![stat_date],"dd/mm/yyyy")))
ORDER BY [Invoice No].Date;

It now works fine

Thanks for your help.

 

[CJ_London]

don't forget my comments about date and needing to format the date as US