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

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

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

Formating Date Field In Query Grid

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

Query Criteria Not Working Correctly

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

Reports :: Get Last Record To Query Correctly

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

How Do I Correctly Reference A VBA Public Function In A Query?

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

Negative Numbers In Query Are Not Sortin Correctly.

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

Customer ID Doesn't Show Up Correctly In A Query Or Table

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

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

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 11 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

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







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