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.
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 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?
We have Access 2000 database. Everythign is working fine but suddenly it have started loosing information. I entered one record last week and its not showing up now. I am not a remote user. I entered the data from local machine. Do someone have any idea about this issue.
I have a form, based on a table, with 3 fields, Ref (a reference number preceded by a G i.e. G14), a Definition (text) and a sort key (number field I put in because I couldn’t work out how to sort on the digits in the Ref).
I have set the records to appear in continuous forms and want them to be sorted in numerical order by the sort key (like they are in the table the form is based on).
However, the form always reverts to sorting them by the ref field, which is text so the order comes out as G1, G10, G11, G2..etc. If I select the sort key field in the form and the sort A-Z button it works but doesn’t stay like it.
I have tried to use the Order By field in the properties for the form but even though it says Sortkey in there it doesn’t actually sort by it…..
I have all my active databases on the same shared folder over the server. Database A has three tables linked to database B. The problem is that every morning when we log into database A, the link to one of the tables is missing, always the same table. I re-linked the table in the mornings, users log in and out during the day, no problems at all, the next day, gone again!
Both databases are .mdes. We are using Access 2003. Any ideas?
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.
I have a one to many to many relationship; with the "Cascade update related fields", and "Cascade update related records" on the relationship properties checked.
Since I have split the DB to FE and BE, the relationship properties (for the 3 tables on the FE) is grayed out and cannot be changed. In addition, the "Cascade update related fields", and "Cascade update related records" on the relationship properties is unchecked.
(The relationship properties on the BE is still configured to Cascade update.
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 have a main form that displays four lines of data. I have a tab control with 3 tabs. The form displays perfectly until I navigate to one of the other tabs and back to the main tab. It appears to scroll down a couple of lines and I loose the top line of data.
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;
