Custom Formating Of Dates

Sep 16, 2005

Hi

I am looking for a way to format a date in a textfield troughtout my application. I created a module containing a Function that receive a Date as parameter and returns a string. I put : =formatCustomDate([dbColumnName]) in the source field. Everything works fine when the date is not null. However if the date in the DB is null, empty, etc. The function is not even executed and I get a #Error in the field.

Why is the function not executed? Is there another way to do that? The reason I want to do that is because I want to avoid using the formating embeded in Access. I need the Function to return mm/dd/yyyy (00/00/0000).

Thanks

Jonathan

Here is the code I used to do that:

'************************************************* ********
' Function formatCustomDate(dbDate as String) As String
'************************************************* ********

Public Function formatCustomDate(dbDate As String) As String

'MsgBox dbDate

On Error GoTo err_Wrong_Date_Type

Dim formatedDate As String

If IsNull(dbDate) Or IsEmpty(dbDate) Or dbDate = CStr("") Then
formatedDate = Empty
Else
formatedDate = Format(dbDate, "mm/dd/yyyy")
End If

'MsgBox formatedDate

'MsgBox formatedDate
formatCustomDate = formatedDate

err_Wrong_Date_Type:
If Err.Number <> 0 Then
formatedDate = Empty
formatCustomDate = formatedDate
'MsgBox formatedDate
'MsgBox Err.Number & ":" & Err.Description
Else
formatedDate = Format(dbDate, "mm/dd/yyyy")
'MsgBox formatedDate
formatCustomDate = formatedDate
End If

End Function

View Replies


ADVERTISEMENT

Formating.

Sep 27, 2005

Hello everyone.
I have a multi user database that is used across 2 sites. In one of the sites the regional settings for 'short date format' is set to dd/mm/yy - the other site its set to dd/mm/yyyy. All the data that its the tables are set to dd/mm/yy. When the second site try to use the reports and queries they permanently error due to the fact that the date look up box is setting its self to dd/mm/yyyy as per its short date format. The IT manager there refuses to change this as its - and I quote 'its not y2k compliant !!!! :mad: :eek: :confused: - anyway. I cant change the data in the table to yyyy as its a text feed and Im not in control of that.

I planned on using a hidden text box to format the date to dd/mm/yy and hence bypass the regional settings, but this queries arent recognising the new foramt and are returning null and blank tables. Im now completely lost and stuck. Can anyone help ??

View 7 Replies View Related

Conditional Formating Help Please

Apr 17, 2006

Good morning, all...

I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.

Could someone please get me started with the code to accomplish this?

Thanx in advance for your assistance.
Karen :)

View 2 Replies View Related

Conditional Formating

Jul 13, 2005

Trying to setup a conditional format for a field on a form based on conditions from another field.

I have a form with [Field1] and [Field2]

I have a 3rd field called [New SMF]

What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.

How can I do this?

Jon

View 4 Replies View Related

Number Formating

Apr 25, 2006

Hi All,

I'm new and have a question:

I would like to display the following number 2,550,567 as 2,550 (K)

Does anyone know how to apply this custom format

Thanks in advance

Twin:)

View 3 Replies View Related

Formating Number

Mar 9, 2008

Have searched for a while, but could not find an answer. Probably is very simple.

I have a DB with orders, in order to create an ordernumber i count the orders made this year, get a result and want that result to be 3 digits (eg counted 3 should be "003")
Here is my code, but wont get what i want.

strDatumNu = Format(Now(), "yy")
lngVolgnummer = DCount("[Jaartal]", "tblLevOrder", "[Jaartal] = '" & strDatumNu & "'")
lngVolgnummer = FormatNumber(lngVolgnummer, "000")

If i put "&&&" or "___" instead of "000" i get an type mismatch error

If i have 3 records, it always returns me 3 instead of 003.

Any help?
Thanks, Tom

View 1 Replies View Related

Re Help Me About Date Formating

Sep 28, 2006

hello

i am using a 2 tables where in one table my startdate format is general.
where as in another table date format is shortdate

now i want to compare 2 dates in select query

my query is

rsBreakDown.Open "select Breakdown.*,EquipmentMaster.McName from BreakDown inner join EquipmentMaster on reakdown.McNo=EquipmentMaster.McNo where startdate <=#" & dtpshow.value", conn, adOpenKeyset, adLockOptimistic

here startdate is in general format n dtpshow is in shortdate format

i want to compare these 2 dates

how can i?
is anybody help me?

View 2 Replies View Related

SQL If Statement For Formating

May 17, 2007

Can you do an SQL IIF Statement for formating? I am trying to Bold a value only if the value is greater than a value in another column.

View 3 Replies View Related

Formating Date

