Missing entries

[Baldeagle]

I am using a query to populate my Sub-Ledger table with instructions for the allocation of gifts received from donors who have standing instructions for the allocation of gifts which they make to our charity by bank standing order. Everything is working fine except where two credits are received from a single donor in the same month. The result is that in respect of the second credits no entry is made in the Sub-Ledger table. Can you tell me what I have done wrong in my query? And how can I correct it? The earlier stage of the Macro puts all the month's credits into the Ledger table. I then want to make matching entries in the Sub-Ledger table for each of these entries which will contain usually 2 or 3 subdivisions of the gift.

I'm not very familiar with the forum and don't know where to find the 'CODE tag' for posting my code. So I apologise in advance for simply copying the SQL for my query here -
INSERT INTO [Sub-Ledger] ( [Ledger Number], [Send name?], [Paid?], Amount, Society, Instructions )
SELECT Ledger.[Ledger Number], Yes AS [Send Name], No AS Paid, CCur([Amount]*[Amount (Percentage)]/100) AS Share, [Standing Instructions for Allocations].Mission, [Standing Instructions for Allocations].Instructions
FROM [Standing Instructions for Allocations] INNER JOIN Ledger ON [Standing Instructions for Allocations].[Donor Number] = Ledger.[Donor Number]
WHERE (((Ledger.[Date Paid])>=DateSerial(Year(Date()),Month(Date())-1,1) And (Ledger.[Date Paid])<>DateSerial(Year(Date()),Month(Date()),0)))


ORDER BY Ledger.[Ledger Number];
I look forward to any assistance you can give me. If you need any further clarification just let me know.

[Baldeagle]

WHERE (((Ledger.[Date Paid])>=DateSerial(Year(Date()),Month(Date())-1,1) And (Ledger.[Date Paid])<>DateSerial(Year(Date()),Month(Date()),0)))

Actually the problem isn't as described in my first post!! I have discovered that the missing entries are gifts received on 31 October. Why does my query not select these gifts? Should it not select all gifts received in the previous month?

[JoeM]

I think the issue is with your WHERE clause:

WHERE (((Ledger.[Date Paid])>=DateSerial(Year(Date()),Month(Date())-1,1) And (Ledger.[Date Paid])<>DateSerial(Year(Date()),Month(Date()),0)))

Note that:
DateSerial(Year(Date()),Month(Date()),0)
returns the last day of the previous month, which would be October 31.

So this part of the criteria is saying to exclude anything whose Date Paid is October 31.
So, it sounds like it is "working as designed" (maybe not how it was intended!).

[Baldeagle]

I think the issue is with your WHERE clause:

WHERE (((Ledger.[Date Paid])>=DateSerial(Year(Date()),Month(Date())-1,1) And (Ledger.[Date Paid])<>DateSerial(Year(Date()),Month(Date()),0)))

Note that:
DateSerial(Year(Date()),Month(Date()),0)
returns the last day of the previous month, which would be October 31.

So this part of the criteria is saying to exclude anything whose Date Paid is October 31.
So, it sounds like it is "working as designed" (maybe not how it was intended!).

Thanks for the clarification. I have now changed "<>DateSerial........" to "<=DateSerial....." and that includes the gifts received on the last day of the month.