I don't know VBA and I am not a programmer.
I have a table with locations identified by latitude and longitude.
I have separate fields for degrees minutes seconds and decimal degrees.
For example, 50 degrees 30 minutes 0 seconds is also stored as
50.5 degrees. I have a radial search query that finds all targets
within a given distance from a location. The three fields input by the
user are latitude, longitude and distance. The problem is the query
only works with decimal degrees. I want the user to be able to
input in a format like "50 30 0". How do I convert a character string
with non-numeric characters to a numeric string?
Hi I wish to check my phone number fields for non-numeric characters. If the field contains 0-9 then I want to keep the field otherwise I want to replace the contents with a null. Can you please help? Noel
Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?
I'm trying to do something like this:
Code: Do Until rst.EOF rst.Edit If Left(rst!FldText,10) contains any numeric characters Then rst!FldType = "Mixed" ElseIF Left(rst!FldText,10) contains "PO" Then rst!FldType = "PO" Else rst!FldType = "Std" End If rst.Update
I have been downloading .csv files from a construction website that we use to following projects as they develop. I import the file into Excel and then want to bring it into my database. The issue is with two fields I have that are numeric, however, when downloaded into Excel, the information in the two fields now have an (')added to the beginning and end of the string, i.e. and the second field has an (') and (-) to the data.
'201400409710' '201300697683'
The second field is a zipcode field that comes in like this:
'14063-1127' '14222-1004'
I would like to design a query to strip the ' from each field as well as the (-) in the zipcode field.If I have to write 2 separate queries that is fine.
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 have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.
I have a DB containing over 4 million rows of data, each with a SALES_PERIOD based on YYMM. Our Developers set it up as Text, and index this item accordingly, I would like to format as numeric with the lead zero to allow quicker indexing, but when I format it it returns to text this is the formula I use?
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 ?
Hi, Complete newbie question, I have various yes/no boxes that i want to give a numeric value. The i want to add up these numbers and report it back to a text box.
Any help you can offer would be greatly appreciated.
We still use Visual dBase 5.6 for most all of our database operations. We were considering the change to Access 2007. I was wondering about numeric fields. There are like four different options of numeric fields. Which would I choose for just a plain number field? Also how do I set a fixed field width?
Thanks for any help. I hope this is in the correct spot.
I am using a linked Excel sheet that has formulas linked to fields in Access. Some of my cells (formulas) don't have any information that is being auto populated, therefore empty; however, Access is reading the cells and giving an error message "Numeric Overflow".
Trying to do the following: Having built a table of customers, I need to have an automatically generated ID number for each that IS IN ORDER - which rules out Autonumber and it's gapping tendencies. So, I've developed a new CustID field and set it to "Number", and now just need to figure out how to get it to pull the previous entry and add "1" to it. So it would look like this:
CUSTID NAME 1 Bob 2 Fran 3 Phil 4 Tom 5 (ready for next entry)
Any way to use a function I don't know about, or program some type of solution? Thank you
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
Strange but true. I am trying to numeric data types on my access 2000 where I could input values in table like 0.3567 or 2.3454 Anyway any values of this kind with 4 decimal places. however somehow the values always get rounded to number + 4 zeros as of example: if I put 0.756769 I want it to be as 0.7568 but I get 1.0000 I don't understand this. Can anyone help please? I have not used access for ages and I seemed to be forgeting all this stuff.
Guys, When I generate an excel sheet through vb.net code, I get a Numeric Field OVerflow when the values generated contain a date!
my database is ms access...
The problem is that each excel sheet generated has different columns, depending on what columns the user selects to view. for example: if the user chooses name, age and date, the date would appear on the 3rd column, if the user chooses name, nationality, age, family name, date, then the date would appear on the 5th column, etc.....
Hello, I have a little problem. I am using th sendObject command where 'str_Body' is the concatenated body of the message. It contains some fields from an open form. When opening the email, the value of the numeric fields do not appear exactly in the same format as they look on the form (how they should be). For instance in the form I have number "$293.80" and when opening the email this value becomes only "293.8". Is there anyway that I can fix this up?
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?
I have linked One of My access Table with Excel. I am running the upend Access query to insert record from this table(Table that is linked with Excel table) into another access table. Whenever I run this query I get error "Numeric Value Overflow" nsg box. I checked the data types of all fields in excel and Access table and they are compitable. I am not getting any problem in Linked table. Problem occurs only when I run Append query. Plz help me on this. I wasted my 1 whole day on this problem.:o