Feb 1, 2005

Hi...I am new to access...not sure if we can do that...
is there any way we can enter the date in access this way..

if we enter 01012004..the slash is added automatically..i.e the date becomes 01/01/2004.

It would be great if anyone could help me out..

Regards
Rahul

View 3 Replies View Related

Hel P On Formating A Form Please?

Dec 5, 2005

at the minute i have a form displaying all my customers in one list, i would like to display one customers details one at a time. i was to do this so i can search through my customers etc.

there is something in the properties of the form i need to change so it only displays one customer details per form. can anyone help me?

View 3 Replies View Related

I Need Help With Formating Numbers

May 25, 2006

Hi: I am new at this so forgive the crudity of this code.
I have a form I am trying to do calculations on I am using code to do this as follows.

Private Sub Text108_AfterUpdate()

Dim east As Single
Dim Center As Single
Dim west As Single
Dim Ans As Single

east = Text104
Center = Text106
west = Text108
Ans = east + Center + west

Text110 = Ans / 3




End Sub

Example
East=24.0
Center=25.0
West=25.3

Ans=74.3

Text110=24.76666

I want Text110 to show 24.7

View 1 Replies View Related

Formating Case

Nov 1, 2005

I have a linked table that updates regularly. It stores all words in Upper Case. I would like to mail merge out of a query but I would like to format the words so that the first letter is Upper Case and the rest of the letters are Lower Case. I have tried formating the field in the query but this doen't work.

Any way to do this?

View 7 Replies View Related

String Formating Question

Nov 15, 2006

I have a comment that I pulled with a dlookup from a table. Example below

Interface request received and filed (nk-11/14/2006) Test file sent to vendor. (nk-11/15/2006).

I want to seperate the data based on date. Example below

11/14/2006 Interface request received and filed
11/15/2006 Test file sent to vendor.

Is there an easy way to create a loop to walk through each character and format the original data to the formatted data?

thank you

View 3 Replies View Related

Conditional Formating Of Query

Jul 7, 2006

HI
I am working on a database that holds communications from treatment episodes.
the sub form holds the communications related to that client and that treatment episode.
when you enter a date into the communication record a mesage box asks if this is a new treatment episode and if yes opens a new treatment record. If it is the same treatment episode it just remind you that you are in the same treatment episode.
what i would like to do as an additional cue to the user is that once a client has been discharged from the episode that all communication records change color.
I can get the field that says dc to change color but I want all the fields in all the records from that episode to change color.
any ideas?
thanks

View 1 Replies View Related

Listbox Formating, See Attachement

Mar 17, 2005

Ok, still some visual work to do on this screen, but what i want to do is format the 2nd and 3rd colum as currency. As you can see in the attachement it currentley doesn't show cents. Any help or suggestions how i can correct this would be appreciated.

View 5 Replies View Related

Formating A Subform For Printing

Dec 12, 2006

I have a database that has a form with 3 subforms. Once a user has entered date, there is a command button to print the current record. The problem is, the 3 subforms have a very thick black boarder around them. Is there anyway to remove the thick boarder? Any help would be appreciated.

View 1 Replies View Related

Access Time Formating

Oct 7, 2004

I am constructing a databse for Time and Attendance and wish to have a cumulative total column for the hours worked in the month, the Time in/out, lunch and TotalHours fields are using the ShortTime format but when the total hours exceed 23:59, obviously the field will not accept this Data, any ideas how i can record the TotalHours worked in a month.
Many thanks in advance for any suggestions.

View 3 Replies View Related

Formating Of Text Boxes

Jan 4, 2006

Two questions, which I hope are pretty much straight forward and interlinked.

The first - The below VB is used to populate a text box, taking data from several other fields. Currently, the way I have written the VB takes it right across the VB page. Is there a way I can break it down into sections just to make it easier to work with.

Me.CommentLog = "Appointment held with client - see original log dated " & [Forms]![TblLog4AppointmentUpdate]![DateLog] & ". Outcome of appointment: " & [Forms]![TblLog4AppointmentUpdate]![CommentKept]

Secondly, - commentlog is a memo text box. Is there a way I can get it to populate the memo box again in sections, rather than as single string.

So instead of reading

Appointment held with client - see original log dated 20/01/2005. Outcome of appointment: appointment kept.

It infact reads

Appointment held with client - see original log dated 20/01/2005.

Outcome of appointment: appointment kept.

View 5 Replies View Related

Microsoft Access And Date Formating

Mar 13, 2006

Hi,

I am having trouble with the format of dates comming from access, if I view a table in access with a field of Date/Time the date is formatted correctly as dd/mm/yyyy, but when I retreve the date from my ASP page the format changed to mm/dd/yyyy. I can successfully change the format using a date format function but is there a way of getting access to send the date in the correct format in the first place or know why it's doing this. I am using access 2003 and the following connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:webSites estWebdataBasefcd_db_080306. mdb"

