I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
View RepliesHave searched but could not find my solution. I have a bowling league database and I am doing averages based on games bowled. On certain averages the results are incorrect. Such as
Tot pins = 1169 divided by
tot games = 6
the result should be 194.83
but the result in my query is 196
have tried the Round function, Abs function and cLng function to no avail.
Thank you
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 have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)
I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day.
my Access level - on the lower end of medium experienced.
SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedI'm working on a database to track our program's performance under different grants. Services performed for each grant would be entered on a quarterly basis. I am tracking the number completed for each service during the quarterly period.
I want to create a report that shows progress against benchmarks depending on the most recent quarter completed. Rather than fooling with dates, I put a field in the table where the data is entered for the number of the quarter in which the service was performed. The field, 'Quarter', holds numerical values 1-4 corresponding with the quarter. The table holds entries from different projects, distinguished by the field 'GrantID'.
There will be more than one service entered for each GrantID.
I would like to be able to identify the most recent quarter in which services were performed for each GrantID.
I have tried using the MAX function in a query but it seems to be adding "1" to the highest quarter number in the field for each GrantID. The code is:
SELECT DevEntryQ.GrantID, Max(DevEntryQ.Quarter) AS MaxOfQuarter
FROM DevEntryQ
GROUP BY DevEntryQ.GrantID;
With this query, if the highest quarter number entered in the table for GrantID1 is 3, the query returns "4".
In Excel, I would use an array formula: = {MAX(IF(Table1[GrantID]=GrantID, Table1[Quarter])}.
I want to use the most recent quarter to retrieve that quarter's benchmarks and show the progress toward the benchmark rather than the overall progress toward the goal for that grant and service.
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Using Access 2013
I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause.
where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");
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 a table that has 4 fields; id_vl, id_product, date_vl, vl.
Basically each product is valued either on a monthly, weekly or daily basis. I want to create a filter that is based on a text box that filters the dates from which one can look at the dates of valuation and the associated values.
The problem comes with the products valued on a weekly or monthly basis. For example, if I simply say [tblvl].[date_vl]>=Me.textbox, and me.textbox = 13/09/2013, yet the last value date for a weekly product was 09/09/2013, this record won't be included. Therefore I need to set the date of the previous record as the filter. This is my attempt but it doesnt work...
Code:
SELECT tblVL.Id_VL, tblVL.Id_Product, tblVL.Date_VL, tblVL.VL,
FROM tblVL
WHERE iif(tblvl.date_vl<>me.fees_start_date, [tblVL].Date_VL>=(SELECT
MAX(PrevDate.Date_VL)
FROM tblVL AS PrevDate WHERE PrevDate.Date_VL<[tblVL].[Date_VL] AND PrevDate.Id_Product=tblVL.Id_Product;),tblvl.date_vl>=Me.fees_start_date)
;
I am using the IIf function as follows
Exp1:IIf(field A = "a string value",1,0) this returns the correct value.
However when I try to add a second condition using the IIf function, or use the And Or condition, I do not get the correct value.
I am trying to get a value of 1 in the Exp:Col when field B has a value that is different from field A.
What is the correct syntax? What am I missing??
I have a sql to run and I have to use many values as parameters (over 1000), which is why I am trying to use a SQL inside a IN condition.
I am testing the sql with only 1 value for now. The parameter I am using will be 2208287. This returns in a matter of seconds.
Code:
Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime
From(
SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12) AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id AS Ship_distro, Sum(bc.unit_qty) AS Unit,
[Code] .....
but it does not work when I add a sql inside the IN condition. It actually runs forever and eventually I have to break it to stop. why this is not working with a sql inside a IN condition.
Also note:
table ROSS_REPORT_DISTRO_DTLS_SUM is a linked table
table BOL_CONTNR_SKU16_RTED is a linked table
table CopyOfdata is a local table (This is where I will put all of my values to act as parameters)
Code:
Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime
From(
SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12)
AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id
[Code] ....
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 want to find unmatched records, where there is a condition. Specifically, which employees did not get a specific mail.
My employees are in this table:
tblEmployees
---------------
EmployeeID (PK)
FullName (text)
and sent mails are logged in tblMailsSent
tblMailsSent
--------
MailID (FK)
EmployeeID (FK)
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
I want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
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")))
I want to list all the records which contains data as per my filter form field company.
I have tried this
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")
problem is this i want to display all the records including null, but this code only show all records except null
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")
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.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I am trying to combine the result of two specific (and different) queries on a set of loans. As a result I have to sets of query results:
+ result query 1: with first field LoanID and several other fields
+ result query 2: with first field LoanID and several other fields
Now I want to create a list of the combination of all LoanID's, without duplicates.
How do I do that? I read about UNION but that appears to work only on tables.
Hi,
I have created the following function in my datebase so that i could specify the date a query works from.
Option Compare Database
Function GetParmValue() As Date
GetParmValue = InputBox("Please enter the date you wish to update!?")
End Function
I have a button on a form that runs the query in question, there is also an identical query that needs to be ran using the same result from the input box but rather than it display the input box twice i wondered if there was a way i could use the result from one of the boxes with both queries!?
Here is the code for the button;
Private Sub bImport5_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Update"
DoCmd.OpenQuery "UpdateFuture"
DoCmd.OpenQuery "MoveFuture"
DoCmd.SetWarnings True
MsgBox "Files have now been updated to the specified date and moved to the Future Dated table!", vbOKOnly, "Update Complete"
End Sub
Any ideas guys?
Many thanks
Tim
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
I'm trying to create a query which returns me the next higher number..My table: tblPersons
Code:
ID, Name, balance,
10 John 1000
11 Alice 2000
12 Bob 3000
My query:
Code:
SELECT TOP 1 name FROM tblPersons WHERE( (balance)>([InputBalance])) ORDER BY balance ASC
Input:
[InputBalance] = 500
Result: John
[InputBalance] = 1234
Result: Alice
[InputBalance] = 9999
Result: EMPTY
Result should be Bob
it's possible to combine this logic into one query?