Checking For Strings In A Field

Feb 16, 2008

Hi
I am trying to read a large file and to check if the phone number field is valid.
If there are more than 6 "1"s, "9"s or "0"s in the field, then I want to replace it with a null otherwise, I will treat it as a valid number.
Can you help?
Noel

View Replies


ADVERTISEMENT

Strings Within Strings Using Expression Builder

Jun 6, 2007

Hello

I have two tables with names of people in different forms.

table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname

table2: [surname]

I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]

I've tried using

Like "*"&[surname]&"*"

This returns everything rather than the similar names.

I think this should be simple but can't work it out.

View 1 Replies View Related

Checking A # Field

Sep 4, 2006

Hi guys, a company in my city is giving out prizes, and for each buy you get a sheet with 25 numbers ( kind of bingo thing). Well I have like 300 sheets . So i did an Access where I enter the SheetID and the 25 numbers. Now I have to do another table where I can insert the number that will be draw and whenever one of the sheet is complete, it let me know that I win!!! and the SheetID.
Any ideas!!! please please

View 1 Replies View Related

Set A Value In A Field By Checking A Box

May 13, 2005

Hi,
i want to set values (=numbers) in fields of a table by selecting check-boxes in a form (based on the same table).
as checkbox1 =1
checkbox2= 2
etc
If the box is checked the value should be entered, if unchecked the value should be removed.
Afterwards i want to show the sum of these values in another field in the same form.
I suppose this requires VBA. Could anybody help me plz ?

Thx

View 3 Replies View Related

Checking Field For A Value

Jan 17, 2006

I've had a search in the threaqds for an answer but cannot find anything specific, plus this one has stumped me.

What I need to do is check if a certain field has a value if another field has a specific value ??!!?

Ok so if Combo Box 1 is set to 1, then Combo box 2 must have a value selected.
If ComboBox 1 is set to 2, then Combo Box 3 must have a value.

for any other value for ComboBox 1 box 2 & 3 do not require a value.

If either of these statements are not correct then a message box should come up

Any help would be greatly appreciated

MattP

View 5 Replies View Related

Modules & VBA :: Extract Strings From A Field In Table (Separated By Colon)

Oct 21, 2013

I have a field in a table that I'd like to extra the texts from. The are stored in below format (separated by colons). They don't always the same amount of characters and not all of them have the same amount of texts.

Some may only have Text1:Text2::, while some may have Text1:::Text4.

Text1:Text2:Text3:Text4

View 8 Replies View Related

Checking Empty Field

Jan 22, 2007

Im trying to check if a field is empty (i.e hasnt got anything in it)- but how do i do this. Ive called the field in my sql statement and then Ive put it in a variable- i,
I am then running a if statement

Code:If i = "" theni = "1" ElseEnd If

But this is not working?????

View 4 Replies View Related

Error Checking And Multi Field Searching

Sep 17, 2007

Morning all,

Quick question... I figured out how to make all the searching items work... individually. The problem is is that I want to be able to search with two fields at a time. For example, say i have mo, date, job, pay and i want to seach for the MO and JOB and query the results accordingly. I can't get it to do that. Would I have to make a large If statement? I'm not entirely sure. Also now my problem is that it won't execute my first if statement or I should say it ignores it all togeather. I know it executes the code in the order it is written. If someone could shed some light I would be much apprecative. As always here is the code and attached is the program. What I'm working on is in the query table.

I also would like to be able for it to say if the fields MO and JOB do not comeup witha result for a messege to display there is no result. Or if the field is null not to do anything. I tried doing that in some of my code to no success perhaps I did somthing wrong.

Have a great day!


Dim strSQL As String
Dim sqlStringWHERE As String
Dim strhead As String
Dim strJOIN As String

' strSQL = "SELECT * from Panel WHERE MO like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
strJOIN = " AND "
strhead = "SELECT * from Panel "

If Len(Me.qMO & vbNullString) Then
sqlStringWHERE = "WHERE [MO] like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
Else
sqlStringWHERE = "WHERE [MO] = " & Chr$(39) & Me.qMO & Chr$(39)

sqlStringWHERE = sqlStringWHERE & strJOIN

End If

