i am in a query and trying to use criteria box to get rid of any record that has a blank in the ItemDescrption column.
i have tried Not Like " *"
Not Like "*"
Not Like " "
with no success.
Thanks
I'm using Access and VB for the first time, trying to develop an application for hobby use. I built and populated my database, and I'm building my first module. Unfortunately, one of the most simple actions is tripping me up. I'm attempting to perform an action only if a 255-character text field in a file doesn't contain data, but comparing it to " " or "Space$(n)" doesn't yield the actions I'm expecting.
View 2 Replies View RelatedI have a table with three columns
Column A, Column B, Column C
in these 3 columns i have different values and in two fo these columns there are blanks. I want to search for this blanks and fill them in with a static value. But only the two columns.
How can i do this? Update Queries?
Thanks you
New problem each day! Ha. Ok I have a crosstab query with data in monthly columns for various accounts. For some accounts there is data in jan and march but not feb. Is there a way to get these blanks to be 0 without manually adding them? Thank you!!
View 2 Replies View RelatedHi all,
I have been trying to create a database that looks after staff holidays.
Here's how it looks at present
TBL_Holidays:
Staff_Index
Staff_Name (From a lookup)
Start_Date
Number_of_days
TBL_Holidays_Booked:
Staff_Name
Dates_Booked
Once the form (FRM_Holidays) is filled out I had (until I gave up and decided to ask for help - 2 weeks) a button that went off and run a append query.
Here's my problem. Once the button is pressed I need the TBL_Holidays_Booked to fill with the Dates_Booked.
ie.
John Smith wants his holiday to start on the 1st August for 10 days.
The resulting TBL_Holidays_Booked would look like the following:
John Smith 01/08/06
John Smith 02/08/06
John Smith 03/08/06
..
John Smith 10/08/06
Is there a way to do this? or is there a simplier way of doing it?
Regards
Mark
Hello
I have a back-end database which imports a spreadsheet which is automated. Every-time the spreadsheet is imported a large amount of blanks appear above the information i require.
Is there away i can automatically stop this from happening?
HELP?
creating mailing labels starting with an Excel Workbook imported into Access. When I get to the point of previewing the labels, there seem to be maybe 10 blank labels. I don't have a clue how this could happen but I obviously don't want to waste the ten or so labels (out of about 90 total). setting the print area and removing empty Worksheets, but nothing works.
View 4 Replies View RelatedI have co-workers cutting and pasting names into the database name field and some of them are having an empty space in front of the name. Is there a way to get rid of the space or add a valadation for this? What is wrong with these people, this is just laziness at its best!
When a search for names is brought up, they are not shown due to the space so they are not in A - Z order...
In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.
This is the concatenation code I'm using:
Expr1: [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) etc.
However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.
When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.
How can I achieve this within the above code?
Gary
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Code:
Comments: [QAComments1] & IIf(IsNull([QAComments2]),"","; " & [QAComments2])
This is my edited code to add a third field:
Code:
Comments: [QAComments1] & IIf(IsNull([QAComments2]),"","; " & [QAComments2] & IIf(IsNull([QAComments3]),"","; " & [QAComments3]))
What am I missing to add the other fields?
I have five address fields:
add_1
add_2
add_3
city
county
zipcode
What I want to achieve on my (Word) letters, forms, reports etc is a concatenated list that does not have any blanks!!
city, county and zipcode will always be filled however it is possible that add_2 and add_3 could be blank. I hope I am explaining this correctly?
How do I achieve this? I envisage a query which produces just one list that I can just add to my documents but I am struggling with the formula to ignore the blanks.
I have a crosstab table that is displaying in a form within a form because the form has data coming from multiple tables.
Crosstab Query looks like
Name Sum Cust1 Cust2 Cust3.....
Jon 50 250
Bill 100 50 100
Kevin 200 50 100
Ryan 10 500
The issue are the blanks in the rows for each Name. When I bring it up on the form, I want the columns to be the Customers but only customers with amounts. The original form is there to show the name and position and the form within the form should have 1 row for name of the person that is being selected and columns of customers WITH A BALANCE.
Hi all,Desperate for help on this.The query below ran like a charm for this years database which was full of entries, but when I did a quick test run for next year with limited entries the 'Temp' table fields were all blanked. All the individual queries, 1 through 9, work fine on their own and work fine in this linked form when there is enough data in the database to cover all the fields. Apparently if only one query is blank all the fields will be blank.For instance if there are multiple registered households, table ‘1’ would normally show a count of those households, but if none of those households have children, table '2' which counts children will be blank and in turn blank out all the rest of the fields including table ‘1’.I've tried the ‘is null, '=0" and ‘nz’ routines on the Count(Tablename.Fieldname)'s, but can't seem ‘to get them to output a value of 0 in the null fields. I must be doing something wrong. Any and all help would be very much appreciated.SELECT Year(Now()) AS ProjectYear, [1].CountOfClaimID AS Households, [2].CountOfPersonID AS Children, [3].CountOfPersonID AS Participants, [4].CountOfSponsorID AS SponsorsTotal, [5].CountOfHouseholdID AS Sponsored,[6].FamiliesWithChildren, [7].SponsoredFood, [8].SponsoredGifts, [9].SDSD INTO TempFROM (SELECT Count(Household.ClaimID) AS CountOfClaimID FROM Household GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 1, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus, Members.Status HAVING (((Household.RegStatus)=-1) AND ((Members.Status) = "Child" ))) AS 2, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 3, (SELECT Count(Sponsors.SponsorID) AS CountOfSponsorID FROM Sponsors GROUP BY Sponsors.SponsorStatus HAVING (((Sponsors.SponsorStatus)=-1))) AS 4, (SELECT Count(Sponsorship.HouseholdID) AS CountOfHouseholdID FROM Sponsors INNER JOIN Sponsorship ON Sponsors.SponsorID = Sponsorship.SponsorID GROUP BY Sponsors.SponsorStatus, Sponsorship.SponsorshipYear HAVING (((Sponsors.SponsorStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())))) AS 5,(SELECT Count([Table0].HouseholdID) AS FamiliesWithChildrenFROM (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0) AS 6,(SELECT DISTINCT Count(Household.HouseholdID) AS SponsoredFoodFROM Household INNER JOIN Sponsorship ON Household.HouseholdID = Sponsorship.HouseholdIDGROUP BY Household.RegStatus, Sponsorship.SponsorshipYear, Sponsorship.FoodHAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Food)=-1))) AS 7,(SELECT DISTINCT Count([Household].[HouseholdID]) AS SponsoredGiftsFROM (Household INNER JOIN Sponsorship ON [Household].[HouseholdID]=[Sponsorship].[HouseholdID]) INNER JOIN (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0 ON [Household].[HouseholdID]=[Table0].[HouseholdID]GROUP BY [Household].[RegStatus], [Sponsorship].[SponsorshipYear], [Sponsorship].[Gifts]HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Gifts)=-1))) as 8,(SELECT DISTINCT Count(Household.HouseholdID) AS SDSDFROM HouseholdGROUP BY Household.RegStatus, Household.SDSDHAVING (((Household.RegStatus)=-1) AND ((Household.SDSD)=-1))) as 9; Thanks much,aldo
View 2 Replies View RelatedI've created a report which shows zeros in various columns where there aren't any values to report. I think the report would look better and be easier to read without these zeros. I would like to suppress them and display blanks instead. I'm not sure how to do this.
View 1 Replies View RelatedI was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
Hi,
I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme.
I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible.
The task I have is to join 2 databases and produce a table from which I can run a mailmerge.
I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect)
I now have all the data I require in one table.
THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
In anticipation I thank you very much.
:-)
Hi there,
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Thanks in advance
Col
EXAMPLE
PLACE NAME POINTS
1 John Smith 199
2 Ian Jones 90
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases.
The number is a sequential number i have created and the primary key.
It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables.
However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000.
When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing.
Any ideas.
Thank you in advance
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table?
When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
Thanks guys.
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT...
The field
TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working?
Can I use a simple VBA statement to sum these fields?
:confused:
hi all, this is my problem:
i got a table like this
ID time in time out working hours
1
1
2
2
the working hours is [timeput]-[timein]
how to i create a query so that all the ID 1' s working hours will be summed up ?
:confused: simple query
Critera : <Date()-30 should show records from the last 30 days ???
and : "status"="active" should show records from the last 30 days that are active ????
or am i totally off the mark here ?
vey new to this be gentle :eek:
cheers
I am trying to create an IN query (ACCESS) which will firstly show the customers table (specific details) then the orders details should be in the IN query, I formulated this but it won't work:
SELECT s.CustomerID, Address, City, PostalCode
FROM Customers s
WHERE
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1996')
AND
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1997');
Here is the one I did to show the customers and orders who made orders in 1996 and 97:
SELECT Customers.CustomerID AS Customers_CustomerID, Orders.CustomerID AS Orders_CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Orders.OrderID, Orders.OrderDate
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between #1/1/1996# And #12/31/1997#));
it is probably something simple I am missing here! Sorry to keep asking :(
but I can't seem to figure it out. All I get is Null values when I run the query.
I have a form with a start text box and a end text box. I use the form name sucessfully in the criteria row so I know there is no problem with the form name. I also know there is no problem with the qry name. Does anyone see anything I am doing wrong?!?!
The query I am working in is labeled [qry_old_summary_date_range]. Just wanted to clarify that is not a problem.
1st Pay Increase: IIf([qry_old_summary]![1st Pay Increase]>=[Forms]![Human Resources]![Start] And [qry_old_summary]![1st Pay Increase]<=[Forms]![Human Resources]![End],[qry_old_summary]![1st Pay Increase],Null)