How To Handle Null Fields Requiring Numeric And Text Function Simultaaneously
Jan 8, 2007
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
View Replies
ADVERTISEMENT
May 30, 2005
I've got 2 different lines on a form linked to a table. One of the lines is claim status with either open or closed as the options. The 2nd field is "Date claim closed". I want to require the date to be filled in if "closed" in the status field. Can this be done? If so, what do I need to do?
View 9 Replies
View Related
Aug 16, 2006
How do I handle " in text fields - I have a few customers names like "Zoom" - who insist the name is with " in it.
This buggers up queries and filters etc - becuase of the confusion the " creates.
How do i solve this?
View 3 Replies
View Related
Dec 14, 2006
Hi All,
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.
Thanks any help will be much appreciated :o
View 8 Replies
View Related
Jan 8, 2014
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.
View 11 Replies
View Related
Aug 16, 2014
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.
View 3 Replies
View Related
Aug 6, 2015
I 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] .....
View 14 Replies
View Related
Oct 15, 2013
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"
View 5 Replies
View Related
Sep 12, 2014
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.
View 4 Replies
View Related
Mar 11, 2014
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 ?
View 2 Replies
View Related
Nov 19, 2012
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
View 2 Replies
View Related
Jan 29, 2008
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.
View 1 Replies
View Related
Dec 3, 2014
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
View 9 Replies
View Related
Sep 10, 2007
Hi,
i have created a query from just one table, which is basically a list of Financial transactions.
There is a field called supplier code, in this query.
Most of the transactions have a supplier code, but there are a few that don't.
i've got another table that has the supplier name against the supplier code.
i want to bring this table into the query, so that i can show the supplier names against those records in the query that have a supplier code.
when i add this table and link the 2 tables up and put the supplier name into the query. The query excludes the records with no supplier code.
This is presumably, because they contain null values.
I've been reading about the Nz function and i think this is what i need to use.
i've tried this, but it still only brings through the records with a supplier code and not all of them.
How i've done this is to go into the design mode of the query and in the column that would have null values i have click "build"
in here i have the following formula.
Nz([Supplier Name],"No")
Am i doing anything wrong?
View 2 Replies
View Related
Aug 5, 2013
On a form, I want to disable the save button, 'cmdSave' whilst the form's mandatory fields have been left blank.
I've put in a smart tag, called, 'Req' against each required field and have used the following code on the forms current event.
Code:
Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
[Code] ....
The save button is disabled, but it won't enable again after each field has data entered against it.
I also have this code in the AfterUpdate event in each required field:
Code:
Private Sub cboErrorID_AfterUpdate()
Call Form_Current
End Sub
View 13 Replies
View Related
Nov 16, 2014
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
View 2 Replies
View Related
Jan 29, 2005
I have an input form that has many fields, how do I go about fragmenting the data collection.
I thought I might group related data fileds together ie personal details, contact details and have a separate form for each data grouping or one form and make the fields invisible after they are filled in and then make the new data group fileds visible ie overlaying them so it appears to be a new form. I am not sure and would appreciate guidance.
regards
Peter
View 3 Replies
View Related
Sep 24, 2013
I have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is
Code:
Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value
The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.
The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows
Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])
I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.
Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values
Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])
or in other words
If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....
View 3 Replies
View Related
Jul 14, 2015
I have the below function that works correctly. However, I would like the "Last_Nbr_Assigned" value to start at 0000 and increment by 1. So the very first number assigned would be 0001, then 0002, then 0003, and so on.
This number is automatically assigned so I haven't had luck with the validation properties. And if manually enter 0001 for the "Last_Nbr_Assigned", it will automatically cut off the 3 zeros, leaving me with 2 instead of the desired 0002.
Code:
Function NewRF() As String
Dim startRange_Var As Long, lastNum_Var As Long
On Error GoTo Err_Execute
'Retrieve last number assigned for RF
groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
[Code] ....
View 7 Replies
View Related
Apr 9, 2007
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
View 4 Replies
View Related
Jun 21, 2005
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
View 2 Replies
View Related
Mar 4, 2008
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
View 1 Replies
View Related
Mar 11, 2008
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??
thanks in advance.
View 6 Replies
View Related
Mar 14, 2007
Access 97 SR1
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.
Thanks,
Dave
View 1 Replies
View Related
May 14, 2005
I use Access as my grading program at school. Right now, if I want to excuse a student for an assignment, I delete that record.
What I would like to do is put an "X" or some other string into a field that is numeric and Access would ignore that score when computing the average.
A longshot, but I thought I would ask.
Thanks.
View 3 Replies
View Related
Dec 19, 2005
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?
View 1 Replies
View Related