If Len(Me.qCode & vbNullString) Then
If Len(sqlStringWHERE) = 0 Then
sqlStringWHERE = "WHERE "
End If

sqlStringWHERE = "WHERE [CODE] like " & Chr$(39) & "*" & Me.qCode & "*" & Chr$(39)
Else
sqlStringWHERE = "WHERE [CODE] = " & Chr$(39) & Me.qCode & Chr$(39)

sqlStringWHERE = sqlStringWHERE & strJOIN
End If


strSQL = strhead & sqlStringWHERE

View 7 Replies View Related

Modules & VBA :: Checking Null Values In A Field

Jul 10, 2014

The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.

Code:
rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic
j = rs.fields(0).Value

View 5 Replies View Related

Queries :: Checking Format (Identity Of Field)

May 28, 2013

I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.

View 10 Replies View Related

Modules & VBA :: Checking If Data Entered In Form Field Exist

Jan 17, 2015

I want to check if data entered in a form field is existed

The form is bounded to a table

I used this code

If DLookup("Telegram_Number", "tbl_Violation_Of_Building", "Telegram_Number Like " & Forms!frm_Add_Violation_Building!Telegram_Number) Then
MsgBox ("number existed")
Me.Telegram_Number = ""
Else
End If

everything is ok but if the data is existed the database show the message and clear the field but i'm getting a Run-time error

'-2147352567(80020009)': the macro or function set to the beforeupdate or validationRule property for this field is preventing [ISF] from saving the data in the field

I guess the problem because the form is bounded to a table so he will save automatically

My solution is to unbound the fields and save the data via vba but is there any solution with a bounded form???

View 3 Replies View Related

Modules & VBA :: Checking Checkbox (from Multiple Checkboxes) Based On Field Value

Nov 29, 2013

I have a field 'Payment Types' with values (Cash, Cheque, Debit/Credit Card) and a field 'Payment Received' which is Yes/No.

When putting the order through the user selects the payment type and ticks a box if payment has been received.

On a report for delivery drivers, the owner wants it simple for the driver... he wants all the payment types listed with a checkbox next to each one, then wants the appropriate box ticked if payment has been received.

So I need something on the report (or underlying query) which ticks the appropriate box, i.e.

If payment received = true then payment type checkbox = true.

Or should I put the payments into a separate table with both fields so multiple payment types can be marked as paid?

View 6 Replies View Related

Unit Of Strings

Nov 8, 2005

Hi,

I have 2 strings:

str1 = SELECT Field1, Field2, Field3, Field4 WHERE (Criteria1)
str2 = SELECT Field1, Field2, Field3, Field5 WHERE (Criteria2)

I want to show in listbox only "Field1" from str1 and str2.
How unit 2 strings?

Thank You in advance.

View 4 Replies View Related

How Unit 2 Strings?

Mar 31, 2006

Hi all.

I have 2 strings:

strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx
strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx

I need unit this two strings to third string strSQL3.
I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx

Is it possibly unit strings (not Select Query)?

View 4 Replies View Related

Adding Strings Together

Nov 3, 2007

So I need to know this. How do I:

*Add 2 text fields together with a space between?
*Truncating field 1 to the first character and field 2 to the two first characters?


Sorry for my noob questions :D

View 6 Replies View Related

&gt; Than Comparision In Strings

May 23, 2005

Hi. I am trying to compare 2 string values.

5:00
5:45
9:54
10:15

Lets say those are my values. Well in the formula >"5". 10 Will not show up correctly. Does anybody have a piece of code or something that will correctly display 10 as >"5". Any help greatly appreciated. Thanks

View 3 Replies View Related

Joining Strings

Aug 23, 2005

Seems like a simple question but I am stuck.

I need to join two strings from different records in a table

table eg

ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one


I want to join all the "Desc" strings that have the same ID to get

1 blah blah more blah
2 blahring blah
3 and last one

Can anyone help?? :o

Thanks

Paul

View 1 Replies View Related

Splitting Strings

Dec 15, 2005

I am normalizing data from a spreadsheet of just over 4000 records. The spreadsheet has a "Model" field that contains both the model number and a model description, separated by a space. I would like to split this field into two different fields: "Model_Number" and "Model_Description". The model number is one word of varying lenths.

