I want numeric only input for a certain textbox. I can use a ISNUMERIC function to test this, but prefer to use the Keypress event. So far got the following:
Code:
Private Sub txt_Position_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 8 ' backspace
Case Asc("1") To Asc("9")
Case Asc("-")
If InStr(1, Me.txt_Position.Text, "-") > 0 Or Me.txt_Position.SelStart > 0 Then
[code]...
It is working alright, except I want to avoid input that has multiple leading zeros, such as 00000 or -00000 (i.e. minus sign with mulitple leading zeros).
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response Set dbs = CurrentDb Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I've created a macro to use an unbound textbox to filter a form. In a query, I combined 3 fields to enable an easy search over. Sadly, only text characters work for the search, so whenever I search for numeric values, it returns null. This doesn't happen if I utilise the filter over just a numeric field - only when the search field combined multiple fields.
Is there a simple solution? My marco filter is
Code: [Forms]![STAFF SEARCH FORM]![SEARCHDATA] Like "*" & "[SEARCHFIELD]" & "*"
I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.
How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.
I'm trying to create a validation rule that requires an input of 5 numeric characters OR if the entry begins with a letter it can be any length. For example if the record begins with a number the length must be 5. If it starts with a letter any length is acceptable.
I have an input box and wanted user just to input 7 digital data. However, I am afraid user may enter string data. Is there any way to check and ensure user can only input 7 digital data to that input box?
I'm trying to apply a custom input mask to a text box control. The text box must contain a 43 character string, broken into sections as follows :
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
(The format is quite strict and always comprises alphanumeric characters , hence I figured an input mask would, if nothing else, support the user to key the string accurately from its constituent parts...)
However, when I try to apply the input mask to the text box, I seem to run out of characters - I get as far as the below but the input mask wizard won't let me extend the mask to the final 8 characters
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
Are input masks restricted to 35 characters in length? And if so, how else could I make this, admittedly complex, text input more manageable for end users?
I want to be able to have two text boxes on my form that firstly concatenate the values inputted and then saves this string to a field.
It's a "new record" form that generates a new record in table "tblUsers".
txtB1 & txtB2 and the text boxes would represent First Name and Last Name.
But it can't be allowed to be saved as the acctual names. - Insead I'm looking for a way to take the first two or three letters of the name and concatenate these into one string of text and save that string to the field tblUsers.ConcatenatedName.
I.E.txtB1:"Carl" txtB2:"Chapelle"
would generate "CarCha" and save that from the form to the field ConcatenatedName in tblUsers.
Btw, the values form txtB1 and txtB2 (Carl & Chapelle) cant be saved anywhere either!
Found the answer myself;
create a txtB3
And set that source to Left([txtB1];3) & " " & Left([txtB2];3)
Is there a way to setup the format on fields to only allow text inputs, no numbers wanted. I know I can do this at the table level but is some cases numbers are ok. So I want to code it so I can control when numbers are ok and when there not.
I have a report that selects and shows records where a specified date field is within the range of 2 dates that the user enters.I created 2 unbound textboxes on the report with a Shortdate format and InputMask 0000-00-00;0;_.When user enter correct dates, then everything works fine: selection is properly done, the right records show up.But I have 2 problems:
1-the input mask is not working: the user can input anything! 2-the 2 unbound textboxes do not show the dates entered by the user.
It seems the value entered bu user does not go straight into the unbound textbox. How do I either intercept the value entered by the user directly into the unbound textbox or via a variable?
I just started my project database on my subject ITM4. My Database is entitled PCExpress Inventory System. I would like to know how to input (Text) in a Field where the Data Type is (Number) just like the North Wind.
Can you update a text box on a form based on input of a date control? Here is what I have. I have several date controls on my form, and I want to update a status textbox based on which of these date boxes is filled out, but I need the status to be filled into the table as well. Do I update the field on the table, and then requery the text box? Is there some other method for accomplishing this?
I have a form with a list box on it that has 4 teams (Team1-4)..Below this list box I have a text box:"People on shift tonight"..I want to be able to select a team from the listbox and this prepopulate the people on shift box with the names of the colleagues from the team specified, is there a way to do this?
I have created a member data base form in Access2010.
That has been populated with 366 member records
I have two text box fields that are currently free type fields, Member Type & Member Status.
What is the best way I can change these to show only the relevant options:
i.e. Member Type ( Full, Family, Pensioner, Life. ) Member status ( Current, Lapsed, Deceased)
I have tried to right click the field but the "change to" option is greyed out & not available.
I have also tried to insert a combo box or list box to show these options. The "use control wizard" option has been highlighted but when I drag either a combo box or list box into the form. the wizard does not activate.
I have made my Search Form that will search through my tables, some how I have a syntax error for the following: year, Section and ID, these fields are not text I use numeric values so I think the search is not working for that reason, I am not sure how can I fix it though do I have do add val() or sth.
Private Sub Command8_Click()
On Error GoTo Command8_ClickError
Dim db As Database Dim rs As Recordset Dim rs2 As Recordset Dim strWhereCondition As String Dim strSql As String
strWhereCondition = "" strSql = "Select distinct Id From AcademicVideo Where True "
'================================'
If Not IsNull(ID) And Trim(ID) <> "" Then strSql = strSql & " And [Id] = " & [ID] End If
'================================'
If Not IsNull(Course) And Trim(Course) <> "" Then If InStr(Course, "*") = 0 Then strSql = strSql & " And [Course] = '" & [Course] & "'" Else strSql = strSql & " And [Course] like '" & [Course] & "'" End If End If
'================================'
If Not IsNull([Format]) And Trim([Format]) <> "" Then If InStr([Format], "*") = 0 Then strSql = strSql & " And [Format] = '" & [Format] & "'" Else strSql = strSql & " And [Format] like '" & [Format] & "'" End If End If
'================================'
If Not IsNull([Title]) And Trim([Title]) <> "" Then If InStr([Title], "*") = 0 Then strSql = strSql & " And [Title] = '" & [Title] & "'" Else strSql = strSql & " And [Title] like '" & [Title] & "'" End If End If
'================================'
If Not IsNull([Lecturer]) And Trim([Lecturer]) <> "" Then If InStr([Lecturer], "*") = 0 Then strSql = strSql & " And [Lecturer] = '" & [Lecturer] & "'" Else strSql = strSql & " And [Lecturer] like '" & [Lecturer] & "'" End If End If
'================================'
' If Not IsNull([Section]) And Trim([Section]) <> "" Then ' If InStr([Section], "*") = 0 Then ' strSql = strSql & " And [Section] = " & [Section] & "'" ' Else ' strSql = strSql & " And [Section] like " & [Section] & "'" ' End If ' End If
'================================'
If Not IsNull([Semester]) And Trim([Semester]) <> "" Then If InStr([Semester], "*") = 0 Then strSql = strSql & " And [Semester] = '" & [Semester] & "'" Else strSql = strSql & " And [Semester] like '" & [Semester] & "'" End If End If
'================================'
If Not IsNull([Year]) And Trim([Year]) <> "" Then If InStr([Year], "*") = 0 Then strSql = strSql & " And [Year] = " & [Year] & "'" Else strSql = strSql & " And [Year] like " & [Year] & "'" End If End If
'================================'
If Not IsNull([Description]) And Trim([Description]) <> "" Then If InStr([Description], "*") = 0 Then strSql = strSql & " And [Description] = '" & [Description] & "'" Else strSql = strSql & " And [Description] like '" & [Description] & "'" End If End If
'================================'
Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot) ' If (rs2.RecordCount = Null) Then
' MsgBox "Enter A valid Record"
If (rs.RecordCount = 0) Then MsgBox "Could Not found "
Else strWhereCondition = "[Id] In (" & rs!ID Do While Not rs.EOF strWhereCondition = strWhereCondition & ", " & rs!ID rs.MoveNext Loop strWhereCondition = strWhereCondition & ")" End If rs.Close
If strWhereCondition <> "" Then DoCmd.OpenForm "ACVideo", acNormal, , strWhereCondition DoCmd.Close acForm, "Search AcVideo" End If
In excel spreadsheet, when I eg type in a numeric column 10 1500 so on, I would column11 appear text such as one thounsand and five hundred. How can I convert this?
Im trying to construct a query which returns a field that has 21 different types of values.
Is there a way to assign a text value to each numeric value when the results are displayed?
Eg: 21 = "Local Sales", 22 = "National Sales", 23 = "International Sales".. and so on.
Ive looked at the Choose and IIF functions but I cant seem to work it out
..............
Anouther little problem, which should be basic. I want to exclude certain records with values such as 31 and 41 and 42.
When i type <> 31 or <> 41 <> 42 in the criteria they are all still displayed. Although when I type one of those criteria by themselves they ARE EXCLUDED. Is there something dumb I am missing here??
I am importing a file that has two numeric fields in it. I was asked to import as fixed length text fields with leading zeroes and no decimal point.
I am supposed to export the data in Csv format.
Source field from IBM iSeries DB: quantity is 15,3. Quantity source value is 9, requested text format of five characters 00009, the Csv result is 9. The field in Access is defined as Text 5 characters.
Source field from IBM iSeries DB: price is 17,4. Price source value is 1020.0000, requested text format of nine characters 000001020, the Csv result is 1200.00. The field in Access is defined as Text 9 characters.
I have a number of categories on which I assign a relative risk - at the moment H, M and L. Previously I had these set as a value selection combo and they were as 'text'.
More recently I have changed this now to a lookup and put the risk rating into a separate table. As a consequence the field is now 'numeric' but displays text from my combo.
When running a linked query I now get an error message instead of the required response.
My query should report back a 'Yes' if the queried cell contains an 'H' or 'M'.
Audiometry: IIf([tblRiskProfile]![Noise]="H" Or [tblRiskProfile]![Noise]="M","Yes","No")
However, this obviosuly doesn't work now because of the cell properties being numeric. How do I ammend the string above to run the same query?
I have a query that calculates the number of games won and lost over the course of a season. I would like to export the query results as a report in Excel format.
What I need to do is take the won/loss field in the query WonLost:([GamesWon]&"-"&[GamesLost]) and export the result as text.
When I do it as above, the results in Excel are numeric.
Example: Games Won = 12 Games Lost = 3 it exports it as 9 (12 subtract 3).
I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.
I have a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.
What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.
Can you please anyone suggest a way of doing this.
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"
I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".
