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.
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
I need to format a worksheet in excel after I transfer data to it. I createobject("excel.application") to open an excel spreadsheet. My problem is I need to freeze the first three rows, i.e when you scroll down the spreadsheet the first 3 rows always stay in view. The only way I have seen to do this is use the windows menu form the excel application. I am creating this excel spreadsheet each time a report is run. Does anyone know how to this is VBA??
Thanks
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies View RelatedHello 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 ??
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 :)
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
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:)
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
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?
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 RelatedHi...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
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?
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
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?
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
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
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
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 RelatedI 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 RelatedTwo 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.
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
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
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
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.
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