Queries :: ListBox Row Source Returning Multiple Blanks (Access 2007)
Jul 15, 2013
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...)
View Replies
ADVERTISEMENT
Feb 3, 2015
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
View 5 Replies
View Related
Jun 14, 2013
I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up
Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.
Lets use group as an example.
When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.
Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.
I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.
The SQL:
SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')
[Code] ....
View 2 Replies
View Related
Nov 25, 2013
I'm preparing a query as the control source for an unbound listbox. The following code gives the desired results:
Code:
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK
(My.control will be a control on the form. For the time being, I let the query prompt me for a value.)
It produces two columns like so:
CameraNum Camera ID
1 2
2 3
3 4
4 5
5 6
6 7
8 9
11 12
CameraNum is text; CameraID is numeric.
Now, I'm trying to use a trick I read about that should add a single textual entry to the top of the list like so:
Code:
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK
union
SELECT "(ALL)", "Dummy"
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]));
This produces
CameraNum Camera ID
ALL Dummy
1 2
11 12
2 3
3 4
4 5
5 6
6 7
8 9
The second (numeric) column is now out of order. This is reproducible for other values of my.control. If there are double digit entries they get inserted at the third row.
Why? What am I not understanding about how UNION works?
(BTW, I know I could put the "ALL" entry into tblCameras, thereby avoiding the need for a union, but I'd still like to know why the unexpected result.)
View 6 Replies
View Related
Feb 17, 2015
I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCal Equipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());
how to get the query for the report to return the records.
View 10 Replies
View Related
Jan 29, 2014
I have a listbox which is populated by a query from a table.
I also have a sub which allows users to quickly select multiple items from the listbox (based on a pre-defined criteria) rather than scrolling through and selecting them manually / individually
The sub works fine but when it is finished, the listbox is automatically scrolled to the bottom (or, rather, to the last selected item)
I would like to have the listbox scroll back to the very top once all of the appropriate items have been selected.
(I could loop through the listbox items in reverse - or 'bottom-up' - in the code but that would only have the desired effect if the topmost item was selected. I want it scrolled to the top regardless of whether the topmost item is selected or not).
View 2 Replies
View Related
Jun 10, 2015
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
View 5 Replies
View Related
Sep 21, 2004
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
Thanks,
JGM
onesweetdude@hotmail.com
View 1 Replies
View Related
Jul 15, 2014
I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
View 3 Replies
View Related
Oct 15, 2015
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
View 7 Replies
View Related
Dec 10, 2012
I have a Form with 2 subforms. One subform has an onload property to adjust columnwidths. I am trying to do the same for the other subform - but I get the following error:
The Expression on load you entered as the event property setting produced the following error: A problem occurred while micorosft office access was communicating with the OLE server or Active X Control.
View 3 Replies
View Related
May 6, 2014
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?
View 3 Replies
View Related
Aug 8, 2013
I have been asked to create a database very quickly for a small group that needs to scan multiple pages into an Access 2007 form. They they would like all scanned documents to be converted to PDF and stored on a shared network folder which can be accessed by their group. I have tried several ideas posted but, I have been unable to get any to work correctly. Any sample of working database that the scanning portion is working? I am so involved with other projects right now that I just don't have the time to create a scanning process on the database.
View 5 Replies
View Related
Jul 13, 2015
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
View 7 Replies
View Related
Jan 24, 2014
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
View 7 Replies
View Related
Jul 4, 2014
I have created a series of pie charts in reports based on queries. The data that is being charted are agree, strongly agree, neither agree nor disagree, disagree, and strongly disagree. I want the colors for these to be consistent in all the pie charts but they are coming out different colors on each chart: i.e., agree may be blue on one pie chart but yellow on another pie chart. How can I change the colors either in the pie charts or legends so that they are consistent on all the pie charts?
View 1 Replies
View Related
Dec 17, 2007
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 Related
Dec 17, 2014
I 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.
View 2 Replies
View Related
Aug 1, 2013
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.
View 1 Replies
View Related
Apr 26, 2013
I am creating a query to use in a Word 2007 mail merge.
For simplicity, let's say the query has three fields:
schname for school name, role for staff role, and name for the staff member's name.
The current query shows:
SchoolA, Admin, Jerry Garcia
SchoolA, President, Bob Hope
SchoolB, Admin, Janet Jackson
SchoolB, President, Mae West
I would like a query that would show one line per school like so:
SchoolA, Jerry Garcia, Bob Hope
SchoolB, Janet Jackson, Mae West
I've looked at Allen Browne's ConCatRelated function and can pull up a list of all names in a field called Expr1001, but that doesn't seem to be what I need.
View 5 Replies
View Related
Nov 25, 2013
I've got the following query as the row source for a listbox on a main form. cboShootName is a combobox on that same form.
Code:
SELECT DISTINCT QrySbfShotList.CamerasFK, tblCameras.CameraNum
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]))
UNION
SELECT null, "(ALL)" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]));
When the form loads, it prompts for a value for me.cboShootName, even though I've given the list box a default value. More important, after the cboShootName is established, ie. in its after update event, if I put
Me.lstCamera.requery (lstCamera is the list box in question), the system prompts me for the value of me!cboShootName. I know the cboShootName is in scope because I see its value with a msgbox diagnostic.
The first column of both the cboShootName and lstCamera refer to numeric fields. Both controls are unbound.
What am I missing? Is there a trick to referencing control values in a query?
View 8 Replies
View Related
Jun 4, 2014
I have a table of logged entries. Each record has a date field (ValueDate) and an account identifier field (AccountID)
I also have a table of rates. Each record has the same account identifier field (AccountID), a date field (EffectiveDate) and a rate field (BankRate)
Entries can be logged for any given ValueDate. But there may or may not be a corresponding EffectiveDate in the rates table.
I need to write a query that will return all of my logged entries and the largest EffectiveDate which is on or before the ValueDate (as well as the BankRate corresponding to that EffectiveDate)
This is as far as I've gotten but it returns multiple records for each logged entry. I need one record per logged entry.
Code:
SELECT tblLoggedEntries.EntryID, tblLoggedEntries.AccountID, tmp.BankRate, MAX(tmp.EffectiveDate) AS EffectiveDate
FROM tblLoggedEntries
LEFT JOIN
(SELECT tblRates.AccountID, tblRates.BankRate, tblRates.EffectiveDate
FROM tblRates) AS tmp ON tblLoggedEntries.AccountID = tmp.AccountID
WHERE tmp.EffectiveDate<=tblLoggedEntries.ValueDate
GROUP BY tblLoggedEntries.EntryID, tblLoggedEntries.AccountID, tmp.BankRate
View 11 Replies
View Related
Mar 27, 2014
I have an expression that sets an AI Buy It Now value that is 125% of AI Value and rounds it, which follows AI Buy It Now: Round(([AI Value]*1.25)/10)*10
I need to add to this expression that I only want this calculation applied if AI Values are greater than 999. But I can't figure out how to add this stipulation to the above expression. I have tried IIF but it is not working.
View 5 Replies
View Related
Jan 16, 2014
I have a basic query off a currency table :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy
FROM tblCurrencies
ORDER BY tblCurrencies.Ccy;
Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)
As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?
Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.
So I started with the following :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts
FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy;
But this just gives the number of AccountID's per currency (regardless of duplication within them)
I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.
View 14 Replies
View Related
Apr 17, 2013
I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?
Here is what I have so far:
November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))
View 14 Replies
View Related
Feb 20, 2015
I have a table of transactions (close to 1m records) from which I want to query totals by currency and direction (ins and outs) and then also show the proportion of the overall total for each currency. All amounts are absolute (i.e. ins and outs both represented by positive numbers)
To throw some added complexity into the mix, the transaction table records only refer to account (AccountID), which links to a separate account and currency tables.
I can query the sum totals by currency and direction (although I'm not totally happy with the SQL syntax .
Code:
SELECT tblCurrencies.CurrencyCode,
tmp1.SumPay,
tmp1.SumPayUSD,
tmp2.SumRec,
tmp2.SumRecUSD
[Code] .....
I'm struggling to get the proportions per currency though? I tried adding :
Code:
((SumPayUSD+SumRecUSD)/Sum(tblTransactions.USDAmount)) AS Proportion
But it tells me that the specified field 'tblTransactions.USDAmount' could refer to more than one table listed in the FROM clause of the SQL statement.
View 3 Replies
View Related
