Blank Field
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 Replies
ADVERTISEMENT
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
Sep 15, 2005
What I need to do sounds simple but I am losing my what hair I have left to do it. I have a single table where staff rotate a responsibility every 3 months or so. What I have in one field is the surname of the person currently performing the duty and another with the surname of the person who is next to take on the duty. What I need to query is those posts where a replacement has not been identified for the next say 12 weeks.
e.g. I need to have a query that finds extracts all posts where the field (surname) of the person taking on the role is blank between (date) and (date).
Thanks for any help in advance!
View 1 Replies
View Related
Jan 31, 2006
I can alway make a query like the following one to table and return the records:
SELECT * FROM people WHERE PhoneNo<>"";
However, it returns nothing while it should return a lot of records if I make a query like this:
SELECT * FROM people WHERE PhoneNo="";
The PhoneNo file is of text type. As I know, this field is added after the MS database has been put into use for some time.
Can anyone please explain this to me?
Thanks,
View 1 Replies
View Related
Nov 8, 2006
I have the following calculated field in a query:
LoanNo:IIf([Stats].[LoanCnt]>1,[Stats].[LoanCnt] & " Loans",[Loan].[LnNo])
Within its query, it correctly displays the loan number associated with a particular loan.
However, when I run a second query
SELECT LoanNo
FROM qryLoanInfo
it comes up blank for every record (other fields are OK)
Does anyone have any idea what might be happening?
View 2 Replies
View Related
Oct 24, 2005
Greetings,
I am running some code to work on a table and I want to blank a date field. It is a stock control system where when a vessel is emptied I would like the updated field to become blank as it is empty and therefore has no history (this is stored in another table.)
The code I have tried to us is; (where updated is a date/time field)
Else
rst![Updated] = " "
rst![Volume] = "0"
End If
rst.Update
rst.Close
The message I am getting is 'Data type conversion error'.
How do I get this field to be blank by code?
Thanks in advance
rbinder
View 2 Replies
View Related
Sep 11, 2006
I know this is a simple question. I searched but could not find what I was looking for. Or it is handled in differently than what I am thinking.
I have a field where I enter a dollar amount. But if I do not enter a dollar amount the field displays $0.00. How can I make this field blank if no dollar amount is entered.
Thanks
For any help I can get
View 2 Replies
View Related
May 11, 2014
I would Like to save the data to our Table
[InvoiceTotal] - [InvoicePersonal] "if Invoice Personal contains a value" = [InvoiceShareLoan]
If "InvoicePersonal" does not contain a value, no calculation will be made
View 2 Replies
View Related
Mar 11, 2013
I have a form in access 2010 that prints a document rougly 500 pages in length. I want to know if there is a way to tell the form not to print a particular page if one of the fields on the page returns the word "suspended" and or the field is blank. The page has other data on it, as do they all, but if this particular field comes back null or "suspended" I would rather it just did not print that particular page in its entirety.
View 5 Replies
View Related
Mar 19, 2015
I have a very unusual situation where I need the name of a field in an Access table to end with a blank (i.e. space). I tried doing it trivially while in Design View but no dice. I tried it through some SQL along the lines of the following pseudo code
Select [old table].[Address] as [Address2 ], [old table].*
Into [new table]
From [old table]
but the new field's name "Address2 " lost the final space.A third try was through some syntax for an Alter Table statement to add or modify a field. It didn't pan out.
View 2 Replies
View Related
Dec 8, 2011
I have some code that is below!
Code:
Public Function DegreeChecker(AGShrs As Integer, ASBAhrs As Integer, ASCJhrs As Integer, AAhrs As Integer) As String
Dim Degree As String
If ((AGShrs = 0) And (ASBAhrs = 0)) Then
Degree = "ASBA"
ElseIf ((AGShrs = 0) And (ASCJhrs = 0)) Then
Degree = "ASCJ"
[code]...
The problem is, the information that is populating the query which is running this code may not be there. As in some of the fields, AA, AGS, ASBA, and ASCJ might be left blank. Unless these four fields are populated by something, it returns #Error instead of the specified number.Is there a way to say, If variable is blank, ignore and continue on to check if the next thing is 0, or if none are 0, state Potential.The information in this query is to be exported out to excel to be used as a Mail Merge file.
View 8 Replies
View Related
Sep 30, 2015
I have a main form which has a button to View one of the Reports related to my primary table.The View Report works fine.On close of the Report the program returns to the Main form, and it returns to the record of the Report that was viewed with all of the data for that record showing in all of the Fields, except the Combo Box that I use to look up a record, it is blank.Other than that, the Look Up Combo Box works fine.How do I get the Look Up Combo Box to show the information for that look up field.In the On Close item for the Report I have the following code to get back to the record I want to return to.
Private Sub Report_Close()
DoCmd.OpenForm "ENLARGED PROP INFO", , , "[Name]='" & Me![Name] & "'"
End Sub
View 6 Replies
View Related
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 1 Replies
View Related
Dec 28, 2005
I want to update an MS Access table date field with a blank date. How do I go about it? Right now, if the "dateAskFor_Funds" text field is blank, and I try to update the "AskFor_Funds_Date" field in the database, I get an error. That is why I don't do anything.
What expression should I use in the place of
intJunk = 1
in the code caption below.
Private Sub cmdUpdateApplication_Click()
Dim todaysDate
Dim intJunk As Integer
Dim rst As ADODB.Recordset
todaysDate = Date ' MyDate contains the current system date.
Set rst = New ADODB.Recordset
rst.Open "EA_Apps_List", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
…
…
…
If IsNull(Me!dateAskFor_Funds) Or Me!dateAskFor_Funds = "" Then
intJunk = 1
Else
rst!AskFor_Funds_Date = Format(Me!dateAskFor_Funds, "Short Date")
End If
…
…
…
Set rst = Nothing
End Sub
View 1 Replies
View Related
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
May 7, 2015
I have run into some problems though. I have one main table where all the details of users are imported, I have created several queries for different types of courses users sign up for. I would like that when I select a query it only shows the users who signed up for certain course.So basically what I would like to do is if a field is empty or null to hide the whole row.
View 4 Replies
View Related
Aug 28, 2013
I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.
To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process.
Also, The other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records. Anything else I could put into the criteria to show records that may have a blank field?
View 9 Replies
View Related
Apr 25, 2014
Normally I don't have any troubles sending an e-mail; however, this time I want to include a text field (cc copy) that is sometimes blank. The e-mail will not send if the field is blank. So, I tried using an If Then statement but that doesn't work within the SendObject command.
Here's the code I tried ...
DoCmd.SendObject acSendNoObject, , acFormatRTF, Me.POC1EMail, If Not IsNull([Me.POC2EMail]) Then Me.POC2EMail, , "FOUO: Assistance Request", "Text here", True
View 1 Replies
View Related
Feb 11, 2014
I'm working with a report that totals the number of times a topic is returned from a query. If a topic is not returned at all, i don't want it to show at all. Currently it is showing a blank field for that topic name and blanks in the count as well. Here's the filter i've put in to pull the right data out of my query: =Sum(IIf([Caller Used Resources]="No",1,0))
View 2 Replies
View Related
Feb 21, 2014
I have a form with a subform. The subform is used to report a sales total, for today and for the month. When there are no sales today (a null result in one of the fields) the entire subform goes blank.
How can I make it report a zero in the field instead of blanking the entire subform?
View 1 Replies
View Related
Apr 10, 2014
I have a questions database. When user is filling out the form, the following fields are required: Questions, Author, Type of Question and Answer selected. Answer is selected by clicking on the button next to the Answers. If these fields are not filled out, a user gets a prompt saying that so and so field is blank. If have a problem, it works for all required fields except for Answer. Below is my code. I have attached a screenshot with Author and Answers blank. I only get a warning about the Author and not the Answers.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
nullerr = 0
strnull = ""
If IsNull(Me.txtQuestion) Then
nullerr = 1
[code]..
View 3 Replies
View Related
Jul 18, 2013
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
View 3 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
Oct 8, 2007
Hello.
I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])
It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.
This is probably a totally incorrect way to get this done, but I don't know how else to try it.
Thanks!
Stacey
View 3 Replies
View Related
Dec 27, 2007
Hi all,
I am having a problem getting my query to work properly. I have read through this query section but it just got me more confused. I know some have used IIF function but it didn't work for me.
here's my code:
SELECT tblEmployee.UserName, tblODF.ODFNumber, tblQueue.Queue, tblStatus.Status, tblODF.ODFScanDate
FROM tblStatus INNER JOIN (tblQueue INNER JOIN (tblEmployee INNER JOIN tblODF ON tblEmployee.EmployeeID = tblODF.EmployeeID) ON tblQueue.QueueID = tblODF.QueueID) ON tblStatus.StatusID = tblODF.StatusID
ORDER BY tblEmployee.UserName, tblStatus.Status, tblODF.ODFScanDate;
I want everything to show even if one of the fields is blank.
Thank you
View 14 Replies
View Related
Feb 10, 2014
I have code that I want to cycle through several fields and check instead of coding for each individual field. I thought this might worked on an earlier project but Im lost. When I try to add my code in the "If MyControls Then", I want it to hide the field on a report if it is blank. It doesnt allow me to do MyControls.Visible.
Code:
If Len(Reports!RecallReport!QAW1 & vbnullsting) = 0 Then
Reports!RecallReport!QAW1.Visible = False
End If
but I want the below code to go through each field With QAW in it and hide it if it is blank.Or having it go through each field individual. There are 10 Fields With QAW THen there are another 10 fiels with ShipDist. I would prefer a simple solution to check all at once.
Code:
Dim MyControls As Control
For Each MyControls In Me.Controls ' Iterate through each element.
If InStr(1, MyControls.Name, "QAW") Then
If MyControls Then
End If
End If
Next
I don't even know if I am on the right track or what.
View 2 Replies
View Related
