Text box and SQL to accept number or string, how ???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a text box and a GET button. This is for a user to find a record
The records have an autonumber key (long)
HOWEVER, I also have an alternate reference number table with pointers to the autonumber keys, think of it as an Alias table. These aliases can be numeric or alphanumeric
I want the logic to first search for a regular key and then if it not found to scan the alternate keys
In setting up my first SQL to look for the regular autonumber key, I can't get it to work if I enter an alpha string
Where ID = number fail
If I change it t
Where ID = "'" & number & "'
it also fail

How do I get around this???
 
Say ID is the name of the autonumber field in one table, and AID is the name
of the alternate ID field in the other table, and txtSearch is the name of
the textbox on the form.

Put this in the txtSearch BeforeUpdate event:

(untested)

if isnumeric (me![txtSearch]) then
msgbox "WHERE [ID]=" & me![txtSearch]
else
msgbox "WHERE [AID]=""" & me![txtSearch] & """"
endif

You'd want to cut & paste that code, to get the quote marks correct.

The purpose of the msgboxes is just to display the WHERE clause that you
would use in the "real" code.

PS. I hope you have not called your textbox, number! That is a reserved
word in Access, Look up "reserved words" in online help. Start using some
coding conventions, for example, naming all textboxes with the "txt" prefix.

IsNumeric() is known to return True for various values that most people
would say, are >not< numeric. So you could look at other ways of determining
whether the user's entry was numeric, or not.

HTH,
TC


David said:
I have a text box and a GET button. This is for a user to find a record.
The records have an autonumber key (long).
HOWEVER, I also have an alternate reference number table with pointers to
Click to expand...
the autonumber keys, think of it as an Alias table. These aliases can be
numeric or alphanumeric.
I want the logic to first search for a regular key and then if it not
Click to expand...
found to scan the alternate keys.
In setting up my first SQL to look for the regular autonumber key, I can't
Click to expand...
get it to work if I enter an alpha string
 
Thanks for the input. I created a function

Private Function GoodNumb(varX As Variant) As Boolea
On Error GoTo GoodNumb_erro
varX = varX /
GoodNumb = Tru
Exit Functio
GoodNumb_error
GoodNumb = Fals
End Function
 
Back
Top