Check date of last visited

[friendly]

Using VBA wanted to make sure the client's last visit date to the Bank
and not allow him to exchange within a specified time period, for example, 20 days

I know it's annoying you but thank you

 

[CJ_London]

something like

if dcount("*","visitstable","clientID=" & me.clientID & " AND VisitDate>Date()-20")=0 then
    'not visited within 20 days
else
    'visited within 20 days
end if

 

[pbaldy]

Both posts moderated, now approved.

 

[arnelgp]

If DateDiff("d", Nz(DMax("DateVisited", "Table", "ID=" & ClienID), 0), Date()) < 20 then
' visited the bank less than 20 days
Else
' visited the bank 20 or more days
End if

 

[friendly]

Extend to all sincere thanks and appreciation

 

[friendly]

Dear
can you help me apply the function (DateDiff) described as:

table = tblClients
Date Field = LastVisitGDate
txtboxID = txtClientID

thank you

 

[CJ_London]

if you are going to use the datediff and dmax functions, you need to protect against null values (i.e. no visits recorded) which is why I suggested the dcount method

If you want a translation from the proposal to something you can copy and paste, provide proper details of the tables - it seems unlikely you would be storing a record of visits in a table called tblClients - which implies a single record per client so neither dmax or dcount would be relevant

 

[friendly]

I've tried using function (dcount)
unfortunately didn't work out the way with me
I hope help me apply it.
thank you

 

[friendly]

Is it possible to apply to these way:

table = tblClients
Date Field = LastVisitGDate
txtboxID = txtClientID


if dcount("*","tblClients","clientID=" & me.txtclientID & " AND LastVisitGDate>Date()-20")=0 then
'not visited within 20 days
else
'visited within 20 days
end if

 

[CJ_London]

assuming your field in tblClients is called ClientID and is numeric and your table is constructed something along the lines of

ClientID LastVisitGDate
1...........01/01/2016
2...........05/01/2016
3...........14/02/2016
1...........15/02/2016
1...........30/04/2016
2...........03/05/2016

then it should work, but as indicated in my second paragraph, I suspect your table is not constructed in this way

 

[friendly]

This is My Database:

 

[CJ_London]

even from this small scrap of information, it is clear your data is not normalised (either clientname fields or the lastvisit fields should be in a different table)

Also clientID is your primary key, so each value will be unique - ergo you cannot have multiple visits against a single client ID

Result is that nothing will work as you require. Suggest you google 'normalisation', learn about primary and family/foreign keys (PK & FK) and take a look at some of the templates provided for Access to get a better idea of how to construct your tables

I've returned your db modified to a more normal structure, although I have not made ClientPK an autonumber

 

[friendly]

Super Moderator CJ_London
thank you my sincere thanks and appreciation for your help.

 

[friendly]

Thank you for your feedback and I've got a good idea about (PK * FK)

but my idea is that all visits are saved in the table (tblVisits)
while retaining the last visited in the table (tblClients).

so all I want is last date with history compare
current table (tblClients), not counting over 20 days to data recording and when passed to register
thanks for squeezing

 

[CJ_London]

but my idea is that all visits are saved in the table (tblVisits)
while retaining the last visited in the table (tblClients).

that is not normalised data and can result in errors - you should not be storing data twice.

But if that is what you want to do either solution will still work (subject to the issue you have with clientPK/FK and the first paragraph of post #7) - you might also want to look at the dlookup function an alternative to the dmax function - and perhaps the data is already loaded in your form?

I don't believe in encouraging people to developing what becomes complicated solutions on an unnormalised data structure so good luck with your project