Macros and Programming


CardboxForumsMacros and Programming > "Date Search"

Date Search

Posted By Post


6-Nov-2013 11:20

Can anyone help with a macro that runs a query on a date field, to exclude all dates between the current date and 3 years before that date? I can run it in a query by using Exclude 5/11/13:5/11/10 and that runs okay, but I would prefer it in a macro so that it always runs "todays date" and todays date - 1095 (days). Grateful for any assistance


6-Nov-2013 12:03

Further to my question above, I have come up with:

ActiveWindow.Select"FieldName",DateToCardbox(Date) & ":" & DateToCardbox(Date-1095)

But it does not work, could someone please point out my error?


6-Nov-2013 12:31

x = DateToCardbox(now,"04/07/1976") 'x = Cardbox notation date of today
y = DateToCardbox(DateAdd("yyyy", -3, now),"04/07/1976") 'y = Date -3 years from Now in Cardbox notation

'exclude the records.
ActiveWindow.Exclude "FIELDNAME",y & ":" & x



7-Nov-2013 09:42

Many thanks for your help, that macro runs, but does not select any records. If I run "Exclude 7/11/10:7/11/13" it selects 33 of 49 records. However, when I run that macro it does not select any records at all. Grateful for any further help you can give


7-Nov-2013 10:19

Check if you entered into the selection line right field name.
If you want to exclude on all fields change "FIELDNAME" to "".
Check also after running the macro using Ctrl-H (History window). You can see what is executed.


7-Nov-2013 11:24

I see now you exclude using two digit years (not millennium proof).

So in the macro you have to use also two digit years:

x = DateToCardbox(now,"04/07/76") 'x = Cardbox notation date of today
y = DateToCardbox(DateAdd("yyyy", -3, now),"04/07/76") 'y = Date -3 years from Now in Cardbox notation.

I always suggest using 4 digit year notation.


8-Nov-2013 09:14

Thank you, changed records to 4 digit year and it works!! Very grateful for your help.

© 2010 Cardbox Software Limited   Home