General :: Evaluate String To Make Expression
Jan 11, 2013This returns the correct value Debug.Print [myTable]![myField]
Why does this result in an error? Debug.Print Eval("[myTable]![myField]")
ADVERTISEMENT
Queries :: Can't Evaluate Date In Text String
Apr 17, 2014I'm working with a table of financial transactions. I need to know the date a record relating to cash received actually arrived in our bank, but the software doesn't allow the user to enter this, so I've asked them to enter it into a text reference field, e.g. 'Other Text XX 16/04/14', and then I've got a formula - CDate(Right(Trim([RefField]),8)) - which takes the date from this reference.
This works fine, but it's really important I don't miss any of these records due to the date not being entered, so what I'm trying to make is a formula which will give me the date from the ref field as in the formula above if there is one, but if that formula gives an error (so the date has been missed out or incorrectly entered) then I want to take the date from another field, called [DateAdded] as a failsafe.
I can't work out a formula that won't give me an error, i've tried loads. First I tried :
iif(iserror(CDate(Right(Trim([RefField]),8))),[DateAdded],CDate(Right(Trim([RefField]),8)))
But that gives an error, and I think from looking at forums that Access evaluates both parts of an Iif function so it'll do that.
It seems like other people are saying that you need to use Nz, IsDate or IsNumeric or something along those lines instead of IsError to evaluate the field, but I can't get that to work as it's not a field, it's a portion of a text field.
This is my latest attempt:
Join Date: IIf(IsDate(Right(Trim([RefField]),8)),[DateAdded],[Calc Date]),
where [Calc Date] is an expression field with CDate(Right(Trim([RefField]),8)) in it, but there's the #Error again.
How can I use iif to pick either a date from a text string, or where that results in an error then another date field?
Queries :: String In Expression Is Being Treated As A Parameter?
Apr 30, 2014I'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.
Modules & VBA :: Text String Extraction - Expression Builder?
Jul 15, 2014I'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?
Add A Filter - Syntax Error In String In Query Expression
Mar 9, 2015I'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
Queries :: Use Of String Expression As Field Definition And ARGUMENT In IIF Statement
Apr 25, 2014I 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] & "]" #
Forms :: Can Make A Color A String Value
Oct 28, 2013I'm new to VBA - Access but just dangerous enough to get in trouble. Is there a way to make a variable with the code below? I've been scouring the net all day and can't find answer. What I would like to do is make all the "cirsize1" be a string. Particularly the "Me.cirsize1.ForeColor". if I use the "cs" string it works fine with the Me.cirsize1 = 0 (ie "If cs = 0") This is just a snippet of the code.
Private Sub cirqty1_AfterUpdate()
Dim cs As String
cs = Me.cirsize1
If Me.cirqty1 > 0 Then 'if there is a quantity greater than 0 in circle qty and
If Me.cirsize1 = 0 Then 'if there is 0 in circle size
Me.cirsize1.ForeColor = vbRed 'then make the circle size 0 red
Me.txtCirIPM1 = 0
End If
End If
End Sub
Modules & VBA :: How To Make One String Contains All Of A Column
Jul 25, 2013i want to make a string that contains all of the fields in a column.
I have a table called UserSelectedComponentT with a column called ComponentName.
I want a string that is essentially all the different component names seperated by a " + " .
Convert A Text String To Integer - Check For Error In Expression Builder
Apr 24, 2013I 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.
Make Table Query And Expression Names
Aug 31, 2007Hi all,
I have a Make-Table Query that has five expressions. I have changed the properties of these columns so that, when you view the query, it shows the column names I have chosen. So, instead of Expr1, I get "Haggis", and instead of Expr2, I get "Cold Toast".
BUT, when this query creates a table, the column headings revert to Expr1 and Expr2. Is there any way to make my custom column headings stick in the new table?
I should add that I frequenty run this query and overwrite the table. Thus, even if I go into the table and change the field headings, as soon as it is overwritten these changes revert.
Way To Make A Part Of A String That Is Calculated Into Currency Form?
Feb 28, 2012I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.
Evaluate A Condition
May 11, 2007Please have a look at the following code (I am not good at VBA or SQL).
Me.[txtBox] = DLookup(“[SNum]”, “[Table1]”, “[RecID])
If Me.txtBox =>2 Then
Condition if true
Else
Condition if false
End If
I type a value in my [txtBox], a number. I want to evaluate if the typed value meets the condition or not. [SNum] is serially numbered and [RecID] is autonumber Primary Key. The above code is not working. How do I achieve this? Please help.
Evaluate Open And Closed Dates
Jun 23, 2006I have two date fields that I am using a form to show the open and close date of an investigation. The opened date field is controlled by a textbox elsewhere on the form, but the date closed is controlled by the vbCalendar control. What I am trying to do in coding is to evaluate whether or not the closed date is before the open date. If the date closed is before the date open I would like a message box to open that prevents the user from moving forward and reminding them that the date closed is earlier than the date open. Is it possible with the vbCalendar option being able to change its focus continually.
View 1 Replies View RelatedQueries :: Using Parameter Value To Evaluate Data?
Jan 23, 2014I have query with a parameter [Category]. Based on this value i wish to select team data like if [Category]="Sale" then (team) in (1,9,5), if [Category]="Purchase" then (team)="7" else (team) in (1,9,5,7).
Both the fields team and category are from same table called 'rawdata'
Report displays the data datewise but it is grouping according to team,which is not what i required;
Date CntValues
12/1/2013 1 [team 1]
12/1/2013 3 [team 9]
output should be
12/1/2013 4
that is to avoid duplicate date values.
Evaluate A Field In Access Database
Aug 28, 2014I am trying to evaluate a field in an access database. I have entered iif([fieldx]="Contributor",1,0). The field that I am evaluating has the word Member or Contributor in it, but my formula always returns a zero (0). What am I doing wrong?
View 14 Replies View RelatedQueries :: Evaluate How Many Orders Were Open On Specific Day
Dec 12, 2013Access 2010 project I am trying to develop for my workplace.
I want to be able to show a chart, a graph, of how many open orders we have had during the last month (showing per day) and during the last year (showing per months). In order to do this I am trying to create a set of queries that would provide me with such information in the form/structure;
[Date] [OpenOrdersTotal]
Yyyy-mm-dd 4
Yyyy-mm-dd 5
Yyyy-mm-dd 3
Yyyy-mm-dd 4
And after that I would create a chart I have
1. TblOrders
[OrderID] Autonum
[CaseID] Foreign primary key
[DepartmentID] Foreign primary key
[DateOrderMade] Date (yyyy-mm-dd)
[DateOrderAnswerd] date (yyyy-mm-dd)
2. TblDates
[ReportDate] Date (yyyy-mm-dd)
The evaluation in a query that runs all [ReportDate] logically would say;
Code:
Count(IIF([ReportDate] BETWEEN [DateOrderMade] AND [DATEORDERANSWERD]);1;0)
...but that wont do it.
I have attached a copy of the segment above ....
Queries :: Building Crosstab For Logic To Evaluate 2 Columns
Jul 17, 2013Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.
Objective:
If
A Fed has (1 or many) date submitted
AND
A St has (1 or many) date submitted
Condition is TRUE
How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.
General :: Make A Copy Of BE File Rather Than To Make A Copy Via Code
Nov 26, 2012Would it be ok just to make a copy of the BE file (every so often) rather than to make a copy via code?The user can then just paste over the original if it becomes corrupt.
View 4 Replies View RelatedQueries :: DLookup Command And Evaluate Return Value For Null To Be Used To Control Program Flow
Apr 6, 2015I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
General :: Sum Of Digits In A String
Jun 1, 2015make sum of digits from a string, something like if i have txt1 = "1234567890" to make txt2 = "1+2+3+4+ ...+0".
View 13 Replies View RelatedGeneral :: Error In Connection String
May 21, 2014I faced this error :
Run-time error '-2147467259(80004005)
The database has been placed in a state by user 'Admin' on machine "topleveldomain' that prevents it from being opened or locked.
in vba code :
I write such as :
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:My ProjectBEdatabase1.accdb;"
I have used the ms access 2013.
I also have split this database such as instruction by people but nothing effect.
General :: Count Amount Of String?
Jun 23, 2014In form (datasheet) I have a three columns "RightColors", "LeftColors" and "AmountOfColors"
I want to do something like this:
If I fill 'RightColors' "red;green;blue;", and fill 'LeftColors' "orange;" then in 'AmountOfColors' column should be "4". Sometimes I can fill only 'RighColors' or 'LeftColors;
I think I should add code in after update event. Is this possible to build code which can count colors using ";"? I need loop for this, right?
General :: How To Get Last Character Of The String In Query
Nov 5, 2012How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
General :: String To Return Textbox Sum
Jun 15, 2015I have a textbox on a subform and I want to get the sum in a string as follows.
Code:
Dim s1 As String
s1 = Sum(Forms!CountItem!CountItemLastCount.Form!ThisCount)
MsgBox s1
When I use the above I get an error message saying - Sub or function not defined and it highlights the sum part of the equation.
I have been using the sum criteria in a textbox but if the user doesn't tab of the box then it doesn't see it as being updated.
I have tried me.dirty and everything else that usually works like send keys tab event, requery form and controls, a left mouse click but nothing is working, therefore I thought that code to actually update the textbox may work.
General :: Removing Comma From String
Jun 19, 2015I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?
View 2 Replies View RelatedGeneral :: Undefined Function In Expression
Oct 28, 2014I now try to rework on an old project but when I try to run some queries I get an error message: Undefined function 'Date' in expression . The same happen for the Format function.Both are built in Access functions
View 5 Replies View Related