Home

Macros and Programming

 

CardboxForumsMacros and Programming > "Date Search"

Date Search

Current user: [none]
Register / Log In · Help

Posted By Post

PeterD

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

PeterD

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?

bert

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

Success
Bert

PeterD

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

bert

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.
Regards
bert

bert

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.
Regards
Bert

PeterD

8-Nov-2013 09:14

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

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home