Queries :: String In Expression Is Being Treated As A Parameter?
Apr 30, 2014
I've got a query. One field is an expression on which I'm filtering.
A shortened version of the expression is
Expr1: IIf([Status]=Active,"Yes","No")
There are other parts to the formula, hence why I'm not filtering [Status] itself, but stripping them out gives the same problem.I'm filtering on "Yes".What is happening is when I close the Expression builder, it changes "Active" to a parameter, thusly:-
Expr1: IIf([Status]=[Active],"Yes","No")
Status is a text field, it's a query on a single table, there's no field called Active.
View Replies
ADVERTISEMENT
Apr 8, 2013
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
View 2 Replies
View Related
Aug 1, 2014
I have a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001).
The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.
The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.
For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).
As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.
Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?
View 1 Replies
View Related
Apr 25, 2014
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)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
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] & "]" #
View 7 Replies
View Related
Jul 31, 2013
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
View 3 Replies
View Related
Jan 11, 2013
This returns the correct value Debug.Print [myTable]![myField]
Why does this result in an error? Debug.Print Eval("[myTable]![myField]")
View 5 Replies
View Related
Jul 15, 2014
I'm currently in the process of moving from Excel to Access and am setting up a user table with 5 Fields (Full Name, First, MI, Last, Alias). I would like to only transfer the Full name and have the other field be calculated fields, so they automatically fill in. I was able to get the "Last" field to work, but the others are being tricky. Especially the "Alias" field which would need to take the first letter of the first name, MI letter and the entire last name, like (JPDoe).
The current format of my "Full Name" field looks like, (Doe, John P.) without the brackets of course. I'm new to Access, so I was trying to use the Expression Builder to do all of it, unless there is an easier way?
View 7 Replies
View Related
Mar 9, 2015
I'm experiencing a error when I go to add a filter, "Syntax error in string in query expression "MyFieldName""..If I go to to the table where the field is located, I CAN apply a filter.However, If I throw this single field on a form and switch to datasheet view, all of a sudden I can not filter it.
Additional info: whenever I go to build a query with the field, it throws brackets around this field and no others... this is weird.
Code:
SELECT MyTable.field1, MyTable.field2, MyTable.[problemfield]
FROM MyTable
View 5 Replies
View Related
Apr 24, 2013
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
View 1 Replies
View Related
Sep 19, 2006
Hi Folks,
I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.
Things to mention:
"All" has a value of 1 in the lookup table.
I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.
My first attempt was to put:
iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.
Access' response was that the criteria value was too complicated to resolve.
So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.
Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:
In the criteria for the field in the query:
IfAll([forms]![frmFormName]![cboPrograms])
And in a module:
Public Function IfAll(ProgSel)
Select Case ProgSel
Case 1
IfAll = "Like ""*"""
Case Else
IfAll = ProgSel
End Select
End Function
I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?
Gratefully,
-D
View 3 Replies
View Related
Jul 31, 2013
I want to count the number of occurences in a datatable when loading a form. BUT i want only to count if it is equal to a value on a text box on this same form.
what i made:
1-created a unbound textbox
2-inserted on "control Source" this expression =DCount("*";"T_Pendentes";"[disponente.Procurement]= " & [me].[disponenteProcurement].[Value] & "")
where * is to count all, T_Pendentes is the table, and de expression is the criteria.
View 2 Replies
View Related
Feb 24, 2015
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"
[Code] .....
View 9 Replies
View Related
Jun 19, 2015
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
View 11 Replies
View Related
Sep 18, 2014
I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.
Here is the main DELETE query :
Code:
DELETE DISTINCTROW [tblComments].*
FROM [tblComments]
INNER JOIN [qryCommentsToPurge]
ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.
If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?
Code:
SELECT [tblComments].*
FROM [tblComments]
INNER JOIN [qryIssuesToPurge]
ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];
[code]...
View 1 Replies
View Related
Jul 5, 2007
Hi People
Got another question for you all, since i'm on a mission of doing my annual upgrade of the DB.
My sales manager has asked me to implement a system where the database will flag up any customers who haven't used us for 6 months or longer.
I know i will hav to use a query and the Fields "Customer" & "Date", but cant think of the expression i would hav to use inorder for the database to do this operation.
I also want to add a check box so that, we can tick off customers who arent going to be usin us again, so that the query doesnt flag them up every month.
If any one could help that would be great!!
Thanks
gary
View 2 Replies
View Related
Jun 8, 2005
Is there a way that I can search the queries in my Access Database for certain text string? If someone knows please post some tips.
Thanks...
View 1 Replies
View Related
May 14, 2015
I'm a little new to Access but I'm trying to create an IIf Expression:
iif ( condition, value_if_true, value_if_false )
Mine is: Au_ppb: IIf([Element]="Au" And [Units]="ppb",[Result],Null)
I need to have three criteria's (each looking into a different column). I can get two criterias but not three:
[Element]="Au" And [Units]="ppb"
When I add in the third (And [Priority]="12") it doesn't work .
Also, I need to have about 6 columns with similar IIF statements in the one query in my query and if a row has Null for all then I don't want it visible however if I make the Criteria "Is not Null" on one column or on all it comes up with no results.
View 2 Replies
View Related
Aug 17, 2014
Users add transactions via a form after they put in the total of the transaction I want to run a query that gets a running balance of the client's account to make sure that paying this transaction will not over draw the clients account.What would be the best method of approach?
View 12 Replies
View Related
Apr 2, 2015
I have a text field in a Table and on a Query called "Notes" In that field that has data like below:
[04/02/2015:BD] Project is to be assessed by Solutions Planning
[03/27/2015:BD] Project prioritized
[03/14/15:BR] Entered to system
Im trying to find a way to pull just the most recent line of text, in this case
[04/02/2015:BD] Project is to be assessed by Solutions Planning
into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.
View 10 Replies
View Related
Dec 17, 2013
how I can convert the first column to show the number? I used the expression below, but it does not convert anything with a letter or a 0
Expr1: Val([SPPC TEST]![JULY_ACCRUAL_ADJ_X])
View 7 Replies
View Related
Jul 6, 2013
How to reference current row in SQL string? I have a qst while using RowSource. I have 3 tables:
[1] CITY
col: _value _name
[2] STREET
col: _city _value _name
[3] ADDRESS
cal: city street
The problem ocurred when i designed ADDRESS table:
The RowSource of [city] is "SELECT _name, _value FROM CITY".
And I set the binding-column as 2, so that _name can be displayed and the real value is _value(integer).
But here comes the trouble: how can i set the RowSource of [street]?
The _value of STREET table depends on _city, and _city is _value in CITY table.
E.g. _name of STREET is "Wall Street" when (_city,_value) = (1,1),
But "Lincoln Avenue" when (_city,_value) = (2,1)
I want to use "SELECT _name, _value FROM STREET WHERE _city=CurrentRow.city" as RowSource, but what can replace the "CurrentRow"?
View 2 Replies
View Related
Jul 29, 2014
I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
View 10 Replies
View Related
Sep 12, 2014
I have a number of global variables set when a user logs into my application which i want to use in an SQL string to record these in a table however im not sure how i go about it i know simply inserting the defined names in the SQL wont work.
Is there a way to define these in global functions instead?
View 1 Replies
View Related
May 26, 2013
How many kind of string inbuilt function we have or if we create our on function so how to call that function in your query...
View 6 Replies
View Related
Jul 11, 2014
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01
View 13 Replies
View Related
Jan 12, 2015
Here are the 2 tables that I am working with:
Account
Account
0041980041
0051670051
0052330052
0053490053
Conversion
Account Conversion
0041 10000151
0051 10000152
0052 10000153
0053 100001524
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
View 4 Replies
View Related
