I'm having trouble converting text to proper text in Access 2010 using Windows 8.1 as OS.
The message I keep getting is:
The expression you entered contains invalid syntax. You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
The formula I use is:
strconv([field],3)
I'm trying to turn any period entered into a certain textfield on my form into a comma (to prevent access from turning 1.1 into 11 for example).
I have the on dirty event set to me.fieldname = replace(me.fieldname,".",",").
Everything works fine as long as I use the period key on the numpad. However, when I use the regular period key on the keyboard, it does not convert it.
Anyone know a good invalid character check script? I have two fields, userid and password, that I want to check for invalid scripts. Can't quited figure out how to script it.
Thanks
I have an old Table with Movies, Actress, Actor, & Director Fields & I have converted it to .accdb. I want to turn those short text fields to an ID number. I have built Tables for those fields(indexed no dups) with an ID field. I have over 5000 records in the original table and dont want to input those numbers by hand. I am using Access 2013.
View 5 Replies View RelatedI have a program that has been working for a year or so, it imports records form a text file created by a web site. Someone has entered a hex("1A", "A8" in a name field. It is shutting my program down. I tried a alphanumeric check to replace it and a length check to try and skip the record but the program thinks it is at the end of the file and quits reading records. Is there a way to get rid of characters like this in vba?
"5407 MUYF3I23 GIS..LE "
Here is what I have tried
Code:
If Len(strread) <> 591 Then
MsgBox "Invalid Record Read " & strread
GoTo Skip_Loop
End If
If AlphaNumeric(CStr(Trim(Mid(strread, 32, 11)))) Then
!HT_FName = CStr(Trim(Mid(strread, 32, 11)))
[Code] .....
I have two table
1. dbo.period (OpeningDate, ClosingDate)
2. dbo.data (blah blah, doc_date)
I want to create a view as follows
Select doc_date from dbo.data
where doc_date> 'select OpeningDate from dbo.period'
both doc_date and opening date have the same format
but the error will still appear as follows:
"Conversion failed when converting date and / or time from character string."
I'm just trying to implement a "customised error message" that will pop-up if data is entered and it's invalid. (I've created a "query" and a "search" form which these two link together).
View 3 Replies View RelatedHere is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies View RelatedI have a text box that has an iif statement in it and a referenct to a numerical value that is suppose to be a currency.
The table that is providing the source for the report show the value as $x,xxx.xx and if a create a text box with just it in the report it shows as $x,xxx.xx but when I put it into the iif statement it shows as $xxxx.xx with no comma.
iif statement example in txtbox:
Code:
=iif([Happy]=true,"I am happy with " & [value] & " in profits","I am sad with " & [value] & " in profits")
[value] is suppose to be like $1,111.11 but instead their is no comma showing $1111.11.
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then
Me.txtMaxOrdLimit.Visible = True
Else
Me.txtMaxOrdLimit.Visible = False
End If
I used the get external data tab and went thru the process. everything looked good in the preview but when I clicked finish I got type mismatch errors and the data that was supposed to be in field 1 was in fieild 2 and so on.
View 1 Replies View RelatedI have a single 'text' box on a form that captures test scores. The person inputting the test scores puts in all of the scores separated by commas (77, 85, 100, 75.5).. Up to 6 test scores can be placed in this single text box. The field is formatted for text. What I propose to do is take the numbers in the text box and average them out and then display the test scores average in a number field named 'avescores' next to this field in the form. Once the inputer puts the series of scores into the text box, they would hit an 'update' button next to this box to 'update' the 'avescores' box. Right now they use a number pad and add up the numbers manually then place the average into the avescores box....
View 11 Replies View RelatedWith microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I am trying to make sure that my double will have a comma for thousands, but will also be able to display more than 2 decimal places (probably 4).
I am struggling with what format to use - if the number doesn't have any decimal places, the point is still put in.
#,##0.####
Examples:
23.2 = 23.2
64587.255 = 64,587.255
5000 = 5,000.
I have a text string in the following format "00:09:10". Is there a function I could use that would convert it to HH:MM:SS?
I have been asked to calculate an average of time spent on a certain task and it is stored as text. Does anyone have suggestions on how this could be accomplished?
I am the junior of the ms access.
I try to convert the below text to date
(03/08/2015 19:42)
(31/07/2015 12:20)
CDate([XXXX])
return 3/8/2015 7:42:00 PM (should be 03Aug2015)
return 7/31/2015 12:20:00 PM (should be 31Jul2015)
I have some vba that dump an access table into a recordset and then into an excel spreadsheet. When the data is exported Columns I:Q (which are numbers) appear as text (with the green triangle in the upper left corner). I have a pivot table that refreshed however it can't do anything with text.
I've tried just to change the cells format and it doesn't work. The only way I've been able to change it to a number is to click on the green triangle and select "Convert to Number" How can I do this via VBA? The # of rows will change but the columns will always stay the same.
Hi all,
I would very much appreciate help with this one. So thanks in advance if anyone can help.
I have a table which has a Text Field (it has to be, I'm afraid). The data is listed as follows:
5.6%
12.23%
2.45%
etc.
I need to use these values to multiply other figures in queries. I have tried FORMAT and various other ways in the query to convert the data, but to no avail. All I get as a result is an ERROR. Anyone got any ideas?
Ginny
I have a date value in text format that is 5 character and want to convert it to a proper date format. Here is a sample of the data:
07301 actually represents 7/30/2011. How to actually convert that value to the date format of mm/dd/yyyy?
I've search the forums...If you can find a thread that discusses this, or can give me some advice on this topic, let me know.
Is there any built-in function that will allow me to delete any text before a certain point in a record... Or perhaps a way to use the REPLACE function to do so?
All of my records look like this (They're ticker symbols):
AMZN:Amazon, Inc
WMT:Wal-Mart Inc.
A:Agilen Tech Inc
MSFT:Microsoft Inc.
I just want the company name...Unfortunately, the ticker symbols aren't always the same length, so I can't use the left or right function.
I need to delete the colon and anything to the left of the colon. I've thought of a great use of the replace function for this purpose...I used wildcards...Too bad it didn't work!
Just The Company Name: Replace([Ticker Symbol and Name],"*" & ":","")
Any ideas?
I have a form to capture client information. I would like to have the the first character of the name fields (First and Last) be automatically capitalized. How can I set the field to do this?
View 3 Replies View RelatedHi all,
What can i put on a text box to limit the amount of characters entered like the 'field size' property for a table. I want to limit entry to 40 characters. Thanks in advance for any help,
M-.
like we do when we change our password, if characters are <4 or >10 then warning comes, I am making a password change button and want same thing.
I tried but could not locate any help for this. Can some body help?
Also, if put an If Text2.value = Null then
msgBox "Blank not allowed", vbcritical,"Error"
End If
While debugging I can see Null = Null but it does not give message box.
I tried everywhere in all text boxes of my db and found same thing.
I have a text box control in a form that is bound to a table field. The field is of numeric format that stores time duration in minutes. I would like to apply sort of transparent / automated conversion. It would work so follows:
- when someone read records, the text box would convert a integer number stored in the filed into text so that the text box would show the time duration in this form "HH:MM"
- when someone clicks on the text box to change the value, one would input the value in this form again "HH:MM" but the text would be converted to minutes and then stored in the database.
Hi,
A text box on my form automatically capitalizes the fist letter of a "weekday-word".
Like when I put in "sunday" this text box converts it automatically to "Sunday".
How can I avoid this?
i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below
Quote:
No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001
Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline
Quote:
1 100 null 2 145/Mechanical/0800 Mechanical 3 120/electrical/1620 electrical 4 131/mechanical/0200/dw-001 mechanical