Format Within Query
Aug 21, 2006
Hi
I have a Query that calculates a value SubTotal using one of two values depending on the state of a check box called Euro.
SubTotal: IIf([Euro]=True,[Quantity]*([UnitPrice]*[ExchangeRate])-[Discount],[Quantity]*[UnitPrice]-[Discount])
This works fine. The value is calculated correctly.
I now need to include the currency sign be it either € or
£ in front of the value generated according to the state of Euro. If Euro is True then € if not True then £
Euro is set by clicking on one of two command buttons btnEuro or btnGBP.
ViRi
View Replies
ADVERTISEMENT
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
Aug 31, 2007
Hi , I have the query written and produces the correct results , however I would like to limit the results to two decimal places. It wont allow me to change this in field properties. The seond expression which is $ value does have the options in field properties to limit decimal places.
Margin%: IIf([Sum Of USD Value]<=0,"",([Sum Of USD Value]-[Sum Of WIF])/[Sum Of USD Value])
Margin: [Sum Of USD Value]-[Sum Of WIF ]
Any ideas anyone , as the report looks rubbish showing something 10 digits after the decimal !!:confused:
View 5 Replies
View Related
Sep 28, 2005
i have several forms on my DB that are based on a select query's with a criteria of 'date' I have tried to set the format for this as 'short date' to avoid having to put in the / every time (this works on a report) but despite saving this format when i close the query the info is lost and so the format doesn't work. is there a way of formating this directly in the cel ie '[Date] format dd/mm/yy'
View 1 Replies
View Related
Oct 4, 2005
Hello,
First I apologise if this is in the wrong forum, I couldn't decide and guessed someone may move it if required.
I am currently attempting to create a database for a wine cellar. This requires to be able to count in cases of 12. I want to express the stock as number of cases and number of bottles. For example 38 bottles would be 3 cases and 2 bottles (ie "3-2"). I am not hugely experienced with Access and would appreciate any help.
Many thanks
Wally
View 4 Replies
View Related
Jun 21, 2005
hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:
Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.
thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.
View 3 Replies
View Related
Aug 9, 2005
HOw to use the format function . I had tried to use it but does not work for me .
when i click properties of the particular column in a query and go to format tab , i type in mm:dd:yy( i want to change the format of date ) and then execute the query but nothing happens.
can someone help me ?
View 5 Replies
View Related
Jun 27, 2006
I've got a database of documents, some of which have pages where there is nothing but the character "*" on an occasional page.
I want to write a query that will process these documents, but ignore the pages that contain nothing but that pesky character, "*".
If I wrote a query like
SELECT Min([PageN]) AS OldPageN FROM [ED_Pg_Hist] WHERE NOT [PgDate] LIKE '(*)'
Would it do what I want? Or, should I write my query like this:
SELECT Min([PageN]) AS OldPageN FROM [ED_Pg_Hist] WHERE NOT [PgDate] LIKE '(%)'
If anyone has a comment, suggestion or idea, would you be sl kind as to pass it my way? Thanks in advance!!
View 1 Replies
View Related
Sep 6, 2006
I have the following query
SELECT * FROM Orders WHERE OrderDate Between #4-9-2006# And #4-10-2006#
I expect rows with OrderDate = 5-9-2006 to be returned, however they are not.
Apparently the date formats are interpretted as mm-dd-yyyy instead of dd-mm-yyyy.
Can I set the default date format or is there another solution?
View 2 Replies
View Related
Jan 23, 2007
In my BookingTable I have a StartDate (Format: "dd/mm/yyyy"). For every start date there is a CostOfBooking field also in the query. I want to show a list of all the current StartDates that occur in this month of this year and the cost of that booking. How can this be done?
View 2 Replies
View Related
Oct 29, 2007
Hi,
I have a query in which I am pulling from another table. On the table I have a field called Tag, which consists of data in the form of a combo date/place. Ex) 29OCT7NY date = 10/29/2007; place = NY
When I call on the Tag field in my query, I only want the date portion. So, I used the Left function... Left([Tag],6). But I also want to format the date portion so it is always in the form 10/29/2007 in my query. I tried Format(Left([Tag],6), mm/dd/yyyy) but it did not work. I think it has something to do with the weird form of the Tag field "ddmmmy".
If anyone could help, it would be much appreciated.
Thanks!!
View 2 Replies
View Related
Jan 26, 2005
I am trying to "dummy-proof" an access 2000 DB with a parameter query that asks for the date. I want the parameter to only accept the MED date format (IE - MM/DD/YY) just like the input mask in the Tables design (99->L<LL-00).
Is there any way to do this?
View 1 Replies
View Related
Mar 16, 2005
how do I format the column returned in a query. Like the average ..I want to format the values returned upto 1 decimal place...
Code: "SELECT AVG(PMRating.H1) AS DIV_AVG, Employee.Division FROM Employee LEFT JOIN PMRating ON Employee.TokenID = PMRating.TokenID GROUP BY Employee.Division"
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
Aug 24, 2007
I have a calculated field in my query that takes the difference between to incomes. I want to format this field to currency. How can this be done?
View 1 Replies
View Related
Aug 8, 2005
I have some code that creates a dynamic "Append To" query. The problem is that when the code is run it creates the query but generates a Data Type Mismatch error. I have tracked this down to a [clientnumber] field which is formatted as text in both the main database and the one the Append to query is updating.
The client number appears as a criteria and this works for finding the client's record but it will not append it unless I enclose it in quotes. When it's in quotes it works fine.
Can anybody give me a clue as to how I can include, in the code, a formatting command that makes the information inserted into the criteria field appear enclosed in quotes. Or-
Perhaps explain why the problem exists when both fields are formatted the same in both the main table and the Appended To table.
thanks
View 5 Replies
View Related
Oct 17, 2005
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
View 3 Replies
View Related
Nov 13, 2005
I am getting the following from a query
date|id1_|id2___|
1___|nick|james|
2___|john|nick_|
How can i convert this data this
id_____|date1|date2|
nick___|_1___|_2___|
john___|_1___|_____|
james_|_____|_2___|
Best Regards
View 4 Replies
View Related
Jan 11, 2006
I need to query a table for phone numbers that do not meet the following format: ###-###-####
Any idea on how I can do that??
Hope someone can help!
View 1 Replies
View Related
Feb 6, 2006
All,
I have a Postcode field in my table and I want to be able to check the data to make sure that it is a correct UK postcode.
Is there away that I can find out the format of the data, to be able to run a query against it, or is there a better way of doing it?
I need to account for all types of UK postcodes (A1 2BC, A12 3BC, AB12 3CD, WC2A 3BC). There are also foreign postcodes in this field.
Help appreciated!
View 2 Replies
View Related
Mar 8, 2006
I have set the Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT] field property to Short Date, 99/99/0000;0;_ . When I run the query I get that field formated as a short date, but when I try to create a table from the same query the data returned is no longer in the date format. Is there something I can do to have the make table results look like what I get when I just run the query without creating a table?
SELECT
DSERDBA_RECEIPTS.RCTH_ID_CASE AS [CASE],
DSERDBA_RECEIPTS.RCTH_DT_RECEIPT AS [DT RCTH],
Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT], DSERDBA_DISBURSEMENTS.DSB_DT_DISBURSE INTO [TABLE 1]
FROM [UNIVERSE OF CASES] INNER JOIN (DSERDBA_RECEIPTS INNER JOIN DSERDBA_DISBURSEMENTS ON DSERDBA_RECEIPTS.RCTH_RECEIPT_NO = DSERDBA_DISBURSEMENTS.DSB_RECEIPT_NO) ON [UNIVERSE OF CASES].STAT_CASE_ID = DSERDBA_RECEIPTS.RCTH_ID_CASE
WHERE (((DSERDBA_RECEIPTS.RCTH_ID_CASE)="20028570P"));
View 7 Replies
View Related
Mar 30, 2006
I am a very new access 2003 user and would welcome advice on how to cause the parameter text within [xxxxx...etc]to occupy two lines ( i.e force a carriage return and line feed) and thereby reduce the width of the parameter input box that the user sees. Also are there limits to the number of characters that can be used. I seem to remember a figure of 50 but cannot find it again in any of the books I have.
Many Thanks
Gerry
View 3 Replies
View Related
Oct 10, 2006
I am trying to create a crosstab query that counts the number of events that have been logged for each day of the month. The event date is stored in an ODBC compliant database in date/time format. Unfortunately, the crosstab query counts the number of events for each date/time occurrence.
I have tried entering the following expression in the Field: definition with no success (no records are returned when the query finishes executing):
Expr1:Format([OPEN_TIME]),"mm/dd/yyyy")
I am stumped! Any help would be appreciated.
Thanks,
John
View 3 Replies
View Related
Mar 12, 2007
I would like to delete records using a sql statement and comparing dates.
The dates will be current date and creation date.
Creation date will be imported into the table from another database. Obviously the current date will be rendered by the system.
I would like to delete all records that have a created date less than 30 days old.
Any thoughts.
View 8 Replies
View Related
Apr 5, 2007
Hello,
I have a text file I'm querying that stores a field as a text.
ComponentID:0000000242
I'm inserting this data into a table that stores this value as a number. Thus truncating the leading zeros.
ComponentID:242
I would like to create a query on the table using an InnerJoin on the text file column:ComponentID:0000000242 and the table column:ComponentID:242 as the unique identifier. Doing this join gives a case error because of the different data types.
Can format ComponentID using a query so it reads from my table as 0000000242 and do an InnerJoin to the text file. In vb
String= Format(242, "0000000000")
But I'm finding it difficult to get the query to format this correctly.
Unfortunately, i'm unable to change the column data type in the table structure and am looking for a work around. Is this even possible? Any ideas?
Thanks.
View 1 Replies
View Related
