Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
i have created a query to do the calculation of how much stock came in went out and is still on hand .the problem im having is that it shows me the 0 total for product with no stock in it when i put my criteria as >0 in my goods on hand field it show me no data in the query.
my calculation fields is as follows GoodsOnHand:[GoodsIn]-[GoodsOut] Goods in gets calculated as follows GoodsIn:Nz([SumOfGoodsIn],0) Goods out get calculated same way as goods in.
The Reason i have a sum of goods in is because i have to calculate the quantity in another query because it is spread over lots of locations.
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance Table: tGLCashAcct Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code: UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance] WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012 April 2012 May 2012 June 2012 July 2012 August 2012 September 2012 October 2012 November 2012 December 2012 January 2013 February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
I am getting this error in a query. The field generating the error is a calculated field using a custom function.The function is:
Code:
Public Function DecimalTime(dblEvalTime As Double) As Double DecimalTime = Hour(dblEvalTime) DecimalTime = DecimalTime + (Minute(dblEvalTime) / 60) DecimalTime = DecimalTime + ((Second(dblEvalTime) / 60) / 60) DecimalTime = Round(DecimalTime, 2) End Function
The dbalEvalTime parameter is passed in to the function as (DateIn+TimeIn)-(DateOut+TimeOut).
So the data type passed in is Double and the Function result is Double. The criteria i am applying in the query is simply <0.01. I have formatted the query field as #.00, 0.00 and General Number but it makes no difference.
I have also tried creating a second query using the first as its data source and applying the criteria in that query but still get the same error. Without the criteria the query runs fine.
I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
Code: SELECT prevwd([practice_bacs_submission_date])<Date() AS chase_it, practice_bacs.practice_bacs_submission_date FROM practice_bacs WHERE (((practice_bacs.practice_bacs_submission_date)>#1/31/2013#));
and in the query results I see 0 and -1 as expected for the 'chase_it' expression BUT When I add True (or -1, or 0) as a criteria for 'chase_it', I get the "Data type mismatch in criteria expression" error.So the sql that fails is
Code: SELECT prevwd([practice_bacs_submission_date])<Date() AS chase_it, practice_bacs.practice_bacs_submission_date FROM practice_bacs WHERE (((prevwd([practice_bacs_submission_date])<Date())=True) AND ((practice_bacs.practice_bacs_submission_date)>#1/31/2013#));
In case it's relevant, my function prevwd is:
Code: Function prevwd(dt As Date) As Date 10 On Error GoTo prevwd_Error 20 dt = dt - 1 30 While Weekday([dt]) = 1 Or Weekday([dt]) = 7 Or IsBankHoliday(dt)
[code]...
and this function is used extensively and always works perfectly. I have tried using DateAdd instead of dt = dt - 1, but that made no difference.
Looking to have a count function which calculate data in sense like if records found on 1-jan-2014 the it give answer as 1, same as records for 2-jan-2014 it should return 2 and so on .. in short the criteria must look and give same number for same dates starting from 1
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix, IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
Basically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.
I also have a query for sales with a SellingBranch field on.
I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".
At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.
I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number 1 A1 fjlafs 453453454 3 A2 fsdfsd 343534534 4 A3 gsdgsdg 354543534 5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
Hi, I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 x x (check boxes) Y ACD 2 x x
Query 2. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 10/8/05 (query performs Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
I have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.
Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?
I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:
Code: SELECT * FROM Duplicates_Rule1 WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp GROUP BY [BSEG_DocumentNo] HAVING Count(*)=1 )) AND (Duplicates_Rule1.Check) Is Null) and (Duplicates_Rule1.BSAK_Text <> "FREIGHT") ORDER BY Duplicates_Rule1.BSEG_DocumentNo;
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
I am trying to use criteria for Product column (i.e keep only product A) Total for Sales column to aggregate the values and it seems that is cannot be done simultaneously.
Product Sales A 34 A 45 B 21 A 11 B 23
Eventually i want to get a table like below
Product Sales A 90
I get the following warning : you tried to execute a query that does not include the specifies expression as part of an aggregate function.Why is this happening. I don't want to create 2 different queries to achieve it. Is there a way to do it i the same query?
It is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
I am trying to use a textbox value as a query criteria using the code [Forms]![Home]![mtdDateBox], following the standard structure of referencing a form item. However, I am not returning any records, when it should be giving some. The data in the textbox follows the date format "DD.MM.YYYY" but is used as a string.
Is there something I am missing out? I am using MS Access 2010