Field Type Mismatch on select

[AJ]

I have an input field (InputFieldA) that a user can type either a location#
(alphanumeric - could be all numeric) or zipcode (numeric).
I have the following select:

select * from TableA
where location = InputFieldA

I would like to just do something simple to select the information
regardlesss of whether they type in location# or zip. I thought something
like this would work:

select * from TableA
where location = InputFieldA
or zipcode = InputFieldA

This works if the field is a zipcode (numeric) but not if it contains
characters because it fails with a mis match on field type. (The location is
in quotes, the zipcode is not)

All the code is based on the loaction# so ideally that is what I am trying
to get to. Any thoughts?? Thanks.

 

[Wayne-I-M]

Hi

You could change the format of the zipcode to text.

BUT MAKE A BACKUP FIRST - I'm not sure about US zipcode but I think they are
not used in calculations so they can be placed as text.

 

[Tom Wickerath]

Why does your zipcode field need to remain numeric? You're likely not going
to be performing mathematical operations on zip code data. The following
query works just fine in the sample Northwind database, when search criteria
is entered into a textbox named InputFieldA, in a form named "Form1":

SELECT * FROM Employees
WHERE (((Employees.City)=[Forms]![Form1]![InputFieldA]))
OR (((Employees.PostalCode)=[Forms]![Form1]![InputFieldA]));


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[KC]

Could you to_char the zipcode ie change from numeric to character