IF Statement in Query - Quick Question

  • Thread starter Thread starter J
  • Start date Start date
J

J

Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
 
First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?
 
Hey Duane, thanks for the quick response.

Yes, I know the autonumber rule and unfortunately it has become an
identifier, not a transaction recorder, however I will fix that in the
future...

The [IT #] and [IT Does not exist] are the dialgue box that generate off the
search button. So - Search by IT # - IT #? (if that # doesn't exist) then,
if that # has been removed from the table, [IT # does not exist], enter
another IT # to look for.

I hope that makes it clearer.

J


--
J


Duane Hookom said:
First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?

--
Duane Hookom
Microsoft Access MVP


J said:
Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
Click to expand...
Click to expand...
 
I am lost as to what you want to accomplish. If you want a user to search on
a ticket number, use a combo box on a form. Then you can be sure the ticket
number will exist.

IMHO, there is no place for dialog prompts in any application. You can maybe
get by with prompting for MsgBox responses like Yes, No, Cancel but that is
all.

--
Duane Hookom
Microsoft Access MVP


J said:
Hey Duane, thanks for the quick response.

Yes, I know the autonumber rule and unfortunately it has become an
identifier, not a transaction recorder, however I will fix that in the
future...

The [IT #] and [IT Does not exist] are the dialgue box that generate off the
search button. So - Search by IT # - IT #? (if that # doesn't exist) then,
if that # has been removed from the table, [IT # does not exist], enter
another IT # to look for.

I hope that makes it clearer.

J


--
J


Duane Hookom said:
First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?

--
Duane Hookom
Microsoft Access MVP


J said:
Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I'm not sure why this seems so complicated.

I have a button, on a menu, not a form. That menu button calls the query:
What's the IT #? I enter the IT #.

IF that IT # entered DOESN'T exist, I want the false part of the IF
statement to return 'IT # does not exist', at which time I enter ANOTHER IT #.

The code provided by the initial thread WORKS based on the premise that the
True portion of the IF statement isn't met. The query then presents me with
the dialogue box 'IT # does not exist'. The False portion of the IF statement.

The issue is that the True ISN'T working. The query returns the False
results even when the True portion is fulled.

Example: I know that IT # 2101 exists. When I enter 2101 in the 'IT #',
the True part of the statement, the query returns 'IT # does not exist'. If
I enter the SAME IT # in answer to the False portion of the statement, the
query returns the form with the correct IT # information.

The reason I'm not using a Combo box is because I don't want to go past the
menu to return my query.
J


Duane Hookom said:
I am lost as to what you want to accomplish. If you want a user to search on
a ticket number, use a combo box on a form. Then you can be sure the ticket
number will exist.

IMHO, there is no place for dialog prompts in any application. You can maybe
get by with prompting for MsgBox responses like Yes, No, Cancel but that is
all.

--
Duane Hookom
Microsoft Access MVP


J said:
Hey Duane, thanks for the quick response.

Yes, I know the autonumber rule and unfortunately it has become an
identifier, not a transaction recorder, however I will fix that in the
future...

The [IT #] and [IT Does not exist] are the dialgue box that generate off the
search button. So - Search by IT # - IT #? (if that # doesn't exist) then,
if that # has been removed from the table, [IT # does not exist], enter
another IT # to look for.

I hope that makes it clearer.

J


--
J


Duane Hookom said:
First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?

--
Duane Hookom
Microsoft Access MVP


:

Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
Click to expand...
Click to expand...
Click to expand...
 
I missed any mention earlier of a button on a menu that calls a query. I find
this a bit unconventional and would just use the button to open a form for
the user interaction. If that's not good enough for you, I hope someone else
can provide the solution.
--
Duane Hookom
Microsoft Access MVP


J said:
Sorry, I'm not sure why this seems so complicated.

I have a button, on a menu, not a form. That menu button calls the query:
What's the IT #? I enter the IT #.

IF that IT # entered DOESN'T exist, I want the false part of the IF
statement to return 'IT # does not exist', at which time I enter ANOTHER IT #.

The code provided by the initial thread WORKS based on the premise that the
True portion of the IF statement isn't met. The query then presents me with
the dialogue box 'IT # does not exist'. The False portion of the IF statement.

The issue is that the True ISN'T working. The query returns the False
results even when the True portion is fulled.

Example: I know that IT # 2101 exists. When I enter 2101 in the 'IT #',
the True part of the statement, the query returns 'IT # does not exist'. If
I enter the SAME IT # in answer to the False portion of the statement, the
query returns the form with the correct IT # information.

The reason I'm not using a Combo box is because I don't want to go past the
menu to return my query.
J


Duane Hookom said:
I am lost as to what you want to accomplish. If you want a user to search on
a ticket number, use a combo box on a form. Then you can be sure the ticket
number will exist.

IMHO, there is no place for dialog prompts in any application. You can maybe
get by with prompting for MsgBox responses like Yes, No, Cancel but that is
all.

--
Duane Hookom
Microsoft Access MVP


J said:
Hey Duane, thanks for the quick response.

Yes, I know the autonumber rule and unfortunately it has become an
identifier, not a transaction recorder, however I will fix that in the
future...

The [IT #] and [IT Does not exist] are the dialgue box that generate off the
search button. So - Search by IT # - IT #? (if that # doesn't exist) then,
if that # has been removed from the table, [IT # does not exist], enter
another IT # to look for.

I hope that makes it clearer.

J


--
J


:

First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?

--
Duane Hookom
Microsoft Access MVP


:

Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
Click to expand...
Click to expand...
Click to expand...
 
Well, thanks for your response.
--
J


Duane Hookom said:
I missed any mention earlier of a button on a menu that calls a query. I find
this a bit unconventional and would just use the button to open a form for
the user interaction. If that's not good enough for you, I hope someone else
can provide the solution.
--
Duane Hookom
Microsoft Access MVP


J said:
Sorry, I'm not sure why this seems so complicated.

I have a button, on a menu, not a form. That menu button calls the query:
What's the IT #? I enter the IT #.

IF that IT # entered DOESN'T exist, I want the false part of the IF
statement to return 'IT # does not exist', at which time I enter ANOTHER IT #.

The code provided by the initial thread WORKS based on the premise that the
True portion of the IF statement isn't met. The query then presents me with
the dialogue box 'IT # does not exist'. The False portion of the IF statement.

The issue is that the True ISN'T working. The query returns the False
results even when the True portion is fulled.

Example: I know that IT # 2101 exists. When I enter 2101 in the 'IT #',
the True part of the statement, the query returns 'IT # does not exist'. If
I enter the SAME IT # in answer to the False portion of the statement, the
query returns the form with the correct IT # information.

The reason I'm not using a Combo box is because I don't want to go past the
menu to return my query.
J


Duane Hookom said:
I am lost as to what you want to accomplish. If you want a user to search on
a ticket number, use a combo box on a form. Then you can be sure the ticket
number will exist.

IMHO, there is no place for dialog prompts in any application. You can maybe
get by with prompting for MsgBox responses like Yes, No, Cancel but that is
all.

--
Duane Hookom
Microsoft Access MVP


:

Hey Duane, thanks for the quick response.

Yes, I know the autonumber rule and unfortunately it has become an
identifier, not a transaction recorder, however I will fix that in the
future...

The [IT #] and [IT Does not exist] are the dialgue box that generate off the
search button. So - Search by IT # - IT #? (if that # doesn't exist) then,
if that # has been removed from the table, [IT # does not exist], enter
another IT # to look for.

I hope that makes it clearer.

J


--
J


:

First, I never display my autonumbers to users. If you want or need
consecutive numbers with no gaps then you will need to build some solution to
create the numbers.

What are [IT #] and [IT does not exist]? Are these fields or what?

--
Duane Hookom
Microsoft Access MVP


:

Hey, How can you tell I'm a part-time programmer, my first question was in
January 2009....

I was provided this code by MS, a prior question.

'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.' IT's LATER....

This is the code:

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

Oddly, it only takes the IT # on the 2nd pass. So if I enter IT # 2010,
dialogue box 'IT does not exist' comes up, I enter 2010 AGAIN and it presents
in the form.

Thoughts.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top