Limiting A Field To A Particular String

Dec 14, 2005

On My form I would like to have code to limit what can be put into a field e.g

If Me.Myfield is not = to A, B or C
then
message
"You must enter A, B or C in this field"
EndIf

Any help please ?

View Replies


ADVERTISEMENT

Limiting A Value In A Field

Oct 21, 2005

I'm trying to limit the amount a user can enter into a employee discount field in a table. I would like to set up a validation rule in the table to limit the discount to no greater than $0.20 or 20 cents. Anyone have any recomendations? Thanks..

View 1 Replies View Related

I Need Help Limiting A Field

Nov 8, 2005

Im doing a database booking system and i need to limit the amount of people to 160 per night. At the moment i have a query that sums up the bookings per date but i dont know how to then limit this column to 160.

View 2 Replies View Related

Limiting List Box By Another Field

Apr 25, 2012

I'm trying to limit the funders under previous applications to ones with the same name so then when you click on one it loads that other funding application details...

Code:
SELECT [Sheet1].[ID], [Sheet1].[Funder] FROM Sheet1 WHERE [Sheet1.Funder] Like [Sheet1].[Funder] ORDER BY funder;

Code:
[Sheet1.Funder] Like '" & [Sheet1].[Funder] & "*' ORDER BY funder;

[code]...

View 14 Replies View Related

Limiting Size Of A Field

Aug 14, 2013

Im doing a coursework on ms access on School Management System. I have a table 'tblSubject' containing fields: 'Subject ID' and 'Subject Name' . A student has the right only to choose a maximum of 8 subjects, that is , the field 'Subject ID' must have only 8 records/entries. How to do this ???????

View 1 Replies View Related

Limiting Text In A Memo Field

May 25, 2005

Hi,

Probably a really simple question, but is causing me problems! I need to be able to limit the amount of characters that can be entered into a field. For text fields I can just enter the relevant field size, but can not find an equivalent for a memo field. Validation rule warns you that there are to many characters, but doesn't seem to actually limit you.

Any suggestions would be appreciated.

J

View 8 Replies View Related

Limiting And Filling Fields Based On Another Field

May 1, 2014

The database I am building is for Student & Alumni administration at a non-profit culinary school. In the various forms for entering student information and for defining tests and recording test scores, I would like to not have to re-select things like Class Number or Test number.

I was able to use the Test Number field as Link Master, Child in a Test Results sub-form where Test Parameters is the Master form. But I am not able to link with the Class Number, getting the error message: "The setting you entered isn't valid for this property" even though it is one of the Suggested Link Fields. The form record source is a query linking the Class Table, Test Parameters, Test Results and Students.

I noticed that I can eliminate both Class Number and Test Number from the sub-form. In the underlying query, both these fields are updated. However, the underlying Test Results table does have the Test Number but not the Class Number. While there is still a "queryable" link from the student to the Class Number, I would very much want the Class Number to be stored in the Test Results table.

Is there a way to accomplish this through the sub-form Link Master/Child property or any other/better way to inherit both the Class Number and Test Number in each Test Results record entered?

View 4 Replies View Related

Some (expert) Advice Needed Please (linking Field With String In Other Field

Oct 3, 2005

Hi,

I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.

The two tables I would like to link are,
Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records.
Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.

I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).

The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.

I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?

The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.

I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.

Thanks in advance (I hope),

Onur

View 1 Replies View Related

List Table, Limiting Field List..

Oct 14, 2006

Okay,

The subject is probably confusing but I'll try to explain.
Setup:

Table1
FieldID = Number
FieldName = Text

Table2
FieldID = Number
FieldName = Text
T1_ID = Number

Relationship
Table1.FieldID 1-> * Table2.T1_ID (one to many)

Now, I'm trying to create a 3rd table that has field populated by Table1 and a field populated by Table2 but I want to limit the field populated by table2 by what is selected in the field populated by table1.
Table1:

1st
2nd
3rd
4th

Table2:

1st,January
1st,February
1st,March
2nd,April
2nd,May
2nd,June
3rd,July
3rd,August
3rd,September
4th,October
4th,November
4th,December


Table3:
Table1Field = Number, ComboBox-Source: Table1, BoundColumn=1
Table2Field = Number, ComboBox-Source: Table2, BoundColumn=1

If Table1Field = 1st then
Table2Field Will only allow January, February, or March as selections, since ONLY they are a member of the "1sr" group via the table1 list.

Is this possible, or do i have to do some run-time checking or something?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

View 1 Replies View Related

Adding A String To The End Of A Field

Nov 20, 2007

I have a field that contains various text....(It's a note field that allows 86 characters).

I want to go in to the database and add the string "Conversion 2007" to the end of every record. So leave whatever is in there but add in the string. I would like it to add whatever it will fit, in the cases where there is not enough room.

I figured this was somewhat simple, but I haven't found anything yet.
Thanks.

View 4 Replies View Related

Query Field String Too Long

Mar 8, 2006

Let's say I have a query like:

select [x]+[y] as A, [z]*[A] as B from MyTable.

I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:

select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.

However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?

Any ideas appreciated.

Thanks!

PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))

View 1 Replies View Related

Calculate # Of Occurrences Of String In A Field

Jun 25, 2007

Thought I'd be able to find out easily, but.. here I am...

How can I query the number of occurrences of a string in a field, e.g.:

if a name field has the text: Martin Lacoste

how can I get it to tell me there are two "a"s in the field?

