General discussion

Locked

Access 97 Query

By loydster ·
I have a query with 2 fields. One is a Slip number, which is a number that should increment by one for each record.

Is there anyway to have access tell from one record to the next if the number goes up by more than one? ie. a Slip has been skipped.

These numbers are hand entered, because they relate to hard copies. It cannot be auto incrememnt.

Thx, Loyd

This conversation is currently closed to new comments.

23 total posts (Page 2 of 3)   Prev   01 | 02 | 03   Next
Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 97 Query

by timfox In reply to Access 97 Query

Me, above :)
TimFox

Collapse -

Access 97 Query

by loydster In reply to Access 97 Query

Sorry guys...the last one was easiest for my n00b mind to understand :)

Collapse -

Access 97 Query

by loydster In reply to Access 97 Query

Sorry guys...the last one was easiest for my n00b mind to understand :)

Collapse -

Access 97 Query

by loydster In reply to Access 97 Query

How do I reference the previous records?

Collapse -

Access 97 Query

by spidee In reply to Access 97 Query

You need to use a temporary table:

1. Add a temporary table that stores the slip#.
2. Create a function where everytime a user updates the slip# from the main table, it also updates the temporary table.
3. So next time the user updates/inserts the next slip #, you need to compare the slip# field in the temporary table and your main table if >2

Hope this works.

Collapse -

Access 97 Query

by loydster In reply to Access 97 Query

Poster rated this answer

Collapse -

Access 97 Query

by loydster In reply to Access 97 Query

Poster rated this answer

Collapse -

Access 97 Query

by kees.valkenswaard In reply to Access 97 Query

Well, your problem could perhaps be solved with a query without any VBA.
Suppose you have the slip numbers and other info in a table T. Make sure that field slip is the primary key to avoid duplicates.
Design a query D based on table T. In the grid add field slip and two additional fields:
Plus: [slip] + 1
Min: [slip] ? 1
Sorting ascending for slip may be useful
Design a query Overview based on table T, query D and once more query D which will be called D_1
Remove the links between the slip field. Make a new link between slip in T and plus in D. Change the join type to show all records in T. Also a link between slip in T and min in D_1 with the same join type.
Enter into the grid field slip from T ascending.
Add a new visible field:
What: IIf(IsNull([D].[plus]);IIf(IsNull([D_1].[min]);"start/end";"start");"end")
NOTE If you apply the old-fashioned non-standard US-system ; [semi-colon] may have to be replaced by , [comma]
Add field plus from D with criteria is null
Add field min from D1 with criteria is null on the next line in the grid
Both fields should be invisible [the check box cleared]
Suppose T contains the following slip numbers:
3-5, 7, 9-10, 13, 15-17, 34-120, 140, 150-2000
The query overview would yield:
slip What
3 start
5 end
7 start/end
9 start
10 end
13 start/end
15 start
17 end
34 start
120 end
140 start/end
150 start
2000 end

Good luck

Collapse -

Access 97 Query

by kees.valkenswaard In reply to Access 97 Query

If you want to check at input a macro would be required to compare the number with the maximum of the numbers already added. This would require real sequential entering of the slips. My query would allow non sequential entering, showing afterwards what is missing.

Collapse -

Access 97 Query

by kees.valkenswaard In reply to Access 97 Query

I hope you understood already that the ? in Min should be -
Thus:
Min: [slip] - 1

Back to Web Development Forum
23 total posts (Page 2 of 3)   Prev   01 | 02 | 03   Next

Related Discussions

Related Forums