Field Type Mismatch on select

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.
0
Utf
1/23/2008 2:21:13 AM
access 16762 articles. 3 followers. Follow

3 Replies
480 Views

Similar Articles

[PageSpeed] 56

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.


-- 
Wayne
Manchester, England.



"AJ" wrote:

> 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.
0
Utf
1/23/2008 8:40:01 AM
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
__________________________________________

"AJ" wrote:

> 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.
0
Utf
1/23/2008 8:41:00 AM
Could you to_char the zipcode ie change from numeric to character

"AJ" wrote:

> 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.
0
Utf
1/23/2008 4:04:01 PM
Reply:

Similar Artilces:

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

When hitting reply to a message, no "Bcc:" header field appears
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi. When I hit &quot;Reply&quot; or &quot;Reply All&quot; to a message in Entourage, the composition window that appears has the usual &quot;To:&quot;, &quot;Cc:&quot;, and &quot;Subject:&quot; form fields near the top, but not one for &quot;Bcc:&quot;. I've looked all over Preferences but don't see an option for this. What am I missing? <br><br>Thanks, <br><br>-c On 2/26/10 11:51 AM, cmetzler@officeforma...

Fields
I am working in an education template and want to see if there is a way to create a field that can be added to periodically to show all the courses a person takes from my instructors. I created a field named course but it is only allowing me to put one course in. How can I make the field list all the courses? Thanks, Nantika -- Enjoy every day! On Sat, 17 Apr 2010 09:18:01 -0700, Nantika <Nantika@discussions.microsoft.com> wrote: >I am working in an education template and want to see if there is a way to >create a field that can be added to periodically ...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Change Backcolor for the selected line
I would like to be able to highlight or change the color of a single line on a subform formated as a continuous form. The user selects one line from many possible lines on a continuous form to respond to. When he selects the line another pop-up form is display which the user must complete. I would like to maintain some kind of highlight (backcolor?) so that the user can clearly see which line he is reponding to. Setting the backcolor after selecting a line sets the back color for the entire form, which really defeats the purpose. Any help? -- Message posted via AccessMons...

modify description field in reports
When I print my PO's I can make the part # field in the view report screen wider but when i print it, it defaults back to the regular size. The problem is it cuts off my part numbers on some of my long numbers. Is there a way to overcome this? you must modify the po.xml file directly. "www.choppersapparel.com" <www.choppersapparel.com@discussions.microsoft.com> wrote in message news:8B4E31D5-10A7-4A99-913D-F8F6E3BD7043@microsoft.com... > When I print my PO's I can make the part # field in the view report screen > wider but when i print it, it defaults ...

How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Mismatched Memory -- could this be my problem?
Could mismatched memory modules be the reason why my computer freezes up with some programs? The SIW utility tells me that my two memory modules don't seem to be matched. One is DDR and the other is DDR2. The specs for my ABIT KU8 motherboard call for DDR, not DDR2, if that makes a difference. Here is what the ISW utility shows? MEMORY SUMMARY Maximum Capacity 1024 MBytes Maximum Memory Module Size 4096 Memory Slots 2 Error Correction None DRAM Frequency 163.6 MHz <--- should be 200.07? Memory Timings 3-3-3-7 (CL-RCD-RP-RAS) CAPACITY Slot1 1536MB <--- wrong for K...

Data types.
Hello, I have TextBox1 with this Value = 57.7 I need do something like this MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 I need transform text value to number value. Thanks Tom I found the problem. I need transform 57.7 to 57,7 "Tom" <tom@fake.com> p�e v diskusn�m p��sp�vku news:ezqjoGcLEHA.2260@TK2MSFTNGP09.phx.gbl... > Hello, > > I have TextBox1 with this Value = 57.7 > I need do something like this > > MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 > > I need transform text value to number value. > &g...

Multi-Select Listbox VBA code causing overflow in loop
Private Sub Form_Current() Dim oItem1 As Variant Dim oItem2 As Variant Dim bFound1 As Boolean Dim bFound2 As Boolean Dim sTemp1 As String Dim sTemp2 As String Dim sValue1 As String Dim sValue2 As String Dim sChar1 As String Dim sChar2 As String Dim iCount1 As Integer Dim iCount2 As Integer Dim iListItemsCount1 As Integer Dim iListItemsCount2 As Integer sTemp1 = Nz(Me!SignsList.Value, " ") sTemp2 = Nz(Me!SymptomsList.Value, " ") iListItemsCount1 = 0 iListItemsCount2 = 0 bFound1 = False bFound2 ...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Select more than 1 object in the lookup field
I would like to have the ability to select more than one object in a lookup field. Is there a way to accomplish this? Thanks No. The only exception to this is the party list (like the to, cc and bcc fields in an email), but this is an internal thing and cannot be used in customizations. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup ---------------------------------------------------------- "Onetreeup" <Onetreeup@discussions...

