Format Query If Condition Is Met
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 Replies
ADVERTISEMENT
Aug 9, 2015
condition formatting a date field in access.The query used for this field produces a date or "NA" based on the formula below. so when the date shows up as "NA" then i have set the condition formatting of the cell to grey and this works works well. but when it shows up with a date it doesnt format to a grey.the formatting pane has these two expressions
1. Q_Induction_Date = "NA" then grey the cell
2. Q_Induction_Date < DATE() then grey the cell and this DOESNT work. i have also tried the function Now() and that doesnt work either.
Formula i use in the query is
Q_Induction_Date: IIf([y].[INDUCTION_NA]=-1,"NA",IIf(IsNull([induction_date]),"NA",Format([induction_date],"dd/mm/yyyy")))
View 4 Replies
View Related
Mar 1, 2006
i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..
note..the bold bit is the bit i am questioning.
WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"
or something like this??
View 3 Replies
View Related
Jan 9, 2006
Hi there, I currently have the below tables:
tbl_stores
ID NAME
123 Store A
456 Store B
tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
Any ideas? Thanks for your help with this one!
Cheers
Tony
View 2 Replies
View Related
Feb 4, 2008
Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?
View 7 Replies
View Related
Jun 15, 2006
I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.
View 7 Replies
View Related
Mar 25, 2008
Hello,
I've faced a - for me - tricky problem when I've created a query from code. I have an MS SQL backend and in it there is a table which has a long time format field. I want to make an sql query where the condition is a time data. Unfortunately when I enter a time which is surely in the table, there is no result of the query. I've tried to solve it on several ways, time formatting, etc., but I had always the same result, to wit no result.
Does anyone idea, how could I solve it?
Any tips are appreciated very much!
Many thanks in advance!
VoiD:confused:
View 1 Replies
View Related
Feb 26, 2014
I am trying to copy notes from one table to another table where a condition has to be met and I can't figure out how to do that in an APPEND query.
Each record has a unique number that comes into my Initial Table in my Access database from a construction program I download. In this particular table both the [DNJTNo] and [DNVersionNmbr] fields can have duplicate numbers. However, when I run the query I want it to add a record to the second table only after the query checks that the [DNJTNo] in combination with the [DNVersionNmbr], is not already in the table. If it is, I do NOT want it appended to the second table. (I have attached a picture of the query in APPEND design stage).
View 2 Replies
View Related
Oct 24, 2013
Is it possible to set any label id as query parameter in sql code or access?
View 5 Replies
View Related
Aug 23, 2013
I am creating a search function to search a name. The DB stores it as [First Name] , [Last Name].... This query works
Code:
SELECT *
FROM tblVolunteers
WHERE ((([First Name] & " " & [Last Name]) Like "*bob j*"));
It will show me records for Bob Johnson, Bob James and Bob Jones....
I am trying to duplicate this with a DoCmd.OpenForm statement... Replacing the static name with a field variable...
Code:
Dim whereClause As String
whereClause = "[First Name]" & " " & "[Last Name] Like " '*" & volunteerName & "'*""
DoCmd.OpenForm "Volunteers", acNormal, , whereClause, , , False
View 4 Replies
View Related
Aug 19, 2014
I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote
Code:
Is Null
.
under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.
View 1 Replies
View Related
Mar 17, 2015
I have a query which working fine:
Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...
But now I would like to add next condition to this query
Code:
where [kolory p] is not null or [kolory t] is not null
- these fields are string
I don't know how to write this condition and add to my exiting strSQL string
View 4 Replies
View Related
Feb 24, 2015
I'm trying to have a single or multiple query criteria based on what the user checks on a form.
I can't get the True condition to work at all, I get no records. Here is what I'm using
IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])
If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.
View 14 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
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 3 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
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
