Hi,
I have problem in creating conditional expression for query table. I want to add IF Else statement in the statement so that if the value is more than 50, a new field called grade will display the grade of "A" in the new field.
Can anyone teach me how to write conditional expression.
Thanks
Dawn
Hello,
I have a date field in my form that I would like to have the date change color sixty days prior to the date entered. Can someone give me the expression to use in conditional formatting? For example: If the date is 9/30/05, I would like it to change color on 8/2/05.
Thanks
Hi, can anyone help me? I'm using an access database to collect information and pull reports. One of the fields in one of the tables contains dates and time. In my query I would like to be able to state that records created after 17:00 and before 08:00 of the next day, get counted in next day reports. (i.e. Record 1 created 26.05.05 at 18:01 would show in my report as coming in on 27.05.05 at 08:00 am). Thanks,
View 5 Replies View RelatedHello, I need help writing a formula in a query
I have a database where I need to be able to pull different prices according to type.
For example, if it is a SH then I will need it to pull a certain price of an item under the SH level
if it is a DD, then I will need to pull a price of the item under the DD level
How can I write this formula in my query as an expression or do I need to write it as SQL dlookup.
If it is the dlookup, how do I write that.
Help!!!
I having been going mad for a week trying to write a query, please help me.
I am trying to get the MaxServicedate for each Product (PIDFK) for every Location (LID), but then I also need the MaxServicedate regardless of Product use, for each Location that is smaller than the previosly obtained MaxServicedate.
I hope that makes sense.
I have tried this query
----------------------------------------------
SELECT T.LID,
T.PIDFK,
T.MaxOfServicedate,
Max(tblTakings.Servicedate) AS NextMax,
T.Installdate
FROM
(SELECT tblLocations.LID,
tblTakingsProductMM.PIDFK,
Max(tblTakings.Servicedate) AS MaxOfServicedate,
tblLocations.Installdate
FROM (tblLocations INNER JOIN tblTakings ON tblLocations.LID = tblTakings.FKLID)
INNER JOIN tblTakingsProductMM ON tblTakings.TID = tblTakingsProductMM.TIDFK
GROUP BY tblLocations.LID, tblTakingsProductMM.PIDFK, tblLocations.Installdate) AS T
LEFT JOIN tblTakings ON T.LID = tblTakings.FKLID
WHERE (((tblTakings.Servicedate)<[T].[MaxOfServicedate]))
GROUP BY T.LID,
T.PIDFK,
T.MaxOfServicedate,
T.Installdate
ORDER BY T.LID,
T.PIDFK
-------------------------------------
But I am missing some records!!
The subquery gives me all 90 records with the MaxServicedate for each PIDFK in each LID
The main query is missing the records that dont have a service date previous to the MaxServicedate in the subquery!
Any help appreciated.
Thanks
I have a form (frmTraveler) in which a user is given a selection of levels for a type of trip from a drop down box (TripLevel). Basically, if a user picks level A, then I would like a value to be written to the corresponding record in another table (tblHotel). The PK is an autonumber (TravelID) and the tables have a one to many relationship. Hope that's enough information for some tips! :)
View 1 Replies View RelatedHi folks,
I have a query which queries another query. I need to re-write the whole thing in SQL, but struggling with incorporating the nested query.
here's the main query:
SELECT tblProductSubCategory.SubCatDesc AS Products, qrySummaryStage3.[Early Cash Sales], qrySummaryStage3.[Late Cash Sales], qrySummaryStage3.[Cash Variance], qrySummaryStage3.[Cash Variance %], qrySummaryStage3.[Early Credit Sales], qrySummaryStage3.[Late Credit Sales], qrySummaryStage3.[Credit Variance], qrySummaryStage3.[Credit Variance %], qrySummaryStage3.[Early Total], qrySummaryStage3.[Late Total], qrySummaryStage3.[Variance Total], qrySummaryStage3.[Total Variance %], tblBudgetNew.Amount AS [Late Budget Amount], [Late Total]-[Late Budget Amount] AS [Budget Variance], qrySummaryStage3.[Early Cash Margin %], qrySummaryStage3.[Late Cash Margin %], qrySummaryStage3.[Early Credit Margin %], qrySummaryStage3.[Late Credit Margin %]
FROM (qrySummaryStage3 INNER JOIN tblBudgetNew ON qrySummaryStage3.SubCatID = tblBudgetNew.SubCatID) INNER JOIN tblProductSubCategory ON qrySummaryStage3.SubCatID = tblProductSubCategory.SubCatID
WHERE (((Month([date]))=1) AND ((Year([date]))=2005) AND ((tblBudgetNew.Site)=[Enter Site ID]) AND ((tblBudgetNew.Type)="Monthly Breakdown"));
and here's the nested query , called "qrySummaryStage3":
SELECT tblProductSubCategory.SubCatID, tblReportTemp1.[Early Cash Sales], tblReportTemp1.[Late Cash Sales], [Late Cash Sales]-[Early Cash Sales] AS [Cash Variance], IIf(nz([early cash sales],0)=0 Or [Early Cash Sales]=0,0,[Cash Variance]/[Early Cash Sales]) AS [Cash Variance %], tblReportTemp1.[Early Credit Sales], tblReportTemp1.[Late Credit Sales], [Late Credit Sales]-[Early Credit Sales] AS [Credit Variance], IIf(nz([Early Credit Sales],0)=0 Or [early Credit Sales]=0,0,[Credit Variance]/[Early Credit Sales]) AS [Credit Variance %], [Early Cash Sales]+[Early Credit Sales] AS [Early Total], [Late Cash Sales]+[Late Credit Sales] AS [Late Total], [Late Total]-[Early Total] AS [Variance Total], IIf([Early Total]=0,0,[Variance Total]/[Early Total]) AS [Total Variance %], IIf([Early Cash Sales]=0,0,([Early Cash Sales]-[Early Cash Cost])/[Early Cash Sales]) AS [Early Cash Margin %], IIf([Late Cash Sales]=0,0,([Late Cash Sales]-[Late Cash Cost])/[Late Cash Sales]) AS [Late Cash Margin %], IIf([Early Credit Sales]=0,0,([Early Credit Sales]-[Early Credit Cost])/[Early Credit Sales]) AS [Early Credit Margin %], IIf([Late Credit Sales]=0,0,([Late Credit Sales]-[Late Credit Cost])/[Late Credit Sales]) AS [Late Credit Margin %]
FROM tblReportTemp1 RIGHT JOIN tblProductSubCategory ON tblReportTemp1.ProductSubCatId = tblProductSubCategory.SubCatID;
Thanks in advance!
I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.
Code:
Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
[Code]......
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
Here's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
View 13 Replies View Relatedall using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
View 4 Replies View Relatedsee below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
[Code].....
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.
Code:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _
" Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,
[Code] ....
While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this:
=IIf([GroupPos]=1,[Year])
Change the GroupTotal Control Source to this:
=IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code:
Dim ds1 As Recordset
Dim ds2 As Recordset
myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione
FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
[code]....
Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)
I have tried to acheive this using the code below:
[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--
IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--
when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?
Many Thanks
Ian
My Access is very rusty and I really can't get my head round how to do this!
I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.
This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.
I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?
Thanks
i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.
when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.
so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.
thanks
Hi,
I have a query that combines fields with an IIF statement adding a comma as the seperator "," is it possible to put a condition on it so that if a field had soley an integer or even a string with a value of 4 or less characters that the comma will be excluded, so for example:
A1 A2
1 Parlmont avenue
changes to:
1, Parlmont avenue
want it to change to:
1 Parlmont avenue
this is how the IIF statement looks like:
IIF(IsNull([I1].[A1]),[I1].[A2],[I1].[A1] & IIf(IsNull([A2]),"",", " & [A2])) AS Expr2
where I1 is table and A1,A2 are first and 2nd fields respectively, any suggesetions very much appreciated.
M-.
I have a query that brings up a job#, its start date (mm/dd/yyyy), and its
priority status (Low, Medium, or High).
Is there SQL I can use to make another query that will look at the start date,
and if it is a week old, change the priority status to Medium, and if it's a
month old, change the priority to High?
Thanks!!:)
HI
I am working on a database that holds communications from treatment episodes.
the sub form holds the communications related to that client and that treatment episode.
when you enter a date into the communication record a mesage box asks if this is a new treatment episode and if yes opens a new treatment record. If it is the same treatment episode it just remind you that you are in the same treatment episode.
what i would like to do as an additional cue to the user is that once a client has been discharged from the episode that all communication records change color.
I can get the field that says dc to change color but I want all the fields in all the records from that episode to change color.
any ideas?
thanks
Hi guys,
I have a form with an option group. It has 2 options. The first one has a value of 1, the second has a value of 2. This option group is named "category".
Separate from this I have a query with several fields. In one of these fields, I need to enter a criteria that enters "LOE*" if the option group value is 1, or if the value is 2, I need it to enter "IDC*" in the criteria. My form that the option group is on is named "SelectWell". This is what I have tried so far:
Like IIf([Forms]![SelectWell]![category]=1,"LOE *","IDC*")
This works correctly if the option group value is 2, but doesn't work for the 1 value (LOE*).
Thanks,
Chris
Hi, I have the following situation:
I have two tables: userdata and superusers and a function username() which can be used for acquiring the username of the active user.
Table userdate contains a column 'username'. With the following SQL query the active user can obtain the records from the userdata table belonging to himself with a query like:
SELECT * FROM userdata
WHERE userdata.username = username()
Now, what I want to do is that IF the username (obtained using the function call (username()) exists in table superusers ALL records have to be shown, just with:
SELECT * FROM userdata
I want to do everyting with only one query, so that if the query is executed either all records from userdata are shown (when username is in superusers table) or only the ones that belong to the users.
Is there something like a conditional statement to create a conditional WHERE statement, or maybe another solution to solve this problem?
Thanks in advance.
Happy new year all. I have 2 tablesI have a query that if any tbl2.num=tbl1.num AND tbl2.action = 'confirmed' this row should be selected for deletion from tbl1. I've tried linking the tables and using an iif statement, but no good and I'm not sure what else to do- any ideas?Forgot to add- tbl1.num might or might not exist on tbl2
View 2 Replies View RelatedI have a query set up to prompt users when their license will expire (30 days out). Problem is that there are 2 types of licenses and some have different dates. I would like to have a report where I could see all the people that are about to expire either it be one license or the other or both. Thank you please help.
View 1 Replies View RelatedDear all,
I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.
I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1
how can I write this expression
thanks