Character limit on a textbox

[newguy86]

I have a text field in a table that logs the date/time that a record was created. I am building a form where I only need to display the date portion of the field but I cannot find anything that will allow me to do that easily.

For example, here is what the text in the field looks like:

1/6/2010 9:18:56 AM

This is what I want to display on the form:

1/6/2010

I figured that I would just have to write something similar to a Len function in the Format area of the fields properties but nothing jumps out at me in the help section.

Any help you guys can provide would be greatly appreciated.

Travis

http://www.enterprise.com

 

[genomon]

Have a look at the Format function in help. Format(yourfield, "m/d/yyyy") comes to mind...

Ni neart go cur le cheile.

 

[Andrzejek]

How about:

Dim str As String
str = "1/6/2010 9:18:56 AM"
MsgBox Format(str, "M/D/YYYY")

Have fun.

---- Andy

 

[traingamer]

How about

Format(YourString, "mm/dd/yyyy")

?


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

(I guess I should hit 'refresh' before I hit submit.)

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[genomon]

Hey traingamer that looks awfully familiar. Great minds think alike

Ni neart go cur le cheile.

 

[newguy86]

Oddly enough, when I put that into the Format field it keeps resetting it to something like this:

"For"m"at("y"ourfiel"d", m/d/yyyy)"

Any suggestions?

Travis

http://www.enterprise.com

 

[genomon]

Yes. "yourfield" is the name of a field bound to your form or report, and may need to be enclosed in square brackets [] if you are not programming an event in VBA. There is no "format field" in a text box, just a property. You can also put it in the control source as =Format([fieldname],"m/d/yyyy"). If you put it in the property sheet, simply enter m/d/yyyy or choose "short date" from the dropdown.

Ni neart go cur le cheile.

 

[newguy86]

That's what I had done when I was getting that result.

Keep in mind guys that this is a text field not a date/time field.

Travis

http://www.enterprise.com

 

[dhookom]

You could convert to a date and then set the format property or just set the control source to:

=Left([WhyCantYouProvideAName],Instr([WhyCantYouProvideAName]," ")

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

dhookom,
I considered converting to a date but with the number of records there are already in the database it would be a huge undertaking to do that. So it didn't seem like it would be worth it.

Also I tired your other suggestion and it gives me an error stating "The expression you entered is missing a closing parenthesis, bracket, or vertical bar." I tried adding a closing parenthesis at the end of your code and whenever I view the field on the form it displays "#Error".

=Left([DateReceived],InStr([DateReceived],"10"))

Travis

http://www.enterprise.com

 

[Ray1127]

Not exactly sure what your doing but Instr takes 3 parameters.

InStr(1,[DateReceived]," ")

The first parameter is the start point or the 1st letter, The second parameter is the string to be searched and the third parameter is the string searched for. The Result is the position of that string.

For example:
Instr(1,"This is a string", "is a") Results in
6

Instr(1,This is a string","is") Results in 3 In your case it would be Left([DateReceived],Instr(1,[DateReceived]," ")-1)

Where Date Received = "01/01/2010 5:45 PM" Then the Result of the Instr would be 11 so you would minus 1 to get the leftmost 10 or 01/01/2010

Where Date Received = "1/1/2010 5:45 PM" The result of the instring would be 9 minus 1 gives 1/1/2010

 

[dhookom]

This should work

=Left([DateReceived],Instr([DateReceived]," "))

You don't have to put a starting value with Instr() but this should also work.

=Left([DateReceived],Instr(1,[DateReceived]," "))

Also, if you are displaying this in a text box, the name of the text box can't be the same as the name of a field.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

a form where I only need to display the date portion...

but with the number of records there are already in the database it would be a huge undertaking

It would not be any undertaking. You are not converting raw data, you are displaying data in a text box. The only undertaking is to add the code to the text box. Leave the table alone.

Ni neart go cur le cheile.

 

[WizDumb]

yourtextbox = format(cdate([yourfield]),"Short Date")

Solution tested.

 

[newguy86]

Thank you guys for the help! The field is now displaying exactly as I need it.

Here is my final result:

=Left([Text206],InStr(1,[Text206]," "))

dhookom,
I read what you wrote about the field names and it dawned on me that I was using the field name in the table instead of the field name on the form which was causing some of my problems.

Travis

http://www.enterprise.com