Queries :: Conditional Change To Date Query?
Feb 13, 2014
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?
View Replies
ADVERTISEMENT
Sep 9, 2014
In my query I have a date field and it is half UK british and half US. I have my regional date set to UK on my laptop.
how to change it to same date? I don't mind either, I just want it to be consistent.
View 14 Replies
View Related
Feb 4, 2014
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.
View 4 Replies
View Related
Aug 5, 2013
I am struggling to change the format of 2013-07-29 11:33:03.6160000 to show dd/mm/yyyy
View 4 Replies
View Related
Dec 23, 2014
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")...
View 2 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
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...
View 7 Replies
View Related
Mar 26, 2013
I have a simple crosstab query:
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.
Can I do this?
View 3 Replies
View Related
Oct 18, 2013
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.
View 4 Replies
View Related
Aug 1, 2013
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.
Access 2007.
View 3 Replies
View Related
Apr 19, 2007
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.
View 6 Replies
View Related
Jan 8, 2014
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.
View 7 Replies
View Related
Aug 19, 2015
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"
Neither of which work ....
View 13 Replies
View Related
Feb 7, 2008
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
thanks in advance
View 1 Replies
View Related
Mar 5, 2014
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.
View 1 Replies
View Related
Apr 5, 2013
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?
View 8 Replies
View Related
Apr 17, 2015
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.
View 3 Replies
View Related
Sep 23, 2013
I'm having a format problem. I have a query (Q1) that, among other things format a date field as YYYYMM (Field1).
I have a second query (Q2) whose data source is Q1.
In Q2 I need to link Q1 to a table but Field1 is being reformatted as text (confirmed by running a make table query).
I want Field1 to be Number format to eliminate a mismatch error.
I know how to change the format of a field in a table using VBA but I cant seem to find a way of changing the format within a query.
View 2 Replies
View Related
Feb 10, 2014
In a table of a SQL-Server 2008 I have a fieldname "Alter", the German word for age.
When sending a PT-qry to the server I get an error "Incorrect syntax near the keyword 'Alter'"
Even when I try to set "Select tblmytable.Alter as Age" I get the same error.
To change the fieldname is almost impossible, as there is to much code to change.
View 3 Replies
View Related
Feb 3, 2014
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?
View 1 Replies
View Related
Oct 29, 2013
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.
View 3 Replies
View Related
Jul 16, 2014
Need sorted Query to be used in a Listbox. The attachment shows a made-up view of the objective.
The first column shows the change in value, then blanks for the first column's next rows, until there is a change of value again.
I do something like this in Excel where the first change in value is bolded. This is to make a list box more readable.
View 13 Replies
View Related
Jan 21, 2015
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.
View 3 Replies
View Related
Jun 5, 2013
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.
DoCmd.Close acQuery, "LinkTableManager_Frmqry", acSaveNo
how to stop the message appearing when the form is closed.
View 5 Replies
View Related
Nov 8, 2013
Is there a way of using a text field value as the heading for query column?
ie change...
Column header: FieldInATable
to
[form1].[field2]: FieldInATable?
View 1 Replies
View Related
Aug 13, 2013
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?
View 4 Replies
View Related
Aug 14, 2015
Is it possible to run a query and have the background color of the query field be highlighted if a certain criteria is met?
I'd like to use the expresion builder if possible.
For example if the values in field A and field B do not match, then field C should be a yellow background.
If this is possible, how is it done?
View 1 Replies
View Related
