Vic,
I don't do any editing with the queries. I just have them in edit mode becuase that is the default in Access and I don't mess with it.
The first two just increment a counter. It is very crude, but, as I said, I'm not really a VB programmer. My last programming events were using Quick Basic lots of years ago.
The third query has joins in it that pulls information from three tables, one of which is my counter. The counter makes the other two tables give-up data on a 1:1 relationship. The counter needs to run to 3335.
There are gaps in the numbering of my MFG_Groups. So, I'll need some way of not printing a report with no data. If this is too complicated, no big deal, I have another crude way of leaving the blank reports behind.
By the numbers, the queries are:
2)UPDATE TCounter SET TCounter.TCounter = [TCounter1]+1
WITH OWNERACCESS OPTION;
3)UPDATE TCounter SET TCounter.TCounter1 = [TCounter]
WITH OWNERACCESS OPTION;
4)SELECT M_MFG_GROUP_NOTES.MFG_GROUP, M_MFG_GROUP_NOTES.MFG_NOTE_ID, R_MFG_GROUP.MFG_NAME, R_MFG_GROUP.MFG_DESP, M_MFG_GROUP_NOTES.NOTE_TEXT, M_MFG_GROUP_NOTES.ADDED_BY, M_MFG_GROUP_NOTES.MODIFIED_BY, IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,9,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,8),IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,10,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,9),IIf(Mid(M_MFG_GROUP_NOTEs.last_modified,11,1) Like " ",Left(M_MFG_GROUP_NOTEs.last_modified,10),"ERROR"))) AS Moddate, M_MFG_GROUP_NOTES.LAST_MODIFIED INTO T2
FROM TCounter INNER JOIN (M_MFG_GROUP_NOTES INNER JOIN R_MFG_GROUP ON M_MFG_GROUP_NOTES.MFG_GROUP = R_MFG_GROUP.MFG_GROUP) ON TCounter.TCounter = R_MFG_GROUP.MFG_GROUP
ORDER BY M_MFG_GROUP_NOTES.MFG_GROUP, M_MFG_GROUP_NOTES.LAST_MODIFIED;
In that center section, I'm picking the mm/dd/yy out of a variable length field that has time stuck on it, an example of what is in that field is:
8/16/1997 2:03:21 PM
1/25/2006 8:45:27 AM
so, the query looks to find that first gap and then, based on that, takes the left hand set of characters.
I hope this helps.
Patrick