Thanks for any help and guidance you can give

Joolz

View 1 Replies View Related

Expression In Query Not Formating Correctly

Sep 26, 2006

Hi all,

I have a query that I have an expression in that is returning a text value instead of a number value. The field in my table is a number value but when I use the expression it returns it as text. I have done several searched but can't seem to find anything that matches formatting an expression as a number. Here is the expression I am using, the reason I am using it is because I need to return a zero if no records matched the criteria.

total: Nz(Sum([act_hours]),0)

Thanks

View 3 Replies View Related

Formating Number Within Text String

Oct 6, 2006

looked around but could not find the answer.
i have a text box that might or might not contain text and number data. an example of the data in the text box might be;

bob roberts, 17/03/2006, aged 23

what i would like is some VB code that would:

check anywhere in the text box for number characters that are 8 characters in length with a comma at the end [12345678,] as in the first example. and change this to 12/34/5678,

bob roberts, 17032006, aged 23
would become 'on lost focus'
bob roberts, 17/03/2006, aged 23

anybody any idea how to do this?

thanks

View 2 Replies View Related

Conditional Formating On Continous Form

Sep 24, 2004

Help

I am trying to change the color to display when the order
close to red.whilst leaving the open records as default.
Iam attempting this on a continous form.
Currently operating access 2000

Many thanks

desperate

View 1 Replies View Related

Formating Local Date Problem,

Dec 1, 2004

Hi helpers,
I need to have a specialized field for my voucher's local date which is totaly based on different calendar and formats with the system's. (English)
So I created a feild in my table, as text data, and gave it a YYYY/MM/DD input mask in forms, but this field in many places like combo boxes, and reports controls is shown as a number, such as 31940. pls note that there is no similar calendar format in Windows system for my purpose.
How can I avoid this, I mean I need this text (my local calendar date) to be shown as it is and entered. I dont need this feild for calculations, so text is appropriate.
thanks in advance.

View 1 Replies View Related

Formating Calculated Fields - SQL & List Box

Jan 6, 2005

Hi,

Is there a easy way of formating/rounding a number returned to a ListBox by a SQL_Query?

I have a List box in a Report that gets it's data from a SQL Query
via ListBox.RowRource = SQL_Statement

this SQL_Statement cointains two calculated fields that returns a number (float)
In the report I get the numbers as float - that is in exponential view (0.29143256234E10) etc
but this makes it difficoult to read.
I have tried multiple solutions and searched the net but still am stuck (exept doing it in a CPU
consuming way).

Is there a easy way of formating/rounding a number returned to a ListBox by a SQL_Query?
I want a result that have a maximum of 3 digits after "zero".

Thx

View 4 Replies View Related

Expression Builder Or Conditional Formating

May 30, 2005

Hi, can anyone help me? I'm using an access database to collect information and pull reports. One of the fields in one of the tables contains dates and time. In my query I would like to be able to state that records created after 17:00 and before 08:00 of the next day, get counted in next day reports. (i.e. Record 1 created 26.05.05 at 18:01 would show in my report as coming in on 27.05.05 at 08:00 am). Thanks,

View 5 Replies View Related

Date Formating In A Label Print

Feb 27, 2007

I am new to Access and I have just imported my old ham radio logs from a DOS based Smart database - rememeber that one.

I have created a reasonable functioning table. I have also created a Query to sort certain records for which I want to print labels to affix to my radio card which I wish to send to other radio stations (called QSL cards). I have created a Report to run the Query and to print a label. Here is my label. It is a mixture of text strings and data from my log database Query.


To Radio 9U9Z QSL QSO
dd/mm/yy UTC Freq Mode RST
24-Feb-07 18:44:00 18.148 ssb 55
73's de Mark N1XX (WAB 11XXX)


Here is the code for the date time etc line - the 3rd line down on the label above - taken from the design view


=Trim([Date] & " " & [Time_UTC] & " " & [Frequency] & " " & [Mode] & " " & [Rpt_Sent])



I am not sure how to format the date to print just the hours and minutes. I would have hoped that since the main log table was using a short time that it would have folowed automatically. In fact the time was printing as 12 hour time and I had to go into my Windows XP preferences and change the pc to a 24 hr clock to get it to print as a 24 hour clock. I tried setting the pc to an hrs and minutes time but it didn't fix my label format. It still shows seconds. I would like to get label to print 24hr hours and minutes and be able to set my pc back to a 12 hour clock format.

Thanks for your help. I still have loads to learn with this database program.



thanks Mark

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved