transfer money from by Customers

[Mehvan]

Hello Everyone



I have a question about transfer money from by Customers

I have a table for transferring money ( InvoiceID , Customer_name , Date , amount and Note )

I would like to make query for transferring money , When 1 Customer don't transfer money more than 10 days , it tells me , this customer didn't transfer money 10 days or more .
and also I don't want His name appear less than 10 days , but when He transfers many times , His name don't repeat only last transfer + 10 days .



I used this Query , but When pass more than 10 days His name don't appear , I want to appear His name is more than 10 days .

[ranman256]

You cant add numbers to dates like integers, you must use DATEADD function

DateAdd("d",10,[date_in])

[Mehvan]

but I want to know how many days He didn't sent any amount , I would like to appear the number of days , not date/time
I would like to tell me , This customer didn't sent to you amount 15 days ago less or more .

[ssanfu]

I would use

Days: DateDiff("d",Date_in, Date)

Now() is date and time, Date() is the date only

In the query, under the criteria for the Days column, you might try entering:
>10



Would need to see raw data (records) and the expected results to give a better solution.

[Mehvan]

I already created it , but I don't want to repeat name of customers , and I want to use last date of sent money
use last date + 10 then tells me , this customer didn't sent money more than 10 days , it isn't care 10 days or more , only I want to tell me this customer didn't send money 10 days or more .



If we look at this above picture , we will see every customers sent money 2 times with difference date
but when I use : datediif ("d",[date_in],now()) , it's using the first date , it's appears in the below picture


I would like to use the last date and the first date isn't appear .

I used bu this way :

but when I want to open this Query , it's opening by this way



When I enter on OK button then it will open without a problem

[andy49]

Try this. Create a grouped query of maxed dates.



Then another linking maxed dates to the original table



Produces your expected output.


Sent from my iPhone using Tapatalk

[pbaldy]

You cant add numbers to dates like integers, you must use DATEADD function

DateAdd("d",10,[date_in])

Using DateAdd() gives more clarity, but you can do it:

?#1/22/17# + 2
1/24/2017
?now() + 1.5
1/22/2017 10:30:11 PM

[Mehvan]

thank you very much

I changed field of DateDiif from Group by to Expression .

[ssanfu]

You get the error for "MaxOFDate_In" because the query doesn't recognize that field name. Try using "date_in" - that field should have the max date because of the Max() function in the Total row.

[Mehvan]

field of ( date_in ) had a max in criteria , I used max for Date_in , and should I use that field I used max for it , because if I don't use that field I used max for it , it will gives me an error result

I used that way , but It gave me an error result , It didn't give me last date .