Wild Cards For Both Text And Numbers....

Dec 14, 2005

Hi,

I'm trying to search a field which has both names and numbers in it.

For purely text entries the wildcard search works just fine.... like this:

Like "*" & [What is Last Name?] & "*"

I can type in "Smi" and get all of the Smith, Smiths, etc.



However, when I try to use this for entries which include numbers (123), which should return "Smi123" I don't get any results at all.

Any suggestions? I can put in the SMI123 as straight text in the criteria box, and will get the appropriate record.... so I know it is there!

Thanks .....

Gordon

View Replies


ADVERTISEMENT

Id Cards

May 24, 2006

Hello. Is it possible to create a database that will print id cards?

I currently use an "off the shelf" piece of software to track and issue plastic id cards. The software was quite expensive initially and over the years, as my pc/os changed it wouldn't work under w2000 so I am using it on a laptop running w98.

If I want reports printed I have to change cables etc (no money for an upgrade apparently!) so I thought maybe I could import the existing data into an access database, it's simply name/job title/dept etc. but I'm not sure if I could print cards from it. The existing software has a card editor where I drop desired fields on to it and use that layout for issuing the card.

Is there a way to do this?

Thanks.

View 1 Replies View Related

Numbers Stored As Text Convert To Numbers?

Jan 10, 2007

Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?

View 1 Replies View Related

Sorting Text Field Containing Both Text And Numbers Sequentially?

Mar 26, 2014

We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.

We label them with a hostname depending on their branding. Example below.:

APPLE01
APPLE02
APPLE03

ANDROID01
ANDROID02
ANDROID03

WINDOWS01
WINDOWS02

In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of

APPLE01
APPLE02
APPLE10
APPLE100
APPLE101
APPLE11
APPLE110

ANDROID01
ANDROID02
ANDROID10
ANDROID100
ANDROID101
ANDROID11
ANDROID110

We would like to have them show up as

APPLE01
APPLE02
APPLE10
APPLE11
APPLE100
APPLE101
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID11
ANDROID100
ANDROID101
ANDROID110

What is the best way to tackle this?

View 1 Replies View Related

How To Have Text And Not Numbers In Text Data Type

Jan 13, 2006

I was wondering if there is a way of only allowing text in a text field - we need to do this for validation purposes when inporting a text file into a database. I have tried input mask but this only checks when entering new data - I have tried to use validation rule like"???????" however this means that there has to be 7 letters in each field and this is not the case. - the field is for town and a couple of the records in this field have numbers at the end which are incorrect that we want the database to reject :eek:

View 1 Replies View Related

Using A * (Wild Card) In Pop Up Box?

Aug 3, 2004


Is it possible to use a * (Wild Card) in a pop up box for a query?

I read the previous post about pop up boxes for quires and it worked worked great! I created one for my month field

My criteria in my search fieled is a follows:
05-May
06-June
07-July
08-August
etc...

As you can see someone would have to type a 08-August to get the results for August, can a wild card be used? Example 08* would return all of 08-August data. I tried it but it wouldn't return any data.

Any help would be appreciated.

Thanks, Scott

View 6 Replies View Related

Wild Card In IIF Statement

Jan 28, 2008

I am using a criteria entry form as the source of parameters for a select query, and I am using IIF statements in the query so that if the user does not want to specify for that particular field, the search still works based on the other parameters specified. Therefore the statement I am using is;

IIf([Forms]![Criteria Form]![Customer Account] Is Not Null,[Forms]![Criteria Form]![Customer Account],[Data]![Customer Account]).

This works.

However for some of the criteria I want to also use wild cards, so that the user can enter just part of the data, and the relevant records will be returned. I understand that wild cards cannot be embedded into the IIF statement above.

I have tried many methods, and trawled the web for ideas, but I cannot get this to work. I either want the user to enter the wild card themselves ie "100*" returning records starting with "100", or for the query to append the wild card itself.

Can someone help please?

View 7 Replies View Related

Wild Card Name Searching Help

Feb 27, 2008

How would I make a query find a person like this:

Inputted Data: Smit
Outputted Data: Smith, Smithy etc..

