i need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments' section. Therefore Cdate alone won't work.
So far i have tried:
Code:
IIf(Len([TabDL].[PROMISE]<>10),#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1
The intention being to insert a holding date 01/01/1900 if the field contains too many characters to be a date alone. I've tried multiple variations of this code but keep getting errors across the board. Is my logic or Syntax flawed?
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
I'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
user_change primary key = user_change_id (AutoNumber) user_id (Number) which relates to the user_id from "users" table change_type (Text) action_date (Date/Time) ...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
FirstName and Surname as Row Headings. Date as Column Heading. OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
I have a subform that is purely to display information (no edits will be made on it). The subform has a special color on it (company color) that I can not deviate from. My problem is the first record in the subform (displayed as a continuous form) gets the focus. The font is white and standard access has a white background. My end goal would be to elimnate the focus for any record in the subform (if it is possible). I know that I could change the color with conditional formatting but the colors in conditional formatting do not match the company color.
I have a form in datasheet with the fields "Date In" and "Date out" among other.
I want to be able to Change the backcolour of the "Date In" to red, yellow, or green depending on the length of time elapsed between current date and date in. This works fine on the conditional formatting, but now I only want to apply this formatting IF the "Date Out" is empty.
G'day, I'm trying to replace a date on a table using an update query. I have the criteria set to [enter date] but it won't put it in the update to block. I know this has to be one of those easy one but after a search and using help I can't nail it down. Much appriciated, Tim
Guess I should add that I'm going to use a command button on a form to run the query and I want it to prompt me for the date.
I have a subform with two fields [status] and [process], i am trying to get the backcolor of procces to change to red if the status field contains the text "expired".
I have tried vba. plced on the after update
if me.status = "expired" then me.process.backcolor = vbred else me.process backcolor = vbwhite end if this
Changes the color of the field [process] but also all process field colors to red even if the status field does not contain "expired".
I think conditional formatting is the way to go but i think i need an expression to complete this.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have created a table called - "Test" The properties of the table is listed below
Table Name: Test Field Name: ADMDAT2 (Text) DISDAT2 (Text) Operation Date (Date/Time)
I have written a query to populate a field where the Operation Date is between the ADMDAT2 and DISDAT2
Expr1: IIf([ADMDAT2] Is Null,"",IIf([Operation Date]>=[ADMDAT2] And [Operation Date]<=[DISDAT2],"Match"))
Unfornately it returns and ERROR message... I believe this may be because, the data type of the field, matching a Text with a Date/Time, I have rerun the query using a sample table where all the fields are Date/Time, and it work perfectly.
What i need help with how do i convert a text field into a Date/Time in a query?
So i can place that in the query before i populate the Test table. therefore it all should be date/time
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I use expression on the query field, but after I run it, the field change its format from number to text, how to reformat on the query criteria to change the text to integer format?
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
I have inherited an Access 2003 database and have been asked to improve the functionality of said database. Specifically, I have been asked to create some charts for improved reporting. I used the wizard to create a crosstab query to support the chart(s):
Code:
PARAMETERS [Forms]![Switchboard]![ActualDate] DateTime; TRANSFORM Sum(qryPMCompletion.CountOfWOs) AS SumOfCountOfWOs SELECT qryPMCompletion.PA_Priority FROM qryPMCompletion GROUP BY qryPMCompletion.PA_Priority PIVOT qryPMCompletion.wo_Completed WITH OWNERACCESS OPTION;
Now what has happened is that the wo_Completed is a Yes/No field so my column headings and therefore my legend comes up as "-1" and "0" instead of "Closed" and "Open".
Is there anyway to change the name of the column headings or shall I just create my own legend?
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice 01/01/2015 101.45 02/01/2015 104.70 03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
I have a form with a child that holds a query and for 99.9% of the time remains locked as it's for viewing data only. However, if the user wishes to make a change then they need to press an unlock button first then make the changes.
The problem I have is when closing the form; if this child's query has been unlocked I'm getting a message asking do I wish to save the changes to the layout of the child's query. The answer will always be "No".
I've added the following line of code to all sorts of procedures with no success.
I know that i can edit contents in a single table query and it will update the data in the underlying table, but how do i gain the ability to change data in a query when 3 tables are involved?
