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 1 of 3)   01 | 02 | 03   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 97 Query

by mslizny In reply to Access 97 Query

You need another field, with an IIF function something like this:

IIF SlipNo. is not equal to previous record SlipNo. +1, Yes, No

This means that in this field if the SlipNo. is not equal to the previous record's SlipNo.=1, you would see the value No, otherwise it would be Yes.

You would want to be sure the SlipNo. field is truly a number field.

Collapse -

Access 97 Query

by mslizny In reply to Access 97 Query

sorry, due to a typing error, the "This means" should be SlipNo.+1

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 Shanghai Sam In reply to Access 97 Query

When do you want to do the check?
If you want to do the check after a user enters the number, then I would use a function to do the check at whatever point needed (ie. Close form, textbox_After_Update etc). Do a SELECT query in the function and ORDER BY slipNo, move the cursor to the last entered record and check the SlipNo value. If this value is not equal to the current slip number minus one, then raise the error. (All as follows:)
------------------------Start code example
Private Function Last_Slip_No As long

Last_Slip_No = 0

Dim dbsSlipper as Database
Dim rstSlips as Recordset

set dbsSlipper = CurrentDb
set rstSlips = dbsSlipper.Execute ("SELECT SlipNo FROM tblSlips ORDER BY SlipNo")

if rstSlips.RecordCount > 0 then
rstSlips.movelast
Last_Slip_No = rstSlips.fields![SlipNo]
end if

rstSlips.close
dbsSlipper.close

End Function
------------------------End code example

This function example should return a number to a calling function that should check for a returned value of 0 (zero), which would indicate an error.

This all for if you want the check performed at a data-entry level.

If you want a data entry check done, you could use the above code with minor changes to fill a text string with the slip numbers that have not been entered within a range.
Another consideration is (if this is a multi-user situation), if another user may be entering the missing number while the check is being run...

Hope it helps
TimFox
(another posting with more info might help if I have missed the point)

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 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 :)

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

Related Discussions

Related Forums