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