Any ideas?

View 5 Replies View Related

Null And Zero Strings

Nov 2, 2006

Hello,

I am having trouble with a query. I have a number of blanks for a field and I tried using the expression from the help:

=IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;LS"))

But my result set is still a lot of blanks.

This is the expression I was trying to use:

IIf(IsNull([tblMainpersondata]![MailingAddrLine2]),[tblMainpersondata]![MailingAddrLine1],[tblMainpersondata]![MailingAddrLine2])

On most of these, the result was a blank, instead of MailingAddrLine1. If there was visible data for MailingAddrLine2, it appeared.

Any help or suggestions would be greatly appreciated!

View 2 Replies View Related

Comparing Two Strings

Jan 29, 2007

Hey Guys,

Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?

E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?

Cheers

View 5 Replies View Related

Compare Strings In SQL

Jul 10, 2007

Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!

Regards,
Anyi

View 13 Replies View Related

So Confused With Strings!!! Please Help!!!

Nov 15, 2004

Hi all,

Please can someone help. I am trying to pass a query to the database from vba but can't seem to get the string concatenation of fields correct.

Clinical area and title of audit are database fields. Any ideas please? Thanks!

Here is what I have:

StLc = "[Clinical Area] = """ & Me![Combo309] & """ & """ And """ & [Title of Audit] = """ & Me![Combo309] & """"



View 1 Replies View Related

Counting With Strings

Dec 18, 2004

Hoi,


I have a loop where i use a string (strCount). The first time I run the loop strCount has de value A. The second time strCount must become B, the third time C, etc

I already tried to strCount = strCount + 1 but it did not work
does someone know how to do this?

greets
Koen

sorry formy wad english

View 2 Replies View Related

Help With Normalization (strings, Oh My)

Feb 9, 2005

This is a database that was handed off to me for upgrading.

The person who initially created it made the Name field in the table contain the entire name... So a single entry in the field looks like

CHRIS R LOUNSBURY

The table is in the attached table. This is merely a sampling. The main table has over 3,000 names in it. Is there an easy (or maybe not so easy) way to automatically parse that data and split the names into their own fields.

An example would be the string CHRIS R LOUNSBURY. Run code which takes the string left to right to the first blank space, and splits it off into First Name field. Take the last part of the string (right to left) to the first white space and put it in the Last Name field.

Is this possible? Or do I need to find myself a temp employee to data entry all this over again

View 14 Replies View Related

Declaring Strings

Oct 1, 2007

Hi all

I have a Row source string which I use quite often. Currently, I declare it in each procedure and copy and paste it in. The problem is when it needs changing, I need to change it in a billion places.

can I declare the string as public and set it as public too so that I can just make a reference to it rather than copy and paste entire slabs of code into each event??

perhaps I need to place it into a module?

Suggestions are much appreciated.

View 12 Replies View Related

How To Sort Alphanumeric Strings

Nov 1, 2006

Access does not have an option to sort alphanumeric strings properly with the result that sorting a column with the following (sort of) data is almost impossible.

Flat A1
Flat A10
Room A13
Room A2
Room A21
B5
Flat 5b
10k1
10 k3
10 k12
10 k20
10 k2

etc

I need to be able to get addresses sorted correctly and they always have a mixture of alphabetic and numeric characters.

The steps I have followed to try and achieve this are as follows:

Create a matchfield containing the data for sorting (typically the street number followed by the flat number/name)

Split the matchfield into separate fields where there are spaces. "Room A13" becomes "Room", "A13"

Then split the fields/columns by separating the alphabetic and numeric portions - i.e. "Room A13" becomes "Room", "A", "13". This I have not been able to achieve successfully.

(Thinking about it the first step of separating by spaces is probably not necessary. All that is needed is to separate the numeric and alphabetic data)).

Once you have separated the data into alaphbetic and numeric content a sort is straightforward.

Why can't Access cope with what I would regard as a fairly basic requirement (i.e. to be able to sort alphanumeric strings correctly).

Does anyone have a suggestion how I can solve this problem until Access is improved?

View 11 Replies View Related







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