Changing Date To Text
Jun 30, 2005
I have searched and found references to converting text to dates. I need to go the other way. I have client# field and a date field. I would like to make an update query to populate a new field by concatenating the two fields minus the "/". I am making a new key field in an existing table. Then in the future when data entry is done, I would like to convert the date and client# as they are entered. I was looking at using the left$ or right$ function, but the dates may be in mm/dd/yyyy format or m/dd/yyyy format or m/d/yyyy and so on, depending on the month and day. Is there an easy way to convert the date to a number? I tried datevalue, but it leaves the "/" in there. I'm not sure how to section off the parts of the date I need, when the month and day vary between 1 and 2 digits. Thanks for any help.
View Replies
ADVERTISEMENT
Dec 9, 2006
Hiya All
Hope you can help me here
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it.
The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
Many Thanks for Looking
Paul
View 2 Replies
View Related
Nov 27, 2007
Hello all,
I'm using M$ Access 2002. I have a query which contains an expr as one of the fields. The expression adds three text fields together to create a salutation. This is the expression:
Expr1: [students].[Fname] & " " & [students].[LNAME] & " " & [students].[gradelvl]
Where all three fields are of the Text data type. I am trying to display the students grades for next year. So if the value of [students].[gradelvl] is "10", it should display "11". If it were a Number data type I think this would be easier for me to figure out.
Any ideas?
Thanks for your help,
cheers maX
View 2 Replies
View Related
Dec 21, 2004
This is very close to the label question that I also have posted. I am trying to get my report to have the back ground of some text boxes turn yellow if they are populated. I can get them to turn yellow, but they turn yellow whether they are populated or not. I have the code in the Report Open event. Here is some of the code that I have.
If Not IsNull(MMSJob) Then
MMSJob.BackColor = 65535
Else
MMSJob.BackColor = vbWhite
End If
Can anyone help me?
learnasugo
View 7 Replies
View Related
Nov 25, 2005
Hello,
in my Access database there is quite a bit of VBA code.
I have set my LAN and file path in some of this code but will need to change it when this database will get to other users using a different LAN and path.
Is there a way I can avoid them to open the code and manually change this information?
I was thinking of some sort of text box where they will write their path (ex. \S01000sharedoffice*.*) and use a cmdbutton to overwrite the existing path?
Thanks.
View 2 Replies
View Related
Oct 2, 2005
Not sure if this is possible but I have a few text boxes on a report set to vertical yes under properties. They print fine, but on the pre-printed forms I'm using they print upside down. Is there a way to realign they way the text prints when using the vertical yes setting? I really need they text to be inverted. I'm I asking an impossible question? Thanks..
View 1 Replies
View Related
Sep 1, 2006
Hi
Is there a way of changing the case of text that has been entered in a table?
Want a form to display capitalised names, say, no matter how they were entered. Know that they can be displayed in all caps using >.
Thanks
View 6 Replies
View Related
Nov 23, 2014
I am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function
View 5 Replies
View Related
Jul 10, 2007
Hi all,
I am trying to have the rowsource change on a "subcategory" combo box depending on what the "category" combo box says.
I added an event procedure to the "Category" combo box which changes the Row Source in the subcategory combo box to something else. I'm not sure that I did this right as if I close the database and reopen it, what I selected in the subcategory combobox is gone and it is blank. Any ideas? Here is my simple, likely incorrect event procedure:
Private Sub Category_Change()
If Category.Text = "Rare Books" Then
SubCat.RowSource = "scRareBooks"
End If
If Category.Text = "Legal Documents" Then
SubCat.RowSource = "scLegalDocuments"
End If
End Sub
Thanks!
View 5 Replies
View Related
Feb 15, 2008
I am using a combo box to enter Employees names for training hours...I want to use a pivotchart but when i use the training hours table/form the pivotchart gives me the combo box numbers instead of the names. I know there is a formula or something I am suppose to use here or even a query but I don't have a clue as to how to do this....if someone would be so kind in directing me with instructions and help...thanks
:confused:
View 4 Replies
View Related
Nov 23, 2005
Hi, im working on my school project for A2.
When the user clicks on say; the customer ID dropdown menu, i want the label text on the right of it to get darker (make it more visible). How can i do this?
(i have attached a picture of my form)
thanks, Robin
View 2 Replies
View Related
Jun 30, 2006
Hi all,
I'd like to change the colour of the text in a command button when the user click the button, but for the text to return to it's original colour when the botton pops back up.
I'm assuming you use the following commands:
Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
*******CODE********
End Sub
Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
*******CODE********
End Sub
But I'm not sure as to how I would enter the correct code.
Any help would be appreciated,
Best Regards,
Aaron
View 2 Replies
View Related
Jan 18, 2005
I have about 25 text boxes on my form that use the dcount or count functions to obtain a number. (Text boxes are labeled 'Text1' through 'Text25')
When the value of the textbox is 0, I would like to have the color of the text box turn red.
Is there a way I can do this using a for loop? Or a with statement?
Checking each one individually just seems like poor programming.
Any thoughts? Ideas?
Thank you.
View 2 Replies
View Related
May 7, 2014
I was trying to see if there is a way to convert text to a hyperlink in a query.
Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.
One of my columns in the query looks like this:
Code:
Notes Link: "notes://pncpgha66/852570CE0056FEF3/0BFCC83A5C11D7C3852576CD0072ED3E/" & [NoteUNID]
I don't see a way to convert that whole part to a hyperlink though. Like I could with a date and text. Cdate().
View 1 Replies
View Related
Nov 24, 2012
I want to change text in txt boxes. when a new customer is being created we are too lazy to put uppercase in. so i want to change the text so the first letter is uppercase. i have used this on every event and it doesn't change a thing.
PHP Code:
Private Sub txtTown_AfterUpdate()LResult = StrConv("TECH ON THE NET", 3)End Sub
Private Sub txtPostcode_AfterUpdate()LResult = StrConv([txtPostcode], 1)End Sub
Private Sub txtStreet_AfterUpdate()LResult = StrConv([txtStreet], 3)End Sub
Private Sub txtFirstName_BeforeUpdate()LResult = StrConv([txtFirstName], 3)End SubÂ
I have this at the top
PHP Code : Option Compare DatabaseOption ExplicitDim LResult As StringDim LResponse As IntegerÂ
View 6 Replies
View Related
Aug 14, 2015
I have three labels that I can't change the text color. I must have them locked or something. What should I check?
View 5 Replies
View Related
Mar 29, 2008
Hi,
Probably this question has been asked before, but I'm new here. Don't know if the question should be asked here and hope that my English is good enough.
I have a database in which I change the content of the records on a regular base. There are two dates in it. One is the day I made/added a new record, one should be the date I made the last change, but I can't get it to work. I have been searching the Help-option in Access as well as Google to find an answer, but so far no results. I don't know anything about Visual Basic, so if you answer please keep it simple.:o
Taco
View 6 Replies
View Related
Jun 8, 2005
I have this code that a member helped me create in one of my forms. However, the date that is being input into the table is 30 Dec 99. It won't change no matter what the code is changed to.
The date is showing up correctly in the subform, it is just not inputting right into the table.
Private Sub Form_Open(Cancel As Integer)
Dim intNQ
intNQ = Nz(DMax("QuizNo", "tblQuizzes", "logon='" & Environ("Username") & "'"), 0) + 1
Me.subrmQuizzes.Form.Logon = Environ("Username")
Me.subrmQuizzes.Form.DATEControl = Format(Now(), "DD MMM YYYY")
Me.subrmQuizzes.Form.QuizNo = intNQ
CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Format(Now(), "DD/MM/YYYY") & " , '" & Environ("Username") & "'"
End Sub
Any ideas?
View 10 Replies
View Related
Jan 9, 2007
I have a table populated with several thousand records-one field is a date field in the format of 5/5/2006. I have to change the data so that it is 5/05/2006 in order to import it into a SQL table.
I'm sure there has to be an easy way to do it, but I can't come up with it. Changing the imput mask on the field affects future data input but does not change what is currently there.
Any ideas or suggestions?
Thanks!
View 2 Replies
View Related
Dec 18, 2006
I have this problem, I have a field that updates by using the now() function. The problem is when I run a date query I have a problem due to the long date format. Is there a way to change all the records with a Long date format to a short date one?
Here is an example of what im working with:
Long Date: Januar 1, 2007 2:00 PM
Short Date: January 1, 2007
When I run a query I only get the records with the short date even though I have records with the Long Date format don't show up.
View 2 Replies
View Related
Jan 9, 2014
I have a box with age in called [agecalc]. I want to change the border of the box to red to indicate they are a Juvenile if under the age of 17.I already have some code to write the text, I just need to change the border of this text box called agebox
=IIf([agecalc]<=17,"Juvenile!!")
View 1 Replies
View Related
Sep 16, 2013
i have a database with a large number of records. Navigating through the records through a form one by one is a pain, so i want to create a text box where you can enter the ID number of the record, and whichever record has the matching ID number it changes to that specific record. I know that something like this is already apparent at the bottom of Access, but i want my user to do everything within the database itself, as all of the panes are removed whilst being used
View 11 Replies
View Related
Apr 7, 2014
I am trying to add a percentage sign (%) after a number I get from a table, thus the number 1,5 should display 1,5% and NOT 150%.
I am using the Format Property of the text field.
I tried #,##% which converted the value to a percentage (1,5 became 150%) Then I tried #,##"%" and #,##\% Access ignored both and changed the value in the property field to #,##% Using the Format function is not an option because it is a bound field which should be editable.
View 3 Replies
View Related
Jul 9, 2007
Hi Guys me again!!
After spending the last few hours searching the forum and net for an answer I have finally conceded again that I need advice
I have a field in a table called DOB (date of birth) this is currently set as a Date / Time field
It did orignally have an input mask for short date i.e 11/11/2011 but i have removed this for now.
On my form (Patients) there is also some code in the after update properties box of DOB to calculate a persons age which is entered into another text field on the form.
What I need is a customised message box if the user enters the date incorrectly i.e 11/11/11 instead of 11/11/2011 alternativley is there a way to change what the user puts in i.e 11/11/11 to 11/11/2011 automatically after udating but keeping the code to calculate the age?
Hope this isn't to confusing...
Thanks in anticipation
Paul
View 7 Replies
View Related
Jul 27, 2005
I am working on a french version of Access but in my english forms, I need the current date field to be written in english - is there a way/expression to change the language of the date field on a form (from french to english)?
View 1 Replies
View Related
Nov 13, 2006
Hi All,
Is it possible to change the format of a date picker, for instance once i have selected a date, its is visible as 09/11/2006, can this be edited to just the month and the year 11/2006.
View 3 Replies
View Related
