Trying to resolve with an IF Statement

[J]

I have a table that is an auto-number field that creates sequential ticket #'s.

During testing I created several records in my table that were not real so I
deleted from the table.

The result is gaps between #'s.

I want to query on specific ticket #'s and if they do not exist, return a
[Not Found] dialogue box that allows you to enter another ticket #. It's
possible that the 2nd ticket # cannot be found either but I'll worry about
that later.

Initially, not realizing that the # keyed may not exist, I tried:

Like "*" & [IT #] & "*"

This worked with the exception that it returned a blank record, but I don't
know if/how to expand the Criteria to allow the option of trying a 2nd #.

Then I tried the following IF Statement:

IIf([IT #],[tbl_Incident_Table_MT]![Incident_IDNumb],[IT # Does Not Exist])

This worked really well in that if the ticket # doesn't exist a dialogue box
appears indicating the 'IT # Does Not Exist' and allows you to key in a 2nd
#. However, the query results were any given number of records, including
the 2nd #, but not exclusive to that #.

Thoughts?

Thanks

 

[Michel Walsh]

Under the column INcident_IdNumb, try the criteria:



= iif( 1 = DCount("*", "Incident_Table_MT", "Incident_IDNumb=" & [IT #]),
[IT #], [IT does not exist])





Vanderghast, Access MVP

 

[J]

Thank you for the assistance. Sorry it took me so long to get back to you.
--
J

Under the column INcident_IdNumb, try the criteria:



= iif( 1 = DCount("*", "Incident_Table_MT", "Incident_IDNumb=" & [IT #]),
[IT #], [IT does not exist])





Vanderghast, Access MVP

I have a table that is an auto-number field that creates sequential ticket
#'s.

During testing I created several records in my table that were not real so
I
deleted from the table.

The result is gaps between #'s.

I want to query on specific ticket #'s and if they do not exist, return a
[Not Found] dialogue box that allows you to enter another ticket #. It's
possible that the 2nd ticket # cannot be found either but I'll worry about
that later.

Initially, not realizing that the # keyed may not exist, I tried:

Like "*" & [IT #] & "*"

This worked with the exception that it returned a blank record, but I
don't
know if/how to expand the Criteria to allow the option of trying a 2nd #.

Then I tried the following IF Statement:

IIf([IT #],[tbl_Incident_Table_MT]![Incident_IDNumb],[IT # Does Not
Exist])

This worked really well in that if the ticket # doesn't exist a dialogue
box
appears indicating the 'IT # Does Not Exist' and allows you to key in a
2nd
#. However, the query results were any given number of records, including
the 2nd #, but not exclusive to that #.

Thoughts?

Thanks