Queries :: Not Recognizing Expression As A Valid Field Name When Applying Data To A Chart
Jun 18, 2015
So I'm new to Access, and I am trying use a query that can be referred to by a chart. So the idea is that I use the query to select data only from the date range that the user chooses on the home screen of the database for their chart (using the command Between [Forms]![Home Screen]![From] And [Forms]![Home Screen]![to])..Although it has been working fine for charts that only have two parameters, when I attempted to make a line graph that sorts by 3 parameters (i.e. date and amount for different types of something), it stops. I get the message that "The Microsoft Office Access database does not recognize [Forms]![Home Screen]![From] as a valid field name or expression" or something to that effect.I'd rather not remove the whole specification created by using the dates from the home screen, as it has been working fine on all other aspects of my charts and reports.
I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!
Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?
The structure of the table on which the report is based (via a crosstab query) is :
transaction date auto number ID transaction type (either payment or receipt, chosen by form's drop down box) - TEXT amount - CURRENCY receipt type - TEXT payment type - TEXT fundno - TEXT
The crosstab query design is as per the attached jpeg file
=IIf([Details]="COST OF PRODUCT",[PerUnit]*[Quantity],0)
it keeps coming back with 0 even when the text says COST OF PRODUCT
I have also tried: =IIf([Details]="COST OF PRODUCT*",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT*",[PerUnit]*[Quantity],0)
I'm using the expression builder to create it in a total box in the report footer, I have a box that gives me the total of the report using a sum and I need to show the cost of product separately to this as I need to use it in another calculation.
I am currently building a stock control system in access. I have a form with which the user selects a stock item from 3 combo boxes (stock name, stock weight and stock grade). The combo box links to a table of stock items. A stock item is made up of a name, weight and grade (all separate fields in the stock items table).
I wish to setup the form so as the user selects the stock name from the first combo box. Based on the value selected by the user, the appropriate stock weight(s) assoicated with that stock name will be displayed in another combo box.
Basically, I wish to take the result from one field in a form and apply its result to a query(?) in another field so as to filter(?) my result accordingly.
While applying filter in forms, i have 6 fields in the form and i want to display the data even one of the filed is with 0.
I used the below code, but this one display the data where all the fields are 0. I tried or in place of and but still its not working.
Private Sub Form_Open(Cancel As Integer) Me.Filter = "[Placed]=0 and [receievd]=0 and [Ordered]=0 and [processed]=0 and [delivered]=0 and [closed]=0" Me.FilterOn = True End Sub
I have a query that serves as the data source for a report. Previously, I had a unit cost value that the User entered as a parameter, but I wound up adding an unbound text box on my form so that I could validate the input before executing the query. So now, the parameter is coming from a form. The text below is from my query, but Access still pops up with a parameter box as though Expr3 is undefined.
Expr3: [Forms]![BridgeRptsF]![CostSF]
I have verified that the field name on the form is correct, and whenever I try to run the report, I do it from the [BridgeRptsF] form and I make sure the CostSF field is populated. I feel like this is a pretty straightforward fix but I've not stumbled upon it yet.
In the query, I have set the OrderID criteria to: [Forms]![sfrmOrders]![txtOrderID] Whenever I try to run the query from the open form & subform, an Access 'Enter Parameter Value' window pops up.
I would like to use this Criteria for many different features in my db (SendObject, etc.), but I cannot find how to set the OrderID based on the current form.
If anyone can see what I am doing wrong, I would appreciate your help!
I have a login screen to open a form. I would like to filter the form based on what user logs in. In my tbluser, I have a trainer name, a UserLogin, and a password. I would like the form to filter on trainer name based on userlogin.
I know the code: DoCmd.ApplyFilter , "Trainer = 'Joe Smith'" but how to I edit the code to change as the tbluser changes?
I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.
What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.
I have the following SQL for it:
SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent] FROM ATB GROUP BY ATB.FC, ATB.MMDDYY HAVING (((ATB.MMDDYY)=Date()-Day(Date())));
Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.
I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date, Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name], Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
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 this expression I created and I want to know how to format it as currency. I know how to do it with a regular field but the option is not available for an expression.
The expression:
Code : Charge: IIf([Rate] Is Not Null,[Rate],"0.00")
Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2] FROM [E&I Table] WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV")) GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4% 90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
I have created a text box for people to enter search criteria, e.g. customer surname. The results appear in a list box underneath. When I select the record it should open up the customer's details but in Access 2013 it produces an error "The value you entered isn't valid for this field". The first sub-routine pulls the search data into a Row Source:
Private Sub txtCustomerSearch_Change() On Error GoTo Err_txtCustomerSearch_Change Dim strSource As String strSource = "SELECT DistinctRow CustomerNo, [End Date], Surname, FirstName, Address1,
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.
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.
