I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."
What am I doing wrong?
Screencaps attached.
Thanks.
I've been using the ctrl+F search in order to search my db for a string or phrase (from a form). Now, after I put it on the Intranet and accesss it through there, I use ctrl+F and if I search for something that is not there, it doesn't return a "did not find" box. It's like it just keeps searching.
Is using the find feature like this just bad form and I should write queries for users to search the db?
Comments/critiques/criticisms welcome. Thanks.
i have a query with this basic structureSELECT id FROM table1,(SELECT SUM(field2) as total2 FROM table2) - (SELECT SUM(field3) as total3 FROM table3) as totalto understand better let's say that:table1 are clientstable2 is money that enters for clientstable3 is money that exits from clientsi want to obtain a balance. the problem is that if table2 or table3 has no records for certain client, this client is exluded from resultsany ideas?i tried using ISNULL like this:SELECT id FROM table1,ISNULL((SELECT SUM(field2) as total2 FROM table2),0) - ISNULL((SELECT SUM(field3) as total3 FROM table3),0) as totalbut it didn't workedthis is the querySELECT id, UPPER(apellido_titular) + ', ' + nombre_titular AS padre, (SELECT SUM(facturas_items.importe) AS totalf FROM facturas INNER JOIN padres ON facturas.id_padre = padres.id LEFT OUTER JOIN facturas_items ON facturas.id = facturas_items.id_documento WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) - (SELECT SUM(recibos_items.importe) AS totalr FROM padres INNER JOIN recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN recibos_items ON recibos.id = recibos_items.id_recibo WHERE (padres.id = p.id)) AS totalFROM padres pWHERE (activo = 1) AND ((SELECT SUM(facturas_items.importe) AS totalf FROM facturas INNER JOIN padres ON facturas.id_padre = padres.id LEFT OUTER JOIN facturas_items ON facturas.id = facturas_items.id_documento WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) - (SELECT SUM(recibos_items.importe) AS totalr FROM padres INNER JOIN recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN recibos_items ON recibos.id = recibos_items.id_recibo WHERE (padres.id = p.id)) > 0)ORDER BY UPPER(apellido_titular) + ', ' + nombre_titularsorry for my poor english
View 3 Replies View RelatedI'm trying to use a DLookup to get a specific value from a field in a table.
This is what my code currently looks like;
Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
what I'm doing wrong?
I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:
Code:
SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];
Table examples:
Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX
[code]...
I have already made a query with all the information needed. What I am trying to do is create a form where a user can open the database and enter a unique number and have information from 10 fields associated with that number show.
View 5 Replies View RelatedI have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I have a Combobox so the enduser can search and select items for the form below it rather than clicking the arrows for Next/Previous.However I have an issue with DLookup queries.My Combobox is called 'F_ComboBox' (I use F_ in my forms to indicate it's a form textfield and not from a table).My first field in the form, called 'F_ProdID', I want it to select the rows ID from the Combobox option selected. So it's simply:=[F_ComboBox]Which works fine, it shows the ID for what I selected... but when I do this to show my ProductName...=DLookup([ProductName], 'Products', [ProdID] = [F_ComboBox])Or the same but using the first textfield:=DLookup([ProductName], 'Products', [ProdID] = [F_ProdID])It loads once but if I select another item out of the Combobox it doesn't change, it just stays on the last result. So the query works, just doesn't refresh when a new item is picked from the ComboBox.Am I doing something wrong?Thanks,Nick.
View 1 Replies View RelatedCreated a button through button wizard that is supposed to open a form to add a new record, but all of the fields don't clear out. Only some fields clear and other fields actually populate data from another record.
Snip1 shows my form with a record selected. When I click the 'New Waste' button, you can see that the record ID goes to '(New)', but the fields actually populate data from another record.
This even happens if I set 'Data Entry' to yes for the form.
here's the code behind my button:
Code:
Private Sub btnNewWaste_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
I even commented out my code for duplicating my record just in case but that didn't make a different.
Code:
Private Sub btnDuplicateRecord_Click()
Dim ctrl As Control
For Each ctrl In Me.Form.Controls
If ctrl.Tag = "DefaultMe" Then
ctrl.DefaultValue = """" & ctrl & """"
End If
[Code]....
edit: this problem persists in a backup database that only contains one test record. The button pre-populates data that doesn't exist in the back-up database.
So, I have two forms that I am trying to make work the same way.
With F1Entry I can use the combo box in the header to select different request numbers.
With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.
Database attached
Hello,
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded.
Is there a setting on the parent form that also has to be changed, to make the new binding?
Thanks,
Aleksander
Hi All
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")
Any suggestions would be greatly appreciated
cheers
sallee
OK so here is a working query:
SELECT Assets.*
FROM Assets
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.
I tried this but it now gives back over 220 repeating results.
SELECT Assets.BarcodeNumber ,
Employees.UserID ,
Building_names.BuildingName,
Assets.Floor ,
Assets.BuildingLocation ,
Assets.DeskLocation ,
Employees.FirstName ,
Employees.LastName ,
Employees.SSO
FROM Assets ,
Employees,
Building_Names
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
I am sure it something simple but I am a novice at this so please help me. :D
lets say I have a table with five tasks for five employees and each employee's data type is with 'Yes or No'.
I need to have a desired result to check
1.if all the employees have completed that particular task then the result should be 'YES'
2.if anyone employee has not completed that particular task then the result should be 'NO'
so, for each task there must be a check on all the five employee's data type.
I have a table like this
Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...
I want to do a query to return just one row per unique ItemNo
So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog
I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.
I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.
I have a bound form that is used to enter company info (address, name, category...etc). When the user closes the form, if Company name, province or category are left blank, I warn them with a message box asking if they want to exit and undo changes, or return to the form to fill in the missing info.
When they choose the option of returning to the form, I get 2 errors. You must enter a value in "tblCompanies.category", and "You can't save the record at this time, do you want to exit without saving?". If I click "No" on the second warning, focus is set to the missing data control and I can continue working.
How can I prevent the record from being saved when I choose to return to the form to fill in the blank records?
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Error
Dim Response As Integer
' Determine if required fields are populated.
If IsNull(Me.txtCompanyName) Then
Response = MsgBox("Company name is a required field. Do you wish to discard changes and exit?", vbYesNo,
[Code] ....
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
I want to execute a delete query on a table of account records. The query contains two tables linked on account number, one with a list of unique records, linked to the other with "many" records to be deleted. For each unique record from table A, I want the delete query to delete all records with a matching account number in table B. However, I get a message stating that I "could not delete from the specified table." Huh???
I can manually delete from table B, or I can get the query to work if I don't try using table A as the control table and instead specify literal criteria. I have tried all three join types but still no luck. Any ideas?
Many thanks in advance for any help provided-
SLH
Hello,
I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical.
Does anybody know why this is happening?
Thank you.
The query is:
SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work.
Why?
I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked.
Any ideas?
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
Hi,
I have a query for a subform which is displayed in datasheet mode (see attachment). I've created a form based on a query and used it as a subform.
The "casenumbers" per clients are displayed from 1 and upwards, I would like to have them sorted Descending. So in the query I sorted them that way. When I run the query they are displayed in descending order. When I open the subform based on that query however it is not. I can sort descending anyway by clicking the column on which I want to sort and do it that way, but that becomes bothersome and it doesn't need to be that way.
Record source is ok:
SELECT KlantNAW.ClientNumber, CaseInfoTable.Casenumber, CaseInfoTable.DateInitialCall, CaseInfoTable.DateCaseCreation, CaseInfoTable.CaseAccepted, CaseInfoTable.[1stAppointmentDate], CaseInfoTable.DateCaseClosed, CaseInfoTable.About1 FROM KlantNAW INNER JOIN CaseInfoTable ON KlantNAW.ClientNumber=CaseInfoTable.ClientNumber;
Order by:
Casenumber DESC
I thought, well, lets create a new form based on the query to see if that changes anything, maybe I've changed something I shouldn't have.
But nope, same result.
How can make the form sort descending in datasheet mode?
When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?
View 7 Replies View RelatedI've encountered a simple problem. =) I have a report based on a query. Of course due to the relations the query table should affect the report. But it does only with the filter parameters I put in the Query Design View, the quick ones ("filter by selection", etc.) that are in the result table view work there but doesn't affect the report. Still the query asks me to save it each time I apply any of these quick filters but make no changes at the end.
Any ideas how to make them work in the report? Or these filters are temporal and shouldn't work in that way? If so, what could I do to simplify customer's life?
(Access 2003, Access2000 based database)