The formula I have entered in the criteria of my query is:
Like [Enter the person's last name]

With this formula, if I type Smith, then the data that comes out is Smith. But if I type in smit or smi, Smith doesnt come out.

Thanks.

View 4 Replies View Related

Wild Card Searches

Jun 22, 2005

Ive read loads of posts about Wildcard searches but am unable to find the answer to my problem. In one of my boxes on my search form the user places in the search criteria value, this can be from A1 - A16 all the way to G16. At the moment i'm using this as my seasrch query:

[Forms]![Search]![BoxNo]

So if I put in C1 then I get all the C1's, great. If I want to wildcard then I put in Like "*" & [Forms]![Search]![BoxNo] &"*", and again it works, this time it call enything with C1 in it i.e C13.

How can I adjust my query to do both, sometimes I only want the exact match othertimes I want to wildcard :confused:

View 4 Replies View Related

Converting Text To Numbers

Jun 14, 2006

I wondered if someone could help.

I am using the Val () Function to convert a text field to number within a query which still gives me a text output.

P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.

View 1 Replies View Related

How To Convert Text To Numbers?

Mar 24, 2007

Hello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!

View 11 Replies View Related

Treating Text Like Numbers

Oct 12, 2006

HI,
I have a field that contains mainly numbers, but the data type is set to Text because there are a few records where the numbers have a letter suffix.

Eg. Invoice numbers 100, 101, 102, 102A, 103 etc.

These suffixes are a result of poor data entry when the data was stored in Excel.

What I was to be able to do is sort this field and treat all entities as numbers. Currently when I sort it, it shows:

1
10
11
2
20
21
3
...etc

Is this possible, even though the data type is Text?

View 2 Replies View Related

Text + Numbers In 1 Field

Feb 17, 2005

Is it possible to have text and numbers in one field as access asks you specify the data type of that field?

View 1 Replies View Related

Wild Card On Search Form Issue

Nov 3, 2007

Hi all,

I need a little help with a wild card issue, Ive looked and looked and cant spot whats wrong.

I have two forms "Requisitioner" & "Engineers", each form has a subform.
These are basic search forms (unbound) that find related records in the subforms. The work is done in the query of the subform and then the form is refreshed. I have used this approach many many times without issue until now.

If the engineers form is opened "G*" is entered into the text box then all the entries beginning with "G" are found in the subform.

If the "*" is then removed from the text box leaving "G" no results are found in the subform, (Working Correctly)

Now if the "*" is added again "G*" then the subform shows all the entries beginning with "G"


Everything is good with the Engineers Form, If the same steps are made on the Requisitioners form after adding the "*" again no records are displayed.

I could change the way this works by doing a recordset find but i really want to know why this is working on one form but not on another.

I would appreciate any help with this.

Garry

View 1 Replies View Related

Queries :: Wild Card Searches With Brackets

Jun 26, 2014

I want to prompt the user to enter a merchant name, but want the results to return close matches.I know how to use the wildcard in the Criteria field of the query, but I want to use brackets.I know that "*Southwest*" Will return Southwest Airlines.So I tried *[Southwest Airlines]* and it treats the criteria like a text string.

View 1 Replies View Related

Forms :: Hyphen Not As Wild Card Character

May 18, 2013

I have a series of dates that I inherited from former DB. Some of the dates are Mon-Fri, Mon-Thu, Sun-Fri, etc. That is, there is a hyphen "-" in the middle of the text. I have an unbound control with an IIF statement using the LIKE condition, I just get an error. I tired the following to no avail. Using Access 2010.

IIF([Day1] Like "*-*", 5, 1)
IIF([Day1] like "-", 5, 1)
IIF ([Day1] like '-', 5, 1)
IIF ([Day1] like [-], 5, 1)

I've looked it up and the hyphen _ is used as a wild card character, but I need to include it just as a dash.

View 3 Replies View Related

Calculate Numbers From A Text Field?

Dec 27, 2006

Ok, this one has me stumped.

I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.

How do I apply the calculations on the field choices if the field properties have to be text instead of number?

Thanks,

bugleboy:confused:

View 10 Replies View Related

Show Text In Drop Down Box Instead Of Numbers

May 31, 2007

Hi *,OK, still new to Access so I apologize in advance if that is a dumb question ;)I have a table "survey" where I wanna store number values (0,25,50,75,100 respectively). The user is supposed to use a form to input the data into the table. For this purpose, he/she is supposed to use a drop down box.So, is it possible that the drop down box does not show the values but instead a verbal description of these values (very important, important, average, etc.)? What I have tried was to create a table with the number values and use this table as data source for the drop down box... it shows the values but for the end user, verbal descriptions would be better...Thanks!Steve

