Substituting Data From Another Field For Blank Values
Aug 29, 2006
I've got a database with a field PKGNum, that was typically a three digit number, that used to be the primary tracking number for each project. You could have the same package number for multiple customers. About two years ago they began to use what they now call a PMIS number which is a unique number assigned for each project. So I have a seperate field in my database to track that.
I use a query to select information from my database to generate labels for my files. What I want to be able to do is have the system understand that if the field PMIS is blank, for a given record, to give me the PKGNum instead, so that the correct color coded value will print out on the labels. What I've been doing is having folks doing the data entry put the package number into the PMIS field if it's an old file, essentially repeating the value, so that I can pull the report using just the PMIS field for the color coded.
However, I would prefer to not have them have to enter duplicate information. Is there a way to tell the system when it's running the query, if PMIS is blank temporarily fill in the field with the value from PKGNum?
Any and all assistance is greatly appreciated.
View Replies
ADVERTISEMENT
Mar 18, 2013
I am creating a report that contains name andd birthdate on one line. I need to have up to seven lines in the report for some groups, but do not want to leave blank lines where no names exist. I am working in 2010 and have read about canshrink in 2007, is this approach availabel in 2010 and for date as well as text?
View 1 Replies
View Related
Aug 30, 2006
Hi Folks,
I am creating an IF CASE statement in SQL that basically takes the 2 last characters in a field and if they happen to be 'HX' then a special rate is used to calculate the cost whereas if their is no 'HX' as the last 2 characters of the field then a different rate is applied.
(Right([BOARD SPEC DATA],2)) has always worked flawlessly in SQL to render similar solutions. However, unfortunetly the weekly extract that comes into us permits for massive amounts of blank but real spaces after the data in [BOARD SPEC DATA] field so what ends up happening is that (Right([BOARD SPEC DATA],2)) yields a (SPACE SPACE) value even thou many of these values should be 'HX' as they are the last 2 real non-space values at the end of the field.
If there any easy way to fix this keeping in mind that this is how we get the extract everyweek so a search/replace on spaces won't work ... is there any way I can use a Right command but ignore spaces so that it simply renders the 2 real characters at the end of the field as oppossed to blank spaces?
Any help is greatly appreciated.
Thank you very much,
Joe
View 3 Replies
View Related
Jun 19, 2013
I have a form, combo box selection, when i make a selection, all the info is good on all my tabs, which holds the subform.My issue is, on one of the subform, it's a data entry form, where I want it to be on a new record page. instead it list all records. I want my users to see a blank page, but have one field populated. is this possible?
1) blank data entry page, but with a populated field?When a CU Name is selected,I want the (Enter Note) tab to go to a data entry or last page or new page.I have tried putting it to data entry form, but the CU Number will not populate.
View 1 Replies
View Related
Feb 26, 2015
The DolphinBatchNo has number data type but the following sql statement doesn't capture the ight records. it doesn't check id dolphinBatchNo is blank.
Code:
strsql="select * from `MasterTBL` where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " and PolicyStatus='Live' and DolphinBatchNo is null order by PolicyNumber"
View 2 Replies
View Related
May 21, 2014
I think I have just finished designing my database and I tried to test it and I couldn't enter any new records as it says "Can't enter data into blank field on "one" side of outer join" whenever I try and enter info and I don't know much SQL to work out what has happened. It probably causes this too but I also cannot select check boxes.
The form where I try and enter the info is called Crisis_support_workers v3. I have attached my database so you can look at what I have done.
View 1 Replies
View Related
Mar 5, 2008
hello,
I have two columns Column A and Column B. Both column has either a numeric values or blank.
I am trying to sum both columns Column A and Column B, but unable to get values in Sum Column C where either Column A or Column B has blank values.
Can anyone suggest what I should do?
([ColumnA]+[ColumnB])
View 3 Replies
View Related
Oct 30, 2014
I have two issues with the report generation from a query:
(i) Sometimes blank values appear in the report in the serial number column generated dynamically (setting the control source property =1 and running sum = overall), but when clicked inside the particular box, the correct serial number appears
(ii) When grouped on a field, the particular field value appears sometimes more than once in the report inside the same group.
How to get rid of this?
View 1 Replies
View Related
May 3, 2005
Hi,
Each client has either an entry with a correspondence number or a notes number. So, the table would look somewhat like this:
Client: 333, Correspondence: 1, Notes: Blank
Client: 333, Correspondence: 2, Notes: Blank
Client: 333, Correspondence: Blank, Notes: 3
Client: 333, Correspondence: Blank, Notes: 4
How would I make a report that will make it look like this
Client: 333, Correspondence: 1, Notes: 3
Client: 333, Correspondence: 2, Notes: 4
Thanks! Hope that was clear.
G
View 4 Replies
View Related
Mar 21, 2013
I'm trying to execute a query that will delete values in a certain column, but I'm getting no positive result.
Quote:
--- Table1 ---
Original:
col1 col2
-----------
text1 Stext1
[Code]...
View 7 Replies
View Related
Nov 24, 2012
I have one combo box bounded with two fields, Id and Description through a query.
I entered a new id in bonded table but for some reason I don't want to enter description now.
And if I delete the entered id then I will loss that unique id which is using for description, which I don't want.Now the problem is one blank space showing in my combo box.
So what criteria I should use in query criteria to avoid blank spaces?
View 5 Replies
View Related
Feb 28, 2013
After creating several combo boxes in my new forms, I noticed that there were blank values being displayed. Is there anyway of creating of creating a listing of unique values and not displaying any blank values for the user to select from?
View 1 Replies
View Related
Dec 19, 2013
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.
View 14 Replies
View Related
Aug 12, 2015
Created a new Query (called qry_Temp) from and existing Query (qry_Test that has fields Field1 - Field5) using QueryDef , and inserted a new Field (FieldX) into it (see example below) using the .Parameters property, then opened a Recordset based on the new Query in order to attempt to enter data values into the new Field (FieldX) for each record in the query - but cannot assign any data values to this new Field (but can to the existing fields) in the new Query?
Dim DB As Database
Dim rs As DAO.Recordset
Dim qdfNew As QueryDef
Dim strSQL1 As String
Dim strSQL2 As String
Dim Value1 As Integer
[Code] ....
View 6 Replies
View Related
Feb 28, 2014
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
View 4 Replies
View Related
Nov 18, 2013
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
View 2 Replies
View Related
Feb 10, 2014
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;
Why some contacts work and some don't ??
View 7 Replies
View Related
Mar 2, 2015
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click()
Dim strCriteria As String
strCriteria = createCriteria("[Introductions].Town", "List78")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Ownership", "List52")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Company", "List54")
[Code] ....
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
View 5 Replies
View Related
May 22, 2015
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
View 14 Replies
View Related
Apr 14, 2007
Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.
I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:
Do Until blnFound = True Or rs.EOF
'DEBUG: answer = MsgBox("ok", vbYesNo)
'DEBUG: MsgBox rs("Account Name")
If rs("Account Name") = strAName Then
MsgBox "DEBUG: FOUND " & rs("Account Name")
blnFound = True
End If
rs.MoveNext
Loop
The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).
I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).
The SQL statement responsible is as follows:
strASQL(i) = "INSERT INTO " & strTableName & "([CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3])" & _
" SELECT [CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3] " & _
"FROM tblCustomer WHERE [" & strField(i) & "] LIKE '" & strValue(i) & "';"
strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D
Any suggestions?? Cheers.
View 12 Replies
View Related
Mar 20, 2015
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center
Job Type
Hours Paid
Pay Code
Earnings Amount
20530
Security Guard II
7.5
REG
190.37
[Code] .....
View 14 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
Feb 18, 2007
Hi all
I have a numeric field in a form which initially is set to 0. When the user clicks or tabs to this field, it is blanked out in the got focus event by setting the field to "". This works great. Now, if the user exits the field by clicking elsewhere or tabing out of it, without entering a value, I would like the 0 value to be inserted again. I have tried many things to make this work without any success. Is there someone who knows the right way to do this?
Thanks in advance
View 2 Replies
View Related
Jul 6, 2006
I am building a query for a report - I want to concatenate several fields, but if one of them is blank I don't want it added to the concatenation.
Example: LastName= Kamp
FirstName= Jay
SpouseFirstName = ""
It is ok if the SpouseFirstName is not null
Kamp, Jay & Jill
This is what I get when SpouseFirstName="": Kamp, Jay &
What I want to see when SpouseFirstName="": Kamp, Jay
SELECT Inventory.Code, Inventory.MLS, Inventory.Pin, Inventory.LockBox, Inventory.Vacant, concatenate Code: Original - concatenate Code [LastName] & ", " & [FirstName] & " & " & [SpouseFirstName] AS Expr1, [LastName] & ", " & [FirstName] & " & " & [SpouseFirstName] AS Expr1, Inventory.CompanyName, Inventory.Phone, Inventory.Phone2, Inventory.Cell, Inventory.OfficePhone, Inventory.Ext, Inventory.Fax, Inventory.PropertyDesc, Inventory.Address, Inventory.City, Inventory.State, Inventory.Zip, Inventory.email, Inventory.SellerAddress, Inventory.SellerCity, Inventory.SellerState, Inventory.SellerZip, Inventory.TPUser, Inventory.TPPwd, Inventory.Expire, Inventory.ListPrice, Inventory.OriginalListPrice, Inventory.SoldPrice, Inventory.CloseDate, Inventory.Offer, Inventory.LastName
FROM Inventory;
View 2 Replies
View Related
Aug 2, 2007
Hi everyone,
I have a a table called [Email Database] with many fields
I need to create a query that replaces all Null values with blanks or ""
I'm not that familiar with SQL so any help would be appreciated!
View 1 Replies
View Related
