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.
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?
Hi,
I have a field in my database which captures either single digit numbers or comments in text format. I want to be able to count the numbers but obviously I've had to use a memo field in order to capture both numbers and text.
The only way I can think of is to take the field and look for single character responses, then convert these into a number field so that it can be counted.
Does anyone have any idea how to do this?
Thanks.
I am trying to convert an excel formula into the expression builder on my form.
the excel formula is this:
IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),
[Code] ....
In the expression builder I put this:
= IF(AND( [Objective Total] >=2.65, [KSF Totals] <1.85),"Poor",IF(AND( [Objective Total] <2.65, [KSF Totals] <1.85),"Unacceptable",IF(AND( [Objective Total] >=2.65, [KSF Totals] >=1.85, [KSF Totals] <2.65),"Generally",IF(AND( [Objective Total] <2.65, [Objective Total] >=1.85, [KSF Totals] <2.65, [KSF Totals] >=1.85),"Poor",
[Code] ....
It gives me an error 'expression you entered contains invalid syntax, you may have entered a comma without a preceding value.'
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))
When I click on Form View, I get "#Name?" in the calculated field.
Any ideas? Thanks.
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))
When I click on Form View, I get "#Name?" in the calculated field.
Any ideas? Thanks.
I know how to do this in VB.NET ... I have a form with a combo box. I would like to create an "on change" event where it updates the text boxes on that same form depending on what's been selected in the combo box.
I don't know how to change the combo box's selected index so it defaults to the first item so there's no blanks/errors in my form.
So when the combobox contents is changed, I'd like a routine that updates all the text boxes kind of like this:
sub update()
TextBox = "SELECT * FROM Table_Name WHERE ID =" & Combobox.text
end sub
what's wrong with this iif statement?
=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 connecting acces to oracle servers via ODBC .
In the table there is a date time stamp - format data type text:
20130225060621
I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.
this is what I am currently using (which is OK) BUT I need to calculate against the time also.
Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))
I spoke to an IT guy who works in TOAD program and he gave me this SQL:
select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';
this is the format I would like but cant make this work.
Using Access 2010 and ODBC connections to pull data from 3 sources (SQL Server 2008, a customized Documentum application, and Windows AD accounts). Problem is user_login_name is sometimes recorded as lowercase, sometimes as propercase, and sometimes as uppercase depending on which table or source being used. For example, lowercase would be "abcd123", propercase would be "Abcd123", and uppercase would be "ABCD123". I thought I could just wrap each join comparison in the From stmt in the query(s) with the UCase() function, but Access doesn't like that.
How can I convert or cast the various user_login_names within the FROM join statement to be the same text case so joins will pull correct data?
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
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
What the easiest way to turn text to an integer - ir have vaiable defined as long and an inputbox - want to keep asking for an input until I get an integer.
Have IsNumeric - can this be applied to int.
Or could I use localised error handling?
Thanks
Iff(fico>600,1,0) as g,
I found fico is a string in access table. so the above does not work.
How to fix this problem
Thank you very much.
When I enter a date into the [Return Text Date] field on my table and then select a value from the [Upgrade Fee] combo box followed by a selection from the [Upgrade Investment Class] combo box i want the value (or Date) in my Eligibility Date field to be amended by the given amount.
I have an expression, listed below, that i want to repeat (copy + Paste would be good) and amend the values. I have entered 2 and that seems to be all it will allow. I want to have 30 possible permutations to the fields 0 - 300 (in 50 intervals) by 5 (1,2,3,4 & 5)
The Code works below but I cant have anymore code in the expression builder
IDEAS please people.......
=IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=1,[Return Text Date]+180,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=2,[Return Text Date]+150,""))IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=3,[Return Text Date]+120,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=4,[Return Text Date]+90,""))
Hi!
I need some help.
I want to create a formula (using expression builder or something else) who will separate the word id=276616652 lang=FR temp=80 in a new column (for each word)
Id=276616652&Lang=FR&Client=Diesel649&Temp=80
by the way, im using this in a log database
I've built a query against a form and included a calculation to give me the average sum which looks like this :
=Sum([data field])/31*12.5
This work fine however, what is the best way to overcome the different number of days in the month ie; 28, 30 and 31? I've looked at the Expression Builder but getting lost :rolleyes:
Regards
Richard
Hi all,
i have a field that uses Date/Time as the data type and what i want to do is to create records that only have the time between the hours of 9:00am and 9:15pm.... i think i need to use the expression builder for this, but im not really sure on the expression to use.. any ideas ???:confused:
Hi,
I am fairly new to Microsoft Access and can't get to grips with the expression builder. I have several fields where I need to add validation rules and no matter what I try I can't seem to get it right. E.g I'm trying to set a validatio rule for only allowing locations into a certain field. Could someone please give me some help or point me in the direction of a good guide?
Need to use CAST to return integer value of string (digits as data type string).
Where clause looks like this:
... Where Cast([Price File] as int) > 0
works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?
Hi, i am making a stock control program in Access. I have designed several tables, forms and queries etc. but have come stuck at the point where i want to create a button so when i click it, it automatically changes the stock level of all the products that are low in stock, to their "target stock level".
I am confused about how to do this. I tried making a button and going into the expression builder and putting:
=[Product Stock Level]=([Product Target Stock]-[Product Stock Level])+[Product Stock Level]
This would basically change the stock level to the target stock level. But when i press the button, nothing happens.
Any help on this matter is greatly appreciated.
Thanks alot.
Im looking for a guide to teach myself how to use the expression builder.
I know how to write constraints in SQL, so with a bit of luck it will be similar. :P
A list of every command you can use might be just as good as a tutorial.
Can anyone point me in the right direction?
Thanks.
Hi,
I am trying to write an expression in my query which, given a condition, it uses the following criteria:
"Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea"
So far I have come up with this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea")
However the Query builder complains the expression is incorrect or too complex etc. etc...
If I simplify it to this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire")
it works fine but unfortunately this isn't what I need.
Any help much appreciated,
Matt.
Hi all,
Should you write an expression the criteria field in a query and use IIF, an interesting little bug appears that I have not managed to get around. Should you want the expression to return a range as the criteria (i.e. Between ... And ... ) it can't do it because IIF tries to evaluate the range before it passes the result back to the query. I need it to actually pass the Between And to the query to use as it's criteria. It can't do this, so it passes back a null and of course the query fails to produce any meaningful result.
I need to do this as I have two date text boxes in a form so that people can search through specific dates, but in the table these date fields can be blank. (Basically it's a Date Completed field, so if the job hasn't been completed, the date is left as Null) So i need users to be able to leave both text boxes blank so that the resulting query will display the Null values as well. If they leave one blank, it auto sets itself to the a minimum/maximum date. When both boxes are blank i need to return nothing so the criteria is effectively blank or = Is Null And Is Not Null to display all records.
Example of WIP
IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True And IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,#15/01/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True,Between #01/01/2007# And #01/04/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,Between [Forms]![frmListImgChng]![txtDateComp2] And #31/12/2030#,Between [Forms]![frmListImgChng]![txtDateComp1] And [Forms]![frmListImgChng]![txtDateComp2])))
The red bits of code are test values. The first one passes back to the query as it is not a range, the second does not as it is.
Cheers,
Matt
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this:IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model])this is not working. it is still returning the query blank. if i put valid critera in the dropdown it comes back Fine.how do i make is so if a field is blank it will return it as null or not even there.Note: also tried this;Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Nullworks, But comes back as too complex after a few searches and when i open the query there is a million or's in there. so that wont work.
View 3 Replies View RelatedNow I have been working for a few months with ACCESS but one thing that keeps giving me trouble and that is the "Expression Builder". I look at the Microsoft Help and it is not good in explaining this part of ACCESS. I have Access books and they are not good with explaining this aspect of Acess. Is there anyone with a good source to point me in the direction to learning the Expression Builder better then I know now?
View 1 Replies View Related