Trying to implement something like a while loop... newbie here, so be gentle please

[ddc_operations]

I have macro to update a database based on the date I pull some records. I built a form to enter the date, then I press a button I created to append the info corresponding to that date into an archive table. Very easy for single dates.



However, I also want to be able to run it for a range of dates without having to manually enter each one. So, I built a form in which I can input a "start_date" and an "end_date" and would like to figure out how to build a macro/write code so that I can set a local var (i.e. "temp_date") to "start date", then call a loop that says "while "temp_date" <= "end_date" call "append macro". then set "temp_date" to "temp_date + 1". (which should then just jump back to the top of the while loop until "temp_date" = "end_date")

Any advice on how to do this? I have not really hand-coded in Access before, and don't know how to do it, nor where to actually call whatever function I code. I am sure I can figure out the exact syntax without much hassle by looking it up on the internets, but it's really how to implement it within the Access landscape that I don't know how to do.

I hope that's clear enough to understand, and I appreciate any advice in advance.

Side note: even though I have a "set warnings" = "no" entry in my macro, I am still presented with several pop-ups making me click "yes" through the append process. Has anyone experienced this before and know a way around it? I have used that command within a macro build before (successfully), and I can't figure out why it would not work now.

Thanks all!

[Gicu]

I assume you run a couple of action queries, first an append query from your source table to the archive followed by a second delete query to remove those records from the original source. If that is right the easiest way to do what you're asking is to modify these two queries to use

Between Forms!frmYourForm!StartDate And Forms!frmYourForm!EndDate

in the criteria row for that specific date field instead of just

=  Forms!frmYourForm!YourDate

.

Cheers,

[ddc_operations]

Thank you! This is a great solution; and I thought we had it! But it doesn't work... for the following reason, which maybe you can help me work through... I had forgotten that the append query actually invokes several other queries that uses a variable '_date' (from the original enter date form that I use to do each append date one at a time) to do what they have to do for each specific date... so I would have to define '_date' as "start_date" within the criteria of the main append query and then '_date' as 'start_date'+1 and so on, until I reach 'end_date' (i think, at least).

Any ideas? I know this is a bit clunky, but like I said, it's my first time around, so some stuff is weird. I don't know if it's possible to share the database somehow (provided I can remove any sensitive info), but if that can be helpful, please let me know how.

Thanks!

[Gicu]

You need to use the date interval (criteria) in either the final query (append) or the source queries, no need to have it in both. To do what you describe (the loop) you will need to use VBA so it might get a bit complicated. It would be great if you could upload a small sample of your db with only the objects (tables\queries\forms) required to illustrate the issue (and no sensitive data). You should zip it then click on the Go Advanced button and click the Manage Files button (the forum also has a How To Attach Files shortcut at the top right of the page).

Cheers,

[ddc_operations]

Thanks again... I will upload it in the morning tomorrow... I have to pull out a lot of sensitive stuff, and won't have the time today. I look forward to seeing what you have to say tomorrow (should you have time). Thanks!

[ddc_operations]

Attached (hopefully) is a zip file of what I am working on -- there are some unused queries, tables, and macros in there, but I worry when I start deleting stuff that I will mess everything up. I removed all sensitive info from the tables (again, hopefully), and I believe it still works.

Currently, on FRM_MF there are several buttons... the three useful ones are
- enter date (when the pop up opens, do not use any button on there except for the exit one)
- choose date range (that pop up is where i pull the report I want from)
- update to archive

My process after I get the data into the tables (which, i have linked in my full DB):
- enter date, then click update to archive (for each date I want to add to the archive)

To get my report:
- choose date range, enter the dates I want reported, click preview report in choose date range window

you can delete several rows of dates from the archive table and then run as above to see it in action

So, what i'd like to do, is to be able to update my archive with a range of dates, so once the tables (push-post, via, vib, viwod) are filled with data, I can don't have to go through a freaking click parade to get all of the pertinent data updated to my archive.

Feel free to bash any part of my design or strategy, as it will help me learn! I won't be insulted.

Thanks again for your help!

[Gicu]

Hi,

I had a quick look and found out that there is an problem with what you are trying to implement. I will not touch much on the design as it seems that you put a lot of time into it (naming convention is a big one but again I'll leave it for now).

The main obstacle(s) in implementing the desired archiving by date range lays within the design of the five APP_ queries. Specifically the calculated field that replaces the missing various dates with the date entered in the LU_Date table. If you would add multiple days to LU_Date (from start date to end date) those queries will return multiple records (cartezian product) for each record (one set for each day in the LU_Date) because the lack of joins between the tables. And from there all the other queries that are using them will get impacted by this.

Not sure how you would solve this, you could choose to use either the start or the end date like in this updated expression:

DATE APPROVED1: IIf([date approved] Is Null,[StartDate],[date approved]) 'note that StartDate would be calculated as min(LU_Date]![date_])

Let me know please.

Cheers,

[ddc_operations]

Thanks again, Vlad. Not sure that the solution you suggest would work for my needs, as I must have dates correctly corresponding to the activity that i am tracking, and as I understand what you are saying, when running a range of dates I would have to choose which one gets appended to all of the data (correct me if I'm wrong)

Although, the way I am doing it now is (I'm sure VERY) inefficient, it does work, so I don't know how wise it is to continue to band-aid a less than optimal database. In the short term, I can use what I have.... HOWEVER.... I am very interested to hear what you have to say about the design. I know it's clunky and very "green". I'm happy to scrap it and build again from the ground up if you have some suggestions. It would give me more experience and help me keep good practices going forward, as I have a few new projects I need to get a jump on coming up.

You rock, man. Really appreciate the help.

[Gicu]

You're very welcome!

I am attaching an updated version so you see what I meant. Only the missing dates will be replaced by the start date, if the dates are populated they will stay as recorded. The biggest question is why are those dates missing in the source tables. Can you update them in there during the loading process? If yes, then the new process with having a date range would work OK.

I would recommend you start looking into using VBA instead of macros when developing as there is a lot more information available on the web and most Access developers prefer it over macro language. Access has a built in button on the form design to convert the existing macros in VBA, and, while the output is not the prettiest, it will help you understand the construct.

Following sound naming conventions is also very important as it will make development easier to implement and follow. Have a look at this link for one example:http://www.databasedev.co.uk/naming_conv.html. In general you should avoid spaces and special characters in the any names (fields, objects) as they could create problems and will force you to wrap them in square brackets.

Cheers,
Vlad

[ddc_operations]

All of this advice is awesome. Thank you. To answer your question, the reason (at least as I remember it) that some dates are missing in the source table is the way I was handling when an "operator" had no activity for a particular function on a particular day. It creates the record so that it can be displayed alongside the others, and then a "0" is counted; so it was a necessary evil to get the output that I desired (again, that's if I remember and read back on my programming of the queries correctly).

I would like to learn more about VBA, as I prefer the ability and flexibility to code by hand when necessary and I feel that I get a little lost in Access in terms of remembering why I did what I did (at least coding by hand allows you to more easily document what's going on).

I'll start a ground up build again and see what I can do; might take me a bit, but I think it will be worth it in the long run, and I will for sure implement the appropriate naming conventions so as not to drive myself crazy in the future.

Thanks again - and if you have any other suggestions... let 'em fly!

-D

[Gicu]

Good luck with your project and come back here if you have any more questions!

Cheers,