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.
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.
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"
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
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?
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.
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!
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).
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.
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?
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.
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.
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:
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'
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.
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 ?
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?
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?
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.
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?
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"
