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 Replies
ADVERTISEMENT
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
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
Nov 13, 2007
Hey.
How do I format Date field in query grid to make field into "11/11/2007" if the data originally has time, or in form "11 Nov, 2007" ?
View 2 Replies
View Related
Nov 17, 2004
I have a field in my DB called "Category". Users are allowed to imput any text in that field. I have a particular problem in which the LIKE command isn't working as I think it should with the limited help files of Access explanation of use. The category field has multiple entries, for example - HEQ, PBH5, PBH4, SWA, SWA2, ALL - etc. I'm trying to pull the records from that field with specifics.... in other words I may need to get All the records that have "PBH5" in them. However, if if PBH5 isn't the FIRST entry in that field the LIKE criteria doesn't work. I have tried Like "PBH5*" LIke "PBH4* etc to no avail. Any ideas? It can't be that difficult, but I cannot find anything in any book I've purchased on it, or any of the knowledge base information at Microsoft.com.
View 2 Replies
View Related
Sep 17, 2013
I have a table that gets updated by a query(For Invoices).When I query that Invoice table for the last record, I do not get the last one written. It stops at number 12. I am using the Totals option with Invoice number field and setting to "Last".is there another way I can get the last record to query correctly. I am trying to populate an invoice report.
View 6 Replies
View Related
Jan 14, 2008
Hi all,
I'm trying to get a public function to work within the design view of a query. The function is defined as:
Public Function Percentile_(fldName As String, _
tblName As String, p As Double, _
Optional strWHERE As String = "") _
As Double... ( I can put the whole code if u need it)
Any thoughts about how I can make the Percentile function calculate values according to a GroupBy statement and not for whole data set?
View 14 Replies
View Related
Sep 26, 2007
I have a table with data that is similar to this:
HCP1 DATE (Fields)
-.1 7/19/2007
-.2 6/14/2007
0 9/9/2007
1.2 4/23/2006
-.3 4/4/2007
2.4 3/7/2007
0 2/5/2006
1.1 2/6/2007
Etc. There are 0's, negative numbers and postive numbers. Some with at least one decimal point. I am trying to sort the data by the highest number in HCP1 field. When I run the query in Access it looks great. But when run through ASP on the server it is only giving me the negative numbers, no 0's no positve numbers. It would return -.1 in this sample data as the first record.
Anyone know what might be going wrong on the server side of things. The query statement looks the same on both sides. (server and access)
Thanks for any help.
View 5 Replies
View Related
Oct 11, 2004
When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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 11 Replies
View Related
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
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
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
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
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