I can use InStr to find one, and make a few more queries to find a few subsequent, but the data I need to search could have 30-40 occurrences of the desired text in a field.

Ideas?
Thanks!
Martin Lacoste

View 6 Replies View Related

IIF Statement To Pull String From Memo Field

Sep 12, 2006

I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data.
Here's an example:

4000 C7875
9003267 T7761
90000167/4010/T6895
4010 T7152A
TPCA #1756/2914

The data I'm after is
C7875
T7761
T6895
T7152A
*No data from the 5th row since the data after the "T" isn't numerical

So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.

My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.

My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?

View 5 Replies View Related

AutoNumber Field And SQL String INSERT INTO Statement

Apr 4, 2008

In my statment below the ID is an autonumber. I placed it in the first part ofthe SQL string..but not where I have to variables referenced...

I have to add it to the SQL statement...Its not a varible although I can make it one by getting the largest number nad adding one...
Do I have to make it a non-autonumber and do it like I said above

Or is there another way to treat the autonumber (ID) in the SQL INSERT INTO statement...

THanks




Code:MySQL = "INSERT INTO dbo_data ("MySQL = MySQL & "ID,Name,Owners_Residence,Notice_Expiration,Notes,N otice_Number,Status,Notice_Date,Property_Address,N uisance_Type,Addition_Name,Range,Lot,Block,Zip_Cod e,Display_PID,Sec_tion,Township,Property_City,Prop erty_State,Property_Zip"MySQL = MySQL & ") values ("MySQL = MySQL & "'" & varName & "','" & varOwners_Residence & "','" & varNotice_Expiration & "','" & varNotes & "','" & varNotice_Number & "','" & varStatus & "','" & varNotice_Date & "','" & varProperty_Address & "','" & varNuisance_Type & "','" & varAddition_Name & "','" & varRange & "','" & varLot & "','" & varBlock & "','" & varProperty_Zip & "','" & varDisplay_PID & "','" & varSec_tion & "','" & varTownship & "','" & varProperty_City & "','" & varProperty_State & "','" & varZip_Code & "'"MySQL = MySQL & ");"

View 14 Replies View Related

Forms :: How To Cancat A String With Number Field

Jun 24, 2015

I have a table and i want to have the id of this table as :

ST_1
ST_2
ST_3

I mean ST_automatic number. I want to know how i can create it. Is it possible to set the first value of auto number field in ms access? Also it is ms a ccess web database?

View 3 Replies View Related

Modules & VBA :: How To Find If Field Contains Number In The String

Oct 7, 2014

I am looking to find if a field contains a number and then build a case statement depending on which number is found. The field will contain data just like this:

Quote:

Repaired frequency response and grounding issues. Replaced 2 Hybrids, capacitors, and connectors. Tested MER/BER and operation to specs.

Here is my code that did not work:

Code:
If Me.txt_work_comm1 Like "*Hybrid" Then
'Sets up auto priced based on number of hybrids entered
'1 hybrid
If Me.txt_work_comm1 Like "*1" Then
strCriteria = "repair_item = 'Charter RF Amplifier Repair + 1 Hybrid' AND profile_types = 'Alpha'"

[Code]....

To Summarize:
1. I need to find if the word "Hybrid" or "Hybrids" is in the field
2. Then I need to know how many to determine a price

View 1 Replies View Related

String On Button To Update Field Values

Jul 9, 2013

I get an error for this code: this is the error, i can firgure out how to have the string not be the textbox, but sub in the texbox name.

Code:
myFormstr = Array("F_Value1", "F_Value2", "F_Value3", "F_Value4", "F_Value", "F_Value6", "F_Value7")
For Each mystr In myFormstr
me.cboProduct = Me.mystr
Next mystr

View 10 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Splitting Text Values From A String Into Separate Field

Jan 25, 2005

I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"

I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.

I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.

Can someone show me the syntax for this.

Thanks!

View 1 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Queries :: Count Of Records Equal To String In Different Field

Dec 24, 2013

I am looking to achieve a column within a query which gives me the count of records that are equal to a string in a different field, e.g.

Field1 Count
aaa21 3
aab01 2
aaa21 3
aaa21 3
aab01 2

View 2 Replies View Related

Modules & VBA :: SQL Query Field Based On A String From Input Box

Sep 10, 2013

How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:

Code:

Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql

If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.

View 1 Replies View Related

Queries :: Find All Field Header Names Containing String

Mar 21, 2013

I need a query to find all the field header names that contain string "PL-" and along with records contain a value with the field name containing "PL-" within a Access DB table.

View 3 Replies View Related

Forms :: Changing Format Of Field Based On String Length

Jun 11, 2014

I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.

I have tried it using code:

If Len(Me.FormFieldName)=11 Then
Me.FormFieldName.Format = "@@@-@@@-@@@@-@"
End If

Is it even possible and if so is my syntax correct or am I way off base?

View 8 Replies View Related

Modules & VBA :: Insert CR / LF In Memo Field Based On Search String

Jan 16, 2014

I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:

A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.

I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.

The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:

Code:
Dim db As DAO.Database

Code:
Dim db As DAO.Recordset
Dim srtSQL As String

[code]...

View 14 Replies View Related

Queries :: Use Of String Expression As Field Definition And ARGUMENT In IIF Statement

Apr 25, 2014

I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.

I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.

But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.

The field in the statement [calc number] is a date conversion factor that changes everyday.

I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)

CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0

I do not have greater-than key on this international keyboard !

When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.

I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #

View 7 Replies View Related







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