Queries :: Crosstab Query Error 3070 Because Of Date Field(s)
Feb 4, 2015
I have simple crosstab query that I use to pull data about how many products is selled in one day/week/month and so on. On entry form I have two critical date fields, Starting/Ending date. It all works perfect except when I put indetical values in both those fields.
For example I have sold over 100 items on 4th of february 2015. year. So, in Starting/Ending date field of entry form, I put value from 04/02/2015 to 04/02/2015 in order to see how much items was selled that day.
This is the error I receive:
Quote:
An error was encountered
Description: The Microsoft Office Access database engine does not recognize '' as a valid field name or expression.
I'm using Access 2007 and have a query in which I have a Where clause that includes a TempVars variable. Here is the SQL:
Code:
SELECT tbl_CostCenter.*, tbl_OpEx.*, qry_OpExByCC.* FROM (tbl_CostCenter INNER JOIN tbl_OpEx ON tbl_CostCenter.[Cost Center] = tbl_OpEx.[Cost Center]) INNER JOIN qry_OpExByCC ON tbl_CostCenter.[Cost Center] = qry_OpExByCC.[Cost Center] WHERE tbl_OpEx.[Base Year]=TempVars!CurrentBaseYear ORDER BY tbl_CostCenter.[Cost Center];
I get runtime error 3070 stating that "The Microsoft Access database engine does not recognize 'TempVars!CurrentBaseYear' as a valid field name or expression." When I run the query without the Where clause, it runs fine, except it includes all years, not just the one I need.
However, when I use the immediate window to check the value of TempVars!CurrentBaseYear, I get a result of 2012 which is what I expected. So how can it not be recognized in the query? I thought that one of the perks of using a TempVars variable is that it can be used in queries.
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
The code behind the report - sets on open_report event is the following however i cant see any issues with it as i use it elsewhere..
Code: ' Create underlying recordset for report using criteria entered in Dim intX As Integer Dim qdf As QueryDef Dim frm As Form ' Set database variable to current database. Set dbsReport = CurrentDb
[code]....
I will also attach a copy of how i set the criteria in the query and the parameters
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading [Plan Type]- Group By / Column Heading [Pmt Amt]- Sum / Value [Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code: CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
I got an error (The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070) ) when I tried to use the ranking system described by meloncolly and sbaxter in previous threads, created a query using the rank, and then tried to make a crosstab query with it.
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
AutoNumber(primary key) SerialNumber Station Defect Type Date
Basically now I need to be able to get the result i've gotten by a date range, but the crosstab query won't let me do it the way you can in a regular select query...help..
I am trying to create a crosstab query that counts the number of events that have been logged for each day of the month. The event date is stored in an ODBC compliant database in date/time format. Unfortunately, the crosstab query counts the number of events for each date/time occurrence.
I have tried entering the following expression in the Field: definition with no success (no records are returned when the query finishes executing):
Please help I have a cross tab query which is based on a simple query which gives me a summary of sales (sales by sales rep and sales by job type) is there any way i can put a date range into this? everything I've tried doesn't work. It works in a simple query but i cant summarize the simple query. Thank you in advance.
Hi have a table tblImportLearnerFunding with the fields [LCCode], [Date] and [Total Learner Funding]
I want to create a table to see the funding generated for each LCCode in custom date ranges: LCCode, DateRange1, DateRange2, DateRange3
I can almost get it to work using the crosstab query wizard, but I need to specify the date ranges. Instead of Jan I want to sum the funding in the date range 01/01/2005 to the 28/01/2005 and so on.... Does anyone have any clues?
Here's the Crosstab SQL:
TRANSFORM Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [SumOfTotal Learner Funding] SELECT tblImportLearnerFunding.LCCode, Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [Total Of Total Learner Funding] FROM tblImportLearnerFunding GROUP BY tblImportLearnerFunding.LCCode PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable SELECT QryTable01.productName FROM QryTable01 GROUP BY QryTable01.productName PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable] t Then the report row with the values:
Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?
Code: PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime; TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM] FROM Query1 WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null)) GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp] PIVOT Format([Date Stamp],"Short Date");
I want to show this in a report, similar to an spreadsheet, with:
ProductionForecastProductId = Rows ProductionForecastMonth = Columns ProductionForecastQuantity = Data
I thought of using a crosstab query like this:
Code: TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity SELECT tblProductionForecast.[ProductionForecastProductId] FROM tblProductionForecast GROUP BY tblProductionForecast.[ProductionForecastProductId] PIVOT tblProductionForecast.[ProductionForecastMonth];
but I don't want the average of ProductionForecastQuantity. I want the actual value!
Is a crosstab query the wrong method to use?
If no, how do I show the actual value of ProductionForecastQuantity
I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.
I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:
10 Metre 12 Metre 14 Metre 17 Metre 20 Metre 3 Metre 4 Metre 5 Metre etc
But I need to show it as:
3 Metre 4 Metre 5 Metre 10 Metre 12 Metre 14 Metre 17 Metre etc.
The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.
I've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
I have generated a table using a crosstab query and it gives the information as below
12/10 12/11 12/12 13/01 Part 1 1 2 Part 2 4 4 4 Part 3 5
So basically part, qty used, year/month used - it works fine up to this point.
Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months
Or
Create another query to do this.
But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).
The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.
I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:
TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8)) GROUP BY IUIDCODES.CODEDESCRIPTION PIVOT CASESTATUSCODES.CODEDESCRIPTION;
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber] FROM Cultureworkload GROUP BY Cultureworkload.Reasonfortesting PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
