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.
Bob
i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.
in my Query1, i have 2 fields that use a question in the field part of the qry like:
Desired FiscalYear: [Enter Fiscal Year] EG 2007
Desired FiscalMonth: [Enter Fiscal Month] EG 2
And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.
I start it out here but it is wrong i know.
Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])
Thanks you.
While using list boxes i noticed that numeric fields were getting left aligned in the columns. Is there any way to get them right aligned ?
View 2 Replies View RelatedI 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.
View 3 Replies View RelatedI have a Query contains field that is :
Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")
but [SerName] is a text type field that is not Primary Key.
I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :
Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")
but this returns all [NAll] records. I use concatenate :
Code:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
[Code] .....
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code:
Option Compare Database
Option Explicit
Private Function IsDuplicateRecord() As Boolean
On Error Resume Next
Dim PreviousRecordID As Long
IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
Have a Make table query that needs to create (add) several new fields where each field must be numeric design.
Have tried:
Score1: Not Null - does not seem to work (results in a Binary field)
Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.
Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.
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.
What is wrong with this query?
=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))
What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.
Hi friends,
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables.
A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it.
I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
Can anybody help me at this?
pc
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.
Thanks in advance
Martin
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".
Does anyone have a solution to this?
Thanks for your help.
Numeric format was set to
$Currency 2 decimals
after getting the input form to work...
the numbers now round to the nearest dollar?!*?
What could I have messed up?
Any help appreciated.
Sink or Swim
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
I have tons of values for example...
043401792930179293000000000000
486934620469284720452458733452
548065110164648465132013541355
I only want to keep the values in red. 2nd, 3rd and forth columns of each value. Is there some sort of Trim I can do?
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
Exit Sub
Command8_ClickError:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub
Hi,
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.
Thanks
dfuas
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.....
Does anyone know the way around this??
thanx!
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?
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?
Thank you everyone
Hello there,
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?
Would that be possible?
Thank you in advance
Cheers
Hi All,
This question has probably been asked before, but couldn't find a related topic. My question:
How can I import long numeric values (integers) greater than 2,147 billion (standard long integer). I tried a lot, but couldn't find the answer.
Thanks.
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
View 8 Replies View Related