granting users Send as permissions causes errors unless they select the FROM address from the GAL
I've got some users who have two mailboxes, each with a different address. They need to be able to select which address they send from, on occasion. I've given them "send on behalf" and "send as" permissions the 2nd accounts. But, unless they select the FROM address from the GAL, they get the following error: You do not have permission to send to this recipient. If they manually type in the address, they receive this error. This is only a problem because I would prefer to hide the 2nd account from the GAL, to avoid confusion. Does anyone know a way around this?...

Keep data that is entered in a field until changed
Access 2000, I want to retain a field contents that is entered, until it is changed. Like a default, except that it will only change whenever the user changes the field contents. How do I accomplish that? "lths-kblank" wrote: > Access 2000, I want to retain a field contents that is entered, until it is > changed. Like a default, except that it will only change whenever the user > changes the field contents. How do I accomplish that? If you set a default value for a field, the field will retain that value. If you change the value, the field will save the change. ...

Combining two types of functions. Select Query and DLookup
I would like to write a code that performs the following action: 1. If the value in a comboBox Project Number is 19912 2. Perform a select query on comboBox Task Number and comboBox National Site ID 3. If the value in a comboBox Project Number is not 19912 4. Perform a select query on comboBox Task Number and a DLookup () on comboBox National Site ID I tried the code below but it's not working. Any ideas on how to do this? If Me.cmbProjectNumber.Value="19912" then cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM [InScope Table] WHERE [Proje...

Tender Type scan code????
Hi, Does anyone knows how to use the scan codes on the tender type? I have a programable keyboard and want to program in one button a macro that would open the tender screen and put the cursor in the food stamp box. Any ideas? Sacha. ...

Investment type change
Need to change a mutual fund from stock type to mutual fund type. I'm assuming that you meant this as a question of how to do this. These instructions should get you there: http://www.bollar.org/msmoney/#Q81. "Jim Signom" <signom@tds.net> wrote in message news:OrQlAn5jDHA.2488@TK2MSFTNGP12.phx.gbl... > Need to change a mutual fund from stock type to mutual fund type. > > ...

Locating content types, libaries, and related lists
We're planning to use MOSS 2007 as a repository for our project documents and email. We want to use a single container, and rely on metadata and search instead of a folder structure (and instead of different libraries for each project). At the top level of the site collection, I created a content type with a lookup column to a list of clients/projects. The document library itself is in a subsite. My concern is that once the library fills up there's going to be a lot of data in there, all with critical metadata attached. Maybe there would be less risk if the conte...

Text field updating problem
Group, I am using the following control source for a text box on my form =DLookUp("[LastOfRoomChangeDate]","res_source_step 2","[RID] = [Forms]![res_residents]![RID]") The lookup was working great, not anymore...now it only updates when clicking the textbox...or waiting until it adds updates it almost a 2 minutes later or by making the textbox the first tab stop...... I am using Access 2007....any ideas to what might be causing it to run this way...or preventing my field from being updated One solution would be to Recalc the form, e.g.: Me.Recalc Ano...

FRx Forecaster
When posting Transaction Batches in FRx Forecaster, the field that shows the batch number is not long enough and therefore cuts off most of the batch description. This makes it difficult for users to know which batch that want to post. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the...

Showing argument descriptions as an XLL worksheet function is type
Hi, Apologies if this is wrong newsgroup, I wasn't sure if this or microsoft.public.excel.sdk is better. We're developing a set of XLL worksheet functions that have 5-8 difficult to remember arguments. While the argument descriptions passed to the registration function display properly in the function wizard, they do not appear in the common case of typing the function in the formula bar. In addition, I have these related (but lesser) concerns: - My worksheet function description does not appear when my function name is being typed in the Formula Bar. ...

ActiveX Form Controls preventing Field Updating
Hi folks. Has anyone seen this problem or know a solution? 1. Hit F9. 2. Type the following field code: { TIME \@ "hh:mm:ss" } 3. Save the document (if in Word 2007, save it in the 97-2003 format). 4. Close the document. 5. Re-open the document. 6. Notice the time has correctly updated. 7. Do it again if you want (steps 4 & 5) - the time keeps updating, as it should. So far, so good. 8. Now select View > Toolbars > Control Toolbox (in Word 2003) or Developer tab > Controls group > Legacy Tools button > ActiveX Controls > Check Box (in Word 2007). ...

cutom type charts
mine is excel 2000 two questins 1.in thecutom tpe chart there is only one userdefined chart called default the built in is greyed out. In my partition C i have a file called <GR8galry.gra> is available. i dont know how to open this file (.gra) and how to use it to bring the various customtype charts like floating chart etc. 2. how to draw sin curve as trend line to a chart. thanks and regards GR8galry is the chart type gallery for Microsoft Graph, the little brother of Excel which provides the chart features in PowerPoint and Word. The chart types are not interchangeable wi...