View 2 Replies View Related

Importing Combination Text And Numbers

Oct 13, 2005

I am trying to import a field into a data, which is either a number, a number and text or just text. Within Excel the format has been set to text. The data type for the field in Access has been set to text and yet for those records which are only numbers when imported the record is shown as a number in scientific format. Does anyone have any ideas how I can get the number to be a number in its full format?

View 2 Replies View Related

Inserting Numbers Into Table Instead Of Text

Mar 8, 2006

Hi,

I have a strange problem, I have created this query:

INSERT INTO WEEKONE ( Assigned_To )
SELECT QPTActions.Assigned_To
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');

It works, but it inserts Numbers into the Assigned_To column in the table WEEKONE. When I looked in the QPTActions database the values are text but the type of column was originally set to Number. I changed this to Text but its still inserting Number values into the destination table. How can I fix this?

View 1 Replies View Related

Numbers With In A Disordered Text Field

Aug 3, 2006

--------------------------------------------------------------------------------

Hi,
Could you help me with the following.

Lets say I have table with one of the field header as 'notes'.
The notes is of type 'text'

In the notes column information like the following is typed out

Record 1 - MAX O.D 3.456" Min ID 1.2 and OAL 3.4"

or

Record 2 - MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches

Please note that as shown in the example there is no standard way of entering data. The only order that is followed is that the first numerical value corresponds to OD, 2nd corresponds to ID and 3rd corresponds to overall length.


I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.

So the end result should be 2 more columns with max od as one and min length as the other.
Regards
George

View 1 Replies View Related

Option Stores Numbers, Need Text

May 23, 2006

Hi,

I am using group options and they store a number instead of the vaules you typed in, is there anyway of making a group option that will store values and not numbers?

View 9 Replies View Related

Replacing Numbers With Their Text Meaning

Aug 19, 2004

I created a database that has a field called "Corrective Actions." I want the user to be able to type in a series of numbers, in the following format: 001, 002, 051, 123. Each number has a certain meaning. For example, 001 means "Upgrade to current revision," 002 means "Replace Unit," and so on. Here is what I would like to happen: The user will type in the numbers in the appropriate format. When the user presses the tab key to go to the next field, I want the numbers to be replaced with the actual meaning. My problem is that I can not figure out how to accomplish this. Can anyone help me?

View 5 Replies View Related

Separating Numbers & Text Into 2 Columns

Dec 29, 2005

Hi!!

I have a quick question. I have a field that has information that looks like the below. I need it to be in 2 separate columns, but there is no space between the numbers and text otherwise I would know how to do this in excel. Anyone know how to do this is excel or access?

11000031377A & A AMERICAN DETECTIVE BUREAU


It should be separated as....

11000031377 A & A AMERICAN DETECTIVE BUREAU

Any and all help would be appreciated!!! Thanks!

View 2 Replies View Related

Modules & VBA :: Converting Text To Numbers

Sep 19, 2014

I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?

View 4 Replies View Related

Forms :: Storing Numbers And Not Text

Apr 24, 2014

I have a table with an auto number primary key field, and then several other fields, Address, Unit, Owner. I created a form with a combo box that looks up the Address from another table and populates the three fields when you click a record. I have it set to store the first value, (Address) in the table.

When I open the table, it appears as the primary key number, and not the address. It's very frustrating, as this happens a lot to me. What am I doing wrong? and how can I store the value I want, (Address) and not the primary key number?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved