Format Within An Expression
May 22, 2006
I have an update query which calculates the difference in hours and the difference in days(in seperate fields) between two date and time values. The formulas is below
DateDiff("d",[Admission Date],[D/C Date / Time]))
This works fine as long as there is a value in the [D/C Date / Time]feld. When there isn't a value in this field I have added the Iif function below:
IIf([D/C Date / Time]=Null,DateDiff("d",[Admission Date],[Enter date],DateDiff("d",[Admission Date],[D/C Date / Time]))
However I get a conversion failure error message because i believe the [Enter date] is not formated correctly. This is the date and time format that I am using 99/99/99" "00:00;0;_
Can anyone help me fix this formula so that it works.
Thanks
View Replies
ADVERTISEMENT
Jun 4, 2014
I have this expression I created and I want to know how to format it as currency. I know how to do it with a regular field but the option is not available for an expression.
The expression:
Code : Charge: IIf([Rate] Is Not Null,[Rate],"0.00")
View 1 Replies
View Related
Feb 8, 2006
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
36.7768595041322
38.6666666666667
15.6448202959831
etc
the expression i use is:
Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
View 7 Replies
View Related
Apr 4, 2014
I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.
New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))
The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.
View 2 Replies
View Related
Mar 22, 2014
Expr1 is a field in a report that is bound to this query.
timeline.CompanyName doesn't usually have any data in it, and when it does it needs to be removed and manually input into companies. Company Name.Is it possible to make timeline.CompanyName output as italics? Otherwise we dont know which field is populating.
Code:
SELECT [timeline.CompanyName] & [companies.CompanyName] & " (" & [companies.city] & ", " & [companies.state_id] & ") ~ " & [timeline.Title] AS Expr1, timeline.showitem, timeline.EntryId, timeline.EmployeeId, timeline.CompanyName, timeline.EmpStartDate, timeline.EmpEndDate, timeline.Super, timeline.CoPhone, timeline.Notes, timeline.Title, timeline.company_id, timeline.start_date, timeline.end_date, timeline.assignment, timeline.notes_staff, timeline.showitem
FROM timeline LEFT JOIN companies ON timeline.company_id = companies.company_id
WHERE (((timeline.showitem)="false"))
ORDER BY timeline.end_date DESC
WITH OWNERACCESS OPTION;
View 3 Replies
View Related
Jan 30, 2008
Hi,
I am trying to insert a text box value into a text field value in a table. I am checking to see if the value is already in the table but come across an error on the second click. The first click enters the number, and the second click gets error. I know where the error is but I'm not sure why or how to fix it.
Code:Dim db As DAO.DatabaseDim rs As DAO.RecordsetSet db = CurrentDb()Set rs = CurrentDb.OpenRecordset("CVHOLD", dbOpenDynaset)If Not rs.BOF Then rs.MoveFirstrs.FindFirst "[Batch Number] = " & Me![Batch Number]'Error here on second passIf (rs.EOF) Then 'Seems to insert new record properly 'No Batch Number in Table yet. rs.AddNew rs("Batch Number") = Me![Batch Number] rs("Date Closed") = Now() rs.Update rs.CloseElse 'never enters, jumps to Error label 'Batch already listed in table MsgBox ("Batch already in table. Updating Closed date")End IfErr_cmdHoldStatus_Click: MsgBox Err.Description
This is what the debugger outputs for my values
Code:Me![Batch Number] : 81697 : Object/Textbox rs("Batch Number") : "81697" : Object/Field rs.BOF : False : Boolean rs.EOF : False : Boolean
View 1 Replies
View Related
Oct 29, 2013
I have two tables in a query joined by a ID field. The problem is in the one table the ID fiels is a text format and in the other table its a number format so the query builder doesnt like that and gives me the Type mismatch in expression error.
There's no way around it though; i need it to be those formats in its respective tables for reasons i wont go into here for simplicity.
View 2 Replies
View Related
Jun 8, 2005
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
View 1 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Jul 30, 2013
I have a list of dates in the mm/dd/yyyy format and I am looking to get it into the fiscal format of yyyyww. I am able to do this with the datepart and format functions, but I need to make it so that the fiscal month begins in January but the first week starts if there are three or more days in the week. For instance if Jan 1st is a Friday then this stands as the first fiscal week, if it is a Saturday then it does not count as the first week.
datepart and format functions have the Use the first week in the year that has at least 4 days for the firstweekofyear option but I need it where it has at least three to make it work.
View 2 Replies
View Related
Jul 7, 2015
I want to format the text using format function. how i format the word apple to "apple" (With Quatation mark).
str = Format(Me.word, xxxx)
View 3 Replies
View Related
May 4, 2012
I am exporting data from access 2007 to Excel 2007 using VBA code. I have a whole number, which I want displayed as whole number. But after the export, the number is using the 1000's seperator in Excel. So for example if my original number in access table is 12000, it is showing up as 12,000 in the excel file.It has something to do with the NumberFormat property. I have tried doing this but doesn't work.
objSheet.Range("A1:A7").NumberFormat = "General"
View 2 Replies
View Related
Mar 2, 2012
We're using Access 2010 on Windows 7.We have a large database design where we send reports as e-mail attachments. We need to change the way the file is automatically created when sent. I goes to snapshot form every time we attach it to an e-mail. How can we get rid of the snapshot .snp file format? How can we change it to pdf or accbd?
View 3 Replies
View Related
Mar 15, 2006
Hi:
I'm using this expression...
=DateDiff("ww",#8/26/2005#,[cDate])
I want to add to it the ability to exclude five one week periods which are school vacations.
How can I do that?
Thanks,
xeb
View 1 Replies
View Related
Oct 22, 2006
PART NUMBER: IIf([MANUFACTOR SENT]=2,[GE PART #])OR IIf([MANUFACTOR SENT]=3,[PHILLIPS PART #])OR IIf([MANUFACTOR SENT]=1,[SYLVANIA PART #])
CAN ANYONE HELP ME FIX THIS EXPRESSION
View 1 Replies
View Related
Jun 11, 2007
I have got the following code as an expression for a report:
=Int(([Amount])/50000)*50000 & " - " & Int((([Amount])/50000)+1)*50000
How do I get it to pick figures greater than 50,000 and then figures greater than 100,000 plus?
View 3 Replies
View Related
Jun 25, 2007
I need help setting up an expression. I need to set the default value = date + 2. But this also needs to take into account working days. I am just having problems with the syntax for this, as i would normally do this in VBA and do not have much experience with Expression Builder. And yes it does have to be done in Expression Builder.
I know this is horribly wrong... thats why I am asking for help.
IIf ((WeekDay(DateAdd ("d", 2, Date)) = 1, (DateAdd("d", 3, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) = 7, (DateAdd("d", 4, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) < 7 AND IIf (WeekDay(DateAdd ("d", 2, Date)) > 1, (DateAdd ("d", 2, Date),(DateAdd("d", 2, Date)))
I need 3 If statements:
1 for if its Saterday
1 for if its Sunday
1 for if its neither.
In VBA I would do something like this:
If WeekDay(DateAdd ("d", 2. Date)) = 1 Then Box = Date() + 3
If WeekDay(DateAdd ("d", 2. Date)) = 2 Then Box = Date() + 4
If WeekDay(DateAdd ("d", 2. Date)) > 1 And WeekDay(DateAdd ("d", 2. Date)) < 7 Then Box = Date() + 2
Please any assistance would be appreciated
View 6 Replies
View Related
Feb 11, 2008
Hi All,
could i get some advise on an expression i need in a query?
i have 5 fields
SumMats,SumLabs,MatsDed,LabsDed,Ttreat
i need another field that will give a value on a condition. the condition is that if LabsDed isNull, MatsDed will be divided by Ttreat otherwise LabsDed will be divided instead. can this be done? if so, how could i do it?
many thanks,
Nigel
View 1 Replies
View Related
Jun 14, 2006
I need an expression for a table. I'm making a game database and for the ID input I need an expression.
For example if have a XBOX game I want the ID to be X1 or X2 etc, same goes for pc games, GameCube games: PC1 or PC2, NGC1 or NGC92 etc.
I thougt it would be "X???" Or "PC???", but that doesn't work.
Does someone knows?
Please help.
View 4 Replies
View Related
Jan 9, 2007
Okay
Maybe i am totally confused but here is initially what i have and what i want to do.
I currently have a db for carrer development-- i have three fields
Last Review Date
Next Discussion Due
Status
My next discussion due is on the fly-- i did a dateadd (by one year) from the last review date to get the current result for the next disussion due--
here is what i want to do with the status and am confused with how to do it--
i want the status to have three options-- completed,overdue and upcoming--
i want these to be autogenerated but am unable to find what expression i need-- as the IIf expression only gives the option of two possible results--
so how would i go about doing the expression with having three possible outcomes?
View 6 Replies
View Related
Nov 10, 2005
I am trying to execute a query that calculates a retail pricing off the whsl pricing. This is the excel calculation i use: =IF(F2<10,(SUM(F2*4)),(SUM(F2*2))). I changed the IF to IIF, and replaced "F2" with "[WHSL PRICE].
However, I am getting an error that the "category" expression is not included in the function (when I have all the fields in the query) or the function if I have just the field I am trying to calculate in the query. Can somone help me understand what I am doing wrong.
I would also like this calculation to save in the table, if possible. As I use this information to update my fields in my web database.
Best Regards,
Irish
View 11 Replies
View Related
Mar 1, 2006
Ok, I have a query that has a range of number for 1 - 126, I need and an expression that will flag sub-ranges in the list of numbers.
Example I want to group or flag results 0-25 as one group, 26-50 as another, 50 - 75 and 76 -100.
Any ideas? I statred with an IF than expression, but I can't figure how to indicate each range.
thanks
-pete
View 1 Replies
View Related
Mar 3, 2006
;) I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
Any suggestions?
View 2 Replies
View Related
Jun 1, 2006
I having some trouble trying to use an expression in my query,
I have 3 indivisual exoressions in 3 fields I which work perfectly, however I want only 1 field produced so I tried placing 1 after the other as below:
Sector: IIf([SpacePos]=3,Left([PostalCode],[SpacePos]) & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=4,Left([PostalCode],[SpacePos])+"" & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=5,Left([PostalCode],[SpacePos]-1) & Mid([PostalCode],[SpacePos]+1,1))
I have also tried Or inbetween them but no luck either, I get the "contains invalid syntax" errors etc.
Any advise appricated
View 2 Replies
View Related
Jun 23, 2006
How can i write an expression in a query field that will return the following text; "A","B","C","P" and "F" Base on another field when the values of that field are <=44.44, between 44.45 and 54.44, Between 54.45 and 64.44, Between 64.45 and 74.44 and above 74.45 respectively?
View 2 Replies
View Related
Jul 20, 2006
How do I fix this to specify each table
Description: [level] & " " & [room] & " " & IIf(IsNull([direction]),[location],[direction] & " " & [location] & " " & [fixture] & " " & [note])
Description: [level] [level table] & " " & [room] [room table] DOESNT WORK
HELP
View 1 Replies